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
-------------------------------------------------------------------------