Oracle Database Discussions

than PARALLEL hint?
and 2nd quesion: if PARALLEL is not enabled in a session, it was omit?
and 3nd quesion: which is better set compress on table or partition
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jun 22 19:57:30 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE "GDRK"."LT2_701_RANGE"
2 ( "INT_CODE" NUMBER,
3 "STAT_UNIT_CODE" VARCHAR2(20),
4 "ORGN_TYPE_CODE" CHAR(1) NOT NULL ENABLE,
5 "ORGN_CODE" VARCHAR2(26) NOT NULL ENABLE,
6 "ORGN_NAME" VARCHAR2(60) NOT NULL ENABLE,
7 "UUID" VARCHAR2(36) NOT NULL ENABLE,
8 "CYC_CODE" VARCHAR2(2) NOT NULL ENABLE,
9 "H10" VARCHAR2(2),
10 "H11" VARCHAR2(3),
11 "H12" VARCHAR2(1),
12 "H13" VARCHAR2(1),
13 "H14" VARCHAR2(1),
14 "H15" VARCHAR2(1),
15 "H16" VARCHAR2(1),
16 "H17" VARCHAR2(1),
17 "H18" VARCHAR2(1),
18 "H19_01" VARCHAR2(3),
19 "H19_02" VARCHAR2(1),
20 "H2" VARCHAR2(1),
21 "H20" VARCHAR2(4),
22 "H3_01" VARCHAR2(2),
23 "H3_02" VARCHAR2(2),
24 "H4_01" VARCHAR2(1),
25 "H4_02" VARCHAR2(1),
26 "H5_01" VARCHAR2(1),
27 "H5_02" VARCHAR2(1),
28 "H6" VARCHAR2(1),
29 "H7" VARCHAR2(1),
30 "H8" VARCHAR2(1),
31 "H9" VARCHAR2(4),
32 "REG_NUM" NUMBER,
33 "ERA_NUM" NUMBER,
34 "POWER_1" FLOAT(126),
35 "POWER_2" FLOAT(126)
36 )
37 PARALLEL
38 partition by range(INT_CODE) --substr(c1,1,1)
39 (
40 partition p0 values less than (4410000000000000000),
41 partition p1 values less than (4420000000000000000),
42 partition p2 values less than (4450000000000000000),
43 partition p5 values less than (maxvalue));

Table created.

SQL> set lines 132
SQL> set pages 9999
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
12032

SQL> set timi on

SQL> insert /*+PARALLEL( LT2_701_RANGE,4)*/ into LT2_701_RANGE select * from LT2_701;

539398 rows created.

Elapsed: 00:01:18.56
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
12032

Elapsed: 00:00:01.11
SQL> rollback;

Rollback complete.

Elapsed: 00:00:06.79
SQL> insert /*+append */ into LT2_701_RANGE select * from LT2_701;

539398 rows created.

Elapsed: 00:00:15.34
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
24320

Elapsed: 00:00:00.13
SQL> commit;

Commit complete.

Elapsed: 00:00:00.05
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
24320

Elapsed: 00:00:00.13
SQL> alter table LT2_701_RANGE move compress;
alter table LT2_701_RANGE move compress
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object


Elapsed: 00:00:00.00
SQL> alter table LT2_701_RANGE move partition p0 compress;

Table altered.

Elapsed: 00:00:38.62
SQL> alter table LT2_701_RANGE move partition p1 compress;

Table altered.

Elapsed: 00:00:23.06
SQL> alter table LT2_701_RANGE move partition p2 compress;

Table altered.

Elapsed: 00:00:02.34
SQL> alter table LT2_701_RANGE move partition p5 compress;

Table altered.

Elapsed: 00:00:03.71
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
7040

Elapsed: 00:00:00.13
SQL> truncate table LT2_701_RANGE;

Table truncated.

Elapsed: 00:00:01.61
SQL> select table_name,compression from tabs where table_name='LT2_701_RANGE';

TABLE_NAME COMPRESS
------------------------------ --------
LT2_701_RANGE

Elapsed: 00:00:00.11
SQL> insert /*+append */ into LT2_701_RANGE select * from LT2_701;

539398 rows created.

Elapsed: 00:00:47.63
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
7040

Elapsed: 00:00:00.13
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL> truncate table LT2_701_RANGE;

Table truncated.

Elapsed: 00:00:01.43
SQL> ALTER SESSION ENABLE PARALLEL DML;

Session altered.

Elapsed: 00:00:00.00
SQL> insert /*+PARALLEL( LT2_701_RANGE,4)*/ into LT2_701_RANGE select * from LT2_701;

539398 rows created.

Elapsed: 00:00:10.07
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
7296

Elapsed: 00:00:00.14
SQL> alter table LT2_701_RANGE move partition p0 compress;
alter table LT2_701_RANGE move partition p1 compress;
alter table LT2_701_RANGE move partition p2 compress;
alter table LT2_701_RANGE move partition p5 compress;

Table altered.

Elapsed: 00:00:17.30
SQL>
Table altered.

Elapsed: 00:00:10.60
SQL>
Table altered.

Elapsed: 00:00:01.04
SQL>
Table altered.

Elapsed: 00:00:04.06
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
7296

Elapsed: 00:00:00.13
SQL> truncate table LT2_701_RANGE;

Table truncated.

Elapsed: 00:00:01.45
SQL> alter table LT2_701_RANGE move partition p0 nocompress;
alter table LT2_701_RANGE move partition p1 nocompress;
alter table LT2_701_RANGE move partition p2 nocompress;
alter table LT2_701_RANGE move partition p5 nocompress;

Table altered.

Elapsed: 00:00:00.09
SQL>
Table altered.

Elapsed: 00:00:00.08
SQL>
Table altered.

Elapsed: 00:00:00.09
SQL>
Table altered.

Elapsed: 00:00:00.09
SQL> insert /*+PARALLEL( LT2_701_RANGE,4)*/ into LT2_701_RANGE select * from LT2_701;

539398 rows created.

Elapsed: 00:00:05.07
SQL> alter table LT2_701_RANGE move partition p0 compress;
alter table LT2_701_RANGE move partition p1 compress;
alter table LT2_701_RANGE move partition p2 compress;
alter table LT2_701_RANGE move partition p5 compress;

Table altered.

Elapsed: 00:00:15.99
SQL>
Table altered.

Elapsed: 00:00:10.10
SQL>
Table altered.

Elapsed: 00:00:00.95
SQL>
Table altered.

Elapsed: 00:00:03.75
SQL> select sum(blocks) from user_extents where SEGMENT_NAME='LT2_701_RANGE';

SUM(BLOCKS)
-----------
7296

Elapsed: 00:00:01.12