pass in a bind B1, B1 will either be null or an ID, if its null I want a full table scan, if populated I want an index on the ID used
A full table scan is always used whether :B1 is populated or not. Hinting the index does NOT work, it does a full table even with the hint.
This gets called thousands of times a day from a procedure, Ive rewritten it so it does an if then else on B1 and it calls 1 of 2 statements, 1 on the full table if null and then just a select on the ID if B1 does have a record. Works.
But got me thinking on how we could force the index here without rewriting, is there any way with that current statement either through a forced hint or a profile or a query rewrite?
SQL> create table t1_test
2 (col1 number);
Table created.
SQL>
SQL> create index idx on t1_test(col1);
Index created.
SQL>
SQL> begin
2 for i in 1..100
3 loop
4 insert into t1_test(col1) values(i);
5 end loop;
6 end;
7
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'t1_test')
PL/SQL procedure successfully completed.
SQL>
SQL> variable B1 number
SQL> exec :B1 := 1
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly
call a select, then call with index hint and then call with the hint and without the OR clause, only the last one uses the index. for sure youre going to say well yeah thats expected, Im looking for the alternative, how can you force the index the way its written or can you at all
SQL>
SQL> select *
2 from t1_test t
3 where (:B1 is null or t.col1 = :B1);
Execution Plan
----------------------------------------------------------
Plan hash value: 2936842077
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 18 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1_TEST | 6 | 18 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B1 IS NULL OR "T"."COL1"=TO_NUMBER(:B1))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>
SQL>
SQL>
SQL> select /*+ index (t idx) */*
2 from t1_test t
3 where (:B1 is null or t.col1 = :B1);
Execution Plan
----------------------------------------------------------
Plan hash value: 2936842077
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 18 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1_TEST | 6 | 18 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B1 IS NULL OR "T"."COL1"=TO_NUMBER(:B1))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>
SQL>
SQL> select /*+ index (t idx) */*
2 from t1_test t
3 where (t.col1 = :B1);
Execution Plan
----------------------------------------------------------
Plan hash value: 2353645359
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX | 1 | 3 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."COL1"=TO_NUMBER(:B1))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>