DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Limited size of Resultset

  1. #1
    Join Date
    Jul 2000
    Posts
    11
    Hello,

    Our programs generate SQL statements on a huge table (1.9M) resulting in a huge resultset :; we are interested only in the first 100's records.

    I try to limit the size of the resultset with a rownum < 100 clause : I need to use the following syntax;

    select a.* from
    (select /*+index(cthndl cthndl9) */ * from
    COSMOS.CTHNDL
    WHERE CNID03 >'TOLU '
    ORDER BY CNID03 ASC, HDDT03 DESC, HDTD03 DESC) a where rownum < 100;

    This Inline View is needed because otherwise I do not get the first 100 records in the ordered sequence.

    The autotrace tells me :
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=148597 Card=261225 Bytes=176588100)
    1 0 COUNT (STOPKEY)
    2 1 VIEW (Cost=148597 Card=261225 Bytes=176588100)
    3 2 SORT (ORDER BY STOPKEY) (Cost=148597 Card=261225 Bytes =109453275)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CTHNDL' (Cost=123661 Card=261225 Bytes=109453275)
    5 4 INDEX (RANGE SCAN) OF 'CTHNDL9' (NON-UNIQUE) (Cost=1262 Card=261225)

    Statistics
    ----------------------------------------------------------
    3273 recursive calls
    1 db block gets
    121255 consistent gets
    26925 physical reads
    0 redo size
    21557 bytes sent via SQL*Net to client
    1091 bytes received via SQL*Net from client
    8 SQL*Net roundtrips to/from client
    7 sorts (memory)
    0 sorts (disk)
    99 rows processed

    It seems that the TABLE ACCESS (BY INDEX ROWID) is costing me very much.

    eg. CNID > 'TOLU' 250.000 records 8 seconds
    CNID > 'EISU' 1.6 M records 57 seconds

    The time needed is proportional to the number of records found in the index range scan.

    However the index has an exact match with the order by clause.

    My questions :

    Is there a possibility to have the STOPKEY in the INDEX RANGE SCAN, reducing the physical reads ?

    In other words, how can I tune this statement further ?

    Regards,

    Luc

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    In both of your cases for different WHERE CNID03 > 'SOMETHING' conditions your query returns very large part of your table (250K and 1.6M rows out of 1.9M rows). If most of your WHERE condition returns such large portion of the table you should not force the use of your index, you'd better do something to *prevent* the use of this index! With full table scan and the proper setting of DB_FILE_MULTIBLOCK_READ_COUNT parameter your query will perform much better.

    Also, what do you mean by "the index has an exact match with the order by clause". Is it the function-based index? Or how else can you get "CNID03 ASC, HDDT03 DESC, HDTD03 DESC" order from the index without performing an explicit sort?

    [Edited by jmodic on 12-04-2001 at 02:47 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jul 2000
    Posts
    11
    Hello,

    With an exact match, I indeed mean that the Column sequenc in the create index statement is (CNID03 ASC, HDDT03 DESC, HDTD03 DESC) just like in the order by clause.

    It is a pity that the TABLE access by rowid is not stopped by a STOPKEY. So I began to experiment :

    If I retrieve in first instance all rowid, from the requested query, and them look up these values in the actual table, I get :

    select * from COSMOS.CTHNDL
    where rowid in
    (select a.rowid from
    (select /*+index(cthndl cthndl9) */ rowid from COSMOS.CTHNDL
    WHERE CNID03 >'TOLU '
    ORDER BY CNID03 ASC, HDDT03 DESC, HDTD03 DESC
    ) a
    where rownum < 100
    )


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=60949 Card=261225 Bytes=113110425)
    1 0 HASH JOIN (Cost=60949 Card=261225 Bytes=113110425)
    2 1 VIEW OF 'VW_NSO_1' (Cost=3299 Card=261225 Bytes=1828575)
    3 2 SORT (UNIQUE) (Cost=3299 Card=261225 Bytes=1828575)
    4 3 COUNT (STOPKEY)
    5 4 VIEW (Cost=2678 Card=261225 Bytes=1828575)
    6 5 SORT (ORDER BY STOPKEY) (Cost=2678 Card=261225 Bytes=7314300)
    7 6 INDEX (RANGE SCAN) OF 'CTHNDL9' (NON-UNIQUE) (Cost=1262 Card=261225 Bytes=7314300)
    8 1 TABLE ACCESS (FULL) OF 'CTHNDL' (Cost=21144 Card=1954399 Bytes=832573974)

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    112 db block gets
    140515 consistent gets
    100642 physical reads
    0 redo size
    21958 bytes sent via SQL*Net to client
    1091 bytes received via SQL*Net from client
    8 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    99 rows processed

    This leads to more physical reads, but reduces the cost. By the way, can I compare costs between 2 statements?

    Because the SQL statements are generated (emulation of ISAM through SQL), we will have lots of statements like these with many huge resultsets, (most of the records are not needed). So I want to reduce the physical reads, in order to have a better response time.


    If I limit myself to the fetch of the rowid's, I get :
    select a.rowid from
    (select /*+index(cthndl cthndl9) */ rowid from
    COSMOS.CTHNDL
    WHERE CNID03 >'TOLU '
    ORDER BY CNID03 ASC, HDDT03 DESC,
    HDTD03 DESC) a
    where rownum < 100


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2678 Card=261225 Bytes=1828575)
    1 0 COUNT (STOPKEY)
    2 1 VIEW (Cost=2678 Card=261225 Bytes=1828575)
    3 2 SORT (ORDER BY STOPKEY) (Cost=2678 Card=261225 Bytes=7314300)
    4 3 INDEX (RANGE SCAN) OF 'CTHNDL9' (NON-UNIQUE) (Cost=1262 Card=261225 Bytes=7314300)


    Statistics
    ----------------------------------------------------------
    8 recursive calls
    0 db block gets
    1241 consistent gets
    0 physical reads
    0 redo size
    3857 bytes sent via SQL*Net to client
    1091 bytes received via SQL*Net from client
    8 SQL*Net roundtrips to/from client
    3 sorts (memory)
    0 sorts (disk)
    99 rows processed

    The physical reads disappeared because everything is in memory already.

    This seems a much faster solution (if I try it with other key values also). Afterwards I need to perform for each record a

    select * from COSMOS.CTHNDL where rowid = :1

    This will increase the number of roundtrips, but perhaps it makes the application 'acceptable'.

    Can we combine both last statements in one statement without the FULL table scan ????



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width