分区索引的logging属性

创建没指定logging_clause 的分区索引
SQL> CREATE INDEX prod_idx ON hash_products(category_id) LOCAL STORE IN (tbs_01, test_space);

Index created.

查看 user_indexes 视图的logging值是空,user_indexes 视图可以查看非分区索引的logging属性

SQL> select index_name,partitioned,logging from user_indexes where index_name='PROD_IDX';

INDEX_NAME                     PARTITIONED     LOG
------------------------------ --------------- ---
PROD_IDX                       YES

创建非分区索引

SQL> drop index PROD_IDX;

Index dropped.

SQL> CREATE INDEX prod_idx ON hash_products(category_id);

Index created.

SQL> select index_name,partitioned,logging from user_indexes where index_name='PROD_IDX';

INDEX_NAME                     PARTITIONED     LOG
------------------------------ --------------- ---
PROD_IDX                       NO              YES

user_part_indexes 视图的def_logging字段标识创建分区索引时是否指定了logging_clause ,以及指定的是logging还是nologging

如果没指定logging_clause,def_logging的值是 NONE

SQL> select index_name,def_logging from user_part_indexes where index_name='PROD_IDX';

INDEX_NAME                     DEF_LOG
------------------------------ -------
PROD_IDX                       NONE

如果指定logging_clause是logging或者nologging,def_logging的值是YES 或者NO

创建分区索引指定logging_clause 是logging

SQL> drop index PROD_IDX;
Index dropped.
SQL> CREATE INDEX prod_idx ON hash_products(category_id) logging  LOCAL STORE IN (tbs_01, test_space);
Index created.
SQL> select index_name,def_logging from user_part_indexes where index_name='PROD_IDX';
INDEX_NAME                     DEF_LOG
------------------------------ -------
PROD_IDX                       YES
创建分区索引指定logging_clause 是nologging
SQL> drop index PROD_IDX;
Index dropped.
SQL> CREATE INDEX prod_idx ON hash_products(category_id) nologging LOCAL STORE IN (tbs_01, test_space);
Index created.
SQL> select index_name,def_logging from user_part_indexes where index_name='PROD_IDX';
INDEX_NAME                     DEF_LOG
------------------------------ -------
PROD_IDX                       NO
create index时没指定logging_clause的分区索引通过以上两个视图无法查看这个索引是logging还是nologging
通过USER_IND_PARTITIONS视图的logging字段可以查看分区索引的每个分区的属性
SQL>  drop index PROD_IDX;
Index dropped.
SQL> CREATE INDEX prod_idx ON hash_products(category_id) LOCAL STORE IN (tbs_01, test_space);
Index created.
SQL> select index_name,partitioned,logging from user_indexes where index_name='PROD_IDX';
INDEX_NAME                     PARTITIONED     LOG
------------------------------ --------------- ---
PROD_IDX                       YES
SQL> select index_name,def_logging from user_part_indexes where index_name='PROD_IDX';
INDEX_NAME                     DEF_LOG
------------------------------ -------
PROD_IDX                       NONE
SQL> SELECT index_name,tablespace_name,logging FROM USER_IND_PARTITIONS WHERE INDEX_NAME='PROD_IDX';
INDEX_NAME                     TABLESPACE_NAME                LOGGING
------------------------------ ------------------------------ -------
PROD_IDX                       TBS_01                         YES
PROD_IDX                       TEST_SPACE                     YES
 
create index时没指定logging_clause的分区索引,它的每个分区的logging属性的默认值是logging还是nologging取决于它所在的表空间是logging还是nologging
 
1.表空间是logging
SQL> select tablespace_name,logging from user_tablespaces where tablespace_name='TEST_SPACE';
TABLESPACE_NAME                LOGGING
------------------------------ ---------
TEST_SPACE                     LOGGING

SQL> select tablespace_name,logging from user_tablespaces where tablespace_name='TBS_01';
TABLESPACE_NAME                LOGGING
------------------------------ ---------
TBS_01                     LOGGING

创建分区索引时没有指定logging_clause,分区索引的每个分区的默认值是logging
SQL>  drop index PROD_IDX;
Index dropped.
SQL> CREATE INDEX prod_idx ON hash_products(category_id) LOCAL STORE IN (tbs_01, test_space);
Index created.
SQL> SELECT index_name,tablespace_name,logging FROM USER_IND_PARTITIONS WHERE INDEX_NAME='PROD_IDX';
INDEX_NAME                     TABLESPACE_NAME                LOGGING
------------------------------ ------------------------------ -------
PROD_IDX                       TBS_01                         YES
PROD_IDX                       TEST_SPACE                     YES
 
2.表空间是nologging
SQL> select tablespace_name,logging from user_tablespaces where tablespace_name='TBS_03';
TABLESPACE_NAME                LOGGING
------------------------------ ---------
TBS_03                         NOLOGGING
SQL> select tablespace_name,logging from user_tablespaces where tablespace_name='TBS_04';
TABLESPACE_NAME                LOGGING
------------------------------ ---------
TBS_04                         NOLOGGING

创建分区索引时没有指定logging_clause,分区索引的每个分区的默认值nologging
SQL> drop index prod_idx;
Index dropped.
SQL> CREATE INDEX prod_idx ON hash_products(category_id) LOCAL STORE IN (tbs_03, tbs_04);
Index created.
SQL> SELECT index_name,tablespace_name,logging FROM USER_IND_PARTITIONS WHERE INDEX_NAME='PROD_IDX';
INDEX_NAME                     TABLESPACE_NAME                LOGGING
------------------------------ ------------------------------ -------
PROD_IDX                       TBS_03                         NO
PROD_IDX                       TBS_04                         NO
 
参考文档:
ALL_PART_INDEXES
CREATE INDEX
ALL_IND_PARTITIONS

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

转载于:http://blog.itpub.net/24756186/viewspace-742774/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值