本文通过创建带有约束的表并进行数据插入、删除等操作，详细记录了约束如何影响redo size，从而揭示了约束对数据库效率的显著影响。实验表明，主键约束在插入大量数据时会导致大量的redo日志生成，而禁用约束可以显著减少redo size。
约束对效率的影响
＝＝＝＝＝＝＝＝＝＝
sqlplus scott/tiger
SQL> create table t1 pctfree 0
2 as
3 select * from dba_objects where 1=2;
Table created.
SQL> alter table T1
2 add constraint T1_PK primary key (OBJECT_ID);
Table altered.
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size'
and b.value > 0
SQL>
SQL> set timing on
17:07:23 SQL> set time on
17:07:24 SQL> create table t2 pctfree 0
17:07:44 2 as
17:07:44 3 select
17:07:44 4 t.Owner ,
17:07:44 5 t.Object_Name ,
17:07:44 6 t.Subobject_Name ,
17:07:44 7 rownum obid ,
17:07:44 8 t.Data_Object_Id ,
17:07:44 9 t.Object_Type ,
17:07:44 10 t.Created ,
17:07:44 11 t.Last_Ddl_Time ,
17:07:44 12 t.Timestamp ,
17:07:44 13 t.Status ,
17:07:44 14 t.Temporary ,
17:07:44 15 t.Generated ,
17:07:44 16 t.Secondary
17:07:44 17 from (select * from dba_objects where rownum <=20000) t,
17:07:44 18 (select rownum rn from dba_objects where rownum <=200) d;
Elapsed: 00:01:04.59
18:36:37 SQL> 18:36:37 SQL> 18:36:37 SQL> 18:36:37 SQL> 18:36:37 SQL>
18:42:08 SQL> select a.name, b.value
18:42:13 2 from v$statname a, v$mystat b
18:42:13 3 where a.statistic# = b.statistic#
18:42:13 4 and a.name = 'redo size'
18:42:13 5 and b.value > 0
18:42:15 6 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 410796268
Elapsed: 00:00:00.00
18:42:16 SQL> set autot traceonly statistics
18:42:42 SQL> INSERT INTO t1 SELECT * FROM t2;
4000000 rows created.
Statistics
----------------------------------------------------------
2013 recursive calls
583664 db block gets
190827 consistent gets
49685 physical reads
694767276 redo size ＝＝ redo非常大,因为约束的索引
794 bytes sent via SQL*Net to client
683 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4000000 rows processed
Elapsed: 00:05:30.97
18:54:18 SQL> set autot off
18:54:23 SQL> select a.name, b.value
18:54:23 2 from v$statname a, v$mystat b
18:54:23 3 where a.statistic# = b.statistic#
18:54:23 4 and a.name = 'redo size'
18:54:23 5 and b.value > 0
18:54:24 6 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1105563544
＝＝ 694767276+410796268
SQL> select 694767276+410796268 from dual;
694767276+410796268
-------------------
1105563544
18:54:46 SQL> rollback;
Rollback complete.
18:55:35 SQL> select a.name, b.value
18:56:17 2 from v$statname a, v$mystat b
18:56:17 3 where a.statistic# = b.statistic#
18:56:17 4 and a.name = 'redo size'
18:56:17 5 and b.value > 0
18:56:18 6 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1151016204
＝＝1151016204-1105563544=45452660
＝＝我们看到INSERT的回滚非常小
＝＝因为INSERT的UNDO是DELETE，只要记录ROWID就行了
＝＝其实UNDO的实体就是空，加操作类型和行号
＝＝另外还有些空间操作
19:03:39 SQL> 19:03:39 SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
19:05:44 SQL> set autot off
19:07:14 SQL> select sum(bytes) from dba_extents where segment_name = 'T1';
SUM(BYTES)
----------
411107328
＝＝该表的大小，跟创建表语句时产生的REDO差不多大
＝＝这里表T1没有记录
Elapsed: 00:00:00.85
19:07:21 SQL> INSERT INTO T2 SELECT * FROM T2 WHERE ROWNUM <=20000;
20000 rows created.
Elapsed: 00:00:00.17
19:08:38 SQL> commit;
Commit complete.
18:55:35 SQL> select a.name, b.value
18:56:17 2 from v$statname a, v$mystat b
18:56:17 3 where a.statistic# = b.statistic#
18:56:17 4 and a.name = 'redo size'
18:56:17 5 and b.value > 0
18:56:18 6 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1699354563
Elapsed: 00:00:00.01
19:08:43 SQL> ALTER TABLE t1
DISABLE PRIMARY KEY ;
19:09:13 2
Table altered.
Elapsed: 00:00:05.31
19:09:18 SQL> select a.name, b.value
19:09:31 2 from v$statname a, v$mystat b
19:09:31 3 where a.statistic# = b.statistic#
19:09:31 4 and a.name = 'redo size'
19:09:31 5 and b.value > 0
19:09:32 6 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1699658564
＝＝我们看到删除索引REDO非常小
19:09:33 SQL> TRUNCATE TABLE T1;
Table truncated.
Elapsed: 00:01:13.04
19:13:02 SQL> 19:13:02 SQL> select a.name, b.value
19:15:08 2 from v$statname a, v$mystat b
19:15:08 3 where a.statistic# = b.statistic#
19:15:08 4 and a.name = 'redo size'
19:15:08 5 and b.value > 0
19:15:09 6 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1699756884
＝＝TRUNCATE有很少的REDO
SQL> INSERT INTO t1 SELECT * FROM t2;
4020000 rows created.
Elapsed: 00:03:11.16
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T2'
Statistics
----------------------------------------------------------
80 recursive calls
200566 db block gets
198588 consistent gets
44039 physical reads
444366592 redo size
785 bytes sent via SQL*Net to client
683 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4020000 rows processed
commit;
＝＝我们看到DISABLE主键后，REDO少了一小半，这是维护索引产生的
＝＝而且现在有20000条违规数据。
SQL> ALTER TABLE t1 ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE t1 ENABLE NOVALIDATE PRIMARY KEY
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.T1_PK) - primary key violated
＝＝ENABLE NOVALIDATE约束需要非唯一性索引来实现
Elapsed: 00:00:09.42
SQL>
SQL> CREATE INDEX NOUNIQ_IDX ON T1 (OBJECT_ID);
Index created.
Elapsed: 00:01:05.99
＝＝创建非唯一性索引
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'redo size'
5 and b.value > 0
6 /
NAME VALUE
---------------------------------------------------------------- ----------
redo size 524147536
＝＝明显比694767276小
SQL> ALTER TABLE t1 ENABLE NOVALIDATE PRIMARY KEY;
Table altered.
Elapsed: 00:00:00.02
SQL> ALTER TABLE t1 ENABLE PRIMARY KEY;
ALTER TABLE t1 ENABLE PRIMARY KEY
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.T1_PK) - primary key violated
Elapsed: 00:00:17.75
＝＝ENABLE 语句默认就是ENABLE VALIDATE，所以不允许老数据有违反约束的数据存在
SQL> delete from t1
where rowid in
( select rid
from (
select rowid rid ,
row_number() over
(partition by OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
OBJECT_ID ,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
order by OBJECT_ID) rn
from t1
)
where rn <> 1
) ;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
20000 rows deleted.
Elapsed: 00:02:35.80
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'T1'
2 1 NESTED LOOPS
3 2 VIEW OF 'VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 VIEW
6 5 WINDOW (SORT)
7 6 TABLE ACCESS (FULL) OF 'T1'
8 2 TABLE ACCESS (BY USER ROWID) OF 'T1'
Statistics
----------------------------------------------------------
48 recursive calls
82249 db block gets
70062 consistent gets
131116 physical reads
11091288 redo size
799 bytes sent via SQL*Net to client
1421 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
20000 rows processed
commit;
＝＝删除违反约束的数据
SQL> ALTER TABLE t1 ENABLE PRIMARY KEY;
Table altered.
Elapsed: 00:00:24.47
复杂索引和约束结合测试
＝＝＝＝＝＝＝＝＝＝＝＝＝
我们知道约束其实是索引来实现的，那我们能不能来创建一个复杂和特殊的索引来实现约束功能呢
SQL> conn scott/tiger
已连接。
SQL> drop table t01;
drop table t01
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在
SQL> create table t01
2 (a int,
3 b int,
4 c varchar2(1) not null);
表已创建。
SQL> create unique index t01_idx on t01
2 (case when c <> 'w' then a end,
3 case when c <> 'w' then b end);
case when c <> 'w' then b end)
*
ERROR 位于第 3 行:
ORA-01031: 权限不足
SQL> conn /as sysdba
已连接。
SQL> grant query rewrite to scott;
授权成功。
SQL> conn scott/tiger
已连接。
SQL> create unique index t01_idx on t01
2 (case when c <> 'w' then a end,
3 case when c <> 'w' then b end);
case when c <> 'w' then b end)
*
ERROR 位于第 3 行:
ORA-01536: 超出表空间'SYSTEM'的空间限量
＝＝非SYS用户不要将默认表空间设置在SYSTEM里
SQL> conn /as sysdba
已连接。
SQL> grant resource to scott;
授权成功。
＝＝RESOURCE角色拥有完全空间资源的权限，对于测试用户要注意空间使用安全
SQL> conn scott/tiger
已连接。
SQL> create unique index t01_idx on t01
2 (case when c <> 'w' then a end,
3 case when c <> 'w' then b end);
索引已创建。
＝＝我们创建的这个索引的含义是
＝＝当c不等于'w'时，a和b字段的联合unique索引
下面对此进行验证
SQL> insert into t01 values (1,1,'w');
已创建 1 行。
SQL> insert into t01 values (1,1,'w');
已创建 1 行。
SQL> insert into t01 values (1,1,'x');
已创建 1 行。
SQL> insert into t01 values (1,1,'y');
insert into t01 values (1,1,'y')
*
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (SCOTT.T01_IDX)
扫一扫
439
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
