两表关联查询-创建约束，外键对比
--开启执行计划：
set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
找不到会话标识符，启用检查
SP2-0611: Error enabling STATISTICS report
错误：统计报告
grant plustrace to scott
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist ---角色不存在
SQL> select * from dba_roles where role='PLUSTRACE'; ---查询角色
no rows selected
SQL> @?/sqlplus/admin/plustrce.sql -----执行脚本
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL> set echo off
-----------------------------
grant plustrace to scott;
SQL> select USERNAME,GRANTED_ROLE from user_role_privs;
USERNAME GRANTED_ROLE
------------------------------ ------------------------------
SCOTT CONNECT
SCOTT PLUSTRACE
SCOTT RESOURCE
----这是最后的：类似的查询；
select empno,ename,sal from scott.emp e,scott.dept d where e.deptno=d.deptno;
--生成测试：
SQL> create table emp1 as select * from emp;
SQL> create table dept2 as select * from dept;
**********测试查询一（两个表，没有索引，没有约束）*********************************
SQL> set autotrace traceonly;
SQL> select empno,ename,sal from emp1 e,dept2 d where e.deptno=d.deptno;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 826 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 826 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT2 | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP1 | 14 | 644 | 3 (0)| 00:00:01 |
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
dynamic sampling used for this statement (level=2)
7 consistent gets
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP1');
*****************************************测试查询二（dept2表中建立了主键约束（建立了索引））*******************************************
--创建外键约束：
alter table emp1 add constraint emp_forkey foreign key(deptno) references dept2(deptno)
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
--没有匹配的这个列表唯一或主键
子表创建与父表的：外键约束，必须要明确父表关联列，有主键或者唯一约束；
SQL> alter table dept2 add constraint p_k primary key(deptno);
SQL> select owner,constraint_name from user_constraints ;
OWNER CONSTRAINT_NAME
-------------------- --------------------
SCOTT P_K
SQL> select INDEX_NAME,TABLE_NAME from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
P_K DEPT2
--建立主键约束后，父表dept2，的列deptno列自动生成索引；
SQL> set autotrac traceonly
SQL> select empno,ename,sal from emp1 e,dept2 d where e.deptno=d.deptno;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | *成本从6减为3
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 420 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 420 | 3 (0)| 00:00:01 | *********从哈希转为嵌套循环
| 2 | TABLE ACCESS FULL| EMP1 | 14 | 238 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| P_K | 1 | 13 | 0 (0)| 00:00:01 | *********全表扫描，转为索引范围
3 - access("E"."DEPTNO"="D"."DEPTNO")
Note
dynamic sampling used for this statement (level=2)
7 consistent gets
***************************************测试三（emp1与dept2建立主外键约束）**************************************
SQL> set autotrace off
SQL> alter table emp1 add constraint f_y foreign key(deptno) references dept2(deptno);
SQL> select owner,constraint_name from user_constraints;
OWNER CONSTRAINT_NAME
-------------------- --------------------
SCOTT P_K
SCOTT F_Y
SQL> select index_name,table_name from user_indexes where table_name='EMP1';
no rows selected
select empno,ename,sal from emp1 e,dept2 d where e.deptno=d.deptno;
| 0 | SELECT STATEMENT | | 14 | 238 | 3 (0)| 00:00:01 | ---因为测试数据量少，成本没有变化
|* 1 | TABLE ACCESS FULL| EMP1 | 14 | 238 | 3 (0)| 00:00:01 | --对DEPT2表根本没有查询；
----------
1 - filter("E"."DEPTNO" IS NOT NULL) ---直接根据dept2,deptno列非空(主键，非空且唯一）；
14 rows selected.
*******测试五、测试四的基础上插入一条数据*******
insert into emp1(empno,ename,deptno) values(8000,'YANG',null);
select empno,ename,sal from emp1 e,dept2 d where e.deptno=d.deptno;
--因为查询是等值连接，所以，执行计划不受影响；
但是会存在脏数据：对于外键约束来说，子表中的数据必须在父表中有匹配值，本次测试中，插入了Null，值没有报错；
insert into emp1(empno,ename,deptno) values(8100,'YG',11);
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.F_Y) violated - parent key not found 外键还是好的；
小结：主外键约束，要保证数据干净，子表中，关联列加上not null 约束靠谱；
alter table emp1 modify deptno not null
*
ERROR at line 1:
ORA-02296: cannot enable (SCOTT.) - null values found
SQL> delete emp1 where deptno is null;
SQL> alter table emp1 modify deptno not null；
