If you read my previous post, you might have noticed that I was using a non-partitioned table, of a somewhat small size. The basic stuff is there, but if you’re managing bigger tables you might want to do it using partitions and do it using parallel DDL, to speed things up.
This raises a new challenge: you might not want to uncompress all your partitions, and those that you decide to uncompress should be able to do it in parallel. The sole propose of this post is to show you how can you do it, assuming you want to uncompress all your HCC compressed partitions in the non-Exadata storage… and in parallel 
Now the steps
1. Create 4 different tablespaces to accommodate the four partitions of the table we’re about to create
2. Create a table called BRAGA (3rd biggest city in Portugal and the finalists in the UEFA Europa Cup next 18th May in Dublin’s Arena). This table is hash partitioned, with four partitions pointing to the four tablespaces created previously. The table will use HCC compression at the default level (QUERY HIGH), which uses the exact same algorithms and transformations as ARCHIVE LOW, but with a smaller Compression Unit (CU).
3. Direct Load the table with info from the Data Dictionary, creating 415805 rows (about 70MB of uncompressed info).
4. Check the space taken by the CUs: 2.75MB. A compression ratio around 25!
5. Gather stats for the table and its partitions
6. Check how the hashing algorithm spread the data across the four partitions. It should be balanced. I’ve written another post this year about this. You should choose the column with the biggest cardinality to spread the data more evenly. It should be a number around 415805/4=103952 rows for each partition.
7. Backup the tablespaces
8. Recover them back to non-Exadata storage, this time to a local disk in node 1 (/home/oracle/lmc). I was using only one instance to avoid having ORA-01157 errors blowing in my face.
9. Check if the datafiles are on the expected place
10. Query table BRAGA and get error ORA-64307 thrown at you
11. Now it’s the tricky bit! First you modify the compression status of each partition to “nocompress” and then …
12. … you uncompressed them at will and in parallel (after enabling parallel DDL at the session level). Each ALTER TABLE braga MOVE PARTITION will be a parallel operation in itself, and can be done in parallel amongst themselves to raise the bar!
13. After the uncompression gets done you’ll see that the table is readable and has 69MB!
14. To play around a bit I’ve decided to compress them again but using OLTP compression and in the end I measure the ratio. Nice one too.
Now the code
create tablespace knod1
datafile '+DGDATA' size 100m
autoextend on next 250m maxsize 5g;
create tablespace knod2
datafile '+DGDATA' size 100m
autoextend on next 250m maxsize 5g;
create tablespace knod3
datafile '+DGDATA' size 100m
autoextend on next 250m maxsize 5g;
create tablespace knod4
datafile '+DGDATA' size 100m
autoextend on next 250m maxsize 5g;
create table lmc.braga ( --column mimic dba_segments
OWNER VARCHAR2(30),
SEGMENT_NAME VARCHAR2(81),
PARTITION_NAME VARCHAR2(30),
SEGMENT_TYPE VARCHAR2(18),
SEGMENT_SUBTYPE VARCHAR2(10),
TABLESPACE_NAME VARCHAR2(30),
HEADER_FILE NUMBER,
HEADER_BLOCK NUMBER,
BYTES NUMBER,
BLOCKS NUMBER,
EXTENTS NUMBER,
INITIAL_EXTENT NUMBER,
NEXT_EXTENT NUMBER,
MIN_EXTENTS NUMBER,
MAX_EXTENTS NUMBER,
MAX_SIZE NUMBER,
RETENTION VARCHAR2(7),
MINRETENTION NUMBER,
PCT_INCREASE NUMBER,
FREELISTS NUMBER,
FREELIST_GROUPS NUMBER,
RELATIVE_FNO NUMBER,
BUFFER_POOL VARCHAR2(7),
FLASH_CACHE VARCHAR2(7),
CELL_FLASH_CACHE VARCHAR2(7)
)
partition by hash(header_block)
partitions 4
store in (knod1, knod2, knod3, knod4)
compress for query high
;
Direct-Load it so HCC can kick-in:
SQL> insert /*+ APPEND */ into lmc.braga
2 select * from dba_segments;
415805 rows created.
select sum(bytes)/1024/1024 Megs
from dba_extents
where segment_name = 'BRAGA';
MEGS
----------
2.75
-- Collect Stats
exec dbms_stats.gather_table_stats('LMC','BRAGA');
select partition_name, num_rows
from user_tab_partitions
where table_name='BRAGA';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
SYS_P61985 101624
SYS_P61986 104043
SYS_P61987 103247
SYS_P61988 106891
Nice Distribution! This means we’ve picked the right column to hash partition it.
Now let’s move it to non-Exadata storage:
1st) Backup.
You can do it one by one or backup them all with the same rman command, see my example:
RMAN> backup tablespace knod1;
Starting backup at 13-MAY-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=693 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=01006 name=+DGDATA/orcl/datafile/knod1.1153.750943521
channel ORA_DISK_1: starting piece 1 at 13-MAY-11
channel ORA_DISK_1: finished piece 1 at 13-MAY-11
piece handle=+DGARCH/orcl/backupset/2011_05_13/nnndf0_tag20110512t120530_0.279.750945931 tag=TAG20110512T120530 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 13-MAY-11
RMAN> backup tablespace knod2,knod3,knod4;
Starting backup at 13-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=01007 name=+DGDATA/orcl/datafile/knod2.1154.750943521
input datafile file number=01008 name=+DGDATA/orcl/datafile/knod3.1155.750943523
input datafile file number=01009 name=+DGDATA/orcl/datafile/knod4.1156.750943525
channel ORA_DISK_1: starting piece 1 at 13-MAY-11
channel ORA_DISK_1: finished piece 1 at 13-MAY-11
piece handle=+DGARCH/orcl/backupset/2011_05_13/nnndf0_tag20110512t120633_0.278.750945995 tag=TAG20110512T120633 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 13-MAY-11
Now restore to “/home/oracle/lmc”:
run {
sql 'alter tablespace "KNOD1" offline immediate';
sql 'alter tablespace "KNOD2" offline immediate';
sql 'alter tablespace "KNOD3" offline immediate';
sql 'alter tablespace "KNOD4" offline immediate';
set newname for datafile '+DGDATA/orcl/datafile/knod1.1153.750943521' to '/home/oracle/lmc/knod1.dbf';
restore tablespace 'KNOD1';
set newname for datafile '+DGDATA/orcl/datafile/knod2.1154.750943521' to '/home/oracle/lmc/knod2.dbf';
restore tablespace 'KNOD2';
set newname for datafile '+DGDATA/orcl/datafile/knod3.1155.750943523' to '/home/oracle/lmc/knod3.dbf';
restore tablespace 'KNOD3';
set newname for datafile '+DGDATA/orcl/datafile/knod4.1156.750943525' to '/home/oracle/lmc/knod4.dbf';
restore tablespace 'KNOD4';
switch datafile all;
recover tablespace 'KNOD1';
recover tablespace 'KNOD2';
recover tablespace 'KNOD3';
recover tablespace 'KNOD4';
sql 'alter tablespace "KNOD1" online';
sql 'alter tablespace "KNOD2" online';
sql 'alter tablespace "KNOD3" online';
sql 'alter tablespace "KNOD4" online';
}
After a while everything is back to non-Exadata storage:
[oracle@db01 lmc]$ ll
total 410144
-rw-r----- 1 oracle oinstall 104890368 May 13 18:12 knod1.dbf
-rw-r----- 1 oracle oinstall 104890368 May 13 18:13 knod2.dbf
-rw-r----- 1 oracle oinstall 104890368 May 13 18:13 knod3.dbf
-rw-r----- 1 oracle oinstall 104890368 May 13 18:13 knod4.dbf
[oracle@db01 lmc]$ pwd
/home/oracle/lmc
Let’s try to query the table:
SQL> select count(*)
2 from lmc.braga;
from lmc.braga
*
ERROR at line 2:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage
This is fine! Now let’s uncompress it in parallel:
SQL> ALTER SESSION ENABLE PARALLEL DDL;
Session altered.
SQL> alter table braga modify partition SYS_P61985 nocompress;
Table altered.
SQL> alter table braga modify partition SYS_P61986 nocompress;
Table altered.
SQL> alter table braga modify partition SYS_P61987 nocompress;
Table altered.
SQL> alter table braga modify partition SYS_P61988 nocompress;
Table altered.
SQL> select sum(bytes)/1024/1024 Megs
from dba_extents
where segment_name = 'BRAGA'; 2 3
MEGS
----------
2.75
SQL> select count(*)
2 from braga;
from braga
*
ERROR at line 2:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage
Still can't!!!!!!! because we haven't released it yet.
SQL> alter table braga move partition SYS_P61985 parallel;
Table altered.
SQL> alter table braga move partition SYS_P61986 parallel;
Table altered.
SQL> alter table braga move partition SYS_P61987 parallel;
Table altered.
SQL> alter table braga move partition SYS_P61988 parallel;
Table altered.
SQL> select count(*)
2 from braga;
COUNT(*)
----------
415805
SQL> select sum(bytes)/1024/1024 Megs
from dba_extents
where segment_name = 'BRAGA'; 2 3
MEGS
----------
69
Wow! Looks like the ratio was 69/2.75=25 or in orageek language:
SQL> select round(69/2.75) Ratio from dual;
RATIO
----------
25
Now that we got kicked off Exaland, we can only use OLTP compression. Let’s do it to all four partitions:
alter table braga modify partition SYS_P61985
compress for oltp;
alter table braga modify partition SYS_P61986
compress for oltp;
alter table braga modify partition SYS_P61987
compress for oltp;
alter table braga modify partition SYS_P61988
compress for oltp;
-- Gosh I was dumm not naming the partitions 
Now rebuild them:
alter table braga move partition SYS_P61985;
alter table braga move partition SYS_P61986;
alter table braga move partition SYS_P61987;
alter table braga move partition SYS_P61988;
SQL> select sum(bytes)/1024/1024 Megs
from dba_extents
where segment_name = 'BRAGA'; 2 3
MEGS
----------
16
Still a good ratio:
SQL> select round(69/16) Ratio from dual;
RATIO
----------
4
Hope this was of some utility.
LMC
