Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, kim.

Asked: May 24, 2012 - 12:25 am UTC

Last updated: May 24, 2012 - 11:22 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

hello tom. i'm programmer using oracle in korea. but i really worry about my broken english.
yesterday study in my office about index skip scan.
if we have two column 'a' and 'b'. and 'a' column only two distinct value 'man' and 'women'. and we give condition only 'b' like b between 1 and 10.
if i use index skip scan this case always read left most index leaf block and right most index leaf block read normally.
i think if i use check constraint column 'a' or list partitioned using column 'a'. left most index leaf block and right most index leaf block still read?

so i was do test.

how can i know 'what index leaf block read for my sql''

-- my sql script
create table b2en.ss_test_no_constraint
(
sex varchar2(5 char)
, sal number(6)
)

insert into b2en.ss_test_no_constraint
select case when mod(rownum,2) = 1 then 'man' else 'woman' end sex
, trunc(dbms_random.value(100,100000)) sal
from dual
connect by level <= 10000

commit

create index ss_test_no_constraint_x01 on b2en.ss_test_no_constraint(sex, sal)

select /*+ index_ss(t1) */ * from b2en.ss_test_no_constraint t1 where sal between 5000 and 8000;

i really rally worry about you can understand my english.
god blessing you.

and Tom said...

An index skip scan will read the interior branch blocks - looking for changes in values. It would not read just the right and the left - it would read some of the interior branch blocks to find out where the value of the leading edge column "changes"

it would not be able to take advantage of a check constraint to read the right/left - as it doesn't do that sort of processing.


So, it'll read interior blocks, branch blocks (navigational blocks) to determine when the value of that column changes and when it does - it will go down and process that part of the index structure all of the way to the leaf blocks, then it goes back to the branch block and continues looking for a value change and if it finds it - it goes down that part of the index again.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions