Playing with interval partitioning...
I create the simplest table possible and insert 3 rows - generating 3 partitions.
SQL> create table d1 (dt date)
2 partition by range (dt) interval (numtoyminterval(1,'MONTH'))
3 (PARTITION P1 VALUES LESS THAN (TO_DATE('08/01/1776', 'MM/DD/YYYY')));
Table created.
SQL> insert into d1 values (to_date('07/04/1776', 'MM/DD/YYYY'));
1 row created.
SQL> insert into d1 values (to_date('09/22/1862', 'MM/DD/YYYY'));
1 row created.
SQL> insert into d1 values (to_date('08/18/1920', 'MM/DD/YYYY'));
1 row created.
SQL> select * from d1;
DT
---------
04-JUL-76
22-SEP-62
18-AUG-20
SQL> select table_name, partition_name from user_tab_partitions where table_name = 'D1';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
D1 P1
D1 SYS_P62
D1 SYS_P63
But when I look at the partition_count in user_part_tables...
SQL> select table_name, partition_count from user_PART_TABLES where table_name = 'D1';
TABLE_NAME PARTITION_COUNT
------------------------------ ---------------
D1 1048575
That's a whole lot of partitions! Clearly that is the maximum possible partitions. It's odd that the developers at Oracle chose to store that value there rather than the actual count of partitions created. They obviously have it available. Ah, the mysteries of the Oracle.
Subscribe to:
Post Comments (Atom)
4 comments:
That's actually correct by definition.
Interval partitioning defines a starting point, followed by an "infinite" (aka 1 million) number of partitions, in the sense that all of the partitions are now "known", they just don't require a dictionary entry or segment until someone tries to put data in one.
Explanation accepted. If I want results relevant to mere mortal, I can find out how many partitions have data using
select count(*) from user_tab_partitions where table_name = 'D1';
Oracle if full of mystery and when try to learn more and more a new set of problem and interesting fact came out. Thanks for providing all the queries and explaining how to create partition. I will try them now and will create some table with partitions.
Great and useful blog. Creating content regularly is very tough. Your points are motivated me. Excellent blog after reading this I am impressed a lot.
Oracle Fusion financial
Post a Comment