[20190524]Table Elimination.txt

[20190524]Table Elimination.txt http://raajeshwaran.blogspot.com/2019/05/table-elimination-part-v.html --//昨天看了以上链接，里面提到rely novalidate，这个方式建立的主键无效吗？ --//这些主要使用在数据仓库，对于这方面信息不熟悉。重复测试，顺便测试Table Elimination。 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试： SCOTT@test01p> grant dba to test identified by test; Grant succeeded. --//以test用户登录: create table emp as select * from scott.emp ; create table dept as select * from scott.dept ; alter table dept add constraint dept_pk primary key(deptno) rely novalidate; alter table emp add constraint emp_fk foreign key(deptno) references dept rely novalidate modify deptno not null; --//分析略. SELECT constraint_name, table_name, constraint_type, status, validated, rely   FROM user_constraints  WHERE table_name in ('EMP','DEPT')    AND constraint_type in ('P','R') ; CONSTRAINT_NAME TABLE_NAME C STATUS  VALIDATED     RELY --------------- ---------- - ------- ------------- ---- EMP_FK          EMP        R ENABLED NOT VALIDATED RELY DEPT_PK         DEPT       P ENABLED NOT VALIDATED RELY     3.测试: TEST@test01p> show parameter optimizer_features_enable NAME                                 TYPE                 VALUE ------------------------------------ -------------------- -------- optimizer_features_enable            string               12.2.0.1 select ename from emp e, dept d where e.deptno = d.deptno; Plan hash value: 4269077325 ------------------------------------------------------------------------------- | Id  | Operation          | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |         |        |       |     3 (100)|          | |   1 |  NESTED LOOPS      |         |     14 |   168 |     3   (0)| 00:00:01 | |   2 |   TABLE ACCESS FULL| EMP     |     14 |   126 |     3   (0)| 00:00:01 | |*  3 |   INDEX UNIQUE SCAN| DEPT_PK |      1 |     3 |     0   (0)|          | ------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / E@SEL$1    3 - SEL$1 / D@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("E"."DEPTNO"="D"."DEPTNO") --//并没有消除Table Elimination TEST@test01p> alter session set query_rewrite_integrity=Trusted; Session altered. select ename from emp e, dept d where e.deptno = d.deptno; Plan hash value: 3956160932 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          | |   1 |  TABLE ACCESS FULL| EMP  |     14 |    84 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$F7859CDE / E@SEL$1 --//要设置query_rewrite_integrity=Trusted才能得到Table Elimination的情况. 4.退出继续测试: TEST@test01p> alter session set optimizer_features_enable='11.2.0.4'; Session altered. Select ename from emp e, dept d where e.deptno = d.deptno; Plan hash value: 4269077325 ------------------------------------------------------------------------------- | Id  | Operation          | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |         |        |       |     3 (100)|          | |   1 |  NESTED LOOPS      |         |     14 |   168 |     3   (0)| 00:00:01 | |   2 |   TABLE ACCESS FULL| EMP     |     14 |   126 |     3   (0)| 00:00:01 | |*  3 |   INDEX UNIQUE SCAN| DEPT_PK |      1 |     3 |     0   (0)|          | ------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / E@SEL$1    3 - SEL$1 / D@SEL$1 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('12.2.0.1')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "E"@"SEL$1")       INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))       LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")       USE_NL(@"SEL$1" "D"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("E"."DEPTNO"="D"."DEPTNO") --//我的测试不行.修改参数optimizer_features_enable='11.2.0.4'. 5.其它: TEST@test01p> insert into dept values (40,'a','b'); insert into dept values (40,'a','b') * ERROR at line 1: ORA-00001: unique constraint (TEST.DEPT_PK) violated INSERT INTO TEST.EMP ( EMPNO ,ENAME ,JOB ,MGR ,HIREDATE ,SAL ,DEPTNO) VALUES ( 7369 ,'SMITH' ,'CLERK' ,7902 ,TO_DATE ('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS') ,800 ,50); * ERROR at line 1: ORA-02291: integrity constraint (TEST.EMP_FK) violated - parent key not found --//删除约束看看看看. TEST@test01p> insert into dept values (40,'a','b'); 1 row created. TEST@test01p> commit ; Commit complete. TEST@test01p> alter table dept add constraint dept_pk primary key(deptno) rely novalidate; alter table dept add constraint dept_pk primary key(deptno) rely novalidate                                 * ERROR at line 1: ORA-02437: cannot validate (TEST.DEPT_PK) - primary key violated TEST@test01p> delete from dept where deptno=40 and loc='b'; 1 row deleted. TEST@test01p> commit ; Commit complete. TEST@test01p> alter table dept add constraint dept_pk primary key(deptno) rely novalidate; Table altered. TEST@test01p> alter table emp add constraint emp_fk foreign key(deptno) references dept rely novalidate ; Table altered. --//相当于对于当前的情况不做检查. TEST@test01p> alter session set query_rewrite_integrity=Trusted; Session altered. TEST@test01p> select ename from emp e, dept d where e.deptno = d.deptno; ENAME ---------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER SMITH 15 rows selected. --//这样查询就是错的.实际上仅仅14条输出. TEST@test01p> alter session set query_rewrite_integrity=enforced; Session altered. TEST@test01p> select ename from emp e, dept d where e.deptno = d.deptno; ENAME ---------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 rows selected.