| 环境准备：建主从表，主表dept,子表emp及主外键约束
======================================================================
chennan@cwgl94>CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
表已创建。
chennan@cwgl94>CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
表已创建。
chennan@cwgl94>ALTER TABLE DEPT ADD CONSTRAINT DEPT_P PRIMARY KEY(DEPTNO);
表已更改。
chennan@cwgl94>ALTER TABLE EMP ADD CONSTRAINT EMP_P PRIMARY KEY(EMPNO);
表已更改。
chennan@cwgl94>ALTER TABLE EMP ADD CONSTRAINT EMP_F1 FOREIGN KEY (DEPTNO) REFERENCES DEPT;
表已更改。
chennan@cwgl94>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
chennan@cwgl94>select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
已选择4行。
======================================================================
第一方面，主表做删除的影响。
chennan@cwgl94>alter session set sql_trace=true;
会话已更改。
chennan@cwgl94>delete from dept where deptno=30;
delete from dept where deptno=30
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (CHENNAN.EMP_F1) - 已找到子记录日志
chennan@cwgl94>alter session set sql_trace=false;
会话已更改。
===================== 下面是sql_trace里的内容 ===========================
......
********************************************************************************
delete from dept
where
deptno=30
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 177 0 0
Execute 2 0.00 0.00 0 2 11 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 179 11 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE
1 INDEX UNIQUE SCAN DEPT_P (object id 42940)
********************************************************************************
select /*+ all_rows */ count(1)
from
"CHENNAN"."EMP" where "DEPTNO" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
6 TABLE ACCESS FULL EMP
********************************************************************************
.......
说明在主表删除时，会查询所有子表，看是否有引用的数据，如果子表的外键没有索引，则会发生全表扫描子表，如果子表很多、记录也很多，那么在Oracle里则是删除非常慢，在db2里会发生日志空间满的情况，还有锁定超时等引发911错误等。
象Fmis3.0的帐套、体系版本等，删除就会出现问题，就是因为有些子表的外键没有建索引。
第二方面，主表的主键若做更新，同样会扫描所有子表，如果子表外键没索引，则全表扫描：
chennan@cwgl94>alter session set sql_trace true;
会话已更改。
chennan@cwgl94>update dept set deptno=50 where deptno=30;
update dept set deptno=50 where deptno=30
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (CHENNAN.EMP_F1) - 已找到子记录日志
chennan@cwgl94>alter session set sql_trace false;
会话已更改。
=============== sql_trace内容 ================
********************************************************************************
update dept set deptno=50
where
deptno=30
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 13 0 0
Execute 1 0.00 0.00 0 1 8 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.02 0 14 8 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE
1 INDEX UNIQUE SCAN DEPT_P (object id 42940)
********************************************************************************
.....
********************************************************************************
select /*+ all_rows */ count(1)
from
"CHENNAN"."EMP" where "DEPTNO" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
6 TABLE ACCESS FULL EMP
********************************************************************************
第三方面的影响是 主、从表的join
emp表的deptno没建索引前的查询：
chennan@cwgl94>ALTER SYSTEM FLUSH SHARED_POOL;
系统已更改。
chennan@cwgl94>select ename,dname from emp e,dept d where e.deptno=d.deptno AND D.DEPTNO=30;
ENAME DNAME
---------- --------------
ALLEN SALES
WARD SALES
MARTIN SALES
BLAKE SALES
TURNER SALES
JAMES SALES
已选择6行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=5 Bytes=90)
1 0 NESTED LOOPS (Cost=3 Card=5 Bytes=90)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 B
ytes=11)
3 2 INDEX (UNIQUE SCAN) OF 'DEPT_P' (UNIQUE)
4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=5 Bytes=35)
建索引后
chennan@cwgl94>CREATE INDEX EMP_I1 ON EMP(DEPTNO);
索引已创建。
chennan@cwgl94>ANALYZE TABLE EMP COMPUTE STATISTICS;
表已分析。
chennan@cwgl94>ANALYZE TABLE DEPT COMPUTE STATISTICS;
表已分析。
chennan@cwgl94>select ename,dname from emp e,dept d where e.deptno=d.deptno AND D.DEPTNO=30;
ENAME DNAME
---------- --------------
ALLEN SALES
WARD SALES
MARTIN SALES
BLAKE SALES
TURNER SALES
JAMES SALES
已选择6行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=90)
1 0 NESTED LOOPS (Cost=2 Card=5 Bytes=90)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 B
ytes=11)
3 2 INDEX (UNIQUE SCAN) OF 'DEPT_P' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=5 By
tes=35)
5 4 INDEX (RANGE SCAN) OF 'EMP_I1' (NON-UNIQUE)
|