ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120514]Invisible Indexes and FK问题.txt

[20120514]Invisible Indexes and FK问题.txt

原创 Linux操作系统 作者:lfree 时间:2012-05-14 16:16:57 0 删除 编辑
[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博客 ” ,链接:http://blog.itpub.net/267265/viewspace-723540/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6293245