Re: Doing large sort in RAM - sort workarea manipulation

From: Timo Raitalaakso <rafu_at_iki.fi>
Date: Sat, 12 Nov 2011 23:24:56 +0200
Message-ID: <4EBEE428.20308_at_iki.fi>


You found the Tapio Lahdenmäki recommended second star index and hinted that correctly to avoid sorting. Something I rediscovered from Richard Foote night quiz
http://rafudb.blogspot.com/2011/09/avoid-temp-usage-while-table-reorg.html

To do that in parallel might be tricky if you need the results serially out of database to some ETL tool. Should there be a possibility to do insert into select in parallel with order by using the possibilitises of partition wise doing stuff. Tony Hasler is talking of such partition wise join in his blog post
http://tonyhasler.wordpress.com/2010/03/10/parallel-query-distribution-methods/. I would quess that requires changing your ETL to be done inside database and the partitioning of source and target to be similar.

--
Rafu

12.11.2011 20:38, GG kirjoitti:
> On 11/12/11 18:45, Jonathan Lewis wrote:

>> 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
>>
>> SQL> select /*+ INDEX(t idx_id) */ * from sort_px t order by id;
>>
-- http://www.freelists.org/webpage/oracle-l
Received on Sat Nov 12 2011 - 15:24:56 CST

Original text of this message