PARTITION_COUNT Shows Large Value 1048575 With Interval Partitioning (Doc ID 1447928.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms


Consider the scenario below:

-- create partitioned table using INTERVAL partitioning
create table t1
(
a number
)
partition by range(a) interval (100)
(
partition t1_100 values less than (100)
);

-- create another partitioned table NOT using INTERVAL partitioning
create table t2
(
a number
)
partition by range(a)
(
partition t1_100 values less than (100)
);

-- Now check the number of partitions in both tables
column interval format a10
select table_name, PARTITIONING_TYPE, STATUS, INTERVAL, PARTITION_COUNT,
(select count(*) from user_tab_partitions pts where pts.table_name =
pt.table_name) real_count
from user_part_tables pt
where table_name like 'T_'
order by table_name;

TABLE_NAME  PARTITION STATUS   INTERVAL    PARTITION_COUNT REAL_COUNT
----------- --------- -------- ----------- --------------- ----------
T1          RANGE     VALID    100                 1048575          1
T2          RANGE     VALID                              1          1

 

-------------------------------------------------------------------------------------------- | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |      |     1 |    13 |     2   (0)| 00:00:01 |       |       | |   1 |  PARTITION RANGE ALL|      |     1 |    13 |     2   (0)| 00:00:01 |     1 |1048575| |   2 |   TABLE ACCESS FULL | T1   |     1 |    13 |     2   (0)| 00:00:01 |     1 |1048575| --------------------------------------------------------------------------------------------
 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms