partitioning

Interval partitioning just got better

connormcdonald , , ,

Interval partitioning was a great feature when it arrived in version 11, because we no longer had to worry so much about ensuring partitions were available for new data when it arrived.  Partitions would just be created on the fly as required.  I’m not going to talk about interval partition in detail because there’s plenty of good content already out there.  But one key element for interval partitioning is that the intervals have to start from somewhere, which is why you always have to define a table with at least one partition.

 

image

 

So what if I want to drop that partition that is the conceptual “starting point”.  Well…I get problems Smile


SQL> create table sales
  2  ( tstamp       timestamp    not null,
  3    empno        number(10)   not null,
  4    ename        varchar2(10) not null,
  5    deptno       varchar2(10) not null
  6  )
  7  partition by range (tstamp)
  8  interval( numtoyminterval(1,'YEAR'))
  9  (
 10    partition p00 values less than (timestamp '2010-01-01 00:00:00')
 11  );

Table created.

SQL>
SQL> insert into sales values ( timestamp '2011-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2012-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0);

1 row created.

SQL>
SQL> col high_value format a36
SQL> col pname format a10
SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P121            2 TIMESTAMP' 2012-01-01 00:00:00'      YES
SYS_P122            3 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P123            4 TIMESTAMP' 2014-01-01 00:00:00'      YES

4 rows selected.

SQL>
SQL> alter table sales drop partition p00;
alter table sales drop partition p00
                                 *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

There is a simple workaround for that issue. If you re-issue the INTERVAL definition for the table, all of the existing partitions will be “upgraded” (or should be it downgraded…I dunno) to being range partitions. Hence we will now have “moved” the starting point, and can then drop the problem partition.


SQL> alter table sales set interval( numtoyminterval(1,'YEAR'));

Table altered.

SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P121            2 TIMESTAMP' 2012-01-01 00:00:00'      NO
SYS_P122            3 TIMESTAMP' 2013-01-01 00:00:00'      NO
SYS_P123            4 TIMESTAMP' 2014-01-01 00:00:00'      NO

4 rows selected.

SQL> alter table sales drop partition p00;

Table altered.

So that’s all pretty easy, but of course, you must now run this “re-interval” command all the time to be sure that you will always be able to drop any partition you want.

Unless of course….. you’re on 12.2 ! Let’s repeat the demo on 12.2


SQL> create table sales
  2  ( tstamp       timestamp    not null,
  3    empno        number(10)   not null,
  4    ename        varchar2(10) not null,
  5    deptno       varchar2(10) not null
  6  )
  7  partition by range (tstamp)
  8  interval( numtoyminterval(1,'YEAR'))
  9  (
 10    partition p00 values less than (timestamp '2010-01-01 00:00:00')
 11  );

Table created.

SQL>
SQL> insert into sales values ( timestamp '2011-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2012-01-01 00:00:00' , 0,0,0);

1 row created.

SQL> insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0);

1 row created.

SQL>
SQL> col high_value format a36
SQL> col pname format a10
SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
P00                 1 TIMESTAMP' 2010-01-01 00:00:00'      NO
SYS_P3415           2 TIMESTAMP' 2012-01-01 00:00:00'      YES
SYS_P3416           3 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P3417           4 TIMESTAMP' 2014-01-01 00:00:00'      YES

4 rows selected.

SQL>
SQL> alter table sales drop partition p00;

Table altered.

SQL>
SQL> select
  2    partition_name pname,
  3    partition_position pos,
  4    high_value,
  5    interval
  6  from   user_tab_partitions
  7  where  table_name = 'SALES';

PNAME             POS HIGH_VALUE                           INT
---------- ---------- ------------------------------------ ---
SYS_P3415           1 TIMESTAMP' 2012-01-01 00:00:00'      NO
SYS_P3416           2 TIMESTAMP' 2013-01-01 00:00:00'      YES
SYS_P3417           3 TIMESTAMP' 2014-01-01 00:00:00'      YES

3 rows selected.

How cool is that! We now automatically modify one of the interval partitions to being a range partition, so you’ll not get the error.

The little features are often the coolest Smile

Partition count for interval partitioned tables

connormcdonald , ,

When dealing with a RANGE partitioned table, the defined partitions dictate all of the data that can be placed into the table. For example, if I have a SALES table as per below


