SQLMDX

Eventually it's all about data

fetch first … percent

Posted by sqlmdx on December 19, 2015

New syntax for so called “Top N” queries was introduced in Oracle 12c.

It can be used to return either fixed number of rows or specified percent of rows in a table.
Obviously latter requires to know the total number of rows to return the result so let’s check what is happening under the hood in this case.

create table t as select rownum id, lpad(' ', 1000, ' ') padding from dual connect by level <= 100000;
create index i on t(id);

Final query in 10053 trace looks as following (formatted text)
(procedure dbms_utility.expand_sql_text could be used to return final text as well)

select "from$_subquery$_002"."ID"      "ID",
       "from$_subquery$_002"."PADDING" "PADDING"
  from (select "T"."ID" "ID",
               "T"."PADDING" "PADDING",
               "T"."ID" "rowlimit_$_  0",
               row_number() over(order by "T"."ID") "rowlimit_$$_rownumber",
               count(*) over() "rowlimit_$$_total"
          from "T" "T") "from$_subquery$_002"
 where "from$_subquery$_002"."rowlimit_$$_rownumber" <=
       ceil("from$_subquery$_002"."rowlimit_$$_total" * 0.01 / 100)
 order by "from$_subquery$_002"."rowlimit_$_  0"

Results of SQL trace (event 10046) are below

********************************************************************************

