Re: Partitioning OCP Question

It's just another example of a verybad question.

Without making too much fuss over the concepts of 'using temporary tables for sorting' and 'sorting large tables' - should we assume the question is supposed to be about sizes of temporary segments when sorts go to disc ?

Of course, answer A is perfectly valid. You CAN avoid using a temporary table if you simply load all the data into a VARRAY, then use a PL/SQL implementation of quicksort to sort it in the array.

Partitioning CAN help you create indexes on very large tables using in-memory sorts if the table is partitioned and the index is to be local and you drive the index creation serially.

Your comment, however, is perfectly reasonable. The size of the sort segment required is usually dependent upon the volume of data, not the definition of the table. However in special cases (e.g. a group by where the grouping columns form the leading edge of the partition key) the optimiser can determine that a partition by partition sort of the data is possible, thus doing one small sort against the data returned from each partition in turn rather than accumulating all the data from all partitions and sorting that in one big sort.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Buck Turgidson wrote in message
<0Edw4.2451$B37.209017_at_bgtnsc06-news.ops.worldnet.att.net>...


>I am baffled by the following question, whose answer is B.  Can someone

 tell


>me the connection between partitioning and temporary sort segments?  Aren't

>only the rows returned by a query sorted, irrespective of the size of the

>tables from whence they came?

>

>Which Oracle8 feature can reduce the size of temporary tables used for

>sorting large tables.

>

>A. VARRAYS

>B. Partitioning

>C. Large Pool

>D. Multiplexing.

>

>