FAQ
A query (with an order by) is able to satisfy it's column list
by scanning an index. This scan will return the rows in sorted
order, but the query still executes a sort (confirmed by 10046
trace). Should not the result set from the fast full scan be
correctly ordered? This would make the sort redundant, but very
expensive in terms of response time.

Table:
random_data

Name Null? Type
------------------- -------- -------------------
REC_NO NOT NULL NUMBER
INSERT_TEXT VARCHAR2(200)
INSERT_DATE DATE
LARGE_RANDOM_NUM NUMBER
SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
ROWID_BLOCKNUM NUMBER
ROWID_ROWNUM NUMBER

select column_name
from user_ind_columns
where index_name = 'IX_RD_SMALL_RN'

COLUMN_NAME

SMALL_RANDOM_NUM

set autotrace traceonly explain
select small_random_num
from random_data
order by small_random_num;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477
Card=1000000 Bytes=2000000)

1 0 SORT (ORDER BY) (Cost=7477 Card=1000000

Bytes=2000000) <------ Is this sort needed?
2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN'

(NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Search Discussions

  • Bobak, Mark at Feb 25, 2004 at 1:34 pm
    Dan,


    Only an INDEX FULL SCAN (walks the tree, does single block reads)
    provides sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block
    reads, discards branch blocks) does NOT provide sorted output.


    -Mark



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and
    a sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting

    A query (with an order by) is able to satisfy it's column list

    by scanning an index. This scan will return the rows in sorted order,
    but the query still executes a sort (confirmed by 10046 trace). Should
    not the result set from the fast full scan be correctly ordered? This
    would make the sort redundant, but very expensive in terms of response
    time.


    Table:
    random_data
    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME
    -----------------
    SMALL_RANDOM_NUM

    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477
    Card=1000000 Bytes=2000000)
    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000
    Bytes=2000000) <------ Is this sort needed?
    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN'

    (NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Mladen Gogala at Feb 25, 2004 at 1:45 pm
    Do you have any reference? Where can I find that in the literature?
    On 02/25/2004 02:37:33 PM, "Bobak, Mark" wrote:
    Dan,

    Only an INDEX FULL SCAN (walks the tree, does single block reads)
    provides sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block
    reads, discards branch blocks) does NOT provide sorted output.

    -Mark



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and
    a sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting


    A query (with an order by) is able to satisfy it's column list
    by scanning an index. This scan will return the rows in sorted order,
    but the query still executes a sort (confirmed by 10046 trace). Should
    not the result set from the fast full scan be correctly ordered? This
    would make the sort redundant, but very expensive in terms of response
    time.


    Table:
    random_data
    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME
    -----------------
    SMALL_RANDOM_NUM

    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477
    Card=1000000 Bytes=2000000)
    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000
    Bytes=2000000) <------ Is this sort needed?
    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN'
    (NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Daniel Fink at Feb 25, 2004 at 2:07 pm
    Let's see if I understand correctly. For an IFS or IRS, the
    blocks of an index are read (in single block read mode) in the
    order of their existence within the tree. For an IFFS, all
    blocks in the segment (below the HWM) are read in order of their
    'physical' existence within the segment. This is not the order
    of their existence within the tree, so the blocks are 'out of
    order'.

    Dan

    "Bobak, Mark" wrote:
    Dan,Only an INDEX FULL SCAN (walks the tree, does single
    block reads) provides sorted output.An INDEX FAST FULL SCAN
    (does not walk tree structure, does multi-block reads,
    discards branch blocks) does NOT provide sorted output.-Mark

    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is
    not, and a sense of humor was provided to console him for what
    he is." --Horace Walpole
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Tanel Põder at Feb 25, 2004 at 9:13 pm
    MessageThat's also the reason why index rebuild requires sorting, controversary to a myth that it doesn't...

    Tanel.

    Original Message -----
    From: Bobak, Mark
    To: oracle-l_at_freelists.org
    Sent: Wednesday, February 25, 2004 9:37 PM
    Subject: RE: Index scan and redundant sorting

    Dan,

    Only an INDEX FULL SCAN (walks the tree, does single block reads) provides sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block reads, discards branch blocks) does NOT provide sorted output.

    -Mark

    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Horace Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting

    A query (with an order by) is able to satisfy it's column list by scanning an index. This scan will return the rows in sorted order, but the query still executes a sort (confirmed by 10046 trace). Should not the result set from the fast full scan be correctly ordered? This would make the sort redundant, but very expensive in terms of response time.


    Table:

    random_data
    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME

    SMALL_RANDOM_NUM



    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477 Card=1000000 Bytes=2000000)
    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000 Bytes=2000000) <------ Is this sort needed?
    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN' (NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)

    ----------------------------------------------------------------

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Mladen Gogala at Feb 25, 2004 at 1:45 pm
    Daniel, If I'm not mistaken, keys are stored in index in sorted
    order so that sort appears unnecessary. Try fiddling around with hints
    like /*+ index_asc(random_data,IX_RD_SMALL_RN) */
    On 02/25/2004 02:34:00 PM, Daniel Fink wrote:
    A query (with an order by) is able to satisfy it's column list
    by scanning an index. This scan will return the rows in sorted
    order, but the query still executes a sort (confirmed by 10046
    trace). Should not the result set from the fast full scan be
    correctly ordered? This would make the sort redundant, but very
    expensive in terms of response time.


    Table:
    random_data
    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME
    -----------------
    SMALL_RANDOM_NUM

    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477
    Card=1000000 Bytes=2000000)
    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000
    Bytes=2000000) <------ Is this sort needed?
    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN'
    (NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jared.Still_at_radisys.com at Feb 25, 2004 at 1:59 pm
    The keys stored in a block are in sorted order.

    The blocks within an index are not necessarily in sorted order.

    Jared

    Mladen Gogala
    Sent by: oracle-l-bounce_at_freelists.org
    02/25/2004 11:43 AM
    Please respond to oracle-l



    To: oracle-l_at_freelists.org
    cc:
    Subject: Re: Index scan and redundant sorting

    Daniel, If I'm not mistaken, keys are stored in index in sorted
    order so that sort appears unnecessary. Try fiddling around with hints
    like /*+ index_asc(random_data,IX_RD_SMALL_RN) */
    On 02/25/2004 02:34:00 PM, Daniel Fink wrote:
    A query (with an order by) is able to satisfy it's column list
    by scanning an index. This scan will return the rows in sorted
    order, but the query still executes a sort (confirmed by 10046
    trace). Should not the result set from the fast full scan be
    correctly ordered? This would make the sort redundant, but very
    expensive in terms of response time.


    Table:
    random_data
    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME
    -----------------
    SMALL_RANDOM_NUM

    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477
    Card=1000000 Bytes=2000000)
    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000
    Bytes=2000000) <------ Is this sort needed?
    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN'
    (NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Bobak, Mark at Feb 25, 2004 at 2:13 pm
    Yep, you got it.



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and
    a sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 3:10 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    Let's see if I understand correctly. For an IFS or IRS, the

    blocks of an index are read (in single block read mode) in the order of
    their existence within the tree. For an IFFS, all blocks in the segment
    (below the HWM) are read in order of their 'physical' existence within
    the segment. This is not the order of their existence within the tree,
    so the blocks are 'out of order'.

    Dan

    "Bobak, Mark" wrote:

    Dan,Only an INDEX FULL SCAN (walks the tree, does
    single block reads) provides sorted output.An INDEX FAST FULL SCAN (does
    not walk tree structure, does multi-block reads, discards branch blocks)
    does NOT provide sorted output.-Mark

    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what

    he is not, and a sense of humor was provided to console him for what he
    is." --Horace Walpole

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Bobak, Mark at Feb 25, 2004 at 2:21 pm
    Mladen,

    A quick tahiti search yielded the following URL:
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opti
    mops.htm#51674

    -Mark

    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and
    a sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Mladen Gogala
    Sent: Wednesday, February 25, 2004 2:46 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    Do you have any reference? Where can I find that in the literature?
    On 02/25/2004 02:37:33 PM, "Bobak, Mark" wrote:
    Dan,

    Only an INDEX FULL SCAN (walks the tree, does single block reads)
    provides sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does
    multi-block
    reads, discards branch blocks) does NOT provide sorted output.

    -Mark



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and
    a sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting


    A query (with an order by) is able to satisfy it's column list
    by scanning an index. This scan will return the rows in sorted order,
    but the query still executes a sort (confirmed by 10046 trace). Should
    not the result set from the fast full scan be correctly ordered? This
    would make the sort redundant, but very expensive in terms of response
    time.


    Table:
    random_data
    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME
    -----------------
    SMALL_RANDOM_NUM

    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477
    Card=1000000 Bytes=2000000)
    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000
    Bytes=2000000) <------ Is this sort needed?
    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN'
    (NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Mladen Gogala at Feb 25, 2004 at 2:01 pm

    On 02/25/2004 03:00:17 PM, "Bobak, Mark" wrote:
    Mladen,

    A quick tahiti search yielded the following URL:
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opti
    mops.htm#51674
    Thanks Mark! You're a treasure!

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Niall Litchfield at Feb 25, 2004 at 5:35 pm
    That is my understanding too.

    -----Original Message-----
    From: AC.GWIA.oracle-l_at_freelists.org
    Sent: Wed 25/02/2004 20:10
    To: Daniel.Fink_at_Sun.COM; oracle-l_at_freelists.org
    Cc:
    Subject: Re: Index scan and redundant sorting

    Let's see if I understand correctly. For an IFS or IRS, the
    blocks of an index are read (in single block read mode) in the
    order of their existence within the tree. For an IFFS, all
    blocks in the segment (below the HWM) are read in order of their
    'physical' existence within the segment. This is not the order
    of their existence within the tree, so the blocks are 'out of
    order'.

    Dan

    "Bobak, Mark" wrote:
    Dan,Only an INDEX FULL SCAN (walks the tree, does single
    block reads) provides sorted output.An INDEX FAST FULL SCAN
    (does not walk tree structure, does multi-block reads,
    discards branch blocks) does NOT provide sorted output.-Mark

    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is
    not, and a sense of humor was provided to console him for what
    he is." --Horace Walpole
    This email contains information intended for
    the addressee only. It may be confidential
    and may be the subject of legal and/or
    professional privilege. Any dissemination,
    distribution, copyright or use of this
    communication without prior permission of
    the sender is strictly prohibited.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Powell, Mark D at Feb 26, 2004 at 8:18 am
    Here is an article hosted on the cooperative FAQ on Jonathan's site that
    demonstrates that Oracle sorts on an index rebuild and that an index rebuild
    will sometimes perform a full table scan rather than read the index:


    When I rebuild an index, I see Oracle doing a sort. Why should this be
    necessary, why doesn't it simply read the existing index ?<?xml:namespace
    prefix = o ns = "urn:schemas-microsoft-com:office:office" />

    http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html
    <http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html>


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Tanel Põder
    Sent: Wednesday, February 25, 2004 10:16 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    That's also the reason why index rebuild requires sorting, controversary to
    a myth that it doesn't...


    Tanel.


    Original Message -----
    From: Bobak, Mark
    To: oracle-l_at_freelists.org
    Sent: Wednesday, February 25, 2004 9:37 PM
    Subject: RE: Index scan and redundant sorting

    Dan,


    Only an INDEX FULL SCAN (walks the tree, does single block reads) provides
    sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block
    reads, discards branch blocks) does NOT provide sorted output.


    -Mark



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and a
    sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting

    A query (with an order by) is able to satisfy it's column list by scanning
    an index. This scan will return the rows in sorted order, but the query
    still executes a sort (confirmed by 10046 trace). Should not the result set
    from the fast full scan be correctly ordered? This would make the sort
    redundant, but very expensive in terms of response time.


    Table:
    random_data

    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME

    SMALL_RANDOM_NUM



    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477 Card=1000000
    Bytes=2000000)

    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000 Bytes=2000000)
    <------ Is this sort needed?

    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN' (NON-UNIQUE)

    (Cost=722 Card=1000000 Bytes=2000000)

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Tanel Põder at Feb 26, 2004 at 8:34 am
    MessageHowever, the first statement in this note is wrong and contradicts with the next one. When doing a fast full scan, Oracle will not traverse from root to leaves, it just reads the segment header block for the index, gets the extent map from there and scans all the extents belonging to index up to it's HWM.

    Tanel.

    Original Message -----
    From: Powell, Mark D
    To: 'oracle-l_at_freelists.org'
    Sent: Thursday, February 26, 2004 4:21 PM
    Subject: RE: Index scan and redundant sorting

    Here is an article hosted on the cooperative FAQ on Jonathan's site that demonstrates that Oracle sorts on an index rebuild and that an index rebuild will sometimes perform a full table scan rather than read the index:

    When I rebuild an index, I see Oracle doing a sort. Why should this be necessary, why doesn't it simply read the existing index ?

    http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Tanel Põder
    Sent: Wednesday, February 25, 2004 10:16 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    That's also the reason why index rebuild requires sorting, controversary to a myth that it doesn't...

    Tanel.

    Original Message -----
    From: Bobak, Mark
    To: oracle-l_at_freelists.org
    Sent: Wednesday, February 25, 2004 9:37 PM
    Subject: RE: Index scan and redundant sorting

    Dan,

    Only an INDEX FULL SCAN (walks the tree, does single block reads) provides sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block reads, discards branch blocks) does NOT provide sorted output.

    -Mark

    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Horace Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting

    A query (with an order by) is able to satisfy it's column list by scanning an index. This scan will return the rows in sorted order, but the query still executes a sort (confirmed by 10046 trace). Should not the result set from the fast full scan be correctly ordered? This would make the sort redundant, but very expensive in terms of response time.

    Table:
    random_data
    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME
    -----------------
    SMALL_RANDOM_NUM

    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477 Card=1000000 Bytes=2000000)
    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000 Bytes=2000000) <------ Is this sort needed?
    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN' (NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)

    ----------------------------------------------------------------

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Khedr, Waleed at Feb 26, 2004 at 8:40 am
    I agree, it's describing index full scan!


    Waleed

    -----Original Message-----
    From: Tanel Põder
    Sent: Thursday, February 26, 2004 9:37 AM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    However, the first statement in this note is wrong and contradicts with the
    next one. When doing a fast full scan, Oracle will not traverse from root to
    leaves, it just reads the segment header block for the index, gets the
    extent map from there and scans all the extents belonging to index up to
    it's HWM.


    Tanel.


    Original Message -----
    From: Powell, Mark D
    To: 'oracle-l_at_freelists.org'
    Sent: Thursday, February 26, 2004 4:21 PM
    Subject: RE: Index scan and redundant sorting

    Here is an article hosted on the cooperative FAQ on Jonathan's site that
    demonstrates that Oracle sorts on an index rebuild and that an index rebuild
    will sometimes perform a full table scan rather than read the index:


    When I rebuild an index, I see Oracle doing a sort. Why should this be
    necessary, why doesn't it simply read the existing index ?

    http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html
    <http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html>


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Tanel Põder
    Sent: Wednesday, February 25, 2004 10:16 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    That's also the reason why index rebuild requires sorting, controversary to
    a myth that it doesn't...


    Tanel.


    Original Message -----
    From: Bobak, Mark
    To: oracle-l_at_freelists.org
    Sent: Wednesday, February 25, 2004 9:37 PM
    Subject: RE: Index scan and redundant sorting

    Dan,


    Only an INDEX FULL SCAN (walks the tree, does single block reads) provides
    sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block
    reads, discards branch blocks) does NOT provide sorted output.


    -Mark



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and a
    sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting

    A query (with an order by) is able to satisfy it's column list by scanning
    an index. This scan will return the rows in sorted order, but the query
    still executes a sort (confirmed by 10046 trace). Should not the result set
    from the fast full scan be correctly ordered? This would make the sort
    redundant, but very expensive in terms of response time.


    Table:
    random_data

    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME

    SMALL_RANDOM_NUM



    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477 Card=1000000
    Bytes=2000000)

    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000 Bytes=2000000)
    <------ Is this sort needed?

    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN' (NON-UNIQUE)

    (Cost=722 Card=1000000 Bytes=2000000)

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Powell, Mark D at Feb 26, 2004 at 8:57 am
    Yes, l will submit a correction to the wording in the article.

    [>>] -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Khedr, Waleed
    Sent: Thursday, February 26, 2004 9:42 AM
    To: 'oracle-l_at_freelists.org'
    Subject: RE: Index scan and redundant sorting

    I agree, it's describing index full scan!


    Waleed

    -----Original Message-----
    From: Tanel Põder
    Sent: Thursday, February 26, 2004 9:37 AM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    However, the first statement in this note is wrong and contradicts with the
    next one. When doing a fast full scan, Oracle will not traverse from root to
    leaves, it just reads the segment header block for the index, gets the
    extent map from there and scans all the extents belonging to index up to
    it's HWM.


    Tanel.


    Original Message -----
    From: Powell, Mark D
    To: 'oracle-l_at_freelists.org'
    Sent: Thursday, February 26, 2004 4:21 PM
    Subject: RE: Index scan and redundant sorting

    Here is an article hosted on the cooperative FAQ on Jonathan's site that
    demonstrates that Oracle sorts on an index rebuild and that an index rebuild
    will sometimes perform a full table scan rather than read the index:


    When I rebuild an index, I see Oracle doing a sort. Why should this be
    necessary, why doesn't it simply read the existing index ?

    http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html
    <http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html>


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Tanel Põder
    Sent: Wednesday, February 25, 2004 10:16 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    That's also the reason why index rebuild requires sorting, controversary to
    a myth that it doesn't...


    Tanel.


    Original Message -----
    From: Bobak, Mark
    To: oracle-l_at_freelists.org
    Sent: Wednesday, February 25, 2004 9:37 PM
    Subject: RE: Index scan and redundant sorting

    Dan,


    Only an INDEX FULL SCAN (walks the tree, does single block reads) provides
    sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block
    reads, discards branch blocks) does NOT provide sorted output.


    -Mark



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and a
    sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting

    A query (with an order by) is able to satisfy it's column list by scanning
    an index. This scan will return the rows in sorted order, but the query
    still executes a sort (confirmed by 10046 trace). Should not the result set
    from the fast full scan be correctly ordered? This would make the sort
    redundant, but very expensive in terms of response time.


    Table:
    random_data

    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME

    SMALL_RANDOM_NUM



    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477 Card=1000000
    Bytes=2000000)

    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000 Bytes=2000000)
    <------ Is this sort needed?

    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN' (NON-UNIQUE)

    (Cost=722 Card=1000000 Bytes=2000000)

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Bobak, Mark at Feb 27, 2004 at 9:55 am
    Geez, who did such a shoddy job reviewing that FAQ entry??


    Oh wait, that was me...

    -----Original Message-----
    From: Powell, Mark D
    Sent: Thursday, February 26, 2004 10:00 AM
    To: 'oracle-l_at_freelists.org'
    Subject: RE: Index scan and redundant sorting

    Yes, l will submit a correction to the wording in the article.

    [>>] -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Khedr, Waleed
    Sent: Thursday, February 26, 2004 9:42 AM
    To: 'oracle-l_at_freelists.org'
    Subject: RE: Index scan and redundant sorting

    I agree, it's describing index full scan!


    Waleed

    -----Original Message-----
    From: Tanel Põder
    Sent: Thursday, February 26, 2004 9:37 AM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    However, the first statement in this note is wrong and contradicts with the next one. When doing a fast full scan, Oracle will not traverse from root to leaves, it just reads the segment header block for the index, gets the extent map from there and scans all the extents belonging to index up to it's HWM.


    Tanel.


    Original Message -----
    From: Powell, Mark D
    To: 'oracle-l_at_freelists.org'
    Sent: Thursday, February 26, 2004 4:21 PM
    Subject: RE: Index scan and redundant sorting

    Here is an article hosted on the cooperative FAQ on Jonathan's site that demonstrates that Oracle sorts on an index rebuild and that an index rebuild will sometimes perform a full table scan rather than read the index:


    When I rebuild an index, I see Oracle doing a sort. Why should this be necessary, why doesn't it simply read the existing index ?

    http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Tanel Põder
    Sent: Wednesday, February 25, 2004 10:16 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    That's also the reason why index rebuild requires sorting, controversary to a myth that it doesn't...


    Tanel.


    Original Message -----
    From: Bobak, Mark
    To: oracle-l_at_freelists.org
    Sent: Wednesday, February 25, 2004 9:37 PM
    Subject: RE: Index scan and redundant sorting

    Dan,


    Only an INDEX FULL SCAN (walks the tree, does single block reads) provides sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block reads, discards branch blocks) does NOT provide sorted output.


    -Mark



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Horace Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting

    A query (with an order by) is able to satisfy it's column list by scanning an index. This scan will return the rows in sorted order, but the query still executes a sort (confirmed by 10046 trace). Should not the result set from the fast full scan be correctly ordered? This would make the sort redundant, but very expensive in terms of response time.


    Table:
    random_data

    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME

    SMALL_RANDOM_NUM



    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477 Card=1000000 Bytes=2000000)
    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000 Bytes=2000000) <------ Is this sort needed?
    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN' (NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)

    ----------------------------------------------------------------

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Powell, Mark D at Feb 27, 2004 at 11:43 am
    Correction submitted. Added references to the Oracle Performance manual
    where topic is discussed. It could be a couple of weeks before the improved
    version replaces the original.


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Bobak, Mark
    Sent: Friday, February 27, 2004 10:58 AM
    To: oracle-l_at_freelists.org
    Subject: RE: Index scan and redundant sorting

    Geez, who did such a shoddy job reviewing that FAQ entry??


    Oh wait, that was me...

    -----Original Message-----
    From: Powell, Mark D
    Sent: Thursday, February 26, 2004 10:00 AM
    To: 'oracle-l_at_freelists.org'
    Subject: RE: Index scan and redundant sorting

    Yes, l will submit a correction to the wording in the article.

    [>>] -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Khedr, Waleed
    Sent: Thursday, February 26, 2004 9:42 AM
    To: 'oracle-l_at_freelists.org'
    Subject: RE: Index scan and redundant sorting

    I agree, it's describing index full scan!


    Waleed

    -----Original Message-----
    From: Tanel Põder
    Sent: Thursday, February 26, 2004 9:37 AM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    However, the first statement in this note is wrong and contradicts with the
    next one. When doing a fast full scan, Oracle will not traverse from root to
    leaves, it just reads the segment header block for the index, gets the
    extent map from there and scans all the extents belonging to index up to
    it's HWM.


    Tanel.


    Original Message -----
    From: Powell, Mark D
    To: 'oracle-l_at_freelists.org'
    Sent: Thursday, February 26, 2004 4:21 PM
    Subject: RE: Index scan and redundant sorting

    Here is an article hosted on the cooperative FAQ on Jonathan's site that
    demonstrates that Oracle sorts on an index rebuild and that an index rebuild
    will sometimes perform a full table scan rather than read the index:


    When I rebuild an index, I see Oracle doing a sort. Why should this be
    necessary, why doesn't it simply read the existing index ?

    http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html
    <http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html>


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Tanel Põder
    Sent: Wednesday, February 25, 2004 10:16 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index scan and redundant sorting

    That's also the reason why index rebuild requires sorting, controversary to
    a myth that it doesn't...


    Tanel.


    Original Message -----
    From: Bobak, Mark
    To: oracle-l_at_freelists.org
    Sent: Wednesday, February 25, 2004 9:37 PM
    Subject: RE: Index scan and redundant sorting

    Dan,


    Only an INDEX FULL SCAN (walks the tree, does single block reads) provides
    sorted output.
    An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block
    reads, discards branch blocks) does NOT provide sorted output.


    -Mark



    Mark J. Bobak
    Oracle DBA
    ProQuest Company
    Ann Arbor, MI
    "Imagination was given to man to compensate him for what he is not, and a
    sense of humor was provided to console him for what he is." --Horace
    Walpole

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, February 25, 2004 2:34 PM
    To: oracle-l_at_freelists.org
    Subject: Index scan and redundant sorting

    A query (with an order by) is able to satisfy it's column list by scanning
    an index. This scan will return the rows in sorted order, but the query
    still executes a sort (confirmed by 10046 trace). Should not the result set
    from the fast full scan be correctly ordered? This would make the sort
    redundant, but very expensive in terms of response time.


    Table:
    random_data

    Name Null? Type
    ------------------- -------- -------------------
    REC_NO NOT NULL NUMBER
    INSERT_TEXT VARCHAR2(200)
    INSERT_DATE DATE
    LARGE_RANDOM_NUM NUMBER
    SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST
    ROWID_BLOCKNUM NUMBER
    ROWID_ROWNUM NUMBER

    select column_name
    from user_ind_columns
    where index_name = 'IX_RD_SMALL_RN'

    COLUMN_NAME

    SMALL_RANDOM_NUM



    set autotrace traceonly explain
    select small_random_num
    from random_data
    order by small_random_num;

    Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477 Card=1000000
    Bytes=2000000)

    1 0 SORT (ORDER BY) (Cost=7477 Card=1000000 Bytes=2000000)
    <------ Is this sort needed?

    2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN' (NON-UNIQUE)

    (Cost=722 Card=1000000 Bytes=2000000)

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 25, '04 at 1:30p
activeFeb 27, '04 at 11:43a
posts17
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase