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 
If it's range partitioned on the ID it may be possible as Oracle could 
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" <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 ;


Other related posts: