-
EBS利用虚拟列及hint 提示优化sql案例一则
利用虚拟列及hint 提示优化sql案例一则
用户反馈，预算系统有一个抽取EBS科目余额数据的程序长时间不出结果，运行了一晚上没出结果。如下是优化思路，用到了oracle的虚拟列和查询提示
优化前现状
--优化前sql SELECT COUNT(1) --此处select原来是具体业务字段，为方便验证改成 count(1) 以便匹配所有行 FROM aaaaaaaaaaaaaaaaaaa rd, bbbbbbbbbbbbbbbbbbbbbbbb sbv --表名已经进行模糊化处理 WHERE rpad(sbv.segment1, 20, '0') BETWEEN rpad(nvl(rd.segment1_low, sbv.segment1), 20, '0') AND rpad(nvl(rd.segment1_high, sbv.segment1), 20, 'z') AND rpad(sbv.segment2, 20, '0') BETWEEN rpad(nvl(rd.segment2_low, sbv.segment2), 20, '0') AND rpad(nvl(rd.segment2_high, sbv.segment2), 20, 'z') AND rpad(sbv.segment3, 20, '0') BETWEEN rpad(nvl(rd.segment3_low, sbv.segment3), 20, '0') AND rpad(nvl(rd.segment3_high, sbv.segment3), 20, 'z') AND rpad(sbv.segment4, 20, '0') BETWEEN rpad(nvl(rd.segment4_low, sbv.segment4), 20, '0') AND rpad(nvl(rd.segment4_high, sbv.segment4), 20, 'z') AND rpad(sbv.segment5, 20, '0') BETWEEN rpad(nvl(rd.segment5_low, sbv.segment5), 20, '0') AND rpad(nvl(rd.segment5_high, sbv.segment5), 20, 'z') AND rpad(sbv.segment6, 20, '0') BETWEEN rpad(nvl(rd.segment6_low, sbv.segment6), 20, '0') AND rpad(nvl(rd.segment6_high, sbv.segment6), 20, 'z') AND rpad(sbv.segment7, 20, '0') BETWEEN rpad(nvl(rd.segment7_low, sbv.segment7), 20, '0') AND rpad(nvl(rd.segment7_high, sbv.segment7), 20, 'z') AND rpad(sbv.segment8, 20, '0') BETWEEN rpad(nvl(rd.segment8_low, sbv.segment8), 20, '0') AND rpad(nvl(rd.segment8_high, sbv.segment8), 20, 'z') AND rpad(sbv.segment9, 20, '0') BETWEEN rpad(nvl(rd.segment9_low, sbv.segment9), 20, '0') AND rpad(nvl(rd.segment9_high, sbv.segment9), 20, 'z') AND rpad(sbv.segment10, 20, '0') BETWEEN rpad(nvl(rd.segment10_low, sbv.segment10), 20, '0') AND rpad(nvl(rd.segment10_high, sbv.segment10), 20, 'z') 执行计划如下： Plan Hash Value : -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 289 | 17585060 | | | 1 | SORT AGGREGATE | | 1 | 289 | | | | 2 | NESTED LOOPS | | 1 | 289 | 17585060 | | | * 3 | TABLE ACCESS FULL | aaaaaaaaaaaaaaaaaaa | 43614 | 10685430 | 468 | | | * 4 | TABLE ACCESS FULL | bbbbbbbbbbbbbbbbbbbbbbbb | 1 | 44 | 403 | | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 3 - filter("RD"."SIGN" IS NOT NULL) * 4 - filter(RPAD("SBV"."SEGMENT1",20,'0')>=RPAD(NVL("RD"."SEGMENT1_LOW","SBV"."SEGMENT1"),20,'0') AND RPAD("SBV"."SEGMENT1",20,'0')<=RPAD(NVL("RD"."SEGMENT1_HIGH","SBV"."SEGMENT1"),20,'z') AND RPAD("SBV"."SEGMENT2",20,'0')>=RPAD(NVL("RD"."SEGMENT2_LOW","SBV"."SEGMENT2"),20,'0') AND RPAD("SBV"."SEGMENT2",20,'0')<=RPAD(NVL("RD"."SEGMENT2_HIGH","SBV"."SEGMENT2"),20,'z') AND RPAD("SBV"."SEGMENT3",20,'0')>=RPAD(NVL("RD"."SEGMENT3_LOW","SBV"."SEGMENT3"),20,'0') AND RPAD("SBV"."SEGMENT3",20,'0')<=RPAD(NVL("RD"."SEGMENT3_HIGH","SBV"."SEGMENT3"),20,'z') AND RPAD("SBV"."SEGMENT4",20,'0')>=RPAD(NVL("RD"."SEGMENT4_LOW","SBV"."SEGMENT4"),20,'0') AND RPAD("SBV"."SEGMENT4",20,'0')<=RPAD(NVL("RD"."SEGMENT4_HIGH","SBV"."SEGMENT4"),20,'z') AND RPAD("SBV"."SEGMENT5",20,'0')>=RPAD(NVL("RD"."SEGMENT5_LOW","SBV"."SEGMENT5"),20,'0') AND RPAD("SBV"."SEGMENT5",20,'0')<=RPAD(NVL("RD"."SEGMENT5_HIGH","SBV"."SEGMENT5"),20,'z') AND RPAD("SBV"."SEGMENT6",20,'0')>=RPAD(NVL("RD"."SEGMENT6_LOW","SBV"."SEGMENT6"),20,'0') AND RPAD("SBV"."SEGMENT6",20,'0')<=RPAD(NVL("RD"."SEGMENT6_HIGH","SBV"."SEGMENT6"),20,'z') AND RPAD("SBV"."SEGMENT7",20,'0')>=RPAD(NVL("RD"."SEGMENT7_LOW","SBV"."SEGMENT7"),20,'0') AND RPAD("SBV"."SEGMENT7",20,'0')<=RPAD(NVL("RD"."SEGMENT7_HIGH","SBV"."SEGMENT7"),20,'z') AND RPAD("SBV"."SEGMENT8",20,'0')>=RPAD(NVL("RD"."SEGMENT8_LOW","SBV"."SEGMENT8"),20,'0') AND RPAD("SBV"."SEGMENT8",20,'0')<=RPAD(NVL("RD"."SEGMENT8_HIGH","SBV"."SEGMENT8"),20,'z') AND RPAD("SBV"."SEGMENT9",20,'0')>=RPAD(NVL("RD"."SEGMENT9_LOW","SBV"."SEGMENT9"),20,'0') AND RPAD("SBV"."SEGMENT9",20,'0')<=RPAD(NVL("RD"."SEGMENT9_HIGH","SBV"."SEGMENT9"),20,'z') AND RPAD("SBV"."SEGMENT10",20,'0')>=RPAD(NVL("RD"."SEGMENT10_LOW","SBV"."SEGMENT10"),20,'0') AND RPAD("SBV"."SEGMENT10",20,'0')<=RPAD(NVL("RD"."SEGMENT10_HIGH","SBV"."SEGMENT10"),20,'z')) 经过分析，发现有如下：都是执行的全表扫描，表的连接为嵌套循环连接，驱动表 aaaaaaaaaaaaaaaaaaa,被驱动表是bbbbbbbbbbbbbbbbbbbbbbbb。走的都是全表扫描，执行次数需要 43614*78113 大约三十多亿次，然后再进行过滤filter。 实际业务中发现此sql运行好几个小时不出结果。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
数据收集
统计数据规模
select count(1) from aaaaaaaaaaaaaaaaaaa;-- 43614 rows select count(1) from bbbbbbbbbbbbbbbbbbbbbbbb; -- 78113 rows- 1
- 2
- 3
偷懒思路-（失败）
利用sql tuning进行分析，让优化器给出优化建议。
经过分析，优化器给出的建议是使用并行，用并行提示，尝试 parallel(2)、parallel(4)、parallel(6)、parallel(8) 均得不到改善，也是长时间不能出结果。需要考虑从sql改写方面入手了。优化思路
查看上面的执行计划发现，没有用到索引，查看这两个表的结构，都没有建立任何索引。
连接条件都是用的函数处理+ between and 进行连接，即使有普通索引的话，oracle优化器也不会用到索引，使用索引的前提是是索引上没有使用函数或者使用函数索引。
确定小表：根据表内数据量和连接条件来确定，aaaaaaaaaaaaaaaaaaa 作为小表驱动表，虽然要轮询43614次。
因此只能将目标放到如何减少内部循环次数上来，经过查看两个表的分布，发现aaaaaaaaaaaaaaaaaaa除了SEGMENT3和SEGMENT5上 是有值分布，其他字段都是空，起不到过滤作用。
从业务层面进行数据分析，aaaaaaaaaaaaaaaaaaa 里面是EBS的FSG报表配置数据，只有COA段3和5配置了，其他COA段都是T 全部汇总。
bbbbbbbbbbbbbbbbbbbbbbbb 而是总账余额数据，包含全部COA段。
考虑使用索引来减少对大表的循环次数，在小表上是没法进行过滤的，因为小表上上没有过滤条件。
因此在大表上bbbbbbbbbbbbbbbbbbbbbbbb 对 (“SBV”.“SEGMENT3”,20,‘0’)，(“SBV”.“SEGMENT5”,20,‘0’) 建立函数索引，因为where条件带函数了，需要保持和where条件一致，这样才能使用索引。create index F35_CUX_GL_SEGMENT_BALANCE_PRC on bbbbbbbbbbbbbbbbbbbbbbbb (rpad(segment3,20,'0'),rpad(segment5,20,'0'));- 1
还一个问题需要解决，使用索引的话，索引字段上匹配的值是要确定的。 BETWEEN
rpad(nvl(rd.segment3_low, sbv.segment3), 20, ‘0’) AND rpad(nvl(rd.segment3_high, sbv.segment3), 20, ‘z’)
这种写法看着是很讨巧，但是是没法用的索引的,需要进行改造。因此引入Oracle虚拟列来进行物化。
经过数据分析，这个地方 (rpad(nvl(segment3_low,‘0’),20,‘0’) 其实是等同于 BETWEEN
rpad(nvl(rd.segment3_low, sbv.segment3), 20, ‘0’) 这个条件的。alter table aaaaaaaaaaaaaaaaaaa add segment3_low_ext generated always as (rpad(nvl(segment3_low,'0'),20,'0')) virtual ; alter table aaaaaaaaaaaaaaaaaaa add segment3_low_ext generated always as (rpad(nvl(segment5_low,'0'),20,'0')) virtual ; alter table aaaaaaaaaaaaaaaaaaa add segment3_high_ext generated always as (rpad(nvl(segment3_high,'z'),20,'0')) virtual ; alter table aaaaaaaaaaaaaaaaaaa add segment3_high_ext generated always as (rpad(nvl(segment5_high,'z'),20,'0')) virtual ;- 1
- 2
- 3
- 4
改造后sql
--先更新统计信息 BEGIN dbms_stats.gather_table_stats(USER, 'aaaaaaaaaaaaaaaaaaa', cascade => TRUE); dbms_stats.gather_table_stats(USER, 'bbbbbbbbbbbbbbbbbbbbbbbb', cascade => TRUE); END; SELECT /*+ parallel(8) */ COUNT(1) FROM aaaaaaaaaaaaaaaaaaa rd, bbbbbbbbbbbbbbbbbbbbbbbb sbv WHERE rpad(sbv.segment1, 20, '0') BETWEEN rpad(nvl(rd.segment1_low, sbv.segment1), 20, '0') AND rpad(nvl(rd.segment1_high, sbv.segment1), 20, 'z') AND rpad(sbv.segment2, 20, '0') BETWEEN rpad(nvl(rd.segment2_low, sbv.segment2), 20, '0') AND rpad(nvl(rd.segment2_high, sbv.segment2), 20, 'z') AND rpad(sbv.segment3, 20, '0') BETWEEN rd.segment3_low_ext AND rd.segment3_high_ext AND rpad(sbv.segment4, 20, '0') BETWEEN rpad(nvl(rd.segment4_low, sbv.segment4), 20, '0') AND rpad(nvl(rd.segment4_high, sbv.segment4), 20, 'z') AND rpad(sbv.segment5, 20, '0') BETWEEN rd.segment5_low_ext AND rd.segment5_high_ext AND rpad(sbv.segment6, 20, '0') BETWEEN rpad(nvl(rd.segment6_low, sbv.segment6), 20, '0') AND rpad(nvl(rd.segment6_high, sbv.segment6), 20, 'z') AND rpad(sbv.segment7, 20, '0') BETWEEN rpad(nvl(rd.segment7_low, sbv.segment7), 20, '0') AND rpad(nvl(rd.segment7_high, sbv.segment7), 20, 'z') AND rpad(sbv.segment8, 20, '0') BETWEEN rpad(nvl(rd.segment8_low, sbv.segment8), 20, '0') AND rpad(nvl(rd.segment8_high, sbv.segment8), 20, 'z') AND rpad(sbv.segment9, 20, '0') BETWEEN rpad(nvl(rd.segment9_low, sbv.segment9), 20, '0') AND rpad(nvl(rd.segment9_high, sbv.segment9), 20, 'z') AND rpad(sbv.segment10, 20, '0') BETWEEN rpad(nvl(rd.segment10_low, sbv.segment10), 20, '0') AND rpad(nvl(rd.segment10_high, sbv.segment10), 20, 'z') --执行计划如下： Plan Hash Value : 2479174775 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 403 | 7034 | 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 403 | | | | 2 | PX COORDINATOR | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 403 | | | | 4 | SORT AGGREGATE | | 1 | 403 | | | | 5 | MERGE JOIN | | 1 | 403 | 7034 | 00:00:01 | | 6 | SORT JOIN | | 43616 | 14349664 | 66 | 00:00:01 | | 7 | PX BLOCK ITERATOR | | 43616 | 14349664 | 65 | 00:00:01 | | 8 | TABLE ACCESS FULL | aaaaaaaaaaaaaaaaaaa | 43616 | 14349664 | 65 | 00:00:01 | | * 9 | FILTER | | | | | | | * 10 | SORT JOIN | | 160492 | 11876408 | 118 | 00:00:01 | | 11 | BUFFER SORT | | | | | | | 12 | PX RECEIVE | | 160492 | 11876408 | 115 | 00:00:01 | | 13 | PX SEND BROADCAST | :TQ10000 | 160492 | 11876408 | 115 | 00:00:01 | | 14 | PX BLOCK ITERATOR | | 160492 | 11876408 | 115 | 00:00:01 | | 15 | TABLE ACCESS FULL | bbbbbbbbbbbbbbbbbbbbbbbb | 160492 | 11876408 | 115 | 00:00:01 | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 9 - filter(RPAD("SBV"."SEGMENT1",20,'0')>=RPAD(NVL("RD"."SEGMENT1_LOW","SBV"."SEGMENT1"),20,'0') AND RPAD("SBV"."SEGMENT1",20,'0')<=RPAD(NVL("RD"."SEGMENT1_HIGH","SBV"."SEGMENT1"),20,'z') AND RPAD("SBV"."SEGMENT2",20,'0')>=RPAD(NVL("RD"."SEGMENT2_LOW","SBV"."SEGMENT2"),20,'0') AND RPAD("SBV"."SEGMENT2",20,'0')<=RPAD(NVL("RD"."SEGMENT2_HIGH","SBV"."SEGMENT2"),20,'z') AND RPAD("SEGMENT3",20,'0')<="RD"."SEGMENT3_HIGH_EXT" AND RPAD("SBV"."SEGMENT4",20,'0')>=RPAD(NVL("RD"."SEGMENT4_LOW","SBV"."SEGMENT4"),20,'0') AND RPAD("SBV"."SEGMENT4",20,'0')<=RPAD(NVL("RD"."SEGMENT4_HIGH","SBV"."SEGMENT4"),20,'z') AND RPAD("SEGMENT5",20,'0')>="RD"."SEGMENT5_LOW_EXT" AND RPAD("SEGMENT5",20,'0')<="RD"."SEGMENT5_HIGH_EXT" AND RPAD("SBV"."SEGMENT6",20,'0')>=RPAD(NVL("RD"."SEGMENT6_LOW","SBV"."SEGMENT6"),20,'0') AND RPAD("SBV"."SEGMENT6",20,'0')<=RPAD(NVL("RD"."SEGMENT6_HIGH","SBV"."SEGMENT6"),20,'z') AND RPAD("SBV"."SEGMENT7",20,'0')>=RPAD(NVL("RD"."SEGMENT7_LOW","SBV"."SEGMENT7"),20,'0') AND RPAD("SBV"."SEGMENT7",20,'0')<=RPAD(NVL("RD"."SEGMENT7_HIGH","SBV"."SEGMENT7"),20,'z') AND RPAD("SBV"."SEGMENT8",20,'0')>=RPAD(NVL("RD"."SEGMENT8_LOW","SBV"."SEGMENT8"),20,'0') AND RPAD("SBV"."SEGMENT8",20,'0')<=RPAD(NVL("RD"."SEGMENT8_HIGH","SBV"."SEGMENT8"),20,'z') AND RPAD("SBV"."SEGMENT9",20,'0')>=RPAD(NVL("RD"."SEGMENT9_LOW","SBV"."SEGMENT9"),20,'0') AND RPAD("SBV"."SEGMENT9",20,'0')<=RPAD(NVL("RD"."SEGMENT9_HIGH","SBV"."SEGMENT9"),20,'z') AND RPAD("SBV"."SEGMENT10",20,'0')>=RPAD(NVL("RD"."SEGMENT10_LOW","SBV"."SEGMENT10"),20,'0') AND RPAD("SBV"."SEGMENT10",20,'0')<=RPAD(NVL("RD"."SEGMENT10_HIGH","SBV"."SEGMENT10"),20,'z')) * 10 - access(RPAD("SEGMENT3",20,'0')>="RD"."SEGMENT3_LOW_EXT") * 10 - filter(RPAD("SEGMENT3",20,'0')>="RD"."SEGMENT3_LOW_EXT")- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
发现这个更奇葩了，加了索引后，直接变成排序合并连接了，纵然加上并行加持也是长时间不出结果，我们预期的是让小表aaaaaaaaaaaaaaaaaaa作为驱动表在外层循环，然后内层大表循环时利用大表上的函数索引和驱动表上的虚拟列来减少大表的循环次数。
因此还需要提示优化器需要按照我们得指示采用 嵌套循环连接，如下加入 leading(rd,sbv) USE_NL(rd,sbv)SELECT /*+ parallel(8) leading(rd,sbv) use_nl(rd,sbv) */ --将表连接方式改为嵌套循环连接用fsg模板驱动过滤余额表 rd.report_id, rd.name, rd.row_name, rd.axis_seq, rd.description, rd.hyperion_line, sbv.ledger_name, sbv.function_currency, sbv.segment1, sbv.segment2, sbv.period_name, sbv.end_date, (CASE WHEN rd.dr_cr_net_code = 'N' AND sbv.account_type = 'R' THEN nvl(sbv.period_net_cr_beq, 0) - nvl(sbv.period_net_dr_beq, 0) WHEN rd.dr_cr_net_code = 'D' THEN nvl(sbv.period_net_dr_beq, 0) WHEN rd.dr_cr_net_code = 'C' THEN nvl(sbv.period_net_cr_beq, 0) ELSE nvl(sbv.period_net_dr_beq, 0) - nvl(sbv.period_net_cr_beq, 0) END) * decode(rd.sign, '-', -1, 1) amount FROM aaaaaaaaaaaaaaaaaaa rd, bbbbbbbbbbbbbbbbbbbbbbbb sbv WHERE rpad(sbv.segment1, 20, '0') BETWEEN rpad(nvl(rd.segment1_low, sbv.segment1), 20, '0') AND rpad(nvl(rd.segment1_high, sbv.segment1), 20, 'z') AND rpad(sbv.segment2, 20, '0') BETWEEN rpad(nvl(rd.segment2_low, sbv.segment2), 20, '0') AND rpad(nvl(rd.segment2_high, sbv.segment2), 20, 'z') AND rpad(sbv.segment3, 20, '0') BETWEEN rd.segment3_low_ext AND rd.segment3_high_ext AND rpad(sbv.segment4, 20, '0') BETWEEN rpad(nvl(rd.segment4_low, sbv.segment4), 20, '0') AND rpad(nvl(rd.segment4_high, sbv.segment4), 20, 'z') AND rpad(sbv.segment5, 20, '0') BETWEEN rd.segment5_low_ext AND rd.segment5_high_ext AND rpad(sbv.segment6, 20, '0') BETWEEN rpad(nvl(rd.segment6_low, sbv.segment6), 20, '0') AND rpad(nvl(rd.segment6_high, sbv.segment6), 20, 'z') AND rpad(sbv.segment7, 20, '0') BETWEEN rpad(nvl(rd.segment7_low, sbv.segment7), 20, '0') AND rpad(nvl(rd.segment7_high, sbv.segment7), 20, 'z') AND rpad(sbv.segment8, 20, '0') BETWEEN rpad(nvl(rd.segment8_low, sbv.segment8), 20, '0') AND rpad(nvl(rd.segment8_high, sbv.segment8), 20, 'z') AND rpad(sbv.segment9, 20, '0') BETWEEN rpad(nvl(rd.segment9_low, sbv.segment9), 20, '0') AND rpad(nvl(rd.segment9_high, sbv.segment9), 20, 'z') AND rpad(sbv.segment10, 20, '0') BETWEEN rpad(nvl(rd.segment10_low, sbv.segment10), 20, '0') AND rpad(nvl(rd.segment10_high, sbv.segment10), 20, 'z') --执行计划如下： Plan Hash Value : 3658724919 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 447 | 266943 | 00:00:05 | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 447 | 266943 | 00:00:05 | | 3 | NESTED LOOPS | | 1 | 447 | 266943 | 00:00:05 | | 4 | PX BLOCK ITERATOR | | | | | | | 5 | TABLE ACCESS FULL | aaaaaaaaaaaaaaaaaaa | 43616 | 14349664 | 65 | 00:00:01 | | * 6 | TABLE ACCESS BY INDEX ROWID | bbbbbbbbbbbbbbbbbbbbbbbb | 1 | 118 | 6 | 00:00:01 | | * 7 | INDEX RANGE SCAN | F35_CUX_GL_SEGMENT_BALANCE_PRC | 134 | | 1 | 00:00:01 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ------------------------------------------ * 6 - filter(RPAD("SBV"."SEGMENT1",20,'0')>=RPAD(NVL("RD"."SEGMENT1_LOW","SBV"."SEGMENT1"),20,'0') AND RPAD("SBV"."SEGMENT1",20,'0')<=RPAD(NVL("RD"."SEGMENT1_HIGH","SBV"."SEGMENT1"),20,'z') AND RPAD("SBV"."SEGMENT2",20,'0')>=RPAD(NVL("RD"."SEGMENT2_LOW","SBV"."SEGMENT2"),20,'0') AND RPAD("SBV"."SEGMENT2",20,'0')<=RPAD(NVL("RD"."SEGMENT2_HIGH","SBV"."SEGMENT2"),20,'z') AND RPAD("SBV"."SEGMENT4",20,'0')>=RPAD(NVL("RD"."SEGMENT4_LOW","SBV"."SEGMENT4"),20,'0') AND RPAD("SBV"."SEGMENT4",20,'0')<=RPAD(NVL("RD"."SEGMENT4_HIGH","SBV"."SEGMENT4"),20,'z') AND RPAD("SBV"."SEGMENT6",20,'0')>=RPAD(NVL("RD"."SEGMENT6_LOW","SBV"."SEGMENT6"),20,'0') AND RPAD("SBV"."SEGMENT6",20,'0')<=RPAD(NVL("RD"."SEGMENT6_HIGH","SBV"."SEGMENT6"),20,'z') AND RPAD("SBV"."SEGMENT7",20,'0')>=RPAD(NVL("RD"."SEGMENT7_LOW","SBV"."SEGMENT7"),20,'0') AND RPAD("SBV"."SEGMENT7",20,'0')<=RPAD(NVL("RD"."SEGMENT7_HIGH","SBV"."SEGMENT7"),20,'z') AND RPAD("SBV"."SEGMENT8",20,'0')>=RPAD(NVL("RD"."SEGMENT8_LOW","SBV"."SEGMENT8"),20,'0') AND RPAD("SBV"."SEGMENT8",20,'0')<=RPAD(NVL("RD"."SEGMENT8_HIGH","SBV"."SEGMENT8"),20,'z') AND RPAD("SBV"."SEGMENT9",20,'0')>=RPAD(NVL("RD"."SEGMENT9_LOW","SBV"."SEGMENT9"),20,'0') AND RPAD("SBV"."SEGMENT9",20,'0')<=RPAD(NVL("RD"."SEGMENT9_HIGH","SBV"."SEGMENT9"),20,'z') AND RPAD("SBV"."SEGMENT10",20,'0')>=RPAD(NVL("RD"."SEGMENT10_LOW","SBV"."SEGMENT10"),20,'0') AND RPAD("SBV"."SEGMENT10",20,'0')<=RPAD(NVL("RD"."SEGMENT10_HIGH","SBV"."SEGMENT10"),20,'z')) * 7 - access(RPAD("SEGMENT3",20,'0')>="RD"."SEGMENT3_LOW_EXT" AND RPAD("SEGMENT5",20,'0')>="RD"."SEGMENT5_LOW_EXT" AND RPAD("SEGMENT3",20,'0')<="RD"."SEGMENT3_HIGH_EXT" AND RPAD("SEGMENT5",20,'0')<="RD"."SEGMENT5_HIGH_EXT") * 7 - filter(RPAD("SEGMENT5",20,'0')>="RD"."SEGMENT5_LOW_EXT" AND RPAD("SEGMENT5",20,'0')<="RD"."SEGMENT5_HIGH_EXT") 可以看到连接方式使用的是 NESTED LOOPS 连接，并且成功利用了索引，我们得目的达到了。 改下后，执行查询，只需要1秒钟结果就出来了。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
参考资料1
表连接方式
嵌套循环（/+ use_nl(d,e) leading(e)/)：
嵌套循环的算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就要被扫描多少次。
嵌套循环被驱动表走索引只能走INDEX UNIQUE SCAN或者INDEX RANGE SCAN。嵌套循环被驱动表不能走TABLE ACCESS FULL，不能走INDEX FULLSCAN，不能走INDEX SKIP SCAN，也不能走INDEX FAST FULLSCAN。
哈希连接（/+ use_hash(d,e) leading(e)/) (/+ use_hash(d,e) swap_join_inputs(e)/)：
HASH连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“select列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash运算生成hash table，当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读入PGA中的work area），对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。哈希连接只支持等值连接。
排序合并连接（SORT MERGE JOIN）：
排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表（Oracle官方认为排序合并连接没有驱动表，笔者认为是有的），然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中的workarea）匹配数据。
两表等值关联，要么走NL（返回数据量少），要么走HASH（返回数据量多），一般情况下不要走SMJ(排序合并连接）。
表连接顺序
针对嵌套循环和哈希连接方式，来确定驱动表和被驱动表,可以用 /*+ ordered / 或者 /+ leading(emp dept) /方式来设置，推荐使用后者。
在pl/sql devloper 中F5查看执行计划
ORA-00904:“OTHER_XML”:标识符无效 报错解决办法:
1.报错的用户登录到sqlplus,执行以下命令: sql> drop table PLAN_TABLE; 2.创建表: sql> @?/rdbms/admin/utlxplan.sql; Table created.- 1
- 2
- 3
- 4
- 5
清空缓存
清空缓存：
ALTER SYSTEM FLUSH SHARED_POOL; --SGA-执行计划 sql缓存 数据字典缓存等 ALTER SYSTEM FLUSH BUFFER_CACHE; --SGA-一个缓存block，减少物理io，这里面有命中率的概念一个构造cr块 ALTER SYSTEM FLUSH GLOBAL CONTEXT; --清空连接池信息- 1
- 2
- 3
参考资料2
https://stackoverflow.com/questions/1947693/sql-optimizing-between-clause
-
相关阅读:
10、Kubernetes核心技术 - Label标签
XSS高级 svg 复现一个循环问题以及两个循环问题
速卖通获得aliexpress商品详情API
数据库：Hive转Presto（四）
一文讲清楚redis的线程池jedis
牛客网-《刷C语言百题》第四期
502 bad gateway原因、解决方法
ES可视化工具--ElasticHD--下载、安装、使用
损失函数与正负样本分配：YOLO系列
java CAS详解（深入源码剖析）
- 原文地址：https://blog.csdn.net/x6_9x/article/details/126160374
