Re: Doing large sort in RAM - sort workarea manipulation
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 12 Nov 2011 17:45:42 -0000
Unless you have defined ID to be NOT NULL (or added a not null constraint, or
add a predicate "id is not null" to the query) then it isn't possible for the
optimizer to produce a path that uses the index for your query as there may be
rows in the table that don't appear in the index.
If the table is locally partitioned with the index then an index-only path may
not (always) be possible - but I'd have to check the different cases to be
sure.
If it's range partitioned on the ID it may be possible as Oracle could
determine
that visiting the partitions in order and doing a full scan on each partition
would get you the right answer, by if it's hash partitioned on ID then Oracle
would have to jump "randomly" from partition to partition to find the next
larger value and I wouldn't expect that to happen.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
----- Original Message -----
From: "GG" <grzegorzof@xxxxxxxxxx>
To: <greg@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, November 12, 2011 4:35 PM
Subject: Re: Doing large sort in RAM - sort workarea manipulation
SQL> @desc sort_px
Name Null? Type
------------------------------- --------
----------------------------
1 ID NUMBER
2 FILLER VARCHAR2(100)
--ID is from rownum (unique numbers)
--filler is rpad('X', 100 , 'X') for every column
--table with 2 partitions , partitioned range by id 1M rows each
--set paralle =2
select * from sort_px order by id ;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
