做系统前的浏览网站能找回吗,营销网站建设的价格,查商标官网,网站维护方法先表明我的立场、我是绝对支持外键一定要加索引#xff01;虽然在高版本的Oracle里、对这个要求有所降低、但依然有如下原因#xff1a;① 死锁外键未加索引是导致死锁的最主要原因、因为无论更新父表主键、或者删除一个父表记录、都会在子表加一个表锁这就会不必要的锁定更多…先表明我的立场、我是绝对支持外键一定要加索引虽然在高版本的Oracle里、对这个要求有所降低、但依然有如下原因① 死锁外键未加索引是导致死锁的最主要原因、因为无论更新父表主键、或者删除一个父表记录、都会在子表加一个表锁这就会不必要的锁定更多的行、从而影响并发性② ON DELETE CASCADE对于删除的每一个父行、都会把子表全表扫描一次如EMP是DEPT的子表DELETE dept WHERE deptno10 会级联至EMP③ 从父表查询子表如EMP是DEPT的子表SELECT *FROM dept,empWHERE emp.deptnodept.deptno anddept.dname :X另外、证明子表由于外键未加索引而被锁住、可经由下列方法ALTER TABLE DISABLE TABLE LOCK;那么、对父表的可能导致表锁的任何 UPDATE 或 DELETE 都会收到如下错误ERROR at line 1ORA-00069 cannot acquire lock -- table locks disable for以下做个简单的外键未加索引的测试建立表hrORCL create table t_father (id number,name varchar2(25),primary key(id));hrORCL create table t_sun (fid number,name varchar2(25),foreign key(fid) references t_father(id));hrORCL select table_name,CONSTRAINT_NAME,STATUS,R_CONSTRAINT_NAME from user_constraints where owner\HR\ and table_name in (\T_FATHER\,\T_SUN\);TABLE_NAME CONSTRAINT_NAME STATUS R_CONSTRAINT_NAME------------------------------ ------------------------------ -------- ------------------------------T_FATHER SYS_C005495 ENABLEDT_SUN SYS_C005497 ENABLED SYS_C005495倒入数据并分析表hrORCL insert into t_father select rownum,rownum||\a\ from dual connect by rownum1000;hrORCL insert into t_sun select rownum,rownum||\b\ from dual connect by rownum1000;hrORCL commit;hrORCL exec dbms_stats.gather_table_stats(ownname\HR\,tabname\T_FATHER\);hrORCL exec dbms_stats.gather_table_stats(ownname\HR\,tabname\T_SUN\);用以下 TOM 给出的脚本检查外键无索引的表COLUMN COLUMNS format a30 word_wrappedCOLUMN tablename format a15 word_wrappedCOLUMN constraint_name format a15 word_wrappedSELECT TABLE_NAME,CONSTRAINT_NAME,CNAME1 || NVL2(CNAME2, \,\ || CNAME2, NULL) ||NVL2(CNAME3, \,\ || CNAME3, NULL) ||NVL2(CNAME4, \,\ || CNAME4, NULL) ||NVL2(CNAME5, \,\ || CNAME5, NULL) ||NVL2(CNAME6, \,\ || CNAME6, NULL) ||NVL2(CNAME7, \,\ || CNAME7, NULL) ||NVL2(CNAME8, \,\ || CNAME8, NULL) COLUMNSFROM (SELECT B.TABLE_NAME,B.CONSTRAINT_NAME,MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,COUNT(*) COL_CNTFROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,POSITIONFROM USER_CONS_COLUMNS) A,USER_CONSTRAINTS BWHERE A.CONSTRAINT_NAME B.CONSTRAINT_NAMEAND B.CONSTRAINT_TYPE \R\GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONSWHERE COL_CNT ALL(SELECT COUNT(*)FROM USER_IND_COLUMNS IWHERE I.TABLE_NAME CONS.TABLE_NAMEAND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,CNAME6, CNAME7, CNAME8)AND I.COLUMN_POSITION CONS.COL_CNTGROUP BY I.INDEX_NAME)/hrORCL /TABLE_NAME CONSTRAINT_NAME COLUMNS------------------------------ --------------- ------------------------------T_SUN SYS_C005497 FID以下进行测试1)Session_A:hrORCL select sid from v$session where sid in (select sid from v$mystat where rownum1);SID----------159hrORCL delete t_sun where fid998;1 row deleted.2)Session_B:hrORCL select sid from v$session where sid in (select sid from v$mystat where rownum1);SID----------142hrORCL delete t_sun where fid123;1 row deleted.3)Session_AhrORCL delete t_father where id555;----请求子表的表锁却不可得之、被hang住了用下面脚本查询数据库锁情况SELECT a.sid ||decode(request,0,\ :holder\,\ :Waiter\) sess_id,blocking_session blocker,lmode,request,a.type,c.object_name,decode(row_wait_obj#,-1,\Holder of Lock !!!\,dbms_rowid.rowid_create(1,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#)) row_id,nvl(SQL_FULLTEXT, \Holder of Lock !!!\) sqltextFROM V$LOCK A, V$LOCKED_OBJECT B, ALL_OBJECTS C, V$SESSION D, V$SQL EWHERE (id1, id2, a.type) in(select id1, id2, type from v$lock where request 0)AND a.sid b.session_idAND b.object_id c.object_idAND d.sid a.sidAND d.sql_hash_value e.hash_value()sysORCL /SESS_ID BLOCKER LMODE REQUEST TY OBJECT_NAME ROW_ID SQLTEXT------------------------------------------------ ---------- ---------- ---------- -- ------------------------------ ------------------ --------------------------------------------------------------------------------159 :Waiter 142 3 5 TM T_SUN Holder of Lock !!! delete t_father where id555159 :Waiter 142 3 5 TM T_FATHER Holder of Lock !!! delete t_father where id555142 :holder 3 0 TM T_SUN Holder of Lock !!! Holder of Lock !!!142 :holder 3 0 TM T_FATHER Holder of Lock !!! Holder of Lock !!!By David Lin2013-06-07Good Luck
