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