Re: Doing large sort in RAM - sort workarea manipulation

From: Jonathan Lewis <>
Date: Sat, 12 Nov 2011 17:45:42 -0000
Message-ID: <FF1049F057DD425BB2DD7F42059E8996_at_Primary>

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.


Jonathan Lewis

  • Original Message ----- From: "GG" <> To: <> Cc: <> Sent: Saturday, November 12, 2011 4:35 PM Subject: Re: Doing large sort in RAM - sort workarea manipulation SQL> _at_desc sort_px Name Null? Type
    • --------

--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 ;

-- Received on Sat Nov 12 2011 - 11:45:42 CST

Original text of this message