参考:
Managing Indexes
logging_clause
NOLOGGINGオペレーションの実行要件
SQL> create tablespace test_part datafile '/opt/oracle/oradata/ractest/test_part01.dbf' size 10m extent management local autoallocate;
表領域が作成されました。
SQL> create user utest identified by utest default tablespace test_part;
ユーザーが作成されました。
SQL> grant dba to utest;
権限付与が成功しました。
SQL> archive log list
データベース・ログ・モード 非アーカイブ・モード
自動アーカイブ 使用禁止
アーカイブ先 USE_DB_RECOVERY_FILE_DEST
最も古いオンライン・ログ順序 14
現行のログ順序 15
SQL> conn utest/utest
接続されました。
SQL> CREATE TABLE test_sales(deptno number, item_no varchar2(20),txn_date date, txn_amount number, state varchar2(2))
TABLESPACE test_part
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('01-04-1999','DD-MM-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-07-1999','DD-MM-YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('01-10-1999','DD-MM-YYYY'))
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('01-01-2000','DD-MM-YYYY'))
(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
)
);
TABLESPACE test_part
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('01-04-1999','DD-MM-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-07-1999','DD-MM-YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('01-10-1999','DD-MM-YYYY'))
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('01-01-2000','DD-MM-YYYY'))
(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
)
);
表が作成されました。
SQL> select table_name,partitioning_type,subpartitioning_type from user_part_tables;
TABLE_NAME
--------------------------------------------------------------------------------
PARTITIONING_TYPE SUBPARTITIONING_TYPE
--------------------- ---------------------
TEST_SALES
RANGE LIST
--------------------------------------------------------------------------------
PARTITIONING_TYPE SUBPARTITIONING_TYPE
--------------------- ---------------------
TEST_SALES
RANGE LIST
SQL> insert into test_sales values(41, 4532130, to_date('05-06-1999','DD-MM-YYYY'), 897231.55, 'TX');
1行が作成されました。
SQL> insert into test_sales values(10, 4532130, to_date('23-01-1999','DD-MM-YYYY'), 8934.10, 'WA');
1行が作成されました。
SQL> insert into test_sales values(20, 5671621, to_date('15-05-1999','DD-MM-YYYY'), 49021.21, 'OR');
1行が作成されました。
SQL> insert into test_sales values(30, 9977612, to_date('07-09-1999','DD-MM-YYYY'), 30987.90, 'FL');
1行が作成されました。
SQL> insert into test_sales values(40, 9977612, to_date('29-11-1999','DD-MM-YYYY'), 67891.45, 'TX');
1行が作成されました。
SQL> CREATE INDEX in_test ON test_sales (deptno) TABLESPACE test_part NOLOGGING PARALLEL (DEGREE 8);
索引が作成されました。
SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,LOGGING from user_indexes where table_name='TEST_SALES';
INDEX_NAME
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
NO
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
NO
SQL> alter index in_test logging;
索引が変更されました。
SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,LOGGING from user_indexes where table_name='TEST_SALES';
INDEX_NAME
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
YES
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
YES
SQL> drop index in_test;
索引が削除されました。
SQL> CREATE INDEX in_test ON test_sales (deptno) TABLESPACE test_part PARALLEL (DEGREE 8);
索引が作成されました。
SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,LOGGING from user_indexes where table_name='TEST_SALES';
INDEX_NAME
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
YES
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
YES
SQL> alter index in_test rebuild nologging parallel(degree 8);
索引が変更されました。
SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,LOGGING from user_indexes where table_name='TEST_SALES';
INDEX_NAME
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
NO
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
NO
SQL> alter index in_test logging;
索引が変更されました。
SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME,LOGGING from user_indexes where table_name='TEST_SALES';
INDEX_NAME
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
YES
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
LOGGING
---------
IN_TEST
TEST_SALES
TEST_PART
YES
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24756186/viewspace-741830/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24756186/viewspace-741830/