Re: Doing large sort in RAM - sort workarea manipulation

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_at_interia.pl> To: <greg_at_structureddata.org> Cc: <oracle-l_at_freelists.org> 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
  • --------

    ---

    1 ID NUMBER 2 FILLER VARCHAR2(100)

--table with 2 partitions , partitioned range by id 1M rows each
--set paralle =2

select * from sort_px order by id ;

--

http://www.freelists.org/webpage/oracle-l Received on Sat Nov 12 2011 - 11:45:42 CST