order_book example

test-case-out.sql

SQL> ;
  1  select
  2      rownum as n,
  3      quantity
  4  from
  5  (
  6      select
  7          ob.order_book_id,
  8          v.quantity
  9      from order_book ob
 10          ,lateral (
 11              select--+ no_decorrelate
 12                 sum(obd.quantity)   as quantity
 13              from order_book_details obd
 14              where ob.order_book_id = obd.order_book_id
 15              and obd.order_type = 'bid'
 16           ) v
 17      where ob.currency_pair_id = 'BTC_USDT'
 18      order by ob.order_book_id desc
 19  )
 20* where rownum <= 10
SQL> /

         N   QUANTITY
---------- ----------
         1        300
         2        300
         3        300
         4        300
         5        300
         6        300
         7        300
         8        300
         9        300
        10        300

10 rows selected.

SQL> @last

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5prjt5vng1kv6, child number 1
-------------------------------------
select     rownum as n,     quantity from (     select
ob.order_book_id,         v.quantity     from order_book ob
,lateral (             select--+ no_decorrelate
sum(obd.quantity)   as quantity             from order_book_details obd
            where ob.order_book_id = obd.order_book_id             and
obd.order_type = 'bid'          ) v     where ob.currency_pair_id =
'BTC_USDT'     order by ob.order_book_id desc ) where rownum <= 10

Plan hash value: 1508068250

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |      1 |        |       |    33 (100)|          |     10 |00:00:00.01 |      23 |
|*  1 |  COUNT STOPKEY                 |                       |      1 |        |       |            |          |     10 |00:00:00.01 |      23 |
|   2 |   VIEW                         |                       |      1 |     10 |   130 |    33   (0)| 00:00:01 |     10 |00:00:00.01 |      23 |
|   3 |    NESTED LOOPS                |                       |      1 |     10 |   300 |    33   (0)| 00:00:01 |     10 |00:00:00.01 |      23 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_ORDER_BOOK         |      1 |  50000 |   830K|     3   (0)| 00:00:01 |     10 |00:00:00.01 |       4 |
|   5 |     VIEW                       | VW_LAT_2131DCCF       |     10 |      1 |    13 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |      19 |
|   6 |      SORT AGGREGATE            |                       |     10 |      1 |    14 |            |          |     10 |00:00:00.01 |      19 |
|*  7 |       INDEX RANGE SCAN         | IX_ORDER_BOOK_DETAILS |     10 |      1 |    14 |     3   (0)| 00:00:01 |     30 |00:00:00.01 |      19 |
--------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$2 / from$_subquery$_001@SEL$1
   3 - SEL$2
   4 - SEL$2 / OB@SEL$2
   5 - SEL$3 / V@SEL$2
   6 - SEL$3
   7 - SEL$3 / OBD@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   4 - access("OB"."CURRENCY_PAIR_ID"='BTC_USDT')
   7 - access("OB"."ORDER_BOOK_ID"="OBD"."ORDER_BOOK_ID" AND "OBD"."ORDER_TYPE"='bid')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "QUANTITY"[NUMBER,22], ROWNUM[8]
   2 - "QUANTITY"[NUMBER,22]
   3 - "V"."QUANTITY"[NUMBER,22]
   4 - "OB"."ORDER_BOOK_ID"[NUMBER,22]
   5 - "V"."QUANTITY"[NUMBER,22]
   6 - (#keys=0) SUM("OBD"."QUANTITY")[22]
   7 - "OBD"."QUANTITY"[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   6 -  SEL$3
           -  no_decorrelate


61 rows selected.

test-case.sql

create table order_book as
select 
  n order_book_id,
  decode(mod(n,2),1,'BTC_USDT','something else') currency_pair_id,
  rpad('x',20,'x') padding
from xmltable('1 to 100000' columns n for ordinality)
/
create table order_book_details as
select 
  n order_book_id,
  decode(mod(n,2),1,'bid','not bid') order_type,
  100 quantity
from xmltable('1 to 100000, 1 to 100000, 1 to 100000' columns n int path '.')
/
create index ix_order_book on order_book(currency_pair_id, order_book_id)
/
create index ix_order_book_details on order_book_details(order_book_id,order_type,quantity)
/
select 
    rownum as n,
    quantity
from
(
    select
        ob.order_book_id, 
        v.quantity
    from order_book ob 
        ,lateral (
            select sum(obd.quantity)   as quantity
            from order_book_details obd
            where ob.order_book_id = obd.order_book_id
            and obd.order_type = 'bid'
         ) v
    where ob.currency_pair_id = 'BTC_USDT' 
    order by ob.order_book_id desc
)
where rownum <= 10;