Indexing only specific rows, IT WORKS NOT – fixed (works)

So, last year I was in Belgrade on asktoms seminar (Belgrade Seminar 2014.zip on asktom.oracle.com), now I have time to play, so I wanted to test indexing only specific rows, I decided 1.

As you will see you can index specific values, but some additional “objects” are necessary to let it work.

SQL> create table test (id_test number generated always as (case when id=1 then 1 else null end), id number) ;

Table created.

SQL> create index idx_test on test (id_test) ;

Index created.

SQL> insert into test (id) select level from dual connect by level /

100 rows created.

SQL> /

100 rows created.

SQL> /

100 rows created.

SQL> commit ;

Commit complete.
SQL> exec dbms_stats.gather_table_stats(user, 'test') ;

PL/SQL procedure successfully completed.

SQL> select DISTINCT_KEYS,BLEVEL,LEAF_BLOCKS,NUM_ROWS,SAMPLE_SIZE from user_indexes where index_name='IDX_TEST' ;

DISTINCT_KEYS BLEVEL LEAF_BLOCKS NUM_ROWS SAMPLE_SIZE
 ------------- ---------- ----------- ---------- -----------
 1 0 1 4 4

SQL> set autotrace on
 SQL> select count(*) from test where id_test=1 ;

COUNT(*)
 ----------
 4
Execution Plan
 ----------------------------------------------------------
 Plan hash value: 2901380809

------------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | 1 | 2 | 1 (0)| 00:00:01 |
 | 1 | SORT AGGREGATE | | 1 | 2 | | |
 |* 2 | INDEX RANGE SCAN| IDX_TEST | 4 | 8 | 1 (0)| 00:00:01 |
 ------------------------------------------------------------------------------

OR

SQL> create table test2 (id number) ;

Table created.

SQL> insert into test2 select level from dual connect by level /

100 rows created.

SQL> /

100 rows created.

SQL> /

100 rows created.

SQL> commit ;

Commit complete.

SQL> create index idx_test on test2 (case when id=1 then 1 else null end) ;

Index created.

create or replace view v_test as select t."ID", (case when id=1 then 1 else null end) index_id from test2 t ;

SQL> select count(*) from v_test where index_id=1 ;

COUNT(*)
 ----------
 4

Execution Plan
 ----------------------------------------------------------
 Plan hash value: 2901380809

------------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | 1 | 2 | 1 (0)| 00:00:01 |
 | 1 | SORT AGGREGATE | | 1 | 2 | | |
 |* 2 | INDEX RANGE SCAN| IDX_TEST | 4 | 8 | 1 (0)| 00:00:01 |
 ------------------------------------------------------------------------------
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s