Optimizer cost

[sonaliak]

I have 2 queries, How do I know which one is better from
the Explain Plan (in general not just this query)?
I have index on
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IX_AUTHATTR_WORK MWEBAUTHATTRIB
IX_AUTHATT_AUTH MWEBAUTHATTRIB
IX_AUTHATT_RES MWEBAUTHATTRIB
IX_RES_ENTITY MWEBRES
IX_WORK_ENTITY MWEBWORK

Why are they not used ?


SQL> Select distinct a.Res_ID
2 from mwebRes a, MwebAuthAttrib b, mwebwork c
3 where a.Res_Status <> 20 AND a.Res_Entity_Type = 3
4 AND a.Res_ID =b.AuthAttr_Res_ID
5 AND b.AuthAttr_Work_ID =c.Work_ID
6 AND c.Work_Entity_Type = 4 AND c.Work_Status <> 40 ;

73 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=192 Card=25 Bytes=34
398)

1 0 SORT (UNIQUE) (Cost=192 Card=25 Bytes=34398)
2 1 HASH JOIN (Cost=166 Card=294 Bytes=34398)
3 2 TABLE ACCESS (FULL) OF 'MWEBWORK' (Cost=58 Card=248 By
tes=9672)

4 2 HASH JOIN (Cost=85 Card=2113 Bytes=164814)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBRES' (Cost=35
Card=25 Bytes=1300)

6 5 INDEX (RANGE SCAN) OF 'IX_RES_ENTITY' (NON-UNIQUE)
(Cost=8 Card=25)

7 4 TABLE ACCESS (FULL) OF 'MWEBAUTHATTRIB' (Cost=44 Car
d=10143 Bytes=263718)

Statistics
----------------------------------------------------------
180 recursive calls
6 db block gets
886 consistent gets
286 physical reads
0 redo size
1553 bytes sent via SQL*Net to client
1348 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
73 rows processed

SQL> Select Res_ID
2 from mwebRes
3 where Res_Status <> 20 AND Res_Entity_Type = 3
4 AND Res_ID IN (Select distinct a.AuthAttr_Res_ID
5 From MwebAuthAttrib a, mwebwork b
6 where a.AuthAttr_Work_ID = b.Work_ID
7 and b.Work_Entity_Type = 4 AND b.Work_Status <> 40);

73 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=250 Card=25 Bytes=16
25)

1 0 HASH JOIN (Cost=250 Card=25 Bytes=1625)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBRES' (Cost=35 Card
=25 Bytes=1300)

3 2 INDEX (RANGE SCAN) OF 'IX_RES_ENTITY' (NON-UNIQUE) (Co
st=8 Card=25)

4 1 VIEW (Cost=214 Card=120 Bytes=1560)
5 4 SORT (UNIQUE) (Cost=214 Card=120 Bytes=91780)
6 5 HASH JOIN (Cost=144 Card=1412 Bytes=91780)
7 6 TABLE ACCESS (FULL) OF 'MWEBWORK' (Cost=58 Card=24
8 Bytes=9672)

8 6 TABLE ACCESS (FULL) OF 'MWEBAUTHATTRIB' (Cost=44 C
ard=10143 Bytes=263718)


Statistics
----------------------------------------------------------
0 recursive calls
6 db block gets
807 consistent gets
282 physical reads
0 redo size
1559 bytes sent via SQL*Net to client
1362 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
73 rows processed
-------------------------------------------------------------------------

[sonaliak]

Do I have to set optimizer_mode=cost
in init.ora file ? What mode is used if I don't set it ?
Which is better rule or cost ?

[dsuva]

if the optimizer is not set explicitly then the optimizer runs in the CHOOSE mode i.e. if the staistics are present in the V$ files the optimizer goes for cost based optimization else it goes for Rule. i feel if you are running an OLTP set up then you should go for COSE based optimization..

[tamilselvan]

Usage of Correlated Sub Query should be avoided in SQL as normally it takes more time to execute.
JOIN is always faster.
By looking into COST in the first line of the output, you can judge which one is better. Lowest cost is better one.