标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-01595 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,741)
- DB2 (22)
- MySQL (75)
- Oracle (1,590)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (161)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (68)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (582)
- Oracle安装升级 (95)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (84)
- PostgreSQL (27)
- pdu工具 (5)
- PostgreSQL恢复 (9)
- SQL Server (30)
- SQL Server恢复 (11)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- GAM、SGAM 或 PFS 页上存在页错误处理
- ORA-600 krhpfh_03-1208
- VMware勒索加密恢复(vmdk勒索恢复)
- ORA-39773: parse of metadata stream failed故障处理
- sql数据库备份失败—失败: 23(数据错误(循环冗余检查)
- vmdk文件被加密恢复(虚拟机文件加密)
- 差点被误操作的ORA-600 kcratr_nab_less_than_odr故障
- win平台19c 打patch遭遇2个小问题汇总
- pg单个数据库目录恢复-pdu恢复单个数据库目录数据
- pg删除数据恢复—pdu恢复pg delete数据
- .[OnlyBuy@cyberfear.com].REVRAC勒索mysql恢复
- 表dml操作权限授权给public,导致只读用户失效
- 21c数据库恢复遭遇ora-600 ktugct: corruption detected
- pg_control丢失/损坏处理
- 当前主流数据库版本服务支持周期-202503
- pg启动报invalid checkpoint record处理
- 删除redo导致ORA-00313 ORA-00312故障处理
- Navicat连接postgresql时出现column “datlastsysoid” does not exist错误解决
- aix磁盘损坏oracle数据库恢复
- pg误删除数据恢复(PostgreSQL delete数据恢复)
标签归档:not in
_optimizer_null_aware_antijoin和not in效率
准备两个测试表
SQL> conn chf/oracle Connected. SQL> select * from v$version; BANNER ---------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SQL> create table t_xifenfei 2 as select * from dba_objects; Table created. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 86259 SQL> create table t_xifenfei1 2 as select * from dba_objects; Table created. SQL> select count(*) from t_xifenfei1; COUNT(*) ---------- 86260 --删除部分记录,用来做not in的内部表 SQL> delete from t_xifenfei where object_id>86200; 918 rows deleted. SQL> commit; Commit complete.
查询_optimizer_null_aware_antijoin隐含参数默认值
SQL> conn / as sysdba Connected. SQL> col name for a52 SQL> col value for a24 SQL> col description for a50 set linesize 150 select a.ksppinm name,b.ksppstvl value,a.ksppdesc description from x$ksppi a,x$ksppcv b where a.inst_id = USERENV ('Instance') and b.inst_id = USERENV ('Instance') and a.indx = b.indx and upper(a.ksppinm) LIKE upper('%¶m%') order by name SQL> SQL> 2 3 4 5 6 7 8 / Enter value for param: _optimizer_null_aware_antijoin old 6: and upper(a.ksppinm) LIKE upper('%¶m%') new 6: and upper(a.ksppinm) LIKE upper('%_optimizer_null_aware_antijoin%') NAME VALUE DESCRIPTION ---------------------------------------------------- ------------------------ ----------------------------- _optimizer_null_aware_antijoin TRUE null-aware antijoin parameter
_optimizer_null_aware_antijoin从11.1.0.6开始引进,默认为true
_optimizer_null_aware_antijoin为true,执行not in
SQL> conn chf/oracle Connected. SQL> set autot trace SQL> set timing on SQL> set lines 150 SQL> set pages 1000 SQL> select count(*) from t_xifenfei1 where object_id not in(select object_id from t_xifenfei); Elapsed: 00:00:00.09 Execution Plan ---------------------------------------------------------- Plan hash value: 4048525918 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 688 (1)| 00:00:09 | | 1 | SORT AGGREGATE | | 1 | 10 | | | |* 2 | HASH JOIN RIGHT ANTI NA| | 1137 | 11370 | 688 (1)| 00:00:09 | | 3 | TABLE ACCESS FULL | T_XIFENFEI | 85341 | 416K| 344 (1)| 00:00:05 | | 4 | TABLE ACCESS FULL | T_XIFENFEI1 | 86260 | 421K| 344 (1)| 00:00:05 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"="OBJECT_ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2472 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
_optimizer_null_aware_antijoin为false,执行not in
SQL> alter session set "_optimizer_null_aware_antijoin"=false; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from t_xifenfei1 where object_id not in(select object_id from t_xifenfei); Elapsed: 00:02:29.64 Execution Plan ---------------------------------------------------------- Plan hash value: 2503880249 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 25M (1)| 86:20:57 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | | 3 | TABLE ACCESS FULL| T_XIFENFEI1 | 86260 | 421K| 344 (1)| 00:00:05 | |* 4 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 5 | 344 (1)| 00:00:05 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS (SELECT 0 FROM "T_XIFENFEI" "T_XIFENFEI" WHERE LNNVL("OBJECT_ID"<>:B1))) 4 - filter(LNNVL("OBJECT_ID"<>:B1)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 52982891 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
这里很明显,当 _optimizer_null_aware_antijoin为false的时候not in效率非常低(当in里面记录多,使用FILTER效率肯定低下).
_optimizer_null_aware_antijoin为false,执行not exists
SQL> alter session set "_optimizer_null_aware_antijoin"=false; Session altered. SQL>select count(*) from t_xifenfei1 b where not exists 2 (select 1 from t_xifenfei a where a.object_id=b.object_id); Elapsed: 00:00:00.07 Execution Plan ---------------------------------------------------------- Plan hash value: 2976307246 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 688 (1)| 00:00:09 | | 1 | SORT AGGREGATE | | 1 | 10 | | | |* 2 | HASH JOIN RIGHT ANTI| | 1137 | 11370 | 688 (1)| 00:00:09 | | 3 | TABLE ACCESS FULL | T_XIFENFEI | 85341 | 416K| 344 (1)| 00:00:05 | | 4 | TABLE ACCESS FULL | T_XIFENFEI1 | 86260 | 421K| 344 (1)| 00:00:05 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."OBJECT_ID"="B"."OBJECT_ID") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2472 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
对于11g的版本可以通过_optimizer_null_aware_antijoin参数开启NULL-aware Anti join特性来提高not in的效率,对于11g以下版本可以通过not exists来提高效率