select *
  from t
 order by id
 fetch first 0.01 percent rows only

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.78       5.19      12500      14297          2          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.78       5.19      12500      14297          2          10

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 103  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  VIEW  (cr=14297 pr=12500 pw=12500 time=2793113 us cost=25015 size=55400000 card=100000)
 100000   WINDOW SORT (cr=14297 pr=12500 pw=12500 time=40556352 us cost=25015 size=100600000 card=100000)
 100000    TABLE ACCESS FULL T (cr=14297 pr=0 pw=0 time=73842 us cost=3918 size=100600000 card=100000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  direct path write temp                        383        0.13          2.39
  direct path read temp                        1851        0.05          1.93
  SQL*Net message from client                     2        3.32          3.32
********************************************************************************

As you can see temp tablespace was used to perform WINDOW SORT operation.
While sorting data for analytic function Oracle apparently needs to order data for all columns in record set.
To avoid sorting unnecessary data we can apply sort only to key column to get row number and then join on rowid.

********************************************************************************

select t.*
  from (select t.rowid row_id,
               row_number() over(order by t.id) rn,
               count(*) over() cnt
          from t) t0,
       t
 where t0.row_id = t.rowid
   and t0.rn <= t0.cnt * 0.01 / 100
 order by t.id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.21          0      28594          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.22          0      28594          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT ORDER BY (cr=28594 pr=0 pw=0 time=218790 us cost=35327 size=104400000 card=100000)
     10   HASH JOIN  (cr=28594 pr=0 pw=0 time=153212 us cost=13439 size=104400000 card=100000)
     10    VIEW  (cr=14297 pr=0 pw=0 time=54365 us cost=4470 size=3800000 card=100000)
 100000     WINDOW SORT (cr=14297 pr=0 pw=0 time=146970 us cost=4470 size=1700000 card=100000)
 100000      TABLE ACCESS FULL T (cr=14297 pr=0 pw=0 time=57786 us cost=3918 size=1700000 card=100000)
 100000    TABLE ACCESS FULL T (cr=14297 pr=0 pw=0 time=50782 us cost=3918 size=100600000 card=100000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        3.81          3.81
********************************************************************************

You can see there are no disk IO since data is cached.
Going forward it’s possible to use only one function – cume_dist.
But anyway Oracle needs to do a full scan twice and window sort.

********************************************************************************

select t.*
  from (select t.rowid row_id, cume_dist() over(order by t.id) rn_pct from t) t0,
       t
 where t0.row_id = t.rowid
   and t0.rn_pct <= 0.01 / 100
 order by t.id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.24       0.25          0      28594          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.26       0.25          0      28594          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT ORDER BY (cr=28594 pr=0 pw=0 time=250442 us cost=35002 size=103100000 card=100000)
     10   HASH JOIN  (cr=28594 pr=0 pw=0 time=198309 us cost=13376 size=103100000 card=100000)
     10    VIEW  (cr=14297 pr=0 pw=0 time=58905 us cost=4470 size=2500000 card=100000)
 100000     WINDOW SORT (cr=14297 pr=0 pw=0 time=192864 us cost=4470 size=1700000 card=100000)
 100000      TABLE ACCESS FULL T (cr=14297 pr=0 pw=0 time=58842 us cost=3918 size=1700000 card=100000)
 100000    TABLE ACCESS FULL T (cr=14297 pr=0 pw=0 time=43720 us cost=3918 size=100600000 card=100000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        4.75          4.79
********************************************************************************

Another approach to optimize sort operation would be achieving WINDOW SORT PUSHED RANK.

var n number
exec select count(*) into :n from t;
********************************************************************************

select t.id, t.padding
  from (select t.*, row_number() over(order by t.id) rn from t) t
 where t.rn <= :n * 0.01 / 100
 order by t.id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.17       0.16          0      14297          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.17       0.16          0      14297          0          10

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  VIEW  (cr=14297 pr=0 pw=0 time=163506 us cost=25015 size=52800000 card=100000)
     10   WINDOW SORT PUSHED RANK (cr=14297 pr=0 pw=0 time=163493 us cost=25015 size=100600000 card=100000)
 100000    TABLE ACCESS FULL T (cr=14297 pr=0 pw=0 time=58456 us cost=3918 size=100600000 card=100000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        4.12          4.12
********************************************************************************

Please note that such approach works only if count specified as bind variable.
Using PL/SQL function or scalar subquery leads to WINDOW SORT instead of WINDOW SORT PUSHED RANK.
However much better results could be achieved if id is not nullable column so Oracle can use an index.

alter table t modify (id not null);
********************************************************************************

select t.id, t.padding
  from (select t.*, row_number() over(order by t.id) rn from t) t
 where t.rn <= :n * 0.01 / 100
 order by t.id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          6          0          10

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  VIEW  (cr=6 pr=0 pw=0 time=76 us cost=14513 size=52800000 card=100000)
     10   WINDOW NOSORT STOPKEY (cr=6 pr=0 pw=0 time=66 us cost=14513 size=100600000 card=100000)
     11    TABLE ACCESS BY INDEX ROWID T (cr=6 pr=0 pw=0 time=48 us cost=14513 size=100600000 card=100000)
     11     INDEX FULL SCAN I (cr=3 pr=0 pw=0 time=214 us cost=224 size=0 card=100000)(object id 91930)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        3.01          3.03
********************************************************************************

Coming back to queries with join by rowid

********************************************************************************

select --+ use_nl(t0 t)
 t.*
  from (select t.rowid row_id, cume_dist() over(order by t.id) rn_pct from t) t0,
       t
 where t0.row_id = t.rowid
   and t0.rn_pct <= 0.01 / 100
 order by t.id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.14       0.13          0        225          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.14       0.14          0        225          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT ORDER BY (cr=225 pr=0 pw=0 time=138794 us cost=121868 size=103100000 card=100000)
     10   NESTED LOOPS  (cr=225 pr=0 pw=0 time=33207 us cost=100242 size=103100000 card=100000)
     10    VIEW  (cr=223 pr=0 pw=0 time=33143 us cost=224 size=2500000 card=100000)
 100000     WINDOW BUFFER (cr=223 pr=0 pw=0 time=154488 us cost=224 size=1700000 card=100000)
 100000      INDEX FULL SCAN I (cr=223 pr=0 pw=0 time=33406 us cost=224 size=1700000 card=100000)(object id 91930)
     10    TABLE ACCESS BY USER ROWID T (cr=2 pr=0 pw=0 time=25 us cost=1 size=1006 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        2.08          2.08
********************************************************************************

So performance was dramatically improved (exactly the same applies to query with row_number & count).
And back to original query

********************************************************************************

select *
  from t
 order by id
 fetch first 0.01 percent rows only

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.71       9.07      14286      14509          2          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.71       9.08      14286      14509          2          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  VIEW  (cr=14509 pr=14286 pw=14286 time=5021911 us cost=14513 size=55400000 card=100000)
 100000   WINDOW BUFFER (cr=14509 pr=14286 pw=14286 time=214144390 us cost=14513 size=100600000 card=100000)
 100000    TABLE ACCESS BY INDEX ROWID T (cr=14509 pr=0 pw=0 time=142117 us cost=14513 size=100600000 card=100000)
 100000     INDEX FULL SCAN I (cr=223 pr=0 pw=0 time=30211 us cost=224 size=0 card=100000)(object id 91931)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  direct path write temp                        363        0.49          4.59
  direct path read temp                        1246        0.09          3.75
  SQL*Net message from client                     2        2.83          2.83
********************************************************************************

So performance of original query was not improved at all!

Thus the best way to get result would be using
either two queries – to get total and to apply row_number
or additional join by rowid.

Also adding not null constraint may significantly improve performance because Oracle can use index to calculate count.
Performance of original query using new syntax is unacceptable and cannot be tuned which makes quite questionable using row limiting clause.

Without not null constraint the best query is the one with additional join.
Because it may perform only one full table scan by adding use_nl hint and allows effective execution of sort operation.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: