Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rudi.

Asked: October 20, 2005 - 10:06 am UTC

Last updated: October 26, 2005 - 12:27 pm UTC

Version: 9.2.0.6

Viewed 1000+ times

You Asked

Hi Tom
I have a partitioned table which has a local partitioned index.
I just noticed that if I force Oracle to use the index instead of performing a sort in the query,the data doesn't get sorted correctly
For example,I have the following table
1 create table test (t1 number(1),my_date date)
2 partition by range(my_date)
3 (partition test_20051010 values less than (to_date('20051011','yyyymmdd')),
4* partition test_20051011 values less than (to_date('20051012','yyyymmdd')))
The table has a local index,as follows
create index test_ix on test(t1) local;

If I run the following query,the results are not sorted,although I would expect them to be sorted

1 select /*+ INDEX_ASC (test "test_ix") */ t1,my_date from test
2* where t1 between 1 and 2
SQL> /

T1 MY_DATE
---------- ---------------
1 20051010_000000
2 20051010_000000
1 20051011_000000

3 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=22)
1 0 PARTITION RANGE (ALL)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TEST' (Cost=1 Ca
rd=1 Bytes=22)

3 2 INDEX (RANGE SCAN) OF 'TEST_IX' (NON-UNIQUE) (Cost=3 C
ard=1)

Thanks a lot



and Tom said...

there is no reason whatsoever to expect data to be sorted unless and until you have an ORDER BY on your query.

None, period, zippo.


An index does not imply sorted, especially with partitions! It read "index 1" and then it read "index 2"

THE ONLY WAY TO GET DATA IN SOME ORDER FROM A RELATIONAL DATABASE IS TO USE ORDER BY

period.


(If I had a dime for every time I've said that.... and no - group by doesn't not cut it either, group by doesn't sort in general)

Rating

  (3 ratings)

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

Comments

A wide-spreaded myth

Andrew Max, October 20, 2005 - 2:19 pm UTC

It's astonishing, but I've already seen similar "do-order-by-yourself" approach.
For example, here:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:9843506698920#9896083461138 <code>

Now I see it's a wide-spreaded myth of some kind: "we can put hint in our query and get rid of that annoying ORDER BY".
However, I agree with Tom 100%. This is really terrible. NEVER do that.

With hint, there are lot of things which may destroy your "ordering":
- Someone dropped your index
- Someone marked index as UNUSABLE and your session is run under SKIP_UNUSABLE_INDEXES set to TRUE.
- Your index is partitioned.
- Some day you decided to use PQ.
...
and so on...

Best regards --
Andrew.

Tom Kyte
October 20, 2005 - 4:56 pm UTC

The optimizer skips your index for whatever reason

your index is sorted binary, not character wise (meaning the index isn't sorted!!! not according to what you end users expect)

there is ONLY ONE WAY TO GET ORDERED DATA

and that is to use order by.


Cannot wait for 10gr2 and people using group by to "sort", they will be big time "surprised" by group by hash

David Aldridge, October 20, 2005 - 10:38 pm UTC

>> Cannot wait for 10gr2 and people using group by to "sort", they will be big time "surprised" by group by hash <<

... and add to the "I wish Oracle had ..." list a new init parameter "sort_on_group_by" :D

Tom Kyte
October 21, 2005 - 8:06 am UTC

never.

Pk enforcement and local partitioned indexes

Rudi, October 26, 2005 - 8:43 am UTC

Hi Tom
Bellow is a quote from the Oracle Performance Tuning Guide:
"The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending
order of their indexed values".
So actually I do have a reason to expect the result set to be sorted.
The only pitfall is locally partitioned indexes,but its not written in the manual.
I have however a followup question :how does Oracle maintain uniqueness for Primary keys which are based on local partitioned indexes?


Tom Kyte
October 26, 2005 - 12:27 pm UTC

but you have multiple indexes to scan in ascending order and they do not say "data will be returned sorted" do they?


unless
until
only if
you have an order can you have any expectation about the order of rows. period.

NO GETTING AROUND IT. PERIOD.

That and indexes store the data sorted in BINARY - not according to sort order necessarily.



last question: that only works if the primary key is in fact part of the partition key, else you will not have a locally partitioned index on the primary key.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.