Lesson for the day

With cost based optimizer if you don’t collect statistics properly then it means that you are screwed.

This is what I did.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

I created the following table.

SQL> create table hx_index_test_tbl(
2 no integer not null,
3 name varchar2(50) not null,
4 old_value varchar2(100),
5 new_value varchar2(100));

Table created.

Next I added these two indexes to the table.

SQL> create index hx_index_test_tbl_idx on hx_index_test_tbl(name,no);

Index created.

SQL> create index hx_index_test_tbl_idx_001 on hx_index_test_tbl(name);

Index created.

Then I populated some data.

SQL> insert into hx_index_test_tbl
2 select row_number() over(partition by name order by 1) no, name, rpad('*',100,'*') old_value, rpad('*',100,'*') new_value
3 from (select 'A'mod(level,100) name from dual connect by level <=1000);

1000 rows created.

SQL> commit;

Commit complete.

Got the statistics gathered.

SQL> exec dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns');

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> set autotrace traceonly explain
SQL> select * from hx_index_test_tbl where name = 'A0';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=10 Bytes=1000)
1 0 TABLE ACCESS (FULL) OF 'HX_INDEX_TEST_TBL' (Cost=5 Card=10 Bytes=1000)

Oops its going for a full scan. It’s not considering my index. I want it to pick up the index HX_INDEX_TEST_TBL_IDX_001.

SQL> select * from hx_index_test_tbl where name = 'A0' and no = 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=100)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HX_INDEX_TEST_TBL' (Cost=2 Card=1 Bytes=100)
2 1 INDEX (RANGE SCAN) OF 'HX_INDEX_TEST_TBL_IDX' (NON-UNIQUE) (Cost=1 Card=1)

But when I add no in the where clause its considering the index why is it so???

The reason is very simple. See how I have gathered statistics.

dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns');

I forgot to gather for Index of this table. Now let me gather for the index of this table also.

SQL> EXEC dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns', cascade=>TRUE);

PL/SQL procedure successfully completed.

Now see what happens...

SQL> select * from hx_index_test_tbl where name = 'A0';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HX_INDEX_TEST_TBL' (Cost=2 Card=10 Bytes=1000)
2 1 INDEX (RANGE SCAN) OF 'HX_INDEX_TEST_TBL_IDX_001' (NON-UNIQUE) (Cost=1 Card=10)


So if you don’t gather stats properly with cost based optimizer you will get weird execution plans.