Oracle Database Discussions

Dears,

Let me present you the model and then I will ask my question

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table t1
  2     as select rownum                  id1,
  3      mod(rownum,1000)                  id2,
  4      lpad(rownum,10,'0')              small_vc,
  5      rpad('x',1000)                   padding
  6  from dual
  7  connect by level <= 10000;

Table created.

SQL> create index t1_ind_id1 on t1(id1);

Index created.

SQL> create index t1_ind_id2 on t1(id2, id1);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 't1', cascade => true);

PL/SQL procedure successfully completed.

SQL> select index_name, num_rows, clustering_factor
  2  from user_indexes
  3  where index_name in ('T1_IND_ID1','T1_IND_ID2');

INDEX_NAME                       NUM_ROWS CLUSTERING_FACTOR
------------------------------ ---------- -----------------
T1_IND_ID1                          10000              1429
T1_IND_ID2                          10000             10000


SQL> select *
  2  from t1
  3  where id1=6;

 Execution Plan
----------------------------------------------------------
Plan hash value: 2367654148

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |  1019 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     1 |  1019 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IND_ID1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID1"=6)

So far so good.

What I want is to know how I can reproduce a real life example where an index skip scan has been choosen by the CBO despite the presence of the "adequat" index.

Here below I tried several examples

SQL> alter index t1_ind_id1 unusable;

Index altered.

SQL> select *
  2  from t1
  3  where id1=6;

  Execution Plan
----------------------------------------------------------
Plan hash value: 2497247906

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |  1019 |  1004   (1)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     1 |  1019 |  1004   (1)| 00:00:03 |
|*  2 |   INDEX SKIP SCAN           | T1_IND_ID2 |     1 |       |  1003   (1)| 00:00:03 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID1"=6)
       filter("ID1"=6)

That's predictable. Let put back the index usable and change its clustering factor

SQL> alter index t1_ind_id1 rebuild;

Index altered.

SQL> select *
  2  from t1
  3  where id1=6;

     
Execution Plan
----------------------------------------------------------
Plan hash value: 2367654148

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |  1019 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     1 |  1019 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IND_ID1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID1"=6)

SQL> exec dbms_stats.set_index_stats(user, 'T1_IND_ID1',clstfct => 20000);

PL/SQL procedure successfully completed.

SQL> select index_name, num_rows, clustering_factor
  2  from user_indexes
  3  where index_name in ('T1_IND_ID1','T1_IND_ID2');

INDEX_NAME                       NUM_ROWS CLUSTERING_FACTOR
------------------------------ ---------- -----------------
T1_IND_ID1                          10000             20000
T1_IND_ID2                          10000             10000


SQL> select *
  2  from t1
  3  where id1=6;

    
Execution Plan
------------------------------------------------------------------------------------------
Plan hash value: 2367654148
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |  1019 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     1 |  1019 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IND_ID1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID1"=6)

Still no success to produce an INDEX SKIP SCAN on T1_IND_ID2 in the presence of index T1_IND_ID1

Any suggestions ?

Thanks

Mohamed Houri
www.hourim.wordpress.com