Re: ROWNUM & server performance

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 11 Nov 2005 07:28:52 -0800
Message-ID: <1131722932.356902.262950@g47g2000cwa.googlegroups.com>

Patrick, run an explain plan on the SQL statement. You will probably see a reference to something called "count stopkey".

> EXPLAIN PLAN SET statement_id = 'mpowel01' FOR
2 select item_no from item_master where rownum < 100 3 /

Explained.

> rem
> rem
> set echo off

QUERY_PLAN

      COST CARDINALITY
---------- -----------
SELECT STATEMENT
        98 99
  2.1 COUNT STOPKEY
    3.1 INDEX FAST FULL SCAN ITEM_MASTER_PRIME UNIQUE         98 459991

How Oracle performs the task will depend on the CBO but you can extect Oracle to stop once it has enough data.

HTH -- Mark D Powell -- Received on Fri Nov 11 2005 - 09:28:52 CST