[20120514]Invisible Indexes and FK问题.txt

SQL> select * from v$version ;

BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

CREATE TABLE t1 AS SELECT rownum id FROM dual CONNECT BY level <= 10;
CREATE TABLE t2 AS SELECT rownum id FROM dual CONNECT BY level <= 10;
ALTER TABLE t1 ADD CONSTRAINT t1_pk UNIQUE (id);
ALTER TABLE t2 ADD CONSTRAINT t1_t2_fk FOREIGN KEY (id) REFERENCES t1 (id);

CREATE INDEX i_t2_id ON t2 (id) INVISIBLE;

会话1:
INSERT INTO t2 VALUES (1);

会话2:

DELETE FROM t2 WHERE id = 2;
DELETE FROM t1 WHERE id = 2;

--测试发现,在这样的情况下,会出现阻塞情况,
--修改索引visible,问题消失.

ALTER INDEX i_t2_id ON t2 (id) VISIBLE;

ITPub博客

[20120514]Invisible Indexes and FK问题.txt

最新文章