[oracle@Enmosdb ~]$ sqlplus eygle/eygle @b
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jul 14 16:55:50 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table tbtemp purge;
drop table tbtemp purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table tbperm purge;
drop table tbperm purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> CREATE TABLE tbtemp (
2 id NUMBER(10),
3 created_date DATE,
4 name VARCHAR2(50)
5 );
Table created.
SQL>
SQL> ALTER TABLE tbtemp ADD (
2 CONSTRAINT pk_tbtemp PRIMARY KEY (id)
3 );
Table altered.
SQL>
SQL> insert into tbtemp select object_id,sysdate,object_name
2 from dba_objects where object_id between 60 and 100;
41 rows created.
SQL>
SQL> CREATE TABLE tbperm (
2 id NUMBER(10),
3 created_date DATE,
4 name VARCHAR2(50)
5 )
6 PARTITION BY RANGE (created_date)
7 (PARTITION p1 VALUES LESS THAN (to_date('2011-07-12','yyyy-mm-dd')),
8 PARTITION p2 VALUES LESS THAN (to_date('2011-07-13','yyyy-mm-dd')),
9 PARTITION p3 VALUES LESS THAN (to_date('2011-07-14','yyyy-mm-dd')),
10 PARTITION p4 VALUES LESS THAN (to_date('2011-07-15','yyyy-mm-dd'))
11 );
Table created.
SQL>
SQL> insert into tbperm select object_id,sysdate-2,object_name
2 from dba_objects where object_id < 21;
19 rows created.
SQL> insert into tbperm select object_id,sysdate-1,object_name
2 from dba_objects where object_id between 21 and 51;
31 rows created.
SQL> commit;
Commit complete.
SQL> ALTER TABLE tbperm ADD
2 CONSTRAINT pk_tbperm PRIMARY KEY (id)
3 USING INDEX (CREATE INDEX pk_tbperm ON TBPERM(id) GLOBAL
4 );
Table altered.
SQL>
SQL>
SQL> SET TIMING ON;
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
Elapsed: 00:00:00.01
SQL> ALTER TABLE tbperm
2 EXCHANGE PARTITION P4
3 WITH TABLE tbtemp
4 WITH VALIDATION
5 UPDATE GLOBAL INDEXES;
Table altered.
Elapsed: 00:00:00.14
SQL>
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
对于全局索引的维护:
********************************************************************************
insert /*+ RELATIONAL(TBPERM) PARALLEL(TBPERM,1) APPEND */ into
"EYGLE"."TBPERM" partition ("P4") select /*+ RELATIONAL(TBPERM)
PARALLEL(TBPERM,1) */ * from "EYGLE"."TBPERM" partition ("P4") delete
global indexes
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 3 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 1 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 39 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD AS SELECT (cr=3 pr=0 pw=0 time=346 us)
0 PARTITION RANGE SINGLE PARTITION: 4 4 (cr=3 pr=0 pw=0 time=53 us)
0 TABLE ACCESS FULL TBPERM PARTITION: 4 4 (cr=3 pr=0 pw=0 time=43 us)
********************************************************************************
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ NO_PARALLEL("TBTEMP") FULL("TBTEMP") NO_PARALLEL_INDEX("TBTEMP")
*/ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "EYGLE"."TBTEMP" "TBTEMP")
SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 39 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=109 us)
41 TABLE ACCESS FULL TBTEMP (cr=7 pr=0 pw=0 time=73 us)
********************************************************************************
insert /*+ RELATIONAL(TBPERM) PARALLEL(TBPERM,1) APPEND */ into
"EYGLE"."TBPERM" partition ("P4") select /*+ RELATIONAL(TBTEMP)
PARALLEL(TBTEMP,1) */ * from "EYGLE"."TBTEMP" insert global indexes
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 7 3 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 8 3 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 39 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=7 pr=0 pw=0 time=519 us)
41 TABLE ACCESS FULL TBTEMP (cr=7 pr=0 pw=0 time=100 us)
********************************************************************************
脚本:
set echo on
drop table tbtemp purge;
drop table tbperm purge;
CREATE TABLE tbtemp (
id NUMBER(10),
created_date DATE,
name VARCHAR2(50)
);
ALTER TABLE tbtemp ADD (
CONSTRAINT pk_tbtemp PRIMARY KEY (id)
);
insert into tbtemp select object_id,sysdate,object_name
from dba_objects where object_id between 60 and 100;
CREATE TABLE tbperm (
id NUMBER(10),
created_date DATE,
name VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION p1 VALUES LESS THAN (to_date('2011-07-12','yyyy-mm-dd')),
PARTITION p2 VALUES LESS THAN (to_date('2011-07-13','yyyy-mm-dd')),
PARTITION p3 VALUES LESS THAN (to_date('2011-07-14','yyyy-mm-dd')),
PARTITION p4 VALUES LESS THAN (to_date('2011-07-15','yyyy-mm-dd'))
);
insert into tbperm select object_id,sysdate-2,object_name
from dba_objects where object_id < 21;
insert into tbperm select object_id,sysdate-1,object_name
from dba_objects where object_id between 21 and 51;
commit;
ALTER TABLE tbperm ADD
CONSTRAINT pk_tbperm PRIMARY KEY (id)
USING INDEX (CREATE INDEX pk_tbperm ON TBPERM(id) GLOBAL
);
SET TIMING ON;
alter session set events '10046 trace name context forever,level 12';
ALTER TABLE tbperm
EXCHANGE PARTITION P4
WITH TABLE tbtemp
WITH VALIDATION
UPDATE GLOBAL INDEXES;
exit;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
