Thanks for the question.

Asked: October 19, 2009 - 11:08 pm UTC

Last updated: November 24, 2009 - 2:34 pm UTC

Version: 9.2

Viewed 1000+ times

You Asked

Respected Sir,

First of all thanks for all your help to oracle community

In non-unique index, rows in leaf block are sorted on <key1,key2...Keyn,row-id>

Since data is sorted first on key1 then on key2 so on.. and finally on rowid

My question is

(1) when performing range scan using non-unique index will oracle get all the rowids in sorted order and that's
helpful during table block access since oracle does not have to jump all across the segment block?

Could you please flash light on this

Thanks

and Tom said...

Well, the rowids will be sorted WITHIN THE SAME KEY1,KEY2,... KEYN value.

But not across them.

What I mean is, suppose the index is on:

create index i on emp(ename,job);

then the index data would be sorted by ename,job,ROWID.

If you query:select * from emp where ename like 'A%';

then the rowids returned from that would not be in order - since we skip over JOBs and the rowids are sorted inside of ENAME,JOB - but not inside of ENAME by itself.

You can and will see query plans that look like this:

create index t1_something_index
select * from t1, t2 where t1.something = :x and t1.key = t2.key;


ops$tkyte%ORA9IR2> create table t1 ( x, y )
  2  as
  3  select rownum, rownum from all_objects;

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table t2 ( a , b , c )
  2  as
  3  select rownum, rownum, rownum from all_objects;

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create index t1_y_index on t1(y);

Index created.

ops$tkyte%ORA9IR2> create index t2_a_b_index on t2(a,b);

Index created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T1', cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T2', cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select * from t1, t2 where t1.y = 42 and t1.x = t2.a;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=22)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=13)
   2    1     NESTED LOOPS (Cost=4 Card=1 Bytes=22)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=9)
   4    3         INDEX (RANGE SCAN) OF 'T1_Y_INDEX' (NON-UNIQUE) (Cost=1 Card=1)
   5    2       INDEX (RANGE SCAN) OF 'T2_A_B_INDEX' (NON-UNIQUE) (Cost=1 Card=1)



ops$tkyte%ORA9IR2> set autotrace off

see how the table access of t2 is "outside" of the loop? That is because the optimizer decided to wait to access T2 until AFTER it got all of the index entries for T2 so it could arrange them by ROWID to make the access more efficient.

Rating

(6 ratings)

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

Comments

A reader, October 21, 2009 - 1:04 pm UTC

Thanks Tom

A reader, November 12, 2009 - 8:56 am UTC

Hello Sir,

<quote>
If you query:select * from emp where ename like 'A%';

then the rowids returned from that would not be in order - since we skip over JOBs and the rowids are sorted inside of ENAME,JOB - but not inside of ENAME by itself.
<quote>

but if query as: select * from emp where ename ='ABC';

then it would return rowid in sorted order

Am I right?

Thanks

November 15, 2009 - 2:00 pm UTC

yes, no, maybe - as ALWAYS "it depends"

If we used the index on (ename,job), then where ename = 'ABC' would tend to return the data sorted by ENAME,JOB,ROWID

So, again, no - for the same reason.

ENAME   JOB    ROWID
------  -----  --------
ABC     JOB1   1000
ABC     JOB2   1
ABC     JOB3   10000000

for example

A reader, November 15, 2009 - 4:29 pm UTC

Thanks Sir for your valuble time

Yes you are right

But Who about ename='ABC' and JOb='JOB1'

ENAME JOB ROWID
------ ----- --------
ABC JOB1 1000
ABC JOB1 1011
ABC JOB1 1025
ABC JOB2 1
ABC JOB3 10000000

So if we have non-unique index(k1,k2,k3....kn,rowid) then "where k1=<value1> and k2=<value2>
and k3=<value3> ..... kn=<valuen>" will return have sorted rowid

Please advice

Many Thanks for your time

Regards

November 23, 2009 - 8:48 am UTC

if you have ename='ABC' and JOb='JOB1' , you would not have the result set you depict, job2 and job3 would not be there.

but, if you have ename='ABC' and JOb='JOB1' AND it was a trivial query of the form "select ename, job, rowid from t where ename='ABC' and JOb='JOB1' " (note: important that it not go to the table, the answer must come entirely from the index for this next bit to be 'probably what happens') - it is likely that the rowids would be returned "sorted"

But, you know what, if you want something sorted - there is precisely ONE WAY to achieve that - use order by. If for whatever reason, it is important to have the data sorted by rowid after ename and job, you MUST order by ename, job, rowid

A reader, November 23, 2009 - 10:05 am UTC

Thanks for your reply.

<quote>
but, if you have ename='ABC' and JOb='JOB1' AND it was a trivial query of the form
"select ename, job, rowid from t where ename='ABC' and JOb='JOB1' "
(note: important that it not go to the table, the answer must come entirely from the index for this next bit to be 'probably what happens') - it is likely that the rowids would be returned "sorted"

<quote>

But say even if I have some column reference in the select not in the index i.e.

"select ename, job, <col1>, <col2> from t where ename='ABC' and JOb='JOB1' "

and havig the same non-unique index as below

ENAME JOB ROWID
------ ----- --------
ABC JOB1 1000
ABC JOB1 1011
ABC JOB1 1025
ABC JOB2 1
ABC JOB3 10000000

the above query access index where ename='ABC' and JOb='JOB1' get rowid in sorted order
since its store as (ename,jod,rowid) and then access table for remaining column

November 23, 2009 - 4:42 pm UTC

but we might

a) go to index
b) get the abc, job1 stuff
c) reorder the rowids for most efficient access
d) and then access the table

step (c) can happen

and the only way to prevent it or make it so it doesn't matter if it happens would be to..............

drum roll please..............

USE ORDER BY

If you want something ordered, order it, else do not expect anything (and you will not be disappointed)

A reader, November 23, 2009 - 9:59 pm UTC

Appreciate for your reply.

I known that in order to get data in sorted order one should use order by clause

But My point here is to who table is access via non unique index

Also from your above reply in step(c) why there is reorder require ?

Since we already have rowid the address of row we can quickly access the records
using a single IO?

It would be great if you Could clarify the reason for reorder ?

Thanks

November 24, 2009 - 10:29 am UTC

... Also from your above reply in step(c) why there is reorder require ?...

it is not required, but the fact REMAINS: it might be done.

You asked me "if I use where x=constant and y=constant, will the data - if retrieved via the index on (x,y) - be ordered by x,y,rowid"

I answered "it depends"

It can and will reorder the rowids retrieved from an index in order to most efficiently access the data. Maybe not in this case - but there is no way one can say "definitely it will be sorted by x,y,rowid"

It might be
It might NOT be

A rowid is the fastest way to a single row - if you have a rowid, you can go to the row.

However, when you get hundreds of rows, you have hundreds of rowids - you have hundreds of things to do, you might do them in the order you get them, then again, you might not.

that is all we need to know.

A reader, November 24, 2009 - 1:56 pm UTC

Thanks Tom

Just one more thing to clarify

>>>
It can and will reorder the rowids retrieved from an index in order to most efficiently access the data
<<<<

Say oracle get rowid in order i.e. 1,2,3.....75 from index scans

now when it start accessing the table block it may happen that the block on which the intial rowid(i.e. 1,2....24) reside may not
be in the cache and the blocks on which middle rowids (i.e. 25....40) reside may found in cache and that may be
the reason why oracle may decide to retrive those rows first and get the one which reside on disk latter out of order...

Can I consider above as one of the possibility that cause oracle to reorder the rowid to efficiently access the data?

Many thanks for valuble time

November 24, 2009 - 2:34 pm UTC

that is not the reasoning. No, it would not look to see what is in the cache or not.

It would wait to get a set of rowids and then organize them to decrease the number of times it would have to get and re-get a block from the cache (regardless of whether it would need a physical IO or not)

It probably would not happen in this trivial case, but in general it will.

Hence, you cannot say anything about the ordering of the rowids in your query unless and until you have an order by.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

non-unique index range scan

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs