[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.