[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 <= 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;