标签：index USED join citicscoma0 -------------------- _. 过滤性 HASH NULL
sql
select --/*+ enable_index_filter(1) enable_hash_join(0) use_nl_with_index(authallrea2_,IDX_DM_20236274)*/
--/*+ no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3) USE_INDEX_SKIP_SCAN(1)*/
citicscoma0_.fd_id as fd1_88_,
citicscoma0_.sync_data_to_calendar_time as sync2_88_,
citicscoma0_.fd_last_modified_time as fd3_88_,
citicscoma0_.doc_subject as doc4_88_,
citicscoma0_.fd_current_number as fd5_88_,
citicscoma0_.fd_feedback_modify as fd6_88_,
citicscoma0_.fd_feedback_executed as fd7_88_,
citicscoma0_.fd_number as fd8_88_,
citicscoma0_.doc_creator_id as doc9_88_,
citicscoma0_.doc_create_time as doc10_88_,
citicscoma0_.fd_department_id as fd11_88_,
citicscoma0_.doc_publish_time as doc12_88_,
citicscoma0_.doc_read_count as doc13_88_,
citicscoma0_.extend_file_path as extend14_88_,
citicscoma0_.fd_use_form as fd17_88_,
citicscoma0_.fd_disable_mobile_form as fd18_88_,
citicscoma0_.doc_status as doc19_88_,
citicscoma0_.auth_att_nodownload as auth20_88_,
citicscoma0_.auth_att_nocopy as auth21_88_,
citicscoma0_.auth_att_noprint as auth22_88_,
citicscoma0_.auth_reader_flag as auth23_88_,
citicscoma0_.fd_change_reader_flag as fd24_88_,
citicscoma0_.fd_rbp_flag as fd25_88_,
citicscoma0_.fd_change_att as fd26_88_,
citicscoma0_.fd_model_name as fd27_88_,
citicscoma0_.fd_model_id as fd28_88_,
citicscoma0_.fd_work_id as fd29_88_,
citicscoma0_.fd_phase_id as fd30_88_,
citicscoma0_.fd_title_regulation as fd31_88_,
citicscoma0_.fd_template_id as fd32_88_,
citicscoma0_.auth_area_id as auth33_88_
from ekp.citics_co_main citicscoma0_
where citicscoma0_.fd_id in (select /*+ no_index(citicscoma1_,INDEX33589155)*/citicscoma1_.fd_id
from citics_co_main citicscoma1_
inner join citics_co_main_areader authallrea2_
on citicscoma1_.fd_id=authallrea2_.fd_doc_id
inner join sys_org_element sysorgelem3_
on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id
where citicscoma1_.doc_subject like '%安鑫固收20号%'
and (sysorgelem3_.fd_id in ('1183b0b84ee4f581bba001c47a78b39d',
'16f579a9a65cbdb583f878940a9b64fd',
'15cedbcac0ca5181d5b9afe47f2875bb',
'171ba1f2a7bee7e39ed03934fafad9e9',
'17216119369cdceaf7068fa49edab66b',
'17494e28b43045f2c07c6d946bb979ad',
'1549efdc8f72b8e452be9524e0eac685',
'1549f068e46b26d8d970562414b8d0e7',
'16b880568d334e113d1603340dabf692',
'1635c98810526a9b5e57e884bd39d66b',
'16fa715c85b35d9db5e65124021b672b',
'16fc74e4d78c015f04f045e41f4950ce',
'17ae0dc5b3ce706d4e90f4c47c5b9e53',
'16f78e2e9c67cf5c645bbcc4c3ba9ead',
'172a74e6a8da8ecf2a296664ae78f80d',
'1725422173088761b8e15d44e21b3ada',
'1886b80ab2b3b37c7456ad24ba8afd30',
'188b81998f6a725d9a39b5541fb895d4',
'15678d66c764ab9b3e616b748de9da06',
'186b16f95b0a606bc2a4cfa4cf1ac8dc',
'1842cc006fa8cfc00e3b1d54b3585ffa',
'18d3915c891f81e75d23ddd497883822' ,
'18ecc3ecbacbfda9ed9dac9483aafb47')))
order by citicscoma0_.doc_create_time desc ,
citicscoma0_.fd_id desc limit 15;
autotrace+et
1 #NSET2: [24824, 1->10, 1371]
2 #PRJT2: [24824, 1->10, 1371]; exp_num(32), is_atom(FALSE)
3 #SORT3: [24824, 1->10, 1371]; key_num(2), is_distinct(FALSE), is_adaptive(0), MEM_USED(30720KB), DISK_USED(0KB)
4 #NEST LOOP INDEX JOIN2: [24823, 1->10, 1371]
5 #DISTINCT: [24823, 1->10, 192], MEM_USED(7KB), DISK_USED(0KB)
6 #PRJT2: [24783, 170960->21, 192]; exp_num(1), is_atom(FALSE)
7 #SLCT2: [24783, 170960->21, 192]; exp11 LIKE '%安鑫固收20号%'
8 #NEST LOOP INDEX JOIN2: [24783, 170960->214969, 192]
9 #NEST LOOP INDEX JOIN2: [25, 170960->214969, 96]
10 #CONST VALUE LIST: [1, 23->23, 48]; row_num(23), col_num(1),
11 #SSEK2: [25, 7433->214969, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER), scan_range[(DMTEMPVIEW_891776973.colname,min),(DMTEMPVIEW_891776973.colname,max))
12 #BLKUP2: [1116, 1->214969, 48]; INDEX33589155(CITICS_CO_MAIN)
13 #SSEK2: [1116, 1->214969, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN), scan_range[AUTHALLREA2_.FD_DOC_ID,AUTHALLREA2_.FD_DOC_ID]
14 #BLKUP2: [1, 1->10, 48]; INDEX33589155(CITICS_CO_MAIN)
15 #SSEK2: [1, 1->10, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN), scan_range[DMTEMPVIEW_891776971.colname,DMTEMPVIEW_891776971.colname]
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
1217553 logical reads
74462 physical reads
0 redo size
10407 bytes sent to client
9555 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
3910 io wait time(ms)
8103 exec time(ms)
已用时间: 00:00:08.103. 执行号:36768608.
SQL> et(36768608);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
1 DLCK 2 0% 16 0 2 0 0 0
0 NULL NULL 0
2 PRJT2 6 0% 15 2 4 0 0 0
0 NULL NULL 0
3 IJI2 17 0% 14 4 33 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
4 CONSTV 21 0% 13 10 2 0 0 0
0 NULL NULL 0
5 PRJT2 46 0% 12 6 44 0 0 0
0 NULL NULL 0
6 SSEK2 110 0% 11 15 20 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
7 NSET2 123 0% 10 1 3 0 0 0
0 NULL NULL 0
8 BLKUP2 164 0% 9 14 40 0 0 0
0 NULL NULL 0
9 DIST 200 0% 8 5 24 7 0 10
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
10 SORT3 205 0% 7 3 13 30720 0 0
0 NULL NULL 0
11 IJI2 2851 0.04% 6 9 1462 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
12 SSEK2 65347 0.82% 5 11 741 0 0 0
0 NULL NULL 0
13 IJI2 104902 1.32% 4 8 645627 0 0 0
0 NULL NULL 0
14 SLCT2 310911 3.91% 3 7 214992 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
15 SSEK2 3024147 38.01% 2 13 429938 0 0 0
0 NULL NULL 0
16 BLKUP2 4447612 55.9% 1 12 859876 0 0 0
0 NULL NULL 0
16 rows got
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
274 logical reads
2 physical reads
0 redo size
3277 bytes sent to client
78 bytes received from client
1 roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
16 rows processed
0 io wait time(ms)
28 exec time(ms)
已用时间: 28.193(毫秒). 执行号:36768609.
加HINT后autotrace+et
--/*+ enable_index_filter(1) enable_hash_join(0) use_nl_with_index(authallrea2_,IDX_DM_20236274)*/
CREATE OR REPLACE INDEX "IDX_DM_20236274" ON "EKP"."CITICS_CO_MAIN"("FD_ID" ASC,"DOC_SUBJECT" ASC) STORAGE(ON "IDX_EKP", CLUSTERBTR) ;
1 #NSET2: [19639, 1->10, 1371]
2 #PRJT2: [19639, 1->10, 1371]; exp_num(32), is_atom(FALSE)
3 #SORT3: [19639, 1->10, 1371]; key_num(2), is_distinct(FALSE), is_adaptive(0), MEM_USED(30720KB), DISK_USED(0KB)
4 #NEST LOOP INDEX JOIN2: [19638, 1->10, 1371]
5 #DISTINCT: [19638, 1->10, 192], MEM_USED(7KB), DISK_USED(0KB)
6 #PRJT2: [19603, 170960->21, 192]; exp_num(1), is_atom(FALSE)
7 #NEST LOOP INDEX JOIN2: [19603, 170960->21, 192]
8 #NEST LOOP INDEX JOIN2: [25, 170960->214975, 96]
9 #CONST VALUE LIST: [1, 23->23, 48]; row_num(23), col_num(1),
10 #SSEK2: [25, 7433->214975, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER), scan_range[(DMTEMPVIEW_891778451.colname,min),(DMTEMPVIEW_891778451.colname,max))
11 #SLCT2: [763, 1->21, 96]; exp11 > 0
12 #SSEK2: [763, 1->214975, 96]; scan_type(ASC), IDX_DM_20236274(CITICS_CO_MAIN), scan_range[(AUTHALLREA2_.FD_DOC_ID,min),(AUTHALLREA2_.FD_DOC_ID,max))
13 #BLKUP2: [1, 1->10, 48]; INDEX33589155(CITICS_CO_MAIN)
14 #SSEK2: [1, 1->10, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN), scan_range[DMTEMPVIEW_891778449.colname,DMTEMPVIEW_891778449.colname]
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
838778 logical reads
24514 physical reads
0 redo size
10415 bytes sent to client
9660 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
882 io wait time(ms)
5115 exec time(ms)
已用时间: 00:00:05.115. 执行号:36768612.
SQL> et(36768612);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
1 PRJT2 3 0% 14 2 4 0 0 0
0 NULL NULL 0
2 DLCK 3 0% 14 0 2 0 0 0
0 NULL NULL 0
3 IJI2 12 0% 13 4 33 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
4 CONSTV 21 0% 12 9 2 0 0 0
0 NULL NULL 0
5 PRJT2 35 0% 11 6 44 0 0 0
0 NULL NULL 0
6 NSET2 125 0% 10 1 3 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
7 SSEK2 148 0% 9 14 20 0 0 0
0 NULL NULL 0
8 BLKUP2 191 0% 8 13 40 0 0 0
0 NULL NULL 0
9 SORT3 209 0% 7 3 13 30720 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
10 DIST 221 0% 6 5 24 7 0 10
0 NULL NULL 0
11 IJI2 2259 0.04% 5 8 1462 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
12 SSEK2 23407 0.47% 4 10 741 0 0 0
0 NULL NULL 0
13 IJI2 28936 0.57% 3 7 215737 0 0 0
0 NULL NULL 0
14 SLCT2 291997 5.8% 2 11 644946 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
15 SSEK2 4685253 93.09% 1 12 429950 0 0 0
0 NULL NULL 0
15 rows got
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
276 logical reads
0 physical reads
0 redo size
3135 bytes sent to client
78 bytes received from client
1 roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
15 rows processed
0 io wait time(ms)
22 exec time(ms)
已用时间: 22.144(毫秒). 执行号:36768613.
标签：index,
USED,
join,
citicscoma0,
--------------------,
_.,
过滤性,
HASH,
NULL
From： https://www.cnblogs.com/lixiaomeng/p/18153768