[20120514]Invisible Indexes and FK问题.txt

[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 CREATE TABLE t2 AS SELECT rownum id FROM dual CONNECT BY level 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;