A few basic notes on Oracle Database Administration.

Wednesday, May 26, 2010

Composite Interval Partitioning isn't as advertised.

Oracle® Database VLDB and Partitioning Guide 11g Release 1 (11.1) Part Number B32024-01 says:

Interval Partitioning

Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition.


You can create single-level interval partitioned tables as well as the following composite partitioned tables:

* Interval-range

* Interval-hash

* Interval-list

Sure, I can create these composite partitions, but the results aren't particularly useful. When I tried. Oracle spread my results nicely between the two hash subpartitions for the manually defined partition, but put everything in the same subpartition for the interval generated partition. Notice that these are identical sets of rows. The only difference is the key to force them into the manually specified partition or the generated partition. I assume that there is a metalink note on this somewhere.

I got equivalent results for interval-list composite partitioning. I won't bore the reader with the step-by-step for that test since the results are also that all rows in the generated partitions are forced into one subpartition.

Here are my results for the interval hash test:


SQL> create table interval_hash (
N number,
N2 number
)
partition by range(N) interval (2)
SUBPARTITION BY HASH (N2)
(partition p1 values less than (2)
(SUBPARTITION p_1 ,
SUBPARTITION p_2
));

Table created.

SQL> BEGIN


FOR i IN 1 .. 15 LOOP

INSERT INTO interval_hash VALUES (5, i);
INSERT INTO interval_hash VALUES (0, i);

END LOOP;
COMMIT;
END;
/

PL/SQL procedure successfully completed.


SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_HASH', granularity=>'ALL');

PL/SQL procedure successfully completed.


SQL> SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
ORDER by table_name, partition_name, subpartition_name;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
INTERVAL_HASH P1..................P_1........................... 6
INTERVAL_HASH P1..................P_2........................... 9
INTERVAL_HASH SYS_P138......SYS_SUBP137............15

(I am having tabbing problems in blogger. I hope that my added lines of dots don't confuse too much)


SQL> select * from interval_hash subpartition(p_2) order by n2;

N N2
---------- ----------
0 1
0 3
0 4
0 7
0 9
0 10
0 12
0 14
0 15

9 rows selected.

SQL> select * from interval_hash subpartition(p_1) order by n2;

N N2
---------- ----------
0 2
0 5
0 6
0 8
0 11
0 13

6 rows selected.


SQL> select * from interval_hash subpartition(SYS_SUBP137) ORDER BY N2;

N N2
---------- ----------
5 1
5 2
5 3
5 4
5 5
5 6
5 7
5 8
5 9
5 10
5 11
5 12
5 13
5 14
5 15

15 rows selected.

7 comments:

Pete Scott said...

Have you tried to use a partition template in your table create?

I think you may get better results that way :-)

Have a look at the docs - "If no subpartition template is specified, and no subpartition descriptor is supplied for a partition, then a single default subpartition is created."

With Oracle you don't need to have each partition sub-partitioned the same way.... makes for fun support

girlgeek said...

By Pete you're right! I changed the table create statement to read:
drop table interval_hash;
create table interval_hash (
N number,
N2 number
)
partition by range(N) interval (2)
SUBPARTITION BY HASH (N2) subpartitions 2
(partition p1 values less than (2)
(SUBPARTITION p_1 ,
SUBPARTITION p_2
));

and lo and behold, the results are as expected - two generated has subpartitions.

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------- ---------------------------------------------------
INTERVAL_HASH.......P1................. P_1......................6
INTERVAL_HASH.......P1..................P_2......................9
INTERVAL_HASH.......SYS_P143............SYS_SUBP141..............6
INTERVAL_HASH.......SYS_P143............SYS_SUBP142..............9

Thanks a million for the clue Pete I never would have found that detail.

sap solution manager said...

I think Interval Partitioning concept will give better results. In this post interval partitioning is explained with other types of partitioning as well. The entire concept is explained with example which gives you a clear view.

erp web application server said...

can you point to a post where I can learn about partitioning from basics?

Teena Seguin said...

Read all the related Posts:


Basic of VBScript Language for QTP

Introduction to QTP (QuickTest Professional) Part2

Introduction to QTP (QuickTest Professional) Part3

Introduction to QTP (QuickTest Professional) Part4

Basic of VBScript Language for QTP

ISTQB

ISTQB Practice Test Part 1
ISTQB Practice Test Part 2
ISTQB Practice Test Part 3
ISTQB Practice Test Part 4
ISTQB Practice Test Part 5
ISTQB Practice Test Part 6
ISTQB Practice Test Part 7
ISTQB Practice Test Part 8
ISTQB Practice Test Part 9

Anonymous said...

thanks for share..

oracle fusion said...

That is a good tip especially to those new to the blogs here but very accurate information.Very nice blog post!! Thanks for the posting that is a really neatly written blog.

Oracle Fusion financial