SQL> create table SALES
  2    ( cal_year  date,
  3      txn_id    int,
         ...
         ...
 24    )
 25  partition by range ( cal_year )
 26  (
 27    partition p_low values less than ( date '2000-01-01' ),
 28    partition p2000 values less than ( date '2001-01-01' ),
         ...
         ...
 34    partition p2016 values less than ( date '2017-01-01' )
 35  );

Table created.

then the existing partitions define a natural upper bound on the value of CAL_YEAR that I can insert into the table. For example, if I attempt to add a row for the year 2018, I get the familiar ORA-14400 that has called out many a DBA at the stroke of midnight on New Years Eve Smile


SQL> insert into SALES
  2  values ( date '2018-01-01', .... );

insert into SALES
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

As many will know, the resolution to this is either a maintenance task to ensure that there are sufficient partitions defined, or to use the INTERVAL partitioning method, which came available in 11g.


SQL> create table SALES
  2    ( cal_year  date,
  3      txn_id    int,
         ...
         ...
 23    )
 24  partition by range ( cal_year )
 25  INTERVAL( NUMTOYMINTERVAL(1,'YEAR'))
 26  (
 27    partition p_low values less than ( date '2000-01-01' ),
 28  );

Table created.

And I can observe partitions being created as required as data is added to the table


SQL> select PARTITION_NAME, HIGH_VALUE
  2  from   user_tab_partitions
  3  where  table_name = 'SALES';

PARTITION_NAME            HIGH_VALUE
------------------------- --------------------------------
P00                       TIMESTAMP' 2000-01-01 00:00:00'

SQL> insert into SALES
  2  values ( to_date('12-DEC-2011'),....);

SQL> select PARTITION_NAME, HIGH_VALUE
  2  from   user_tab_partitions
  3  where  table_name = 'SALES';

PARTITION_NAME            HIGH_VALUE
------------------------- --------------------------------
P00                       TIMESTAMP' 2000-01-01 00:00:00'
SYS_P362                  TIMESTAMP' 2012-01-01 00:00:00'

But this isn’t a post about how interval partitioning is defined, because it’s a topic that is now well understood and well detailed in the documentation and on many blogs.

I wanted to touch on a something more subtle that you might encounter when using interval partitioned tables. Let me do a query on the SALES table, which has been recreated (as INTERVAL partitioned) but is empty. Here is the execution plan when I query the table.


SQL> select * from SALES; --empty


-------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Pstart| Pstop |
-------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |       |       |
|   1 |  PARTITION RANGE ALL|       |     1 |     1 |1048575|
|   2 |   TABLE ACCESS FULL | SALES |     1 |     1 |1048575|
-------------------------------------------------------------

Wow! One million partitions ! That might seem odd, because we know that our table has been defined only with a single partition, and even that might not be instantiated yet depending on our choice of “deferred_segment_creation” parameter on the database. But the explanation is relatively simple. The moment we define a table as interval partitioned, we in effect know “in advance” the definition of every single interval that will ever follow. The starting point for the intervals is known due to the initial partition definition in the DDL, and the size/length of the interval maps out every possible future partition.

image

The maximum number of partitions is 1048575, which is then reflected in the execution plan.

You’ll see similar information when you create an index on such a table. If the index is local, and hence follows the same partitioning scheme as the underlying table, then it too has potentially 1048575 partitions all not yet in use, but known in advance. So if you look at the PARTITION_COUNT column for such an index, you’ll also see that the database will state that it has a (very) high partition count


SQL> create index sales_ix on sales ( some_col ) local;

Index created.

SQL> select TABLE_NAME,INDEX_NAME,PARTITION_COUNT from user_part_indexes;

TABLE_NAME                     INDEX_NAME                     PARTITION_COUNT
------------------------------ ------------------------------ ---------------
SALES                          SALES_IX                               1048575

1 row selected.

So if you see anything suggesting one million partitions, double check to see if you really have that many.

image

When local partitions….aren’t

connormcdonald ,

Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year Smile.

Since 11g, I can sleep easy at night by using the INTERVAL partition scheme. Here’s my table



SQL> create table t ( x date, y int )
  2  partition by range ( x )
  3  interval ( numtoyminterval(1,'MONTH'))
  4  (
  5    partition p201412 values less than ( date '2015-01-01' )
  6  );

Table created.

SQL> insert into t
  2  select date '2015-01-01'+rownum, rownum
  3  from dual
  4  connect by level <= 330;

330 rows created.

SQL> commit;

Commit complete.

