Indexing Strategy and Column Ordering - I

I have been reading a lot of blogs and other discussions on column ordering in an index, its impact.I have tried to consolidate what my learing was along with some of my expericences.Please feel free add to this in case i have missed out or overlooked something.

Index skipscan was in Oracle 9i introduced and i feel that we need to discuss skip scan and then examine the impact of column ordering.


test@TEST> create table skip (a varchar2(10), b number);

Table created.



test@TEST>create index idx_skip on skip(a,b);

Index created.

test@TEST>ed Wrote file afiedt.buf

1 begin
2 for i in 1 .. 30000
3 loop
4 insert into SKIP values('A', i);
5 end loop;
6 commit;
7* end;
test@TEST>/
PL/SQL procedure successfully completed.

test@TEST>ed
Wrote file afiedt.buf

1 begin
2 for i in 30001 .. 60000
3 loop
4 insert into SKIP values('B', i);
5 end loop;
6 commit;
7* end;
test@TEST>

PL/SQL procedure successfully completed.

test@TEST>ed
Wrote file afiedt.buf

1 begin
2 for i in 60001 .. 90000
3 loop
4 insert into SKIP values('C', i);
5 end loop;
6 commit;
7* end;
test@TEST>/

PL/SQL procedure successfully completed.

test@TEST>ed
Wrote file afiedt.buf

1 begin
2 for i in 90001 .. 100000
3 loop
4 insert into SKIP values('D', i);
5 end loop;
6 commit;
7* end;
test@TEST>/

PL/SQL procedure successfully completed.

test@TEST>SET AUTOTRACE TRACEONLY EXPLAIN



test@TEST>select * from SKIP where b=100;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=6)


1 0
INDEX (SKIP SCAN) OF 'IDX_SKIP' (NON-UNIQUE) (Cost=5 Card=1 Bytes=6)





test@TEST>select * from SKIP where b=45000;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=6)


1 0
INDEX (SKIP SCAN) OF 'IDX_SKIP' (NON-UNIQUE) (Cost=5 Card=1 Bytes=6)





test@TEST>select * from SKIP where b=100000;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=6)


1 0
INDEX (SKIP SCAN) OF 'IDX_SKIP' (NON-UNIQUE) (Cost=5 Card=1 Bytes=6)



What is happening here is instead of range scanning the entire index, the index is now treated as a sub index A (1-30000),B(30001-60000),C(60001-90000),D (90001 -100000).

The query select * from SKIP where b=100; would only scan the A (1-30000) part
select * from SKIP where b=45000; This would skip A (1-30000) and scan B(30001-60000) , and same holds true for the last query.

By this way there is no need to range scan the entire index.

Note that the sub index structure/part is not exposed outside this is managed by oracle internally.