[20180808]exists and not exists.txt

来源：这里教程网时间：2026-03-03 11:50:55 作者：

[20180808]exists and not exists.txt --//生产系统遇到的一个性能问题,通过例子来说明: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 SCOTT@test01p> create table t1 as select * from all_objects; Table created. SCOTT@test01p> create table t2 as select object_id,'1' flag from t1; Table created. SCOTT@test01p> select max(object_id) from t2; MAX(OBJECT_ID) -------------- 107828 SCOTT@test01p> update t2 set flag='0' where object_id=107828; 1 row updated. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> create index i_t2_flag on t2(flag); Index created. --//分析表,并且t2的flag字段建立直方图. execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false); execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't2',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 10 ',Cascade => True ,No_Invalidate => false); ` 2.测试: SCOTT@test01p> alter session set statistics_level=all; Session altered. SCOTT@test01p> select object_name from t1 where not exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='1' ); OBJECT_NAME -------------------- T1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID d4qcxhmwy49r1, child number 0 ------------------------------------- select object_name from t1 where not exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='1' ) Plan hash value: 629543484 ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 728 (100)| | 1 |00:00:00.24 | 1667 | 1511 | | | | |* 1 | HASH JOIN RIGHT ANTI| | 1 | 899 | 33263 | 1672K| 728 (1)| 00:00:01 | 1 |00:00:00.24 | 1667 | 1511 | 5536K| 3056K| 5658K (0)| |* 2 | TABLE ACCESS FULL | T2 | 1 | 89876 | 614K| | 46 (3)| 00:00:01 | 89876 |00:00:00.02 | 152 | 0 | | | | | 3 | TABLE ACCESS FULL | T1 | 1 | 89877 | 2633K| | 421 (1)| 00:00:01 | 89877 |00:00:00.11 | 1515 | 1511 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / T2@SEL$2 3 - SEL$5DA710D3 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID") 2 - filter("T2"."FLAG"='1') --//仔细看id-2.过滤条件是 2 - filter("T2"."FLAG"='1').这样即使你建立索引在t2.flag也不会使用.因为flag='1'占大多数. --//实际上对于当前应用改成如下是等效的.因为flag仅仅两种取值'0','1'. SCOTT@test01p> select object_name from t1 where exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='0' ); OBJECT_NAME -------------------- T1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 1y5xvtwz0u11f, child number 0 ------------------------------------- select object_name from t1 where exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='0' ) Plan hash value: 1273788863 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 423 (100)| | 1 |00:00:00.19 | 1518 | 1512 | | | | |* 1 | HASH JOIN RIGHT SEMI | | 1 | 1 | 37 | 423 (1)| 00:00:01 | 1 |00:00:00.19 | 1518 | 1512 | 2168K| 2168K| 697K (0)| | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.04 | 3 | 1 | | | | |* 3 | INDEX RANGE SCAN | I_T2_FLAG | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.04 | 2 | 1 | | | | | 4 | TABLE ACCESS FULL | T1 | 1 | 89877 | 2633K| 421 (1)| 00:00:01 | 89877 |00:00:00.12 | 1515 | 1511 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / T2@SEL$2 3 - SEL$5DA710D3 / T2@SEL$2 4 - SEL$5DA710D3 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID") 3 - access("T2"."FLAG"='0') --//实际上到具体应用object_id字段是主键,如果在上面建立索引,逻辑读更小. CREATE UNIQUE INDEX SCOTT.pk_t1 ON SCOTT.T1 (OBJECT_ID); ALTER TABLE SCOTT.T1 ADD CONSTRAINT pk_t1 PRIMARY KEY (OBJECT_ID); CREATE UNIQUE INDEX SCOTT.pk_t2 ON SCOTT.T2 (OBJECT_ID); ALTER TABLE SCOTT.T2 ADD CONSTRAINT pk_t2 PRIMARY KEY (OBJECT_ID); SCOTT@test01p> select object_name from t1 where exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='0' ); OBJECT_NAME -------------------- T1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 1y5xvtwz0u11f, child number 0 ------------------------------------- select object_name from t1 where exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='0' ) Plan hash value: 4193600567 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.04 | 6 | 2 | | | | | 1 | NESTED LOOPS | | 1 | | | | | 1 |00:00:00.04 | 6 | 2 | | | | | 2 | NESTED LOOPS | | 1 | 1 | 37 | 3 (0)| 00:00:01 | 1 |00:00:00.03 | 5 | 1 | | | | | 3 | SORT UNIQUE | | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 | 2048 | 2048 | 2048 (0)| | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 | | | | |* 5 | INDEX RANGE SCAN | I_T2_FLAG | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 0 | | | | |* 6 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.03 | 2 | 1 | | | | | 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | 1 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 4 - SEL$5DA710D3 / T2@SEL$2 5 - SEL$5DA710D3 / T2@SEL$2 6 - SEL$5DA710D3 / T1@SEL$1 7 - SEL$5DA710D3 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T2"."FLAG"='0') 6 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID") Note ----- - this is an adaptive plan --//而select object_name from t1 where not exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='1' );执行计划不变.不再贴出. --//我有时候想开发写sql代码过脑子没有,有时候真的很无语很无奈...