Oracle Tips and Tricks — David Fitzjarrell

November 19, 2008

‘Magical’ Indexes

Filed under: Indexes,Performance — dfitzjarrell @ 15:32

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

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)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        739  bytes sent via SQL*Net to client
        420  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> 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')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        739  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        740  bytes sent via SQL*Net to client
        420  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> 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)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        739  bytes sent via SQL*Net to client
        420  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> 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')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        739  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        740  bytes sent via SQL*Net to client
        420  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> 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)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        738  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         84  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        738  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         84  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        738  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        100  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        738  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        100  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        420  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> 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))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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.

Advertisements

Create a free website or blog at WordPress.com.