Limited size of Resultset

[DGRFL]

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

[jmodic]

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]

[DGRFL]

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 ????