Hi,
I have to tune following sql which has cost 1830. I am running oracle 9i and table and indexes are analyzed.

[B]SELECT b.s_name, D.login, d.f_name, d.l_name, e.desc
FROM CT_E_Stk_MAP a, ct_stk b, e_info c, user d, e_code e 
WHERE a.ct_stk_id = b.ct_stk_id 
  AND a.user = c.user
  AND a.user= d.id 
  AND c.e_c3 = e.c_value 
  AND e.name = 'EO06Y' 
ORDER BY stk_name[/B]

[U]Table has following index[/U]
[B]ct_stk_id ==> is Pk (unique Index) for ct_stk b table and fk for CT_E_Stk_MAP a table 
user ==> is a PK (unique Index) for e_info c table and fk for CT_E_Stk_MAP a table [/B]
I have also created index for e.name and b.stk_name but it not effective so dropped.

[U]Table has following total records[/U]
[B]USER ==>	     573421	
CT_E_Stk_MAP ==>	121	
ct_stk ==>	       3110	
E_INFO==>             75945
E_CODE==>  	       7141[/B]

[U]After Index for both the table, following is from Auto Trace[/U]	
recursive calls	0
db block gets	0
consistent gets	11254
physical reads	9985


Please let me know if you need more onfo.

thanks,

SELECT b.s_name, D.login, d.f_name, d.l_name, e.desc
FROM ct_stk b,  user d, e_code e 
WHERE e.name = 'EO06Y'
  AND  EXISTS (select '1' from  CT_E_Stk_MAP a ,  e_info c
                          where a.ct_stk_id = b.ct_stk_id 
                           AND  a.user = c.user
                           AND  a.user = d.id 
                           AND  c.e_c3 = e.c_value
              )
ORDER BY stk_name

Thanks. We have Oracle 9i R2 with latest patch applied on sun solaris.

[U][B]Explain plan[/B][/U]
[B][U]Operation[/U] [U]Object Name [/U][U]Rows[/U] [U]Bytes[/U] [U]Cost[/U][/B]
SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 1831
SORT ORDER BY 2 K 346 K 1831
HASH JOIN 2 K 346 K 1756
TABLE ACCESS FULL E_CODE 420 11 K 10
HASH JOIN 3 K 314 K 1745
TABLE ACCESS FULL ct_stk 127 4 K 2
HASH JOIN 3 K 213 K 1742
HASH JOIN 3 K 167 K 1302
TABLE ACCESS FULL CT_E_Stk_MAP 3 K 57 K 5
TABLE ACCESS FULL USER 573 K 19 M 1264
TABLE ACCESS FULL E_INFO 76 K 1 M 432

Thanks, 

Thank you sir. I hope note pad will work the best.

Operation				Object Name	Rows	Bytes	Cost

SELECT STATEMENT Optimizer Mode=CHOOSE			  2 K	 	1831
  SORT ORDER BY						  2 K	346 K	1831
    HASH JOIN						  2 K	346 K	1756
      TABLE ACCESS FULL			E_CODE		420	 11 K	10
      HASH JOIN						  3 K	314 K	1745
        TABLE ACCESS FULL		ct_stk		127	  4 K	2
        HASH JOIN					  3 K	213 K	1742
          HASH JOIN					  3 K	167 K	1302
            TABLE ACCESS FULL		CT_E_Stk_MAP 	  3 K	 57 K	5
            TABLE ACCESS FULL		USER		573 K	 19 M	1264
          TABLE ACCESS FULL		E_INFO		 76 K	  1 M	432

SELECT b.s_name, D.login, d.f_name, d.l_name, e.desc
FROM CT_E_Stk_MAP a, ct_stk b, e_info c, user d, e_code e 
WHERE a.ct_stk_id = b.ct_stk_id 
  AND a.user = c.user
  AND a.user= d.id 
  AND c.e_c3 = e.c_value 
  AND e.name = 'EO06Y' 
ORDER BY stk_name