This:
Select Work_ID From Work
Where DECODE(inwork_level, 2, Work_Par2 ,3, Work_Par3 ,4,
Work_Par4,5,Work_Par5 ,6,Work_par6 ,7,Work_par7 ,
8, Work_par8, 9, Work_Par9) = inProjectID
is the probable "slow" piece as it would be forced in all cases to full scan work to find the value.
So, another option is "OR". Consider (assuming you have work_parN indexed for each N)
ops$tkyte@ORA9IR2> create table t ( work_id int,
2 work_par2 int,
3 work_par3 int,
4 work_par4 int,
5 work_par5 int,
6 work_par6 int,
7 work_par7 int,
8 work_par8 int,
9 work_par9 int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx2 on t(work_par2);
Index created.
ops$tkyte@ORA9IR2> create index t_idx3 on t(work_par3);
Index created.
ops$tkyte@ORA9IR2> create index t_idx4 on t(work_par4);
Index created.
ops$tkyte@ORA9IR2> create index t_idx5 on t(work_par5);
Index created.
ops$tkyte@ORA9IR2> create index t_idx6 on t(work_par6);
Index created.
ops$tkyte@ORA9IR2> create index t_idx7 on t(work_par7);
Index created.
ops$tkyte@ORA9IR2> create index t_idx8 on t(work_par8);
Index created.
ops$tkyte@ORA9IR2> create index t_idx9 on t(work_par9);
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2 ( authattr_id int, authattr_date int, authattr_work_id int );
Table created.
ops$tkyte@ORA9IR2> create index t2_idx on t2( authattr_work_id );
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable inwork_level number
ops$tkyte@ORA9IR2> variable inprojectid number
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> Select /*+ FIRST_ROWS */ Work_ID From t
2 Where DECODE(:inwork_level, 2, Work_Par2 ,3, Work_Par3 ,4,
3 Work_Par4,5,Work_Par5 ,6,Work_par6 ,7,Work_par7 ,
4 8, Work_par8, 9, Work_Par9) = :inProjectID
5 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=1000 Bytes=100000)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=11 Card=1000 Bytes=100000)
<b>that has to full scan -- nothing will change that really -- not even a function based index (since you use a bind in the function itself)</b>
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ work_id from t
2 where (work_par2 = decode(:inwork_level,2,:inProjectId,null))
3 or (work_par3 = decode(:inwork_level,3,:inProjectId,null))
4 or (work_par4 = decode(:inwork_level,4,:inProjectId,null))
5 or (work_par5 = decode(:inwork_level,5,:inProjectId,null))
6 or (work_par6 = decode(:inwork_level,6,:inProjectId,null))
7 or (work_par7 = decode(:inwork_level,7,:inProjectId,null))
8 or (work_par8 = decode(:inwork_level,8,:inProjectId,null))
9 or (work_par9 = decode(:inwork_level,9,:inProjectId,null))
10 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=41 Card=7726 Bytes=772600)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=41 Card=7726 Bytes=772600)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'T_IDX3' (NON-UNIQUE) (Cost=1)
8 3 BITMAP CONVERSION (FROM ROWIDS)
9 8 INDEX (RANGE SCAN) OF 'T_IDX4' (NON-UNIQUE) (Cost=1)
10 3 BITMAP CONVERSION (FROM ROWIDS)
11 10 INDEX (RANGE SCAN) OF 'T_IDX5' (NON-UNIQUE) (Cost=1)
12 3 BITMAP CONVERSION (FROM ROWIDS)
13 12 INDEX (RANGE SCAN) OF 'T_IDX6' (NON-UNIQUE) (Cost=1)
14 3 BITMAP CONVERSION (FROM ROWIDS)
15 14 INDEX (RANGE SCAN) OF 'T_IDX7' (NON-UNIQUE) (Cost=1)
16 3 BITMAP CONVERSION (FROM ROWIDS)
17 16 INDEX (RANGE SCAN) OF 'T_IDX8' (NON-UNIQUE) (Cost=1)
18 3 BITMAP CONVERSION (FROM ROWIDS)
19 18 INDEX (RANGE SCAN) OF 'T_IDX9' (NON-UNIQUE) (Cost=1)
<b>all index range scans and as we'll see in a moment -- it'll only range scan ONE of them for real!</b>
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ *
2 from t2
3 where authattr_work_id in (
4 Select Work_ID From t
5 Where DECODE(:inwork_level, 2, Work_Par2 ,3, Work_Par3 ,4,
6 Work_Par4,5,Work_Par5 ,6,Work_par6 ,7,Work_par7 ,
7 8, Work_par8, 9, Work_Par9) = :inProjectID
8 )
9 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2028 Card=1000 Bytes=139000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=39)
2 1 NESTED LOOPS (Cost=2028 Card=1000 Bytes=139000)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=11 Card=1000 Bytes=100000)
5 2 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=1)
<b>so, that is probably what you are seeing now -- full scan, and then nested loops -- but you could be seeing:</b>
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ *
2 from t2
3 where authattr_work_id in (
4 select work_id from t
5 where (work_par2 = decode(:inwork_level,2,:inProjectId,null))
6 or (work_par3 = decode(:inwork_level,3,:inProjectId,null))
7 or (work_par4 = decode(:inwork_level,4,:inProjectId,null))
8 or (work_par5 = decode(:inwork_level,5,:inProjectId,null))
9 or (work_par6 = decode(:inwork_level,6,:inProjectId,null))
10 or (work_par7 = decode(:inwork_level,7,:inProjectId,null))
11 or (work_par8 = decode(:inwork_level,8,:inProjectId,null))
12 or (work_par9 = decode(:inwork_level,9,:inProjectId,null))
13 )
14 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=15616 Card=7726 Bytes=1073914)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=39)
2 1 NESTED LOOPS (Cost=15616 Card=7726 Bytes=1073914)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=41 Card=7726 Bytes=772600)
5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP OR
7 6 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=1)
9 6 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'T_IDX3' (NON-UNIQUE) (Cost=1)
11 6 BITMAP CONVERSION (FROM ROWIDS)
12 11 INDEX (RANGE SCAN) OF 'T_IDX4' (NON-UNIQUE) (Cost=1)
13 6 BITMAP CONVERSION (FROM ROWIDS)
14 13 INDEX (RANGE SCAN) OF 'T_IDX5' (NON-UNIQUE) (Cost=1)
15 6 BITMAP CONVERSION (FROM ROWIDS)
16 15 INDEX (RANGE SCAN) OF 'T_IDX6' (NON-UNIQUE) (Cost=1)
17 6 BITMAP CONVERSION (FROM ROWIDS)
18 17 INDEX (RANGE SCAN) OF 'T_IDX7' (NON-UNIQUE) (Cost=1)
19 6 BITMAP CONVERSION (FROM ROWIDS)
20 19 INDEX (RANGE SCAN) OF 'T_IDX8' (NON-UNIQUE) (Cost=1)
21 6 BITMAP CONVERSION (FROM ROWIDS)
22 21 INDEX (RANGE SCAN) OF 'T_IDX9' (NON-UNIQUE) (Cost=1)
23 2 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=1)
ops$tkyte@ORA9IR2> set autotrace off
<b>all indexes, if that makes sense. Further, using SQL_TRACE, we find:</b>
select /*+ FIRST_ROWS */ *
from t2 q1
where authattr_work_id in (
select work_id from t
where (work_par2 = decode(:inwork_level,2,:inProjectId,null))
or (work_par3 = decode(:inwork_level,3,:inProjectId,null))
or (work_par4 = decode(:inwork_level,4,:inProjectId,null))
or (work_par5 = decode(:inwork_level,5,:inProjectId,null))
or (work_par6 = decode(:inwork_level,6,:inProjectId,null))
or (work_par7 = decode(:inwork_level,7,:inProjectId,null))
or (work_par8 = decode(:inwork_level,8,:inProjectId,null))
or (work_par9 = decode(:inwork_level,9,:inProjectId,null))
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 115
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T2 (cr=4 r=0 w=0 time=186 us)
3 NESTED LOOPS (cr=3 r=0 w=0 time=168 us)
1 SORT UNIQUE (cr=2 r=0 w=0 time=146 us)
1 TABLE ACCESS BY INDEX ROWID T (cr=2 r=0 w=0 time=103 us)
1 BITMAP CONVERSION TO ROWIDS (cr=1 r=0 w=0 time=89 us)
1 BITMAP OR (cr=1 r=0 w=0 time=85 us)<b>
1 BITMAP CONVERSION FROM ROWIDS (cr=1 r=0 w=0 time=52 us)
1 INDEX RANGE SCAN T_IDX2 (cr=1 r=0 w=0 time=35 us)(object id 36726)</b>
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN T_IDX3 (cr=0 r=0 w=0 time=0 us)(object id 36727)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN T_IDX4 (cr=0 r=0 w=0 time=1 us)(object id 36728)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
0 INDEX RANGE SCAN T_IDX5 (cr=0 r=0 w=0 time=1 us)(object id 36729)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
0 INDEX RANGE SCAN T_IDX6 (cr=0 r=0 w=0 time=1 us)(object id 36730)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN T_IDX7 (cr=0 r=0 w=0 time=0 us)(object id 36731)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN T_IDX8 (cr=0 r=0 w=0 time=0 us)(object id 36732)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN T_IDX9 (cr=0 r=0 w=0 time=1 us)(object id 36733)
1 INDEX RANGE SCAN T2_IDX (cr=1 r=0 w=0 time=9 us)(object id 36735)
<b>only one index was actually even read on T, we can switch the index by switching the inputs:</b>
select /*+ FIRST_ROWS */ *
from t2 q2
where authattr_work_id in (
select work_id from t
where (work_par2 = decode(:inwork_level,2,:inProjectId,null))
or (work_par3 = decode(:inwork_level,3,:inProjectId,null))
or (work_par4 = decode(:inwork_level,4,:inProjectId,null))
or (work_par5 = decode(:inwork_level,5,:inProjectId,null))
or (work_par6 = decode(:inwork_level,6,:inProjectId,null))
or (work_par7 = decode(:inwork_level,7,:inProjectId,null))
or (work_par8 = decode(:inwork_level,8,:inProjectId,null))
or (work_par9 = decode(:inwork_level,9,:inProjectId,null))
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 115
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID OBJ#(36734) (cr=4 r=0 w=0 time=180 us)
3 NESTED LOOPS (cr=3 r=0 w=0 time=163 us)
1 SORT UNIQUE (cr=2 r=0 w=0 time=140 us)
1 TABLE ACCESS BY INDEX ROWID OBJ#(36725) (cr=2 r=0 w=0 time=88 us)
1 BITMAP CONVERSION TO ROWIDS (cr=1 r=0 w=0 time=73 us)
1 BITMAP OR (cr=1 r=0 w=0 time=68 us)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN OBJ#(36726) (cr=0 r=0 w=0 time=1 us)(object id 36726)<b>
1 BITMAP CONVERSION FROM ROWIDS (cr=1 r=0 w=0 time=39 us)
1 INDEX RANGE SCAN OBJ#(36727) (cr=1 r=0 w=0 time=31 us)(object id 36727)</b>
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
0 INDEX RANGE SCAN OBJ#(36728) (cr=0 r=0 w=0 time=1 us)(object id 36728)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN OBJ#(36729) (cr=0 r=0 w=0 time=0 us)(object id 36729)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN OBJ#(36730) (cr=0 r=0 w=0 time=0 us)(object id 36730)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
0 INDEX RANGE SCAN OBJ#(36731) (cr=0 r=0 w=0 time=1 us)(object id 36731)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
0 INDEX RANGE SCAN OBJ#(36732) (cr=0 r=0 w=0 time=1 us)(object id 36732)
0 BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
0 INDEX RANGE SCAN OBJ#(36733) (cr=0 r=0 w=0 time=1 us)(object id 36733)
1 INDEX RANGE SCAN OBJ#(36735) (cr=1 r=0 w=0 time=9 us)(object id 36735)
<b>of course this assumes you are using the CBO as only it is smart enough to know this</b>