In a forum I frequent the following question was presented (I have reposted the question in its entirety here):
Suppose I have a table structure like T1(C1,C2,C3,C4....).
I creates a index indx1 on (C1,C2,C3). I issue three select statements like :
1.select * from T1 where C1=
2.select * from T1 where C2=
3.select * from T1 where C3=
4.select * from T1 where C1= and C2= and C3 =
How the optimizer will use the index for each statements.
Regards,
Sanjoy
Of course the only way to begin to answer that, since the original poster did not provide any DDL or sample data, is to create an example and execute it (we’ll ignore the fact that his three queries are numbered 1 through 4). I’ve posted the results below, this running on an Oracle 11.1.0.6 instance; the statistics reported by autotrace have been removed as they provided no additional value to the example. Notice how the plans change as the data ‘topography’ changes, illustrating that the original question was ambiguous, at best:
SQL>
SQL> --
SQL> -- Suppose I have a table structure like T1(C1,C2,C3,C4....).
SQL> -- I creates a index indx1 on (C1,C2,C3). I issue three select
SQL> -- statements like :
SQL> --
SQL> -- 1.select * from T1 where C1=
SQL> -- 2.select * from T1 where C2=
SQL> -- 3.select * from T1 where C3=
SQL> -- 4.select * from T1 where C1= and C2= and C3 =
SQL> --
SQL> --
SQL> -- How the optimizer will use the index for each statements.
SQL> --
SQL> --
SQL> -- Regards,
SQL> -- Sanjoy
SQL> --
SQL>
SQL> --
SQL> -- Create the table in question
SQL> --
SQL> create table t1(
2 c1 number,
3 c2 varchar2(20),
4 c3 date,
5 c4 varchar2(10),
6 c5 number,
7 c6 number
8 );
Table created.
SQL>
SQL> --
SQL> -- Create the index specified
SQL> --
SQL> create index indx1
2 on t1(c1,c2,c3);
Index created.
SQL>
SQL> --
SQL> -- Load test data
SQL> --
SQL>
SQL> --
SQL> -- Data with unique C1, C2 and C3 values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (i, 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
433 Testing record 433 26-JAN-10 Filler 433 433
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
7748 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9993 Testing record 9993 30-MAR-36 Filler 451 363
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
433 Testing record 433 26-JAN-10 Filler 433 433
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
7748 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9993 Testing record 9993 30-MAR-36 Filler 451 363
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, and a reasonably selective C1
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 43), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
8 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
8 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, with a cycling C1 set of values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 3), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
2 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
2 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with cycling C1 and C2 values and unique C3 values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 3), 'Testing record '||mod(i,3), trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 0 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 0 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
Note that as the selectivity of the leading columns (C1 and C2) decreases the plans change from TABLE ACCESS FULL to INDEX SKIP SCAN when column C1 is not included in the where clause. Note also that the queries did NOT change and that histograms made no difference in the plans.
This changes for 11.2.0.2 (for the curious the results remain the same for version 12.2.0.1):
SQL>
SQL>
SQL> --
SQL> -- I creates a index indx1 on (C1,C2,C3). I issue three select
SQL> -- statements like :
SQL> --
SQL> -- 1.select * from T1 where C1=
SQL> -- 2.select * from T1 where C2=
SQL> -- 3.select * from T1 where C3=
SQL> -- 4.select * from T1 where C1= and C2= and C3 =
SQL> --
SQL> --
SQL> --
SQL> --
SQL> -- Regards,
SQL> -- Sanjoy
SQL> --
SQL>
SQL> --
SQL> -- Create the table in question
SQL> --
SQL> create table t1(
2 c1 number,
3 c2 varchar2(20),
4 c3 date,
5 c4 varchar2(10),
6 c5 number,
7 c6 number
8 );
Table created.
SQL>
SQL>
SQL> --
SQL> -- Create the index specified
SQL> --
SQL> create index indx1
2 on t1(c1,c2,c3);
Index created.
SQL>
SQL>
SQL> --
SQL> -- Load test data
SQL> --
SQL>
SQL> --
SQL> -- Data with unique C1, C2 and C3 values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (i, 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
433 Testing record 433 07-MAY-13 Filler 433 433
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
7748 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 11 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 11 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9993 Testing record 9993 10-JUL-39 Filler 451 363
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
433 Testing record 433 07-MAY-13 Filler 433 433
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
ws processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
7748 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 11 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 11 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9993 Testing record 9993 10-JUL-39 Filler 451 363
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, and a reasonably selective C1
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 43), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
8 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 9 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 16 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 16 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
8 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 9 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 16 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 16 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, with a cycling C1 set of values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 3), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
2 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 20 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 20 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
2 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 20 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 20 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with cycling C1 and C2 values and unique C3 values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 3), 'Testing record '||mod(i,3), trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 0 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 0 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL>
Notice that in the later release INDEX SKIP SCAN is chosen by the optimizer when any indexed column other than the leading column is used in the where clause.
So how does Oracle treat a ‘select * from t1 where c3 = trunc(sysdate+9)’ query when the index is built on columns (c1,c2,c3)? That depends entirely upon the data and how skewed (or not) it may be. [It also depends upon the Oracle version in use, as releases after 8.1.7.4 implemented changes in the available query plans and how indexes could be used. Again note the differences between how 11.1.0.6 and 11.2.0.2 behave.] Because of deletes/inserts a plan can change even though the query has not, so there is no ‘definitive’ answer to the question as written. The conditions are simply too vague to produce repeatable results.
And that’s a definite maybe.
Oracle Tips and Tricks -- David Fitzjarrell 