Oracle执行计划和SQL如下：
默认情况走hash join
alter SESSION SET statistics_level = all;
select * from
( select rownum as indexindex, a.* from
( select
(to_char(to_date(a.init_date, 'YYYYMMDD'), 'YYYY-MM-DD')) as init_date ,
(case when length(a.fund_account) >= 0 then a.fund_account else lpad(a.fund_account, 0, '0') end) as fund_account ,
(a.futu_code) as futu_code ,
(a.futu_business_id) as futu_business_id ,
(a.entrust_bs) as entrust_bs ,
(case when a.arbitrage_type = 'H' and a.futu_exch_type = 'F1' then 'S' else a.hedge_type end) as hedge_type ,
(a.current_amount / a.amount_per_hand) as hold_hand ,
(a.futu_business_price) as open_price ,
(a.pre_square_price) as pre_square_price ,
(a.square_price) as square_price ,
(a.hold_income) as hold_income ,
(a.hold_income_float) as hold_income_float ,
-- (right('00000000' || a.futures_account, 8)) as futures_account ,
(a.hold_margin) as hold_margin ,
(a.money_type) as money_type ,
(to_char(to_date(to_char(a.open_date, '00000000'), 'YYYYMMDD'), 'YYYY-MM-DD')) as open_date ,
(case when a.arbitrage_type = 'H' then '1' else '2' end) as covered_flag
from
his_fuholdsinfo a,
fucontractinfo c
where
(a.futu_code = c.futu_code and a.futu_exch_type = c.futu_exch_type)
and ((a.branch_no not in ( 3025, 3022 )
--and a.fopt_type = '0'
--and a.deliver_flag <> '1'
--and a.current_amount <> 0
--and a.init_date = 20230725
--and (a.futu_exch_type not in ( 'F3', 'F5') or (a.futu_exch_type in( 'F3', 'F5') and c.expire_date > 20230725))
) )
order by a.init_date, a.position_str)
a)
where indexindex >= '95000' and indexindex < '100000';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
Plan hash value: 3978906109
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5000 |00:00:01.42 | 25592 | | | |
|* 1 | FILTER | | 1 | | 5000 |00:00:01.42 | 25592 | | | |
|* 2 | VIEW | | 1 | 749K| 5000 |00:00:01.42 | 25592 | | | |
| 3 | COUNT | | 1 | | 671K|00:00:01.35 | 25592 | | | |
| 4 | VIEW | | 1 | 749K| 671K|00:00:01.32 | 25592 | | | |
| 5 | SORT ORDER BY | | 1 | 749K| 671K|00:00:01.27 | 25592 | 104M| 3484K| 92M (0)|
|* 6 | HASH JOIN | | 1 | 749K| 671K|00:00:00.28 | 25592 | 1922K| 1922K| 1935K (0)|
| 7 | INDEX FAST FULL SCAN| FUCONTRACTINFO_PKEY | 1 | 11937 | 11300 |00:00:00.01 | 47 | | | |
|* 8 | TABLE ACCESS FULL | HIS_FUHOLDSINFO | 1 | 749K| 808K|00:00:00.16 | 25545 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:SYS_B_17)>TO_NUMBER(:SYS_B_16))
2 - filter(("INDEXINDEX"<TO_NUMBER(:SYS_B_17) AND "INDEXINDEX">=TO_NUMBER(:SYS_B_16)))
6 - access("A"."FUTU_CODE"="C"."FUTU_CODE" AND "A"."FUTU_EXCH_TYPE"="C"."FUTU_EXCH_TYPE")
8 - filter(("A"."BRANCH_NO"<>:SYS_B_14 AND "A"."BRANCH_NO"<>:SYS_B_15))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
46 rows selected.
强制控制走NL
Plan hash value: 3173656197
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5000 |00:00:01.71 | 358K| | | |
|* 1 | FILTER | | 1 | | 5000 |00:00:01.71 | 358K| | | |
|* 2 | VIEW | | 1 | 749K| 5000 |00:00:01.71 | 358K| | | |
| 3 | COUNT | | 1 | | 671K|00:00:01.65 | 358K| | | |
| 4 | VIEW | | 1 | 749K| 671K|00:00:01.62 | 358K| | | |
| 5 | SORT ORDER BY | | 1 | 749K| 671K|00:00:01.57 | 358K| 104M| 3484K| 92M (0)|
| 6 | NESTED LOOPS | | 1 | 749K| 671K|00:00:00.54 | 358K| | | |
|* 7 | TABLE ACCESS FULL| HIS_FUHOLDSINFO | 1 | 749K| 808K|00:00:00.16 | 25545 | | | |
|* 8 | INDEX UNIQUE SCAN| FUCONTRACTINFO_PKEY | 808K| 1 | 671K|00:00:00.28 | 333K| | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:SYS_B_17)>TO_NUMBER(:SYS_B_16))
2 - filter(("INDEXINDEX"<TO_NUMBER(:SYS_B_17) AND "INDEXINDEX">=TO_NUMBER(:SYS_B_16)))
7 - filter(("A"."BRANCH_NO"<>:SYS_B_14 AND "A"."BRANCH_NO"<>:SYS_B_15))
8 - access("A"."FUTU_CODE"="C"."FUTU_CODE" AND "A"."FUTU_EXCH_TYPE"="C"."FUTU_EXCH_TYPE")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
47 rows selected.
LightDB 默认hash 情况：
# LightDB rownum 走 HASH情况：
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on lt_tmp_alias_0x2cd1620 (cost=192560.20..199294.79 rows=1036 width=248) (actual time=9737.122..10373.653 rows=5000 loops=1)
Filter: (((lt_tmp_alias_0x2cd1620.indexindex)::numeric >= '95000'::numeric) AND ((lt_tmp_alias_0x2cd1620.indexindex)::numeric < '100000'::numeric))
Rows Removed by Filter: 666072
-> Subquery Scan on a (cost=192560.20..195150.43 rows=207218 width=248) (actual time=9641.778..10209.417 rows=671072 loops=1)
-> Sort (cost=192560.20..193078.25 rows=207218 width=271) (actual time=9641.775..10123.443 rows=671072 loops=1)
Sort Key: a_1.init_date, a_1.position_str
Sort Method: external merge Disk: 88976kB
-> Hash Join (cost=389.43..159286.03 rows=207218 width=271) (actual time=1.960..3636.273 rows=671072 loops=1)
Hash Cond: (((a_1.futu_code)::text = (c.futu_code)::text) AND ((a_1.futu_exch_type)::text = (c.futu_exch_type)::text))
-> Seq Scan on his_fuholdsinfo a_1 (cost=0.00..44307.60 rows=808608 width=114) (actual time=0.004..145.134 rows=808608 loops=1)
Filter: (branch_no <> ALL ('{3025,3022}'::bigint[]))
-> Hash (cost=219.86..219.86 rows=11305 width=15) (actual time=1.844..1.845 rows=11305 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 655kB
-> Index Only Scan using idx_fucontractinfo1 on fucontractinfo c (cost=0.29..219.86 rows=11305 width=15) (actual time=0.005..0.729 rows=11305 lo
ops=1)
Heap Fetches: 0
Planning Time: 0.371 ms
Execution Time: 10390.564 ms
(17 rows)
LightDB rownum 走 NL情况
select * from
( select rownum as indexindex, a.* from
( select /*+use_nl(a c)*/
(to_char(to_date(a.init_date, 'YYYYMMDD'), 'YYYY-MM-DD')) as init_date ,
(case when length(a.fund_account) >= 0 then a.fund_account else lpad(a.fund_account, 0, '0') end) as fund_account ,
(a.futu_code) as futu_code ,
(a.futu_business_id) as futu_business_id ,
(a.entrust_bs) as entrust_bs ,
(case when a.arbitrage_type = 'H' and a.futu_exch_type = 'F1' then 'S' else a.hedge_type end) as hedge_type ,
(a.current_amount / a.amount_per_hand) as hold_hand ,
(a.futu_business_price) as open_price ,
(a.pre_square_price) as pre_square_price ,
(a.square_price) as square_price ,
(a.hold_income) as hold_income ,
(a.hold_income_float) as hold_income_float ,
-- (right('00000000' || a.futures_account, 8)) as futures_account ,
(a.hold_margin) as hold_margin ,
(a.money_type) as money_type ,
(to_char(to_date(to_char(a.open_date, '00000000'), 'YYYYMMDD'), 'YYYY-MM-DD')) as open_date ,
(case when a.arbitrage_type = 'H' then '1' else '2' end) as covered_flag
from
his_fuholdsinfo a,
fucontractinfo c
where
(a.futu_code = c.futu_code and a.futu_exch_type = c.futu_exch_type)
and ((a.branch_no not in ( 3025, 3022 )
--and a.fopt_type = '0'
--and a.deliver_flag <> '1'
--and a.current_amount <> 0
--and a.init_date = 20230725
--and (a.futu_exch_type not in ( 'F3', 'F5') or (a.futu_exch_type in( 'F3', 'F5') and c.expire_date > 20230725))
) )
order by a.init_date, a.position_str)
a)
where indexindex >= '95000' and indexindex < '100000';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on lt_tmp_alias_0x2bb7910 @"lt#2" (cost=0.71..412815.27 rows=1036 width=248) (actual time=848.617..6341.381 rows=5000 loops=1)
Filter: (((lt_tmp_alias_0x2bb7910.indexindex)::numeric >= '95000'::numeric) AND ((lt_tmp_alias_0x2bb7910.indexindex)::numeric < '100000'::numeric))
Rows Removed by Filter: 666072
-> Subquery Scan on a @"lt#1" (cost=0.71..408670.91 rows=207218 width=248) (actual time=0.144..6129.531 rows=671072 loops=1)
-> Nested Loop (cost=0.71..406598.73 rows=207218 width=271) (actual time=0.143..6049.573 rows=671072 loops=1)
-> Index Scan using idx_his_fuholdsinfo1 on his_fuholdsinfo a_1 @"lt#0" (cost=0.42..49580.07 rows=808608 width=114) (actual time=0.018..425.548 rows=808608
loops=1)
Filter: (branch_no <> ALL ('{3025,3022}'::bigint[]))
-> Index Only Scan using idx_fucontractinfo1 on fucontractinfo c @"lt#0" (cost=0.29..0.31 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=808608)
Index Cond: ((futu_code = (a_1.futu_code)::text) AND (futu_exch_type = (a_1.futu_exch_type)::text))
Heap Fetches: 0
Planning Time: 0.345 ms
Execution Time: 6342.221 ms
(12 rows)
LightDB limit 走 HASH情况
------------------------------------------------------------------------------------------------------------------------------
Limit (cost=139026.98..141801.98 rows=5000 width=240) (actual time=6758.923..6784.655 rows=5000 loops=1)
-> Subquery Scan on lt_tmp_alias_0x2bb7558 (cost=83526.98..198532.97 rows=207218 width=240) (actual time=6244.875..6781.419 rows=105000 loops=1)
-> Result (cost=83526.98..196460.79 rows=207218 width=271) (actual time=6244.873..6771.309 rows=105000 loops=1)
-> Sort (cost=83526.98..84045.02 rows=207218 width=215) (actual time=6244.670..6325.747 rows=105000 loops=1)
Sort Key: a.init_date, a.position_str
Sort Method: external merge Disk: 80392kB
-> Hash Join (cost=389.43..53086.81 rows=207218 width=215) (actual time=2.046..693.792 rows=671072 loops=1)
Hash Cond: (((a.futu_code)::text = (c.futu_code)::text) AND ((a.futu_exch_type)::text = (c.futu_exch_type)::text))
-> Seq Scan on his_fuholdsinfo a (cost=0.00..44307.60 rows=808608 width=114) (actual time=0.005..146.051 rows=808608 loops=1)
Filter: (branch_no <> ALL ('{3025,3022}'::bigint[]))
-> Hash (cost=219.86..219.86 rows=11305 width=15) (actual time=2.009..2.010 rows=11305 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 655kB
-> Index Only Scan using idx_fucontractinfo1 on fucontractinfo c (cost=0.29..219.86 rows=11305 width=15) (actual time=0.008..0.738 rows=11
305 loops=1)
Heap Fetches: 0
Planning Time: 0.402 ms
Execution Time: 6799.185 ms
(16 rows)
LightDB limit 走 NL情况
LightDB limit执行计划:
select * from (
select /*+use_nl(a c)*/
(to_char(to_date(a.init_date, 'YYYYMMDD'), 'YYYY-MM-DD')) as init_date ,
(case when pg_catalog.length(a.fund_account) >= 0 then a.fund_account else pg_catalog.lpad(a.fund_account, 0, '0') end) as fund_account ,
(a.futu_code) as futu_code ,
(a.futu_business_id) as futu_business_id ,
(a.entrust_bs) as entrust_bs ,
(case when a.arbitrage_type = 'H' and a.futu_exch_type = 'F1' then 'S' else a.hedge_type end) as hedge_type ,
(a.current_amount / a.amount_per_hand) as hold_hand ,
(a.futu_business_price) as open_price ,
(a.pre_square_price) as pre_square_price ,
(a.square_price) as square_price ,
(a.hold_income) as hold_income ,
(a.hold_income_float) as hold_income_float ,
-- (right('00000000' || a.futures_account, 8)) as futures_account ,
(a.hold_margin) as hold_margin ,
(a.money_type) as money_type ,
(to_char(to_date(to_char(a.open_date, '00000000'), 'YYYYMMDD'), 'YYYY-MM-DD')) as open_date ,
(case when a.arbitrage_type = 'H' then '1' else '2' end) as covered_flag
from
his_fuholdsinfo a,
fucontractinfo c
where
(a.futu_code = c.futu_code and a.futu_exch_type = c.futu_exch_type)
and ((a.branch_no not in ( 3025, 3022 )
--and a.fopt_type = '0'
--and a.deliver_flag <> '1'
--and a.current_amount <> 0
--and a.init_date = 20230725
--and (a.futu_exch_type not in ( 'F3', 'F5') or (a.futu_exch_type in( 'F3', 'F5') and c.expire_date > 20230725))
) )
order by a.init_date, a.position_str) limit 5000 offset 100000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Limit (cost=139026.98..141801.98 rows=5000 width=240) (actual time=6784.547..6810.266 rows=5000 loops=1)
-> Subquery Scan on lt_tmp_alias_0x2cd1228 (cost=83526.98..198532.97 rows=207218 width=240) (actual time=6271.900..6807.138 rows=105000 loops=1)
-> Result (cost=83526.98..196460.79 rows=207218 width=271) (actual time=6271.898..6796.907 rows=105000 loops=1)
-> Sort (cost=83526.98..84045.02 rows=207218 width=215) (actual time=6271.715..6352.660 rows=105000 loops=1)
Sort Key: a.init_date, a.position_str
Sort Method: external merge Disk: 80392kB
-> Hash Join (cost=389.43..53086.81 rows=207218 width=215) (actual time=1.901..695.821 rows=671072 loops=1)
Hash Cond: (((a.futu_code)::text = (c.futu_code)::text) AND ((a.futu_exch_type)::text = (c.futu_exch_type)::text))
-> Seq Scan on his_fuholdsinfo a (cost=0.00..44307.60 rows=808608 width=114) (actual time=0.006..141.184 rows=808608 loops=1)
Filter: (branch_no <> ALL ('{3025,3022}'::bigint[]))
-> Hash (cost=219.86..219.86 rows=11305 width=15) (actual time=1.860..1.861 rows=11305 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 655kB
-> Index Only Scan using idx_fucontractinfo1 on fucontractinfo c (cost=0.29..219.86 rows=11305 width=15) (actual time=0.006..0.770 rows=11
305 loops=1)
Heap Fetches: 0
Planning Time: 0.459 ms
Execution Time: 6825.810 ms
(16 rows)
LightDB Limit HASH和NL并行情况
test@test=# set min_parallel_table_scan_size =1;
test@test=# explain analyze
test@test-# select * from (
test@test(# select
test@test(# (to_char(to_date(a.init_date, 'YYYYMMDD'), 'YYYY-MM-DD')) as init_date ,
test@test(# (case when pg_catalog.length(a.fund_account) >= 0 then a.fund_account else pg_catalog.lpad(a.fund_account, 0, '0') end) as fund_account ,
test@test(# (a.futu_code) as futu_code ,
test@test(# (a.futu_business_id) as futu_business_id ,
test@test(# (a.entrust_bs) as entrust_bs ,
test@test(# (case when a.arbitrage_type = 'H' and a.futu_exch_type = 'F1' then 'S' else a.hedge_type end) as hedge_type ,
test@test(# (a.current_amount / a.amount_per_hand) as hold_hand ,
test@test(# (a.futu_business_price) as open_price ,
test@test(# (a.pre_square_price) as pre_square_price ,
test@test(# (a.square_price) as square_price ,
test@test(# (a.hold_income) as hold_income ,
test@test(# (a.hold_income_float) as hold_income_float ,
test@test(# -- (right('00000000' || a.futures_account, 8)) as futures_account ,
test@test(# (a.hold_margin) as hold_margin ,
test@test(# (a.money_type) as money_type ,
test@test(# (to_char(to_date(to_char(a.open_date, '00000000'), 'YYYYMMDD'), 'YYYY-MM-DD')) as open_date ,
test@test(# (case when a.arbitrage_type = 'H' then '1' else '2' end) as covered_flag
test@test(# from
test@test(# his_fuholdsinfo a,
test@test(# fucontractinfo c
test@test(# where
test@test(# (a.futu_code = c.futu_code and a.futu_exch_type = c.futu_exch_type)
test@test(# and ((a.branch_no not in ( 3025, 3022 )
test@test(# --and a.fopt_type = '0'
test@test(# --and a.deliver_flag <> '1'
test@test(# --and a.current_amount <> 0
test@test(# --and a.init_date = 20230725
test@test(# --and (a.futu_exch_type not in ( 'F3', 'F5') or (a.futu_exch_type in( 'F3', 'F5') and c.expire_date > 20230725))
test@test(# ) )
test@test(# order by a.init_date, a.position_str) limit 5000 offset 100000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=114864.34..118095.15 rows=5000 width=240) (actual time=758.225..771.134 rows=5000 loops=1)
-> Subquery Scan on lt_tmp_alias_0x2bb7558 (cost=50248.12..184143.26 rows=207216 width=240) (actual time=641.739..769.061 rows=105000 loops=1)
-> Gather Merge (cost=50248.12..182071.10 rows=207216 width=271) (actual time=641.737..762.043 rows=105000 loops=1)
Workers Planned: 8
Workers Launched: 8
-> Result (cost=40247.98..54364.57 rows=25902 width=271) (actual time=596.126..659.614 rows=11954 loops=9)
-> Sort (cost=40247.98..40312.74 rows=25902 width=215) (actual time=595.587..607.017 rows=11954 loops=9)
Sort Key: a.init_date, a.position_str
Sort Method: external merge Disk: 9672kB
Worker 0: Sort Method: external merge Disk: 8848kB
Worker 1: Sort Method: external merge Disk: 9168kB
Worker 2: Sort Method: external merge Disk: 7336kB
Worker 3: Sort Method: external merge Disk: 9120kB
Worker 4: Sort Method: external merge Disk: 9192kB
Worker 5: Sort Method: external merge Disk: 9056kB
Worker 6: Sort Method: external merge Disk: 9320kB
Worker 7: Sort Method: external merge Disk: 8768kB
-> Parallel Hash Join (cost=319.10..36831.27 rows=25902 width=215) (actual time=0.399..78.808 rows=74564 loops=9)
Hash Cond: (((a.futu_code)::text = (c.futu_code)::text) AND ((a.futu_exch_type)::text = (c.futu_exch_type)::text))
-> Parallel Seq Scan on his_fuholdsinfo a (cost=0.00..35463.45 rows=101076 width=114) (actual time=0.004..16.838 rows=89845 loops=9)
Filter: (branch_no <> ALL ('{3025,3022}'::bigint[]))
-> Parallel Hash (cost=290.84..290.84 rows=1884 width=15) (actual time=0.325..0.326 rows=1256 loops=9)
Buckets: 16384 Batches: 1 Memory Usage: 704kB
-> Parallel Seq Scan on fucontractinfo c (cost=0.00..290.84 rows=1884 width=15) (actual time=0.005..1.320 rows=11305 loops=1)
Planning Time: 0.399 ms
Execution Time: 773.202 ms
(26 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Limit (cost=144848.95..148079.76 rows=5000 width=240) (actual time=1080.123..1092.559 rows=5000 loops=1)
-> Subquery Scan on lt_tmp_alias_0x2bb7590 @"lt#1" (cost=80232.74..214127.87 rows=207216 width=240) (actual time=956.613..1090.423 rows=105000 loops=1)
-> Gather Merge (cost=80232.74..212055.71 rows=207216 width=271) (actual time=956.604..1083.386 rows=105000 loops=1)
Workers Planned: 8
Workers Launched: 8
-> Result (cost=70232.60..84349.19 rows=25902 width=271) (actual time=915.243..987.742 rows=11944 loops=9)
-> Sort (cost=70232.60..70297.35 rows=25902 width=215) (actual time=914.704..927.520 rows=11944 loops=9)
Sort Key: a.init_date, a.position_str
Sort Method: external merge Disk: 8824kB
Worker 0: Sort Method: external merge Disk: 8488kB
Worker 1: Sort Method: external merge Disk: 9592kB
Worker 2: Sort Method: external merge Disk: 8640kB
Worker 3: Sort Method: external merge Disk: 8744kB
Worker 4: Sort Method: external merge Disk: 8544kB
Worker 5: Sort Method: external merge Disk: 9344kB
Worker 6: Sort Method: external merge Disk: 8752kB
Worker 7: Sort Method: external merge Disk: 9544kB
-> Nested Loop (cost=0.29..66815.88 rows=25902 width=215) (actual time=0.047..398.531 rows=74564 loops=9)
-> Parallel Seq Scan on his_fuholdsinfo a @"lt#0" (cost=0.00..35463.45 rows=101076 width=114) (actual time=0.005..18.933 rows=89845 loops=
9)
Filter: (branch_no <> ALL ('{3025,3022}'::bigint[]))
-> Index Only Scan using idx_fucontractinfo1 on fucontractinfo c @"lt#0" (cost=0.29..0.31 rows=1 width=15) (actual time=0.003..0.003 rows=
1 loops=808608)
Index Cond: ((futu_code = (a.futu_code)::text) AND (futu_exch_type = (a.futu_exch_type)::text))
Heap Fetches: 0
Planning Time: 0.319 ms
Execution Time: 1094.469 ms
(25 rows)
阶段小结：
| Oracle NL | Oracle HASH | LightDB rownum HASH | LightDB rownum NL | LightDB limit HASH | LightDB limit NL | LightDB limit HASH/NL并行 |
|---|---|---|---|---|---|---|
| 1.8秒逻辑度38万 | 1.4秒逻辑读25000 | 10.3秒 | 6.3秒 | 6.8秒 | 6.8秒 | 0.7秒/1秒 |
到这里还没完，试一下Oracle分页框架结果一样
SELECT *
FROM (SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM ( select
(to_char(to_date(a.init_date, 'YYYYMMDD'), 'YYYY-MM-DD')) as init_date ,
(case when length(a.fund_account) >= 0 then a.fund_account else lpad(a.fund_account, 0, '0') end) as fund_account ,
(a.futu_code) as futu_code ,
(a.futu_business_id) as futu_business_id ,
(a.entrust_bs) as entrust_bs ,
(case when a.arbitrage_type = 'H' and a.futu_exch_type = 'F1' then 'S' else a.hedge_type end) as hedge_type ,
(a.current_amount / a.amount_per_hand) as hold_hand ,
(a.futu_business_price) as open_price ,
(a.pre_square_price) as pre_square_price ,
(a.square_price) as square_price ,
(a.hold_income) as hold_income ,
(a.hold_income_float) as hold_income_float ,
-- (right('00000000' || a.futures_account, 8)) as futures_account ,
(a.hold_margin) as hold_margin ,
(a.money_type) as money_type ,
(to_char(to_date(to_char(a.open_date, '00000000'), 'YYYYMMDD'), 'YYYY-MM-DD')) as open_date ,
(case when a.arbitrage_type = 'H' then '1' else '2' end) as covered_flag
from
his_fuholdsinfo a,
fucontractinfo c
where
(a.futu_code = c.futu_code and a.futu_exch_type = c.futu_exch_type)
and ((a.branch_no not in ( 3025, 3022 )
--and a.fopt_type = '0'
--and a.deliver_flag <> '1'
--and a.current_amount <> 0
--and a.init_date = 20230725
--and (a.futu_exch_type not in ( 'F3', 'F5') or (a.futu_exch_type in( 'F3', 'F5') and c.expire_date > 20230725))
) )
order by a.init_date, a.position_str) A)
WHERE ROWNUM < 100000)
WHERE RN >= 95000;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5000 |00:00:01.19 | 25592 | | | |
|* 1 | VIEW | | 1 | 749K| 5000 |00:00:01.19 | 25592 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 99999 |00:00:01.18 | 25592 | | | |
| 3 | VIEW | | 1 | 749K| 99999 |00:00:01.18 | 25592 | | | |
| 4 | COUNT | | 1 | | 99999 |00:00:01.17 | 25592 | | | |
| 5 | VIEW | | 1 | 749K| 99999 |00:00:01.16 | 25592 | | | |
| 6 | SORT ORDER BY | | 1 | 749K| 99999 |00:00:01.15 | 25592 | 104M| 3484K| 92M (0)|
|* 7 | HASH JOIN | | 1 | 749K| 671K|00:00:00.29 | 25592 | 1922K| 1922K| 1949K (0)|
| 8 | INDEX FAST FULL SCAN| FUCONTRACTINFO_PKEY | 1 | 11937 | 11300 |00:00:00.01 | 47 | | | |
|* 9 | TABLE ACCESS FULL | HIS_FUHOLDSINFO | 1 | 749K| 808K|00:00:00.17 | 25545 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=TO_NUMBER(:SYS_B_17))
2 - filter(ROWNUM<TO_NUMBER(:SYS_B_16))
7 - access("A"."FUTU_CODE"="C"."FUTU_CODE" AND "A"."FUTU_EXCH_TYPE"="C"."FUTU_EXCH_TYPE")
9 - filter(("A"."BRANCH_NO"<>:SYS_B_14 AND "A"."BRANCH_NO"<>:SYS_B_15))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
47 rows selected.
最后修改时间：2023-08-08 14:20:58
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
