This demonstrates patch for Bug# 22174392. Oracle Database 12.2.0.1 was used in the demonstration.

It improves the costing of FIRST ROWS type queries so that the optimizer makes better use of indexes to sort returned rows.

This is important for applications that return rows (in sorted order) to users that paginate through the result set.

See scripts before.sql and after.sql - spooled 'lst' output files are provided.

Compare the cost in the before and after versions, and also compare the differences in the resulting plans. The costs in the after example (for the FETCH FIRST) are must lower and compare favorably with the ROWNUM queries.

DISCLAIMER:

<br/>-- These scripts are provided for educational purposes only.

<br/>-- They are NOT supported by Oracle World Wide Technical Support.

<br/>-- The scripts have been tested and they appear to work as intended.

<br/>-- You should always run scripts on a test instance.

<br/>

WARNING:

<br/>-- The scripts will drop a table called "T" - use on test database only

@@fixon

set pagesize 1000 linesize 350 trims on

column owner format a10

column edition_name format a10

column object_name format a20

column subobject_name format a20

set timing on

select * from

( select *

from t

order by object_id desc )

where rownum <= 10;

@plan

select * from

( select *

from t

order by object_id desc )

where rownum <= 10;

@plan

select *

from t

order by object_id asc

fetch first 10 rows only;

@plan

select *

from t

order by object_id asc

fetch first 10 rows only;

@plan

select *

from t

order by object_id asc

offset 10 rows fetch first 10 rows only;

@plan

set pagesize 1000 linesize 350 trims on

column owner format a10

column edition_name format a10

column object_name format a20

column subobject_name format a20

set timing on

select * from

( select *

from t

order by object_id desc )

where rownum <= 10;

@plan

select * from

( select *

from t

order by object_id desc )

where rownum <= 10;

@plan

select *

from t

order by object_id asc

fetch first 10 rows only;

@plan

select *

from t

order by object_id asc

fetch first 10 rows only;

@plan

select *

from t

order by object_id asc

offset 10 rows fetch first 10 rows only;

@plan

sqlplus adhoc/adhoc

alter session set "_fix_control"='22174392:OFF';

alter session set "_fix_control"='22174392:ON';

column plan_table_output format a100

SELECT *

FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL -PREDICATE'));