SELECT count(1) INTO result FROM (
    SELECT s_w_id, s_i_id, s_quantity
        FROM bmsql_stock
        WHERE s_w_id = in_w_id AND s_quantity < in_threshold AND s_i_id IN (
            SELECT ol_i_id
                FROM bmsql_district
                JOIN bmsql_order_line ON ol_w_id = d_w_id
                AND ol_d_id = d_id
                AND ol_o_id >= d_next_o_id - 20
                AND ol_o_id < d_next_o_id
                WHERE d_w_id = in_w_id AND d_id = in_d_id
        )
);

create cluster bmsql_stock_cluster (
  s_w_id integer,
  s_i_id integer
)
single table
hashkeys 300000000
hash is ( (s_i_id-1) * 3000 + s_w_id-1 )
size 270
pctfree 0 initrans 2 maxtrans 2
storage (buffer_pool keep) parallel (degree 96);

create table bmsql_stock (
  s_w_id       integer       not null,
  s_i_id       integer       not null,
  s_quantity   integer,
  s_ytd        integer,
  s_order_cnt  integer,
  s_remote_cnt integer,
  s_data       varchar(50),
  s_dist_01    char(24),
  s_dist_02    char(24),
  s_dist_03    char(24),
  s_dist_04    char(24),
  s_dist_05    char(24),
  s_dist_06    char(24),
  s_dist_07    char(24),
  s_dist_08    char(24),
  s_dist_09    char(24),
  s_dist_10    char(24)
)
cluster bmsql_stock_cluster(
  s_w_id, s_i_id
);

create unique index bmsql_stock_pkey
  on bmsql_stock (s_i_id, s_w_id)
  parallel 32
  pctfree 1 initrans 3
  compute statistics;

create cluster bmsql_district_cluster (
  d_id integer,
  d_w_id integer
)
single table
hashkeys 30000
hash is ( (((d_w_id-1)*10)+d_id-1) )
size 3496
initrans 4
storage (buffer_pool default) parallel (degree 32);

create table bmsql_district (
  d_id         integer       not null,
  d_w_id       integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  d_next_o_id  integer,
  d_name       varchar(10),
  d_street_1   varchar(20),
  d_street_2   varchar(20),
  d_city       varchar(20),
  d_state      char(2),
  d_zip        char(9)
)
cluster bmsql_district_cluster(
  d_id, d_w_id
);

create unique index bmsql_district_pkey
  on bmsql_district (d_w_id, d_id)
  pctfree 5 initrans 3
  parallel 1
  compute statistics;

create table bmsql_order_line (
  ol_w_id         integer   not null,
  ol_d_id         integer   not null,
  ol_o_id         integer   sort,
  ol_number       integer   sort,
  ol_i_id         integer   not null,
  ol_delivery_d   timestamp,
  ol_amount       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24)
);

create unique index bmsql_order_line_pkey
  on bmsql_order_line (ol_w_id, ol_d_id, ol_o_id, ol_number)
  compute statistics;

Latch Name	Get Requests	Pct Get Miss	Avg Slps /Miss	Wait Time (s)	NoWait Requests
// before
multiblock read objects	42,906	0.24	0.00	0	0	
// after
multiblock read objects	302,570,536	87.49	0.04	22385	0	

Latch Name	Get Requests	Misses	Sleeps	Spin Gets
multiblock read objects	302,570,536	264,712,892	11,385,692	254,114,619

Latch Name	Where	NoWait Misses	Sleeps	Waiter Sleeps
multiblock read objects	kcbzibmlt	0	5,886,699	5,927,472
multiblock read objects	kcbzibmlt: normal mbr free	0	5,498,253	5,457,785

Execution Plan
----------------------------------------------------------
Plan hash value: 3065769087

-----------------------------------------------------------------------------------------------------------
| Id  | Operation			  | Name		  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		  |			  |	1 |    22 |   181   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE 		  |			  |	1 |    22 |	       |	  |
|   2 |   NESTED LOOPS			  |			  |	  |	  |	       |	  |
|   3 |    NESTED LOOPS 		  |			  |  1452 | 31944 |   181   (1)| 00:00:03 |
|   4 |     VIEW			  | VW_NSO_1		  |  1639 | 21307 |	9   (0)| 00:00:01 |
|   5 |      HASH UNIQUE		  |			  |  1639 | 37697 |	       |	  |
|   6 |       NESTED LOOPS		  |			  |  1639 | 37697 |	9   (0)| 00:00:01 |
|*  7 |        TABLE ACCESS HASH	  | BMSQL_DISTRICT	  |	1 |    11 |	1   (0)| 00:00:01 |
|   8 |        TABLE ACCESS BY INDEX ROWID| BMSQL_ORDER_LINE	  |  1509 | 18108 |	8   (0)| 00:00:01 |
|*  9 | 	INDEX RANGE SCAN	  | BMSQL_ORDER_LINE_PKEY |   154 |	  |	3   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN		  | BMSQL_STOCK_PKEY	  |	1 |	  |	0   (0)| 00:00:01 |
|* 11 |    TABLE ACCESS BY INDEX ROWID	  | BMSQL_STOCK 	  |	1 |	9 |	0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   7 - access("BMSQL_DISTRICT"."D_ID"=10 AND "BMSQL_DISTRICT"."D_W_ID"=10)
   9 - access("OL_W_ID"=10 AND "OL_D_ID"=10 AND "OL_O_ID">="D_NEXT_O_ID"-20 AND
	      "OL_O_ID"<"D_NEXT_O_ID")
  10 - access("S_I_ID"="OL_I_ID" AND "S_W_ID"=10)
  11 - filter("S_QUANTITY"<10)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	828  consistent gets
	  2  physical reads
	  0  redo size
	525  bytes sent via SQL*Net to client
	524  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

|*  9 | 	INDEX RANGE SCAN	  | BMSQL_ORDER_LINE_PKEY |   154 |	  |	3   (0)| 00:00:01 |