普通表轉(zhuǎn)分區(qū)表-資料下載頁
2025-07-01 00:30本頁面
【正文】 MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ), PARTITION EMP_P2 VALUES LESS THAN (TO_DATE(39。 19811203 00:00:0039。, 39。SYYYYMMDD HH24:MI:SS39。, 39。NLS_CALENDAR=GREGORIAN39。)) LOGGING NOCOMPRESS TABLESPACE USERS PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ), PARTITION EMP_P3 VALUES LESS THAN (TO_DATE(39。 19900101 00:00:0039。, 39。SYYYYMMDD HH24:MI:SS39。, 39。NLS_CALENDAR=GREGORIAN39。)) LOGGING NOCOMPRESS TABLESPACE USERS PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ))NOCOMPRESS NOCACHENOPARALLELMONITORING。CREATE UNIQUE INDEX ON (EMPNO)LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )NOPARALLEL。ALTER TABLE ADD ( CONSTRAINT EMP_P_CONSTRAINT PRIMARY KEY (EMPNO) USING INDEX )。過渡表ALTER TABLE DROP PRIMARY KEY CASCADE。DROP TABLE CASCADE CONSTRAINTS。CREATE TABLE ( EMPNO NUMBER(4), ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2))TABLESPACE USERSPCTUSED 0PCTFREE 10INITRANS 1MAXTRANS 255STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )LOGGING NOCOMPRESS NOCACHENOPARALLELMONITORING。CREATE UNIQUE INDEX ON (EMPNO)LOGGINGTABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )NOPARALLEL。ALTER TABLE ADD ( CONSTRAINT EMPT_CONSTRAINT PRIMARY KEY (EMPNO) USING INDEX )。DROP TABLE EMP_P,索引定義在中間表的觸發(fā)器、索引、約束和授權(quán)都會重定義至原始表，且原始表的這些定義也會應(yīng)用到過渡表，隨過渡表被刪除而移除，還有過渡表的disable約束重定義至原始表狀態(tài)會成為enableALTER TABLE EMPT RENAME CONSTRAINT EMP_P_CONSTRAINT TO EMPT_CONSTRAINT。ALTER INDEX CREATE_DATE_IND2 RENAME TO CREATE_DATE_IND。Index創(chuàng)建本機分區(qū)索引和表分區(qū)對應(yīng)CREATE INDEX INDX_N1 ON EMP_P(EMPNO) LOCAL。 非前綴indexCREATE INDEX INDX_N2 ON EMP_P(HIREDATE) LOCAL。 前綴index全局分區(qū)索引CREATE INDEX INDX_N3 ON EMP_P(SAL)GLOBAL PARTITION BY RANGE(SAL)(PARTITION P1 VALUES LESS THAN (3000),PARTITION P2 VALUES LESS THAN(6000),PARTITION P3 VALUES LESS THAN (MAXVALUE))。相關(guān)查詢分區(qū)索引查詢SELECT * FROM USER_PART_INDEXES；分區(qū)表查詢SELECT * FROM USER_PART_TABLES。查詢某個分區(qū)下的數(shù)據(jù)SELECT * FROM EMP_P PARTITION(EMP_P1)。查詢二級分區(qū)信息SELECT * FROM USER_TAB_SUBPARTITIONS。查詢再哪個鍵上創(chuàng)建分區(qū)SELECT * FROM USER_PART_KEY_COLUMN
教學(xué)課件相關(guān)推薦
鄂ICP備17016276號-1