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.

10 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?

Tourism in India 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

Rainbow Training Institute said...

Awesome post. your article is really informative and helpful for me and other bloggers too
Oracle Fusion HCM Online Training

Rainbow Training Institute said...

I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.
I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

Anonymous said...

Thanks for such a great article here. I was searching for something like this for quite a long time and at last, I’ve found it on your blog. It was definitely interesting for me to read about their market situation nowadays. Well written article Thank You for Sharing with Us pmp training centers in chennai| pmp training in velachery | project management courses in chennai |pmp training in chennai | pmp training institute in chennai

Tech Leads IT said...

Thanks for sharing such a nice article.
Tech Leads It Provides Oracle Fusion SCM Training in Hyderabad
Oracle Fusion Technical Training
Oracle Fusion Financials Training
Oracle Fusion HCM Training
For more information Visit us: https://www.techleadsit.com/oracle-fusion-scm-online-training-course/

Madhan kumar said...

Hey, Wow all the posts are very informative for the people who visit this site. Good work! We also have a Website. Please feel free to visit our site. Thank you for sharing.
Be Your Own Boss! If you're looking for a change in your work prospects, then let's prepare for your career from here!!!
Self Employment | Women Development | Information Technology | Engineering Courses