SQL> select partition_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME
----------------------------------------------------------------------------
P201412
SYS_P20234
SYS_P20235
SYS_P20236
SYS_P20237
SYS_P20238
SYS_P20239
SYS_P20240
SYS_P20241
SYS_P20242
SYS_P20243
SYS_P20244

12 rows selected.

The problem is … man, do I hate those automatic names Smile. I suppose I can fix them up later, but in the meantime, I’ll create my local index on the table, but I’ll get the names right whilst I do



SQL>
SQL> create index IX on T ( y  )
  2  local
  3  (
  4      partition P201412
  5      partition P201501,
  6      partition P201502,
  7      partition P201503,
  8      partition P201504,
  9      partition P201505,
 10      partition P201506,
 11      partition P201507,
 12      partition P201508,
 13      partition P201509,
 14      partition P201510,
 15      partition P201511
 16  )
 17  ;

Index created.

So far so good… I created 12 partitions in my table, and I explicitly listed 12 partitions in my index. But you need to be careful with such an approach, because if you’ve got that script in (say) your source control system, then even though you’ve specified a LOCAL index, you have also (perhaps unknowingly) set of limit of 12 partitions on the index should be re-run that script. So if I was to drop that index and recreate with the unchanged script (or for example, you’ve done a datapump extraction of DDL etc), then you might get yourself into a jam if the table data changes.



SQL>
SQL>
SQL> drop index ix;

Index dropped.

SQL>
SQL> insert into t
  2  values (sysdate,2000);

1 row created.

SQL>
SQL>
SQL> create index IX on T ( y  )
  2  local
  3  (
  4      partition P201412
  5      partition P201501,
  6      partition P201502,
  7      partition P201503,
  8      partition P201504,
  9      partition P201505,
 10      partition P201506,
 11      partition P201507,
 12      partition P201508,
 13      partition P201509,
 14      partition P201510,
 15      partition P201511
 16  )
 17  ;
create index IX on T ( y  )
                   *
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Now my same DDL does not work, because there are 13 table partitions and I only listed 12 index partitions. The easy workaround is not to list the partitions at all.


SQL>
SQL> create index IX on T ( y  )
  2  local ;

Index created.

SQL>
SQL>

and perhaps have an renaming routine that can be applied after the fact.

A 12.2 treat for the festive season

connormcdonald , , , ,

We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning data is a significant restructure of the data, which thus incurs a large outage and the accompanying planning and coordination.

Unless you’re on 12.2.

Here’s a demo where we can take an existing table and

  • convert it to partitioned on a range scheme, also using intervals so we don’t need to worry about ongoing range creation maintenance, and
  • take one of the existing indexes, and convert it to a local index to mirror the table partitioning scheme, and
  • take the other existing index, and globally partition it.

Oh…and of course, we will do the whole thing online without service disruption

Oh…and of course, we will do it with a single SQL command.

Ab…So….Lute….Ly awesome !



SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 22 09:53:37 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Dec 19 2016 13:38:54 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production


SQL> create table T as
  2  select d.*
  3  from dba_Objects d,
  4       ( select 1 from dual
  5         connect by level <= 20 )   6  where d.object_id is not null; Table created. SQL> create index IX on t ( object_id );

Index created.

SQL> create index IX2 on t ( created, object_name );

Index created.

SQL> alter table T modify
  2  partition by range (object_id) interval (10000)
  3  (
  4    partition p1 values less than (20000)
  5  ) online
  6  update indexes
  7  ( ix  local,
  8    ix2 global partition by range (created)
  9    (
 10     partition ix2_p1 values less than (date '2016-08-01'),
 11     partition ix2_p2 values less than (maxvalue)
 12   )
 13  );

Table altered.

SQL> select partition_name
  2  from   user_tab_partitions
  3  where  table_name = 'T';

PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P1
SYS_P1145
SYS_P1146
SYS_P1147
SYS_P1148
SYS_P1149
SYS_P1150

7 rows selected.

SQL> select index_name, partition_name
  2  from   user_ind_partitions
  3  where  index_name like 'IX%';

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
IX                             P1
IX                             SYS_P1145
IX                             SYS_P1146
IX                             SYS_P1147
IX                             SYS_P1148
IX                             SYS_P1149
IX                             SYS_P1150
IX2                            IX2_P1
IX2                            IX2_P2

9 rows selected.

SQL>

Exceeding 1 million partitions

connormcdonald ,

In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions.  As we saw, the partition numbers are preordained based on the low boundary and the interval size.

