OK, this is my condition and questions.. Assume that this is the only SQL we are using for tables/indexes

SELECT B.DIST_NM
, COUNT(+)
  FROM CUST A, DIST B
 WHERE A.COUNTRY_CD = 'USA'
   AND A.CUST_CD = B.CUST_CD
   AND A.CUST_ID IN (SELECT CUST_ID FROM ORDS WHERE ORDER_CD = '20')
   AND A.CUST_ID IN (SELECT CUST_ID FROM CUST_INFO WHERE INFO = 'N')
 GROUP BY B.DIST_NM

 ........................................

SELECT COUNT(*) FROM CUST WHERE A.COUNTRY_CD = 'USA' -> about half million rows
SELECT CUST_ID FROM ORDS WHERE ORDER_CD = '20' -> about 1.5 million rows
SELECT CUST_ID FROM CUST_INFO WHERE INFO = 'N' -> about 50 rows

1. what are the most effective of creating index for each table ?

   -CUST : COUNTRY_CD + CUST_ID

   -DIST : CUST_CD + DIST_NM

   -ORDS : CUST_ID + ORDER_CD

   -CUST_INFO : INFO + CUST_ID

---> I think above indexes are the most effective indexes. but is there anything else I should think about?

1. Accroding to the index above what is the execution plan going to look like? (join orders? ways? subquery roles?, can change sql if outcome is the same )

Can you explain this to me please... I am thinking since 1. SELECT CUST_ID FROM CUST_INFO WHERE INFO has the least rows it should be executed for the first time, 2. then join with CUST table, 3. then filter with SELECT CUST_ID FROM ORDS WHERE ORDER_CD = '20' subquery output, 4. then join with DIST table and then perform group by ...

Am I on the right track ?? Can anyone give me an advise??

I would write the query as:

SELECT d.DIST_NM, COUNT(*)
FROM CUST c JOIN
     DIST d
     ON c.CUST_CD = d.CUST_CD
WHERE c.COUNTRY_CD = 'USA' AND
      c.CUST_ID IN (SELECT o.CUST_ID FROM ORDS o WHERE o.ORDER_CD = '20')
      c.CUST_ID IN (SELECT ci.CUST_ID FROM CUST_INFO ci WHERE ci.INFO = 'N')
GROUP BY d.DIST_NM;

I would recommend indexes on: CUST(COUNTRY_CD, CUST_ID, CUST_CD), DIST(CUST_CD, DIST_NM), ORDS(ORDER_CD, CUST_ID) AND CUST_INFO(INFO, CUST_ID).

Note the order of the keys in the indexes.

more