分区表中分区索引Logging 及 Nologging问题

我们建立分区表 

CREATE TABLE DFMS.WIP_D_SN_DETAIL
(
  SERIAL_NUMBER      VARCHAR2(25 BYTE)          NOT NULL,
  WORK_ORDER         VARCHAR2(25 BYTE)          NOT NULL,
  MODEL_NAME         VARCHAR2(25 BYTE),
  MODEL_VER          VARCHAR2(25 BYTE),
  PRODUCT_LINE       VARCHAR2(8 BYTE),
  WORK_SECTION       VARCHAR2(30 BYTE)          DEFAULT '0'                   NOT NULL,
  WORK_GROUP         VARCHAR2(30 BYTE)          DEFAULT '0'                   NOT NULL,
  WORK_STATION       VARCHAR2(35 BYTE)          DEFAULT '0'                   NOT NULL,
  ERROR_FLAG         VARCHAR2(1 BYTE),
  IN_STATION_TIME    DATE,
  IN_LINE_TIME       DATE,
  OUT_LINE_TIME      DATE,
  FINISH_FLAG        VARCHAR2(1 BYTE),
  PALLET_NO          VARCHAR2(25 BYTE),
  SCRAP_FLAG         VARCHAR2(1 BYTE),
  NEXT_STATION       VARCHAR2(16 BYTE),
  CARTON_NO          VARCHAR2(25 BYTE),
  OP_NO              VARCHAR2(35 BYTE),
  CUST_NO            VARCHAR2(25 BYTE),
  LEVEL_GRADE        VARCHAR2(8 BYTE),
  REPAIR_COUNT       INTEGER                    DEFAULT 0                     NOT NULL,
  REWORK_COUNT       INTEGER                    DEFAULT 0                     NOT NULL,
  IN_STATION_COUNT   NUMBER(3),
  RETRACTED          VARCHAR2(1 BYTE)           DEFAULT 'N'                   NOT NULL,
  PLANT_CODE         VARCHAR2(20 BYTE)          DEFAULT NULL                  NOT NULL,
  RELEASE_TIME       DATE,
  PARENT_SN_NO       VARCHAR2(25 BYTE),
  CK_LABEL_FLAG      VARCHAR2(2 BYTE),
  CK_LINE_NAME       VARCHAR2(12 BYTE),
  CK_FLAG            VARCHAR2(1 BYTE),
  SHIP_FLAG          VARCHAR2(1 BYTE),
  SHIP_DATE          DATE,
  SEQ_NO             INTEGER,
  DISMANTLE_FLAG     VARCHAR2(1 BYTE),
  TRAVELER_PRINT     VARCHAR2(25 BYTE),
  WEIGHT             VARCHAR2(10 BYTE),
  WEIGHT_UOM         VARCHAR2(10 BYTE),
  PKG_ID             VARCHAR2(50 BYTE),
  PACK_TYPE          VARCHAR2(30 BYTE)
)
TABLESPACE PART_D_TS1
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING 
PARTITION BY RANGE (in_station_time)
( 
  PARTITION P200907 VALUES LESS THAN (TO_DATE('2009-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1,
  PARTITION P201001 VALUES LESS THAN (TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1, 
  PARTITION P201007 VALUES LESS THAN (TO_DATE('2010-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1, 
  PARTITION P201101 VALUES LESS THAN (TO_DATE('2011-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1, 
  PARTITION P201107 VALUES LESS THAN (TO_DATE('2011-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1, 
  PARTITION P201201 VALUES LESS THAN (TO_DATE('2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1, 
  PARTITION P201207 VALUES LESS THAN (TO_DATE('2012-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1, 
  PARTITION P201301 VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1, 
  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_D_TS1 
)
NOCOMPRESS
NOCACHE
MONITORING
NOPARALLEL
ENABLE ROW MOVEMENT;

 

CREATE INDEX DFMS.IDX_WIP_D_SN_DETAIL_SN ON DFMS.WIP_D_SN_DETAIL
(SERIAL_NUMBER)
LOCAL
TABLESPACE PART_I_TS1 ;

CREATE INDEX DFMS.IDX_WIP_D_SN_DETAIL_TIME ON DFMS.WIP_D_SN_DETAIL
(IN_STATION_TIME)
LOCAL
TABLESPACE PART_I_TS1 ; 

 

建立的分区索引中partition 部分在Toad中看到是Logging 的， 整体看Index  Logging 是空值 (在Toad的index  Tab 部分看到是空 )

 

logging_clause    

Specify whether the creation of the index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file. This setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against the index are logged or not logged. LOGGING is the default.

If index is nonpartitioned, then this clause specifies the logging attribute of the index.

If index is partitioned, then this clause determines:

• The default value of all partitions specified in the CREATE statement, unless you specify the logging_clause in the PARTITION description clause

• The default value for the segments associated with the index partitions

• The default value for local index partitions or subpartitions added implicitly during subsequent ALTER TABLE ... ADD PARTITION operations

The logging attribute of the index is independent of that of its base table.

If you omit this clause, then the logging attribute is that of the tablespace in which it resides. 

 

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

转载于:http://blog.itpub.net/35489/viewspace-619456/