索引的nologging

最新推荐文章于 2024-06-06 07:05:08 发布

cri3224

最新推荐文章于 2024-06-06 07:05:08 发布

阅读量831

点赞数

如果某一个大表有索引，且表总是更新，那就最好将索引设为nologging，但这样的话，在实例恢复的时候，是否会有影响呢？
对于索引的nologging，只是在create index和rebuild的时候有用，在dml操作的时候，还是会产生和原来一样的日志。

SQL> desc t;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER(30)
NAME                                               VARCHAR2(20)
SAL                                                NUMBER

SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' ;

NAME BYTES
---------------------------------------------------------------- ----------
redo size 267248
SQL> create index in_t on t(sal) nologging;

Index created.

SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' ;

NAME BYTES
---------------------------------------------------------------- ----------
redo size 282960

可以查出产生15712大小的redo。

SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' ;

NAME BYTES
---------------------------------------------------------------- ----------
redo size 481652

SQL> create index in_t on t(sal);

Index created.

SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' ;

NAME BYTES
---------------------------------------------------------------- ----------
redo size 664932
可以看到产生183280大小的redo。
暂时可以得出使用nologging产生的redo是不使用nologging大小的1/10左右。

2 测试index在nologging和logging情况下，插入数据产生的redo数量。
SQL> create index in_t on t(sal);

Index created.

SQL> set autotrace on
SQL> insert into t values(88888,'ff','17000');

1 row created.

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
-

--------------------------------------------------------------------------------
-

| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01
|

| 1 | LOAD TABLE CONVENTIONAL | T | | | |
|

--------------------------------------------------------------------------------
-

Statistics
----------------------------------------------------------
         36 recursive calls
          5 db block gets
          5 consistent gets
          1 physical reads
          0 redo size
        680 bytes sent via SQL*Net to client
        613 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> insert into t values(88888,'ff','17000');

1 row created.

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
-

--------------------------------------------------------------------------------
-

| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01
|

| 1 | LOAD TABLE CONVENTIONAL | T | | | |
|

--------------------------------------------------------------------------------
-

Statistics
----------------------------------------------------------
          0 recursive calls
          3 db block gets
          1 consistent gets
          0 physical reads
        520 redo size--产生redo大小为520。
        680 bytes sent via SQL*Net to client
        613 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> drop index in_t;

Index dropped.

SQL> create index in_t on t(sal) nologging;

Index created.

SQL> insert into t values(88888,'ff','17000');

1 row created.

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
-

--------------------------------------------------------------------------------
-

| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01
|

| 1 | LOAD TABLE CONVENTIONAL | T | | | |
|

--------------------------------------------------------------------------------
-

Statistics
----------------------------------------------------------
         36 recursive calls
          5 db block gets
          5 consistent gets
          1 physical reads
          0 redo size
        679 bytes sent via SQL*Net to client
        613 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> insert into t values(88888,'ff','17000');

1 row created.

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
-

--------------------------------------------------------------------------------
-

| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01
|

|   1 | LOAD TABLE CONVENTIONAL | T    |       |       |            |
|
--------------------------------------------------------------------------------
-
Statistics
----------------------------------------------------------
          0 recursive calls
          3 db block gets
          1 consistent gets
          0 physical reads
        520 redo size--产生redo大小还是为520。
        680 bytes sent via SQL*Net to client
        613 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed
这样是不是就可以得出index logging只是在create和rebuild的时候才有用呢。。。。。O(∩_∩)O哈哈~