• Welcome
• Projects
• Presentations
• Research

BUFFER (SORT)

Description

Performs a memory sort on a row source

Versions

This operation was introduced in Oracle 9.0.1 though it may have been used in limited circumstances in some earlier versions

This operation is implemented in the following versions

Examples

Example 1

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example has been reproduced on Oracle 10.2 (Linux).

This example requires the following table and index definitions

    CREATE TABLE t1 (c1 NUMBER);
    CREATE TABLE t2 (c1 NUMBER);

At least one of the tables must be analysed

    ANALYZE TABLE t1 COMPUTE STATISTICS;

The statement

    SELECT t1.c1, t2.c1
    FROM t1, t2;

generates the following execution plan

   0     SELECT STATEMENT Optimizer=CHOOSE
   1   0   MERGE JOIN (CARTESIAN) 
   2   1     TABLE ACCESS (FULL) OF 'T1'
   3   1       BUFFER (SORT)
   4   3       TABLE ACCESS (FULL) OF 'T2'

Example 2

This example was developed using Oracle 9.2.0.1 on Windows 2000.

This example has not yet been reproduced on Oracle 10.1 or Oracle 10.2.

This example requires the following table and index definitions

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);

In Oracle 9.0.1 and above the statement

    SELECT c1,c2 FROM t1
    CONNECT BY c1 = PRIOR c2
    START WITH c1 = 0;

generates the following execution plan

   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T1'
   4    2       TABLE ACCESS (BY USER ROWID) OF 'T1'
   5    1     NESTED LOOPS
   6    5       BUFFER (SORT)
   7    6         CONNECT BY PUMP
   8    5       TABLE ACCESS (FULL) OF 'T1'