Does split the big fact table make a difference?

[summer06]

1, The following about composite table compress is from metalink:

While attempting to move a subpartition of a composite table with "compress" option, it fails with the following errors:

ORA-14160: this physical attribute may not be specified for a table subpartition Trying to move at the partition level with "compress" option fails with

ORA-14020: this physical attribute may not be specified for a table partition

SQL> alter table fct_incoming_traffic move subpartition SALES_01_JAN2003
tablespace users compress;
alter table fct_incoming_traffic move subpartition SALES_01_JAN2003
tablespace users compress

*
ERROR at line 1:
ORA-14160: this physical attribute may not be specified for a table
subpartition


SQL> alter table fct_incoming_traffic move partition SALES_JAN2003
compress
2 (subpartition SALES_01_JAN2003 tablespace users,
3 subpartition SALES_02_JAN2003 tablespace users);
(subpartition SALES_01_JAN2003 tablespace users,
*
ERROR at line 2:
ORA-14020: this physical attribute may not be specified for a table partition

Cause
Compress physical attribute is not available for the Subpartitions at the moment.

Currently an Enhancement Request Bug:2210439 exists for the same.
Fix
Compress physical attribute cannot be used for the Subpartitions. So this operation is not possible.
References
Bug 2210439 - To Add Compress Option For Subpartitions
Note 214168.1 - Logging an Enhancement Request


Thanks again!

[slimdave]

The columns are described here: http://download-west.oracle.com/docs...52.htm#1303953

[mike9]

You can not define compression at subpartition level.
But you can define compression at table or partition level.

e.g.
ALTER TABLE sales_2 modify PARTITION sales_2000 COMPRESS
even if sales_2 is subpartitioned.

To compress subpartitions you could do something like this (I have no 9iR2 DB available so I did it on 10g):


SQL> CREATE TABLE sales_2
2 (
3 prod_id NUMBER,
4 cust_id NUMBER,
5 time_id DATE,
6 channel_id NUMBER,
7 promo_id NUMBER,
8 quantity_sold NUMBER(10,2),
9 amount_sold NUMBER(10,2)
10 )
11 NOCOMPRESS
12 PARTITION BY RANGE (time_id)
13 SUBPARTITION BY HASH (cust_id)
14 SUBPARTITIONS 4
15 (
16 PARTITION sales_1999 VALUES LESS THAN (TO_DATE('20000101' , 'YYYYMMDD')),
17 PARTITION sales_2000 VALUES LESS THAN (TO_DATE('20010101' , 'YYYYMMDD')),
18 PARTITION sales_2001 VALUES LESS THAN (TO_DATE('20020101' , 'YYYYMMDD'))
19 )
20 /

Table created.

SQL>
SQL> INSERT /*+ append */
2 INTO sales_2
3 SELECT *
4 FROM sales
5 /

918846 rows created.

SQL>
SQL> COMMIT
2 /

Commit complete.

SQL>
SQL> BEGIN
2 SYS.DBMS_STATS.GATHER_TABLE_STATS (
3 OwnName => 'SH'
4 ,TabName => 'SALES_2'
5 ,Granularity => 'SUBPARTITION'
6 ,Estimate_Percent => 10
7 ,Block_sample => TRUE
8 );
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT table_name
2 , partition_name
3 , subpartition_position
4 , blocks
5 FROM user_tab_subpartitions
6 WHERE table_name = 'SALES_2'
7 /

TABLE_NAME PARTITION_NAME SUBPARTITION_POSITION BLOCKS
------------------------------ ------------------------------ --------------------- ----------
SALES_2 SALES_1999 1 535
SALES_2 SALES_1999 2 527
SALES_2 SALES_1999 3 549
SALES_2 SALES_1999 4 519
SALES_2 SALES_2000 1 295
SALES_2 SALES_2000 2 294
SALES_2 SALES_2000 3 309
SALES_2 SALES_2000 4 283
SALES_2 SALES_2001 1 311
SALES_2 SALES_2001 2 335
SALES_2 SALES_2001 3 328
SALES_2 SALES_2001 4 336

12 rows selected.

SQL>
SQL> CREATE TABLE sales_2_tmp
2 (
3 prod_id NUMBER,
4 cust_id NUMBER,
5 time_id DATE,
6 channel_id NUMBER,
7 promo_id NUMBER,
8 quantity_sold NUMBER(10,2),
9 amount_sold NUMBER(10,2)
10 )
11 COMPRESS
12 PARTITION BY HASH (CUST_ID)
13 PARTITIONS 4
14 /

Table created.

SQL>
SQL> INSERT /*+ append */
2 INTO sales_2_tmp
3 SELECT *
4 FROM sales_2 partition (sales_2000)
5 /

232646 rows created.

SQL>
SQL> COMMIT
2 /

Commit complete.

SQL>
SQL> BEGIN
2 SYS.DBMS_STATS.GATHER_TABLE_STATS (
3 OwnName => 'SH'
4 ,TabName => 'SALES_2_TMP'
5 ,Granularity => 'PARTITION'
6 ,Estimate_Percent => 10
7 ,Block_sample => TRUE
8 );
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT table_name
2 , partition_name
3 , blocks
4 FROM user_tab_partitions
5 WHERE table_name = 'SALES_2_TMP'
6 /

TABLE_NAME PARTITION_NAME BLOCKS
------------------------------ ------------------------------ ----------
SALES_2_TMP SYS_P797 117
SALES_2_TMP SYS_P798 116
SALES_2_TMP SYS_P799 121
SALES_2_TMP SYS_P800 111

SQL>
SQL> ALTER TABLE sales_2 EXCHANGE PARTITION sales_2000 WITH TABLE sales_2_tmp
2 /

Table altered.

SQL>
SQL> SELECT table_name
2 , partition_name
3 , subpartition_position
4 , blocks
5 FROM user_tab_subpartitions
6 WHERE table_name = 'SALES_2'
7 /

TABLE_NAME PARTITION_NAME SUBPARTITION_POSITION BLOCKS
------------------------------ ------------------------------ --------------------- ----------
SALES_2 SALES_1999 1 535
SALES_2 SALES_1999 2 527
SALES_2 SALES_1999 3 549
SALES_2 SALES_1999 4 519
SALES_2 SALES_2000 1 117
SALES_2 SALES_2000 2 116
SALES_2 SALES_2000 3 121
SALES_2 SALES_2000 4 111
SALES_2 SALES_2001 1 311
SALES_2 SALES_2001 2 335
SALES_2 SALES_2001 3 328
SALES_2 SALES_2001 4 336

12 rows selected.

SQL> spool off