![96│2013 ???? White Paper
?? ?? ??? ????? ??? Delete ??? ????? ????? ?? ??? ????
?? Partition Table ??? ??.
????? ???? ?? ??? ??? ???, ?? ??? Key ? ?? Range Partition ?
????, ?? ??? ?? ??? ???? Partition Table ? Alter Table [Partition_Name]
Drop Partition [Partition_Name]??? ?? DML ? ?? DDL ? ??? ??? ? ??.
DDL ?? Delete ??? ???, redo ???? ???? ?? ??? ?? Partition ? Drop
?? ??? ????? ??? ??? ??? ? ?? ?? ??, ?? ?? redo / undo ??
?? ???? ???? Down Time ????? ? ??? ? ? ??.
?? ???? ?? ? ??? ??? ??? ????.
??? ?? DDL (Partition_test)
CREATE TABLE partition_test (TIME_ID DATE ,
DAY_NAME VARCHAR2(9),
DAY_NUMBER_IN_WEEK NUMBER(1,0) ,
DAY_NUMBER_IN_MONTH NUMBER(2,0) ,
CALENDAR_WEEK_NUMBER NUMBER(2,0) ,
FISCAL_WEEK_NUMBER NUMBER(2,0) ,
WEEK_ENDING_DAY DATE ,
WEEK_ENDING_DAY_ID NUMBER ,
CALENDAR_MONTH_NUMBER NUMBER(2,0) ,
FISCAL_MONTH_NUMBER NUMBER(2,0) ,
CALENDAR_MONTH_DESC VARCHAR2(8) ,
CALENDAR_MONTH_ID NUMBER ,
FISCAL_MONTH_DESC VARCHAR2(8) ,
FISCAL_MONTH_ID NUMBER,
DAYS_IN_CAL_MONTH NUMBER ,
DAYS_IN_FIS_MONTH NUMBER ,
END_OF_CAL_MONTH DATE ,
END_OF_FIS_MONTH DATE ,
CALENDAR_MONTH_NAME VARCHAR2(9) ,
FISCAL_MONTH_NAME VARCHAR2(9) ,
CALENDAR_QUARTER_DESC CHAR(7) ,
CALENDAR_QUARTER_ID NUMBER ,
FISCAL_QUARTER_DESC CHAR(7) ,](https://image.slidesharecdn.com/whoracledml-160219072843/85/DML-_Wh-oracle-2-320.jpg)
![【旧版】Oracle Database Cloud Service：サービス概要のご紹介 [2021年7月版]](https://cdn.slidesharecdn.com/ss_thumbnails/ocidatabaseoverview210709-210709093949-thumbnail.jpg?width=560&fit=bounds)
![Oracle Container Engine for Kubernetes (OKE) ご紹介 [2021年5月版]](https://cdn.slidesharecdn.com/ss_thumbnails/okeoverview-210528052755-thumbnail.jpg?width=560&fit=bounds)
![[2015-06-26] Oracle ?? ??? ? ?? ??? 3](https://cdn.slidesharecdn.com/ss_thumbnails/2015-06-26oracle3-150623063728-lva1-app6891-thumbnail.jpg?width=560&fit=bounds)
More Related Content
What's hot (20)
Similar to ??? DML ??? ?? ??????_Wh oracle (20)
![[Pgday.Seoul 2020] SQL Tuning](https://cdn.slidesharecdn.com/ss_thumbnails/pgday-201117134901-thumbnail.jpg?width=560&fit=bounds)
![[2015-06-12] Oracle ?? ??? ? ?? ??? 1](https://cdn.slidesharecdn.com/ss_thumbnails/2015-06-12oracle1-150615000001-lva1-app6892-thumbnail.jpg?width=560&fit=bounds)
More from ?? (20)
??? DML ??? ?? ??????_Wh oracle
- 1. Part 1 ORACLE │95 ??? DML ??? ?? ?????? ??? ?????/DB???? ? ?? ?? ??? ???? ???? ?? ??? ? ?????? ? ???? ????. ??? ??? ? ???? ???? ?? ??? ???? ? ???? ?? ?? ???? ?? ???? ? ? ?? ??????? ??? ???? ?? ????? ?? ? ??? ???. ? ??? ??? ???? ???? ?? ??? ??? SQL Tuning ??? ??? ??? ? ?? ?? ????? ??? ?? ???? ??? ??? ????? ???. ??? ??? ?? ???? redo ? undo ???? ?? ???? ??? ??? ???? ?? ????? ???? ???? ??? ?? ????? ??? ????? ??? ?? ??. ???, ?? ??? ??? DML ? ??? ?, ??? ???? ?? ? ???? Direct Path Insert ? ???? ?? ?? ??? DML ????? ?? ??? ????, ???? Down Time ????? ??? ? ?? ??? ?? ???? ??? ??. ??? Delete ? ?? ?? ?? ?? ??? ???? ????? ???? ??? ?? ?????? ?? ? ? ?? ?? ? ????. ??? ???? ?? ? ??? ??? ????? ?? ??. ??? Delete ??? Table Full Scan ?? Index Scan ?? ?? ?? ???? Access ?? ??? ?? ?????? ????? ??? ? ?? ?? redo, undo ??? ??? ??? ?? ???? ???? ????? ???? ??? ?? ???? Overhead ? ???? ? ? ????? ?? ??? ?? ????. ????? ???? ???? Overhead ?, ?? ??? ?? ???? ?????? ??? ? ?? ?? ???, ? ?? ?? redo ? undo ???? ???? ????.
- 2. 96│2013 ???? White Paper ?? ?? ??? ????? ??? Delete ??? ????? ????? ?? ??? ???? ?? Partition Table ??? ??. ????? ???? ?? ??? ??? ???, ?? ??? Key ? ?? Range Partition ? ????, ?? ??? ?? ??? ???? Partition Table ? Alter Table [Partition_Name] Drop Partition [Partition_Name]??? ?? DML ? ?? DDL ? ??? ??? ? ??. DDL ?? Delete ??? ???, redo ???? ???? ?? ??? ?? Partition ? Drop ?? ??? ????? ??? ??? ??? ? ?? ?? ??, ?? ?? redo / undo ?? ?? ???? ???? Down Time ????? ? ??? ? ? ??. ?? ???? ?? ? ??? ??? ??? ????. ??? ?? DDL (Partition_test) CREATE TABLE partition_test (TIME_ID DATE , DAY_NAME VARCHAR2(9), DAY_NUMBER_IN_WEEK NUMBER(1,0) , DAY_NUMBER_IN_MONTH NUMBER(2,0) , CALENDAR_WEEK_NUMBER NUMBER(2,0) , FISCAL_WEEK_NUMBER NUMBER(2,0) , WEEK_ENDING_DAY DATE , WEEK_ENDING_DAY_ID NUMBER , CALENDAR_MONTH_NUMBER NUMBER(2,0) , FISCAL_MONTH_NUMBER NUMBER(2,0) , CALENDAR_MONTH_DESC VARCHAR2(8) , CALENDAR_MONTH_ID NUMBER , FISCAL_MONTH_DESC VARCHAR2(8) , FISCAL_MONTH_ID NUMBER, DAYS_IN_CAL_MONTH NUMBER , DAYS_IN_FIS_MONTH NUMBER , END_OF_CAL_MONTH DATE , END_OF_FIS_MONTH DATE , CALENDAR_MONTH_NAME VARCHAR2(9) , FISCAL_MONTH_NAME VARCHAR2(9) , CALENDAR_QUARTER_DESC CHAR(7) , CALENDAR_QUARTER_ID NUMBER , FISCAL_QUARTER_DESC CHAR(7) ,
- 3. Part 1 ORACLE │97 FISCAL_QUARTER_ID NUMBER , DAYS_IN_CAL_QUARTER NUMBER , DAYS_IN_FIS_QUARTER NUMBER , END_OF_CAL_QUARTER DATE , END_OF_FIS_QUARTER DATE , CALENDAR_QUARTER_NUMBER NUMBER(1,0) , FISCAL_QUARTER_NUMBER NUMBER(1,0) , CALENDAR_YEAR NUMBER(4,0) , CALENDAR_YEAR_ID NUMBER , FISCAL_YEAR NUMBER(4,0) , FISCAL_YEAR_ID NUMBER , DAYS_IN_CAL_YEAR NUMBER , DAYS_IN_FIS_YEAR NUMBER , END_OF_CAL_YEAR DATE , END_OF_FIS_YEAR DATE ) PARTITION BY RANGE( time_id )( PARTITION partition_test1 VALUES less than (to_date('1998-12-31','YYYY-MM-DD')) , PARTITION partition_test2 VALUES less than (to_date('1999-12-31','YYYY-MM-DD')) , PARTITION partition_test3 VALUES less than (to_date('2000-12-31','YYYY-MM-DD')) , PARTITION partition_test4 VALUES less than (to_date('2001-12-31','YYYY-MM-DD')) , PARTITION partition_test5 VALUES less than (to_date('2003-12-31','YYYY-MM-DD')) ); ??? ?? DDL (Partition_test2) CREATE TABLE partition_test2 (TIME_ID DATE , DAY_NAME VARCHAR2(9), DAY_NUMBER_IN_WEEK NUMBER(1,0) , DAY_NUMBER_IN_MONTH NUMBER(2,0) , CALENDAR_WEEK_NUMBER NUMBER(2,0) , FISCAL_WEEK_NUMBER NUMBER(2,0) , WEEK_ENDING_DAY DATE , WEEK_ENDING_DAY_ID NUMBER , CALENDAR_MONTH_NUMBER NUMBER(2,0) , FISCAL_MONTH_NUMBER NUMBER(2,0) , CALENDAR_MONTH_DESC VARCHAR2(8) , CALENDAR_MONTH_ID NUMBER , FISCAL_MONTH_DESC VARCHAR2(8) , FISCAL_MONTH_ID NUMBER, DAYS_IN_CAL_MONTH NUMBER , DAYS_IN_FIS_MONTH NUMBER , END_OF_CAL_MONTH DATE ,
- 4. 98│2013 ???? White Paper END_OF_FIS_MONTH DATE , CALENDAR_MONTH_NAME VARCHAR2(9) , FISCAL_MONTH_NAME VARCHAR2(9) , CALENDAR_QUARTER_DESC CHAR(7) , CALENDAR_QUARTER_ID NUMBER , FISCAL_QUARTER_DESC CHAR(7) , FISCAL_QUARTER_ID NUMBER , DAYS_IN_CAL_QUARTER NUMBER , DAYS_IN_FIS_QUARTER NUMBER , END_OF_CAL_QUARTER DATE , END_OF_FIS_QUARTER DATE , CALENDAR_QUARTER_NUMBER NUMBER(1,0) , FISCAL_QUARTER_NUMBER NUMBER(1,0) , CALENDAR_YEAR NUMBER(4,0) , CALENDAR_YEAR_ID NUMBER , FISCAL_YEAR NUMBER(4,0) , FISCAL_YEAR_ID NUMBER , DAYS_IN_CAL_YEAR NUMBER , DAYS_IN_FIS_YEAR NUMBER , END_OF_CAL_YEAR DATE , END_OF_FIS_YEAR DATE ); ??? ??(? ??? ??) INSERT /*+ append */ INTO partition_test SELECT a.* FROM sh.times a , ( SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10000 ) b; ?? ???(Partition_test2) ? ??? ?? SQL> delete partition_test2 2 where time_id<= (to_date('2000-12-31','YYYY-MM-DD')); 10960000 ?? ???????. Execution Plan
- 5. Part 1 ORACLE │99 ---------------------------------------------------------- Plan hash value: 3385385008 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 5718 | 51462 | 141K (1)| 00:28:15 | | 1 | DELETE | PARTITION_TEST2 | | | | | |* 2 | TABLE ACCESS FULL| PARTITION_TEST2 | 5718 | 51462 | 141K (1)| 00:28:15 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-ddhh24:mi:ss')) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5516 recursive calls 13289330 db block gets 521869 consistent gets 520081 physical reads 5831072716 redo size 565 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 10960000 rows processed ??? ???(Partition_test)? ??? ?? SQL> alter table partition_test drop partition partition_test1; ???? ???????. SQL> alter table partition_test drop partition partition_test2; ???? ???????. SQL> alter table partition_test drop partition partition_test3;
- 6. 100│2013 ???? White Paper ???? ???????. ??? INSERT ??? ???? Insert ?? ??? ??? ??? Delete ??? ?? ?? ??? ??? ? ? ???. ?? ??? ???? ???? ????(Nologging)? ??(/*+ append */)?? ? Direct Path Insert ? ???? redo ??? ???? ??? ?? ????. ??? ??? ? ?? ??? ?????, ?? ???? ???. ?? ??? Insert ?? ? ???? ???? ???? ????? ???? ??? ???? ??? ?? ??? ????? ?? ??? ??? ????. Direct Path Insert ? ??? ???? ? ?? ??? ?? ??? Insert ??? ????? ?? ???? ??? ??? ????? ??. ???? ??? ?? Direct Path Insert ???? Insert ??? ?????? ???? ?? ???? ??? ? ??? redo ???? ??? ???? ???? Insert ??? ??? ???. ?? ???? ?? ??? ??? ?? Direct Path Insert ??? ??? ?? ?? ??? ? ??? ????. ??? ?? DDL ?? (? ??? Delete ? Partition_test ??? DDL ??) ? ?? ???? ???? ??? ??? Insert SQL> alter table partition_testnologging; ???? ???????. SQL> INSERT /*+ append */
- 7. Part 1 ORACLE │101 2 INTO partition_test 3 SELECT a.* 4 FROM sh.times a , 5 ( 6 SELECT LEVEL 7 FROM dual 8 CONNECT BY LEVEL <= 10000 9 ) b; 18260000 ?? ?? ???????. ? ?: 00:08:50.77 Statistics ---------------------------------------------------------- 37399 recursive calls 4600992 db block gets 323059 consistent gets 493284 physical reads 5177833936 redo size 564 bytes sent via SQL*Net to client 636 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 3 sorts (disk) 18260000 rows processed ??? ???? ???? Insert ?? ??? ?? SQL> truncate table partition_test; ???? ?????. ? ?: 00:00:00.74 SQL> drop index idx_test1; ???? ???????. ? ?: 00:00:00.09 SQL> drop index idx_test2; ???? ???????. ? ?: 00:00:00.03
- 8. 102│2013 ???? White Paper SQL> drop index idx_test3; ???? ???????. ? ?: 00:00:00.03 SQL> INSERT /*+ append */ 2 INTO partition_test 3 SELECT a.* 4 FROM sh.times a , 5 ( 6 SELECT LEVEL 7 FROM dual 8 CONNECT BY LEVEL <= 10000 9 ) b; 18260000 ?? ?? ???????. ? ?: 00:00:24.49 Statistics ---------------------------------------------------------- 13820 recursive calls 532991 db block gets 4210 consistent gets 7 physical reads 1975520 redo size 566 bytes sent via SQL*Net to client 636 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 20 sorts (memory) 0 sorts (disk) 18260000 rows processed SQL> create index idx_test1 on partition_test(time_id, day_name) local; ???? ???????. ? ?: 00:00:26.87 SQL> create index idx_test2 on partition_test(DAY_NUMBER_IN_WEEK,DAY_NUMBER_IN_MONTH) local; ???? ???????. ? ?: 00:00:27.42 SQL> create index idx_test3 on partition_test(WEEK_ENDING_DAY,WEEK_ENDING_DAY_ID)
- 9. Part 1 ORACLE │103 local; ???? ???????. ? ?: 00:00:26.72 ? ??? ??? ?? ??? ???? ??? ?, ?? ???? ???? ??? ?? ??? ??? ?? ??? ? ??? ?? ??? ????. ?? ??? ??? ??? ???? ?? ?? ??? ?? ????? ???? ?????? ??? ???? ???? ?? ??? ? ??? Drop ??? Unusable ??? ???? ????? ??? ???. ??, ??? ???? ?? ???? ?? ????? ??? ??? ? ?? ??????? ??. ????? ?????? redo ??? ????? ? ??? ? ? ?? ????? ?? ??. ??? Index Split? ?? Direct Path Insert ???? ?? ???? ?? Insert ?? ?? ??? ??? ??? ? ??. ???? ?? ????? SYSDATE ? ???? ??? ???. ??, ? ??? Index ? ???? ??? ????, ? ?? Insert ??? ????? 9:1 Index Split ? ???? ???? ??? ???? ??? ???. ?? ?????, Index Split ? ?? ????? ??. ? ??? ??? ? ??? ? ???. ??, 9:1 Split ?? Max ?? ???? ???? ?? ??? Index Leaf Block ? ? ?? ? ?? ??? ?? ?? Free List ? Free Block ? ??? ?? ?? ??? Index Leaf Block ? ???? ???? ??? Free Block ? ???? ?? ???? ?????? Block ? ? ??? ??? ???. ?? ???? ???? ???? ????? Index Size ? ????, Index Split ? ??? ?? ???? ????? ????. ???, Index Split ??? ??? ???.
- 10. 104│2013 ???? White Paper Index Split ? ? ??? redo ? ???? Split ? ???? ???? ???? Locking ? ? ? ? ??? ????. ? Locking ?, Enq : TX – Index Contention ??? Wait Event ? Split ? ???? ???? Split ??? ???? ??. ???, ?? ???? ??? ??? ???? ???? ?? ?? ???? ??? ???? ???? ??, Index Split ? ?? Wait Time ??? ??? redo ?? ??? Insert ??? ??? ????? ????. ???? ?? ??? ? ?? ??? ???? ???? ????? ??? ?? Index ??? ??? ???? ? ???? ??? ????. ? ??? ??? ??? ???? ??? ?, ?? ?? ??? Index Leaf Block ? ??? ? ??? ?? ?? ???? ??? ? ??? ???? ??? ??? ????. ????? Index ?? ?? ??? NDV(Number of Distinct Value) ?? ? ?? Index Split ??? ? ?? ?? ?? ???. ??, ? ???? ??? ?? Index Clustering Factor ? ?? ??? ? ???? NDV ?? ? ??? ????? ??? ??? ??. Array Processing ??? DML ??? ???? Load ??? Update(Delete) ? ? ????? LOOP ???? ?? ??? ???? ??? ??. ? ??? ? Loop ?? 1 ?? DML ??? ???? ? ? ?? DBMS CALL ? ???? ??? ??? ????. ??? Array Processing ? ??? Bulk SQL ? ???? Loop ?? ? ??? SQL ???? ? ??? ????. ?, ??? DML ? ? ??(Limit ???? ???? ??? ??? ? ? ? ??? ???? ?? ??)? ??? ? ???? DBMS CALL ? ???? Loop ? ?? ?? ??? ?? ? ???? ??? ? ? ??. ?? ???? ?? ? ????? ??? ??.
- 11. Part 1 ORACLE │105 Array Processing ? ???? Insert ?? Procedure Source - P_BULK_INSERT_TEST1 CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST1 IS CURSOR sales_cur IS SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD FROM SH.SALES; TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER; SALES_TBL SALES_TBL_TYPE; BEGIN OPEN sales_cur; LOOP FETCH sales_cur BULK COLLECT INTO sales_tbl LIMIT 1000; -- PGA ??? ?? ???? ?? 1000 ??? ?? FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP --?? ??? ???? sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5; END LOOP; -- FETCH ? 1000 ?? SQL 1 ? ???? ?? FORALL i IN sales_tbl.FIRST..sales_tbl.LAST INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID, sales_tbl(i).CUST_ID, sales_tbl(i).TIME_ID, sales_tbl(i).CHANNEL_ID, sales_tbl(i).PROMO_ID, sales_tbl(i).QUANTITY_SOLD, sales_tbl(i).AMOUNT_SOLD ); EXIT WHEN sales_cur%NOTFOUND; END LOOP; CLOSE sales_cur; COMMIT; EXCEPTION
- 12. 106│2013 ???? White Paper WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200)); END; / ??? Loop ? ?? Insert ?? Procedure Source - P_BULK_INSERT_TEST2 CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST2 IS CURSOR sales_cur IS SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD FROM SH.SALES; TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER; SALES_TBL SALES_TBL_TYPE; BEGIN OPEN sales_cur; LOOP FETCH sales_cur BULK COLLECT INTO sales_tbl LIMIT 1000; -- PGA ??? ?? ???? ?? 1000 ??? ?? FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP --?? ??? ???? sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5; END LOOP; -- LOOP ? ???? ??; INSERT SQL 1000 ? ?? FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP -- FORALL ??? ?? INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID, sales_tbl(i).CUST_ID, sales_tbl(i).TIME_ID, sales_tbl(i).CHANNEL_ID, sales_tbl(i).PROMO_ID, sales_tbl(i).QUANTITY_SOLD, sales_tbl(i).AMOUNT_SOLD );
- 13. Part 1 ORACLE │107 END LOOP; EXIT WHEN sales_cur%NOTFOUND; END LOOP; CLOSE sales_cur; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200)); END; / P_BULK_INSERT_TEST2 Vs. P_BULK_INSERT_TEST2 SQL> exec P_BULK_INSERT_TEST1; PL/SQL ??? ????? ???????. ? ?: 00:00:02.20 SQL> SQL> SQL> exec P_BULK_INSERT_TEST2; PL/SQL ??? ????? ???????. ? ?: 00:00:35.08 ?? : Array Processing ? ??? Update Procedure Source CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST2 IS CURSOR sales_cur IS SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD FROM SH.SALES;
- 14. 108│2013 ???? White Paper TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER; SALES_TBL SALES_TBL_TYPE; BEGIN OPEN sales_cur; LOOP FETCH sales_cur BULK COLLECT INTO sales_tbl LIMIT 1000; -- PGA ??? ?? ???? ?? 1000 ??? ?? FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP --?? ??? ???? sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5; END LOOP; -- UPDATE / DELETE ? ?? FORALL i IN sales_tbl.FIRST..sales_tbl.LAST UPDATE SALES SET AMOUNT_SOLD = sales_tbl(i).AMOUNT_SOLD WHERE PROD_ID = sales_tbl(i).PROD_ID AND CUST_ID = sales_tbl(i).CUST_ID AND TIME_ID = sales_tbl(i).TIME_ID AND CHANNEL_ID = sales_tbl(i).CHANNEL_ID AND PROMO_ID = sales_tbl(i).PROMO_ID; EXIT WHEN sales_cur%NOTFOUND; END LOOP; CLOSE sales_cur; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200)); END; /
- 15. Part 1 ORACLE │109 ? ?? ?? ?? ??? ???? ??? ????? ??? DML ??? ?? ???? Process ? ??? ? ?? ???. ??? ? ??? ???? ? ??? ?? ???? ??. ?? Partition Table ?? ??? ??? ??? ???? ??? ??? ?? ???? ??? ? ???? ???? Partition Table ? ???? ??? ??? ??? ???. ???? Offline ? ????? ?? ????? ??? ? ?? ????, ?? ?????? ? ?? ???? Partition Table ? ???? ??? ?????, ?????? ??? ? ? ????? ??? ???? ?? DBA ??? ? ????? ???? ??? ?? ? ??. ??? ? ?????? ??? ??? ? ?? ?????. ?? ?? Partition Exchange(? ? PE) ??? ???? ???. PE ??? ? ??? ?? ???? Partition Table ?? ?? ? ???? ????. ??, ???? ??? ?? ?? ??????? ???? Partition Table ?? ??? ?? ? ??. ?? ???? PE ??? ???? ?? ???? Partition Table ?? ? ?? ??? ??? ? ??? ??. ??? ???? ?? ??? : Partition_test2 : Delete ???? ??? ?? ????? ??? ?? ???? ?? Partition Table ? ??? Partition Table ?? CREATE TABLE partition_test (TIME_ID DATE , DAY_NAME VARCHAR2(9), DAY_NUMBER_IN_WEEK NUMBER(1,0) , DAY_NUMBER_IN_MONTH NUMBER(2,0) , CALENDAR_WEEK_NUMBER NUMBER(2,0) , FISCAL_WEEK_NUMBER NUMBER(2,0) , WEEK_ENDING_DAY DATE , WEEK_ENDING_DAY_ID NUMBER , CALENDAR_MONTH_NUMBER NUMBER(2,0) , FISCAL_MONTH_NUMBER NUMBER(2,0) , CALENDAR_MONTH_DESC VARCHAR2(8) ,
- 16. 110│2013 ???? White Paper CALENDAR_MONTH_ID NUMBER , FISCAL_MONTH_DESC VARCHAR2(8) , FISCAL_MONTH_ID NUMBER, DAYS_IN_CAL_MONTH NUMBER , DAYS_IN_FIS_MONTH NUMBER , END_OF_CAL_MONTH DATE , END_OF_FIS_MONTH DATE , CALENDAR_MONTH_NAME VARCHAR2(9) , FISCAL_MONTH_NAME VARCHAR2(9) , CALENDAR_QUARTER_DESC CHAR(7) , CALENDAR_QUARTER_ID NUMBER , FISCAL_QUARTER_DESC CHAR(7) , FISCAL_QUARTER_ID NUMBER , DAYS_IN_CAL_QUARTER NUMBER , DAYS_IN_FIS_QUARTER NUMBER , END_OF_CAL_QUARTER DATE , END_OF_FIS_QUARTER DATE , CALENDAR_QUARTER_NUMBER NUMBER(1,0) , FISCAL_QUARTER_NUMBER NUMBER(1,0) , CALENDAR_YEAR NUMBER(4,0) , CALENDAR_YEAR_ID NUMBER , FISCAL_YEAR NUMBER(4,0) , FISCAL_YEAR_ID NUMBER , DAYS_IN_CAL_YEAR NUMBER , DAYS_IN_FIS_YEAR NUMBER , END_OF_CAL_YEAR DATE , END_OF_FIS_YEAR DATE ) PARTITION BY RANGE( time_id )( PARTITION part1 VALUES less than (to_date('2002-12-31','YYYY-MM-DD')) ); -- Partiton Table ? ??? ???? Max ?? ?? Partition Exchange ?? SQL> ALTER TABLE partition_test exchange partition part1 WITH TABLE partition_test2 without validation ; ???? ???????. ? ?: 00:00:00.03 ?? ??? ?? ? Rename
- 17. Part 1 ORACLE │111 SQL> DROP TABLE partition_test2; ???? ???????. ? ?: 00:00:00.06 SQL> RENAME partition_test to partition_test2; ??? ??? ???????. ? ?: 00:00:00.03 ?? ?? ???? ??? ???? ??? Partition Table ? ??? ? ??? ? ???. ? ? ??? ???? ?? ????? ??? ?? ????(Primary Key)?? Index ??? ?? ???? ????? ???? PE ??? ????. ?? ?? ??? ??? DML ??? ??? ???? ??? ???? ?? ??? ????? ? ???? redo ? ?? ??? ????? ?? ??? Index ? ????? Drop ??? ?? ? ? ??. ??? ??? ??? ?? ??? ???? DBA ??? ?? ? ???? ??? ? ??? ? ??. ???? ??? ???? ??? ? ?? ?? ??? ????? ???? ??? ??? ? ??? ? ???? ? ???? ??? ???. ??? ?? ??? ???? ????? ??? ? ?? ?? ??? ??? ??. ?? ??? ??? ?? ?? ???? ?? ?? ???? ???? ??? ? ?? ?? ????, ? ? ?? ?? ???? ?? ????? ???. ??????? ?????? ?? ????? ??? ?????. ?? ???? ???? ? ??? ??? ???? ??? ?? ???? ???? ????? ??? ? ??, ? ? ?? ??? ??? ? ?? ??? ??? ?? ?? ? ?? ???. ??? DML ?? ????? ???? Down Time ????? ? ?? ??? ?? ?? ?? ????? ??.