A few basic notes on Oracle Database Administration.

Monday, May 24, 2010

That's a whole lot of partitions!

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.

5 comments:

Connor McDonald said...

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.

girlgeek said...

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';

Anonymous said...

Nice blog

sap erp system said...

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.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.