ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引的nologging

索引的nologging

原创 Linux操作系统 作者:oracle_mao 时间:2013-11-19 18:52:07 0 删除 编辑

如果某一个大表有索引,且表总是更新,那就最好将索引设为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
----------------------------------------------------------

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

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   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
----------------------------------------------------------

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

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   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
----------------------------------------------------------

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

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   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
----------------------------------------------------------

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

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   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哈哈~

 


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24500180/viewspace-777020/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-28

  • 博文量
    94
  • 访问量
    752149