创建没指定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

http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2003.htm#REFRN20156

CREATE INDEX

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5012.htm#i2062403

ALL_IND_PARTITIONS

http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_1102.htm#I1020086

ITPub博客

分区索引的logging属性

最新文章