That also creates an interesting scenario that can catch people out – you might exceed the allowable number of partitions, with an empty table !



SQL> create table t ( x int )
  2  partition by range(x) interval(1)
  3  ( partition p0 values less than (1) );

Table created.

SQL> insert into t values (2000000);
insert into t values (2000000)
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

A value 2,000,000 is attempting to instantiate the “2,000,000th partition” even though all the preceeding partitions do not exist, which exceeds limit for partitions on a table.

So be careful when choosing your interval sizes.

Pending statistics and partition queries

connormcdonald ,

This issue came through from an AskTom question, that turned out to be a known bug. In my talks on optimizer statistics, I’ve often mentioned the benefit of using pending statistics as a risk mitigation facility, and since this bug involved pending statistics, I thought I would bring it to your attention.  The issue occurs when optimizing a query that accesses a single partition via pending statistics.



SQL> create table t
  2  partition by range(x)(
  3     partition p1 values less than (2),
  4     partition p2 values less than (3),
  5     partition p3 values less than (4),
  6     partition p4 values less than (5),
  7     partition p5 values less than (6) )
  8  as
  9  select a.*, mod(rownum,5)+1 x
 10  from all_objects a
 11  where rownum <=10;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |   172 |     5   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |   172 |     5   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    |     2 |   172 |     5   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=2)

So far so good. The table is tiny (only 10 rows, and 2 rows per partition) and hence the optimizer has got this estimate spot on. Now we will

  • inflate one of the partitions with another ~100,000 rows.
  • turn off the publishing of statistics
  • gather a set of pending statistics with the new data

SQL>
SQL> set autotrace off
SQL>
SQL> insert into t
  2  select a.*, 2 x
  3  from all_objects a;

96983 rows created.

SQL>
SQL> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
----------------------------------------------------------------------------------
TRUE

1 row selected.

SQL>
SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

SQL>
SQL> show parameter optimizer_use_pend

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
SQL> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
-------------------------------------------------------------------------------
FALSE

1 row selected.

SQL>
SQL> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         2         19 12-OCT-16

1 row selected.

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         2         19 12-OCT-16

1 row selected.

SQL>
SQL> select num_rows,blocks
  2  from user_tab_pending_stats
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS
---------- ----------
     96985       2032

1 row selected.

At this point, optimization should be unchanged, because we have only collected pending statistics – and we can see that this is the case


SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |   172 |     5   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |   172 |     5   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    |     2 |   172 |     5   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=2)

Once again, so far so good. Now we will change our session so that it picks up the pending statistics


SQL> alter session set optimizer_use_pending_statistics=true;

Session altered.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |   172 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |   172 |     7   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    |     2 |   172 |     7   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=2)

And….. ker-splat Sad smile. We still didn’t pick up the pending statistics. This is the known issue, and it occurs when accessing pending statistics for a single partition. If we go ahead and publish the statistics, then we’ll see the correct numbers come into the optimizer consideration.


SQL> exec dbms_stats.PUBLISH_PENDING_STATS(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      | 96976 |    10M|   556   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      | 96976 |    10M|   556   (1)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T    | 96976 |    10M|   556   (1)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=2)

SQL>

So this is just something to be aware of if you are using pending statistics and partitioned tables.

And there is an easy way to fix this – just move to our Exadata Cloud Express, where the issue is no longer present !



SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 14:01:26 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 11 2016 10:58:30 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production


SQL> create table t
  2  partition by range(x)(
  3     partition p1 values less than (2),
  4     partition p2 values less than (3),
  5     partition p3 values less than (4),
  6     partition p4 values less than (5),
  7     partition p5 values less than (6) )
  8  as
  9  select a.*, mod(rownum,5)+1 x
 10  from all_objects a
 11  where rownum <=10;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     2 |   214 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE    |      |     2 |   214 |     2   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |     2 |   214 |     2   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("X"=2)
       filter("X"=2)

SQL>
SQL> set autotrace off
SQL>
SQL> insert into t
  2  select a.*, 2 x
  3  from all_objects a;

62286 rows created.

SQL>
SQL> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
------------------------------------------------------------------------
TRUE

SQL>
SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','FALSE');

PL/SQL procedure successfully completed.

SQL>
SQL> show parameter optimizer_use_pend

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
SQL>
SQL> select dbms_stats.get_prefs('PUBLISH',user,'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH',USER,'T')
--------------------------------------------------------------------------------
FALSE

SQL>
SQL> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         2          1 12-OCT-16

SQL>
SQL> --exec dbms_stats.gather_table_stats(user,'T',partname=>'P2');
SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows,blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS LAST_ANAL
---------- ---------- ---------
         2          1 12-OCT-16

SQL>
SQL> select num_rows,blocks
  2  from user_tab_pending_stats
  3  where table_name ='T'
  4  and partition_name ='P2';

  NUM_ROWS     BLOCKS
---------- ----------
     62288       1273

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     2 |   214 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE    |      |     2 |   214 |     2   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |     2 |   214 |     2   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("X"=2)
       filter("X"=2)

SQL>
SQL> alter session set optimizer_use_pending_statistics=true;

Session altered.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      | 62282 |  8271K|   348   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE    |      | 62282 |  8271K|   348   (1)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    | 62282 |  8271K|   348   (1)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("X"=2)
       filter("X"=2)

See ? The cloud fixes everything Smile

The non-existent partition

connormcdonald ,

Things get a little interesting in the data dictionary when it comes to interval partitions.  Consider the following example:



SQL> create table t
  2  partition by range(x) interval(1)
  3  ( partition p0 values less than (1) )
  4  as
  5  select rownum x from dual connect by level <= 5; Table created. SQL> select count(*)
  2  from user_tab_partitions
  3  where table_name = 'T';

  COUNT(*)
----------
         6

So our table has 6 partitions. Let’s now check the execution plan for a sample query


SQL> set autotrace traceonly explain
SQL> select * from t where x = 1235;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     3 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |     3 |     4   (0)| 00:00:01 |  1236 |  1236 |
|*  2 |   TABLE ACCESS FULL    | T    |     1 |     3 |     4   (0)| 00:00:01 |  1236 |  1236 |
-----------------------------------------------------------------------------------------------

Well that seems quite odd. If there are only 6 partitions, how come the optimizer suggests we will jump to partition 1236 ?!?!

This is explained by how interval partitions work. With a range partition regime, each partition has a lower bound defined by that of the previous partition. With interval partitions, things are different. In effect, all of the partitions are immediately known at table creation time, because the interval defines all possible partitions that could ever be. They just come into “existence” until required to hold data. Hence partition 1236 is the partition that would come into existence, should the value x=1235 be inserted into the table.

This can get a bit tricky when we actually put that value into the table.


SQL> insert into t values (1235);

1 row created.

So partition 1236 is now in existence right ? Let’s look at our partitions in USER_TAB_PARTITIONS


SQL> select partition_position, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'T';

PARTITION_POSITION PARTITION_NAME                 HIGH_VALUE
------------------ ------------------------------ ----------
                 1 P0                             1
                 2 SYS_P18027                     2
                 3 SYS_P18028                     3
                 4 SYS_P18029                     4
                 5 SYS_P18030                     5
                 6 SYS_P18031                     6
                 7 SYS_P18032                     1236

7 rows selected.

We can see the new partition SYS_P18032 with the boundary to hold our just inserted row, but why is it partition 7 not partition 1236 ? This is possibly why the column is called PARTITION_POSITION not PARTITION_NUMBER. The partition is the 7th “instantiated” partition in our table, which for an interval partitioning scheme is not necessarily the same as the partition number in the table. In fact, simply inserting a new row changes the position for this partition.


SQL> select partition_position, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'T';

PARTITION_POSITION PARTITION_NAME                 HIGH_VALUE
------------------ ------------------------------ ----------
                 1 P0                             1
                 2 SYS_P18027                     2
                 3 SYS_P18028                     3
                 4 SYS_P18029                     4
                 5 SYS_P18030                     5
                 6 SYS_P18031                     6
                 7 SYS_P18033                     601
                 8 SYS_P18032                     1236

8 rows selected.

The PARTITION_POSITION is dynamically assigned. A quick look at the definition for the xxx_TAB_PARTITIONS view shows a simple analytic function to assign the value:


row_number() over (partition by u.name, o.name order by tp.part#)

I have a hypothesis for this. I recall back in Oracle 8, dropping or splitting partitions was very slow, and (I think) that the PARTITION_POSITION was a simple column attribute. Hence when you dropped a partition, internally every row had to be updated (incremented or decremented) to make the PARTITION_POSITION correct. So replacing it with an analytic is an obvious choice, and one made before interval partitioning existed. I stress – this all might be fiction, it is just my recollection of it.

So take care – the partition number you see in an execution plan might not map exactly to the partition position values you see in the data dictionary