It seems like the use of the "first" keyword may be weighted too
much in some way, causing a plan that's generated to use an
index that prevents a sort, but ends up actually causing the
server to have to examine many more rows than a different query
plan.
Here's 2 query plans, 1 using first and order by and the 2nd
just using order by, and from the explain statistics you can see
the 1st query takes longer and has to examine many more rows
even though it's using the "first" keyword.
Here's the table schema:
create table mytab2
(
rec_key serial not null ,
process_dtime datetime year to fraction(3)
) in data lock mode row;
create index ix_mytab_1 on mytab2 (process_dtime) ;
create unique index pux_mytab on mytab2 (rec_key) ;
Then 1 million rows are put into the table, and here is the 1st
query with first and order by that appears to be taking a bad
path:
QUERY: (OPTIMIZATION TIMESTAMP: 05-13-2020 11:21:38)
------
select skip 10 first 20 rec_key
from mytab2
where process_dtime is null
order by rec_key
Estimated Cost: 134
Estimated # of Rows Returned: 10496
1) mytab2: INDEX PATH
Filters: mytab2.process_dtime IS NULL
(1) Index Name: pux_mytab
Index Keys: rec_key (Serial, fragments: ALL)
Table map :
----------------------------
Internal name Table name
----------------------------
t1 mytab2
type table rows_prod est_rows rows_scan time
est_cost
----------------------------------------------------------------
---
scan t1 30 10496 989534 00:00.72 135
So in the bad plan we see the scan has to look at 989,534 of the
1 million rows to satisfy the skip/first to get 30 rows.
2nd query that leaves off skip/first and takes different path
that makes server examine much fewer rows, but has to perform
sort:
QUERY: (OPTIMIZATION TIMESTAMP: 05-13-2020 11:21:39)
------
select rec_key
from mytab2
where process_dtime is null
order by rec_key
Estimated Cost: 6110
Estimated # of Rows Returned: 10496
Temporary Files Required For: Order By
1) mytab2: INDEX PATH
(1) Index Name: ix_mytab_1
Index Keys: process_dtime (Serial, fragments: ALL)
Lower Index Filter: mytab2.process_dtime IS NULL
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 mytab2
type table rows_prod est_rows rows_scan time
est_cost
----------------------------------------------------------------
---
scan t1 10496 10496 10496 00:00.00
1747
type rows_sort est_rows rows_cons time est_cost
------------------------------------------------------------
sort 10496 10496 10496 00:00.01 4363
This case the server only has to examine 10496 rows and then
sort those rows. |
