Alert: 达梦数据库谓词无法推进VIEW，table最大查询优化个数限制（8 or 9）
达梦数据库(v8)在设计和实现上参考了Oracle数据库，并提供了一些兼容Oracle的功能和语法。这使得在某些情况下，可以将Oracle数据库应用迁移到达梦数据库上，而无需进行大量的修改和调整。但并不是所有的Oracle功能和特性都完全支持。在进行迁移或使用Oracle兼容性功能时，建议先进行充分的测试和验证，确保功能的正确性和性能的满足。最近协助一客户优化已上线2年的DM库，当view定义中增加超过一定数量的union all后性能突然变化的案例。
描述一下背景， 某客户的某业务数据量很少，有个前台面页查询数据来自多个表，对加载时间要注较高，数据源自源一个view, view中是几个单表数据的union all, 业务一直加，union all结果集就一直拼，直到增加到第9个union all时，响应时间突然增加近10倍，但是最后的一个view单独做为子查询又是很快(<20ms)， 是不是达梦对一个view里union all个数有限制呢？
构建测试环境
SQL> select * from v$version;
行号 BANNER
---------- ---------------------------------
1 DM Database Server 64 V8
2 DB Version: 0x7000c
3 03134283890-20220720-165295-10045
create table test1(id int,name varchar2(200),addr VARCHAR2(300),sec char(1),dept number(3);
insert into test1 select rownum,'anbob.com'||rownum,lpad('*',30,'*'),'1',mod(rownum,5) from dual connect by rownum<=1e4;
create table test2 as select * from test1;
...
create table test7 as select * from test1;
note:
上面构建几张测试表，可以在id列创建索引或定义主键，但不影响这们测试结果。
创建2张view
CREATE OR REPLACE VIEW V_TEST8 AS SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST1 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST2 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST3 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST4 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST5 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST6 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST7 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST8 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST7 WHERE SEX = '1' ; CREATE OR REPLACE VIEW V_TEST9 AS SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST1 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST2 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST3 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST4 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST5 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST6 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST7 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST8 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST7 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST7 WHERE SEX = '1' ;
Note:
V_TEST8 视图8个union all，一共9张表， V_TEST9 视图9个union all，一共10张表.
达梦数据库执行计划
NSET2 结果收集 执行计划最顶层操作符
PRJT2 投影
SLCT2 选择 表示过滤
FAGR2 快速聚集 select count(*) from 表; 会自动走这个执行计划
CSCN2 全表扫描 cluster index scan 的缩写，达梦默认创建的表是索引组织表
AAGR2 聚集 用于无group by的count,sum,max,min,avg运算
HAGR2 分组聚集 用于全表扫描GROUP BY聚集
BLKUP2 回表
SSEK2 二级索引扫描
SSCN 索引全扫描不回表
CSEK2 聚集索引扫描不回表
对比2个view的谓词推进
SQL> EXPLAIN SELECT * FROM V_TEST8 WHERE ID=1; 1 #NSET2: [22, 56, 226] 2 #PRJT2: [22, 56, 226]; exp_num(6), is_atom(FALSE) 3 #PRJT2: [22, 56, 226]; exp_num(6), is_atom(FALSE) 4 #UNION ALL: [22, 56, 226] 5 #PRJT2: [19, 50, 226]; exp_num(6), is_atom(FALSE) 6 #UNION ALL: [19, 50, 226] 7 #PRJT2: [17, 43, 226]; exp_num(6), is_atom(FALSE) 8 #UNION ALL: [17, 43, 226] 9 #PRJT2: [14, 37, 226]; exp_num(6), is_atom(FALSE) 10 #UNION ALL: [14, 37, 226] 11 #PRJT2: [11, 31, 226]; exp_num(6), is_atom(FALSE) 12 #UNION ALL: [11, 31, 226] 13 #PRJT2: [9, 25, 226]; exp_num(6), is_atom(FALSE) 14 #UNION ALL: [9, 25, 226] 15 #PRJT2: [6, 18, 226]; exp_num(6), is_atom(FALSE) 16 #UNION ALL: [6, 18, 226] 17 #PRJT2: [4, 12, 226]; exp_num(6), is_atom(FALSE) 18 #UNION ALL: [4, 12, 226] 19 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 20 #SLCT2: [1, 6, 226]; (TEST1.SEX = '1' AND TEST1.ID = 1) 21 #CSCN2: [1, 10000, 226]; INDEX33555471(TEST1) 22 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 23 #SLCT2: [1, 6, 226]; (TEST2.SEX = '1' AND TEST2.ID = 1) 24 #CSCN2: [1, 10000, 226]; INDEX33555472(TEST2) 25 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 26 #SLCT2: [1, 6, 226]; (TEST3.SEX = '1' AND TEST3.ID = 1) 27 #CSCN2: [1, 10000, 226]; INDEX33555473(TEST3) 28 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 29 #SLCT2: [1, 6, 226]; (TEST4.SEX = '1' AND TEST4.ID = 1) 30 #CSCN2: [1, 10000, 226]; INDEX33555474(TEST4) 31 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 32 #SLCT2: [1, 6, 226]; (TEST5.SEX = '1' AND TEST5.ID = 1) 33 #CSCN2: [1, 10000, 226]; INDEX33555475(TEST5) 34 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 35 #SLCT2: [1, 6, 226]; (TEST6.SEX = '1' AND TEST6.ID = 1) 36 #CSCN2: [1, 10000, 226]; INDEX33555476(TEST6) 37 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 38 #SLCT2: [1, 6, 226]; (TEST7.SEX = '1' AND TEST7.ID = 1) 39 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 40 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 41 #SLCT2: [1, 6, 226]; (TEST8.SEX = '1' AND TEST8.ID = 1) 42 #CSCN2: [1, 10000, 226]; INDEX33555478(TEST8) 43 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 44 #SLCT2: [1, 6, 226]; (TEST7.SEX = '1' AND TEST7.ID = 1) 45 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7)
Note:
可见view 的查询谓词id=1 推进到了view内部，每个表过滤后聚合，如果表上ID列有索引还能更快。
SQL> EXPLAIN SELECT * FROM V_TEST9 WHERE ID=1; 1 #NSET2: [26, 62, 226] 2 #PRJT2: [26, 62, 226]; exp_num(6), is_atom(FALSE) 3 #SLCT2: [26, 62, 226]; V_TEST9.ID = 1 4 #PRJT2: [26, 2500, 226]; exp_num(6), is_atom(FALSE) 5 #UNION ALL: [26, 2500, 226] 6 #PRJT2: [23, 2250, 226]; exp_num(6), is_atom(FALSE) 7 #UNION ALL: [23, 2250, 226] 8 #PRJT2: [20, 2000, 226]; exp_num(6), is_atom(FALSE) 9 #UNION ALL: [20, 2000, 226] 10 #PRJT2: [18, 1750, 226]; exp_num(6), is_atom(FALSE) 11 #UNION ALL: [18, 1750, 226] 12 #PRJT2: [15, 1500, 226]; exp_num(6), is_atom(FALSE) 13 #UNION ALL: [15, 1500, 226] 14 #PRJT2: [12, 1250, 226]; exp_num(6), is_atom(FALSE) 15 #UNION ALL: [12, 1250, 226] 16 #PRJT2: [9, 1000, 226]; exp_num(6), is_atom(FALSE) 17 #UNION ALL: [9, 1000, 226] 18 #PRJT2: [6, 750, 226]; exp_num(6), is_atom(FALSE) 19 #UNION ALL: [6, 750, 226] 20 #PRJT2: [4, 500, 226]; exp_num(6), is_atom(FALSE) 21 #UNION ALL: [4, 500, 226] 22 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 23 #SLCT2: [1, 250, 226]; TEST1.SEX = '1' 24 #CSCN2: [1, 10000, 226]; INDEX33555471(TEST1) 25 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 26 #SLCT2: [1, 250, 226]; TEST2.SEX = '1' 27 #CSCN2: [1, 10000, 226]; INDEX33555472(TEST2) 28 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 29 #SLCT2: [1, 250, 226]; TEST3.SEX = '1' 30 #CSCN2: [1, 10000, 226]; INDEX33555473(TEST3) 31 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 32 #SLCT2: [1, 250, 226]; TEST4.SEX = '1' 33 #CSCN2: [1, 10000, 226]; INDEX33555474(TEST4) 34 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 35 #SLCT2: [1, 250, 226]; TEST5.SEX = '1' 36 #CSCN2: [1, 10000, 226]; INDEX33555475(TEST5) 37 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 38 #SLCT2: [1, 250, 226]; TEST6.SEX = '1' 39 #CSCN2: [1, 10000, 226]; INDEX33555476(TEST6) 40 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 41 #SLCT2: [1, 250, 226]; TEST7.SEX = '1' 42 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 43 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 44 #SLCT2: [1, 250, 226]; TEST8.SEX = '1' 45 #CSCN2: [1, 10000, 226]; INDEX33555478(TEST8) 46 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 47 #SLCT2: [1, 250, 226]; TEST7.SEX = '1' 48 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 49 #PRJT2: [1, 250, 226]; exp_num(6), is_atom(FALSE) 50 #SLCT2: [1, 250, 226]; TEST7.SEX = '1' 51 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 已用时间: 2.464(毫秒). 执行号:0.
NOTE:
当增加到第9个union all(或union、minus)后, 执行计划改变，谓词并没有推进，而是当view内所有结果聚合后过滤。业务SQL同样的现象，无论第9个union all是什么查询(即使是前8个中的查询)， 排查因第9个union all语法限制。
业务SQL的执行计划对比
select * from WK_V_XXXXX_2023-08-14 where id='11';
77 #PRJT2: [1, 1, 432]; exp_num(18), is_atom(FALSE)
78 #SLCT2: [1, 1, 432]; (TABLE_XXXXXX.IS_DELETE = '0' AND TABLE_XXXXXX.STATUS = '1')
79 #BLKUP2: [1, 1, 432]; INDEX33557689(TABLE_XXXXXX)
80 #SSEK2: [1, 1, 432]; scan_type(ASC), INDEX33557689(TABLE_XXXXXX), scan_range['11','11'] ---id 列 unique index
-- 超过9个(含)union all后的view，执行计划变为下面:
2 #PRJT2: [271, 1162, 864]; exp_num(18), is_atom(FALSE)
3 #SLCT2: [271, 1162, 864]; WK_V_XXXXX_2023-08-14.id = '11'
...
76 #PRJT2: [1, 1, 432]; exp_num(18), is_atom(FALSE)
77 #BLKUP2: [1, 1, 432]; TABLE_XXXXXX_IS_DELETE_STATUS_0717(TABLE_XXXXXX)
78 #SSEK2: [1, 1, 432]; scan_type(ASC), TABLE_XXXXXX_IS_DELETE_STATUS_0717(TABLE_XXXXXX), scan_range[('0','1'),('0','1')]
Note:
谓词没有推进，还使用了选择率不好的二级索引delete state类似性别状态列的索引再回表，所以响应时间明显增加。尝试增加merge hint无法改变执行计划。
查看数据库参数
SQL> select * from v$dm_ini where PARA_VALUE in ('8','9') or MAX_VALUE in ('8','9');
行号 PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE DEFAULT_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- -------------------- ---------- --------- ---------- ------------- ------- ---------- ---------- --------------------------------------------------------------------------- ---------
1 KEEP 8 8 1048576 8 N 8 8 Initial System KEEP Buffer Size In Megabytes IN FILE
2 MTAB_MEM_SIZE 8 1 1048576 8 N 8 8 mtab memory size in KB IN FILE
3 CASE_WHEN_CVT_IFUN 9 0 63 9 N 9 9 Flag of converting subquery in case-when expression to IF operator SESSION
4 SPEED_SEMI_JOIN_PLAN 9 0 31 9 N 9 9 Flag of speeding up the generating process of semi join plan SESSION
5 FORCE_FLUSH_PAGES 8 0 1000 8 N 8 8 Schedule Thread Force Flush Pages SYS
6 IO_THR_GROUPS 8 1 512 8 N 8 8 The Number Of Io Thread Groups(Non-Windows Only) IN FILE
7 HLDR_BUF_SIZE 8 4 1024 8 N 8 8 HUGE table fast loader buffer size in Megabytes SYS
8 DBLINK_LOB_LEN 8 4 1024 8 N 8 8 BLOB/TEXT buffer size(KB) for dblink SYS
9 PWD_MIN_LEN 9 9 48 9 N 9 9 Password min length in byte, it's valid only if PWD_POLICY&2 not equal to 0 SYS
10 UDP_BTU_COUNT 8 4 32 8 N 8 8 Count of udp batch transfer units IN FILE
11 PAGE_TAIL_SIZE 8 0 4294967294 8 N 8 8 page_tail_size READ ONLY
12 DSC_FREQ_CONFLICT 8 1 1000 8 N 8 8 High frequency conflict counts SYS
13 MAX_OPT_N_TABLES 6 3 8 6 N 6 6 Maximum Number Of Tables For Query Optimization SESSION
13 rows got
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%max%\_N\_%') escape '\';
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ------------------- ------- ----- --------- ---------- ------------------------------------------------------------
1 MAX_OPT_N_TABLES SESSION 6 6 6 Maximum Number Of Tables For Query Optimization
2 MAX_N_GRP_PUSH_DOWN SESSION 5 5 5 Maximum Number Of RELs For GRP push down Optimization
3 MAX_OPT_N_OR_BEXPS SESSION 7 7 7 maximum number of OR bool expressions for query optimization
SQL> `p view
SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
行号 NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
---------- ------------------------- ------- ----- --------- ---------- ----------------------------------------------------------------------------------------------------
1 VIEW_PULLUP_FLAG SESSION 0 0 0 the flag of pulling up view
2 VIEW_PULLUP_MAX_TAB SESSION 7 7 7 Maximum number of tables that can be handled when pulling up view
3 COMPLEX_VIEW_MERGING SESSION 0 0 0 Flag of merging complex view into query without complex view
4 VIEW_FILTER_MERGING SESSION 138 138 138 Flag of merging view filter
5 VIEW_OPT_FLAG SESSION 1 1 1 flag of optimize view
6 TRX_VIEW_SIZE IN FILE 512 512 512 The buffer size of local active transaction ids in TRX_VIEW
7 TRX_VIEW_MODE IN FILE 1 1 1 The transaction view mode, 0: Active ids snap; 1: Recycled id array
8 ENABLE_DIST_VIEW_UPDATE SYS 0 0 0 whether view with distinct can be updated
9 DBLINK_OPT_VIEW SYS 1 1 1 dblink query optimize for local view
10 HASH_ACTIVE_TRX_VIEW IN FILE 0 0 0 When value > 10, hash table will be used for active trx view if there was more trxes than configured
11 DROP_CASCADE_VIEW SESSION 0 0 0 Whether to drop cascade view while dropping table or view
12 DSC_TRX_VIEW_SYNC SYS 0 0 0 Whether to wait response after broadcast trx view to other ep
13 DSC_TRX_VIEW_BRO_INTERVAL SYS 1000 1000 1000 Time interval of trx view broadcast
14 CVIEW_STAR_WITH_PREFIX SESSION 1 1 1 Whether append prefix for star item when create view
14 rows got
Note:
在达梦数据库中确实有一些表个数的查询优化限制，但修改参数未能解决，怀疑是数据库软件hard code编码限制。
临时解决方法：
1， 改写SQL减少同一层子查询的个数，如合并前面查询
2， 其中部分union all 创建为中间view, 在主view 拼接
3， 在view定义中再增加一层部分view的子查询,如下：
CREATE OR REPLACE VIEW V_TEST9_2 AS SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST1 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST2 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST3 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST4 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST5 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST6 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST7 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST8 WHERE SEX = '1' UNION ALL select * from ( SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST7 WHERE SEX = '1' UNION ALL SELECT "ID","NAME","ADDR","SEX","REMARK","DEPT" FROM TEST7 WHERE SEX = '1' ) ; SQL> EXPLAIN SELECT * FROM V_TEST9_2 WHERE ID=1; 1 #NSET2: [24, 62, 226] 2 #PRJT2: [24, 62, 226]; exp_num(6), is_atom(FALSE) 3 #PRJT2: [24, 62, 226]; exp_num(6), is_atom(FALSE) 4 #UNION ALL: [24, 62, 226] 5 #PRJT2: [19, 50, 226]; exp_num(6), is_atom(FALSE) 6 #UNION ALL: [19, 50, 226] 7 #PRJT2: [17, 43, 226]; exp_num(6), is_atom(FALSE) 8 #UNION ALL: [17, 43, 226] 9 #PRJT2: [14, 37, 226]; exp_num(6), is_atom(FALSE) 10 #UNION ALL: [14, 37, 226] 11 #PRJT2: [11, 31, 226]; exp_num(6), is_atom(FALSE) 12 #UNION ALL: [11, 31, 226] 13 #PRJT2: [9, 25, 226]; exp_num(6), is_atom(FALSE) 14 #UNION ALL: [9, 25, 226] 15 #PRJT2: [6, 18, 226]; exp_num(6), is_atom(FALSE) 16 #UNION ALL: [6, 18, 226] 17 #PRJT2: [4, 12, 226]; exp_num(6), is_atom(FALSE) 18 #UNION ALL: [4, 12, 226] 19 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 20 #SLCT2: [1, 6, 226]; (TEST1.SEX = '1' AND TEST1.ID = 1) 21 #CSCN2: [1, 10000, 226]; INDEX33555471(TEST1) 22 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 23 #SLCT2: [1, 6, 226]; (TEST2.SEX = '1' AND TEST2.ID = 1) 24 #CSCN2: [1, 10000, 226]; INDEX33555472(TEST2) 25 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 26 #SLCT2: [1, 6, 226]; (TEST3.SEX = '1' AND TEST3.ID = 1) 27 #CSCN2: [1, 10000, 226]; INDEX33555473(TEST3) 28 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 29 #SLCT2: [1, 6, 226]; (TEST4.SEX = '1' AND TEST4.ID = 1) 30 #CSCN2: [1, 10000, 226]; INDEX33555474(TEST4) 31 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 32 #SLCT2: [1, 6, 226]; (TEST5.SEX = '1' AND TEST5.ID = 1) 33 #CSCN2: [1, 10000, 226]; INDEX33555475(TEST5) 34 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 35 #SLCT2: [1, 6, 226]; (TEST6.SEX = '1' AND TEST6.ID = 1) 36 #CSCN2: [1, 10000, 226]; INDEX33555476(TEST6) 37 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 38 #SLCT2: [1, 6, 226]; (TEST7.SEX = '1' AND TEST7.ID = 1) 39 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 40 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 41 #SLCT2: [1, 6, 226]; (TEST8.SEX = '1' AND TEST8.ID = 1) 42 #CSCN2: [1, 10000, 226]; INDEX33555478(TEST8) 43 #PRJT2: [4, 12, 226]; exp_num(6), is_atom(FALSE) 44 #PRJT2: [4, 12, 226]; exp_num(6), is_atom(FALSE) 45 #UNION ALL: [4, 12, 226] 46 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 47 #SLCT2: [1, 6, 226]; (TEST7.SEX = '1' AND TEST7.ID = 1) 48 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 49 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 50 #SLCT2: [1, 6, 226]; (TEST7.SEX = '1' AND TEST7.ID = 1) 51 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 已用时间: 2.888(毫秒). 执行号:0.
— 2023-8-29 update —
经与DM确认原来也可以调整DB参数可以解决，但该参数从官方文档没有找到该值的描述。
SQL> alter session set '**************'=1; --参数暂时不方便公开 DMSQL 过程已成功完成 已用时间: 0.785(毫秒). 执行号:702. SQL> EXPLAIN SELECT * FROM V_TEST9 WHERE ID=1; 1 #NSET2: [24, 62, 226] 2 #PRJT2: [24, 62, 226]; exp_num(6), is_atom(FALSE) 3 #PRJT2: [24, 62, 226]; exp_num(6), is_atom(FALSE) 4 #UNION ALL: [24, 62, 226] 5 #PRJT2: [22, 56, 226]; exp_num(6), is_atom(FALSE) 6 #UNION ALL: [22, 56, 226] 7 #PRJT2: [19, 50, 226]; exp_num(6), is_atom(FALSE) 8 #UNION ALL: [19, 50, 226] 9 #PRJT2: [17, 43, 226]; exp_num(6), is_atom(FALSE) 10 #UNION ALL: [17, 43, 226] 11 #PRJT2: [14, 37, 226]; exp_num(6), is_atom(FALSE) 12 #UNION ALL: [14, 37, 226] 13 #PRJT2: [11, 31, 226]; exp_num(6), is_atom(FALSE) 14 #UNION ALL: [11, 31, 226] 15 #PRJT2: [9, 25, 226]; exp_num(6), is_atom(FALSE) 16 #UNION ALL: [9, 25, 226] 17 #PRJT2: [6, 18, 226]; exp_num(6), is_atom(FALSE) 18 #UNION ALL: [6, 18, 226] 19 #PRJT2: [4, 12, 226]; exp_num(6), is_atom(FALSE) 20 #UNION ALL: [4, 12, 226] 21 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 22 #SLCT2: [1, 6, 226]; (TEST1.SEX = '1' AND TEST1.ID = 1) 23 #CSCN2: [1, 10000, 226]; INDEX33555471(TEST1) 24 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 25 #SLCT2: [1, 6, 226]; (TEST2.SEX = '1' AND TEST2.ID = 1) 26 #CSCN2: [1, 10000, 226]; INDEX33555472(TEST2) 27 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 28 #SLCT2: [1, 6, 226]; (TEST3.SEX = '1' AND TEST3.ID = 1) 29 #CSCN2: [1, 10000, 226]; INDEX33555473(TEST3) 30 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 31 #SLCT2: [1, 6, 226]; (TEST4.SEX = '1' AND TEST4.ID = 1) 32 #CSCN2: [1, 10000, 226]; INDEX33555474(TEST4) 33 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 34 #SLCT2: [1, 6, 226]; (TEST5.SEX = '1' AND TEST5.ID = 1) 35 #CSCN2: [1, 10000, 226]; INDEX33555475(TEST5) 36 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 37 #SLCT2: [1, 6, 226]; (TEST6.SEX = '1' AND TEST6.ID = 1) 38 #CSCN2: [1, 10000, 226]; INDEX33555476(TEST6) 39 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 40 #SLCT2: [1, 6, 226]; (TEST7.SEX = '1' AND TEST7.ID = 1) 41 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 42 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 43 #SLCT2: [1, 6, 226]; (TEST8.SEX = '1' AND TEST8.ID = 1) 44 #CSCN2: [1, 10000, 226]; INDEX33555478(TEST8) 45 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 46 #SLCT2: [1, 6, 226]; (TEST7.SEX = '1' AND TEST7.ID = 1) 47 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 48 #PRJT2: [1, 6, 226]; exp_num(6), is_atom(FALSE) 49 #SLCT2: [1, 6, 226]; (TEST7.SEX = '1' AND TEST7.ID = 1) 50 #CSCN2: [1, 10000, 226]; INDEX33555477(TEST7) 已用时间: 4.104(毫秒). 执行号:0.
— over —
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)评论关闭。