Database Reference
In-Depth Information
Again, this was instantaneous; it was accomplished via simple data dictionary updates - the
WITHOUT VALIDATION
clause allowed us to accomplish that. When you use that clause, the database will trust that the data you are placing
into that partition is, in fact, valid for that partition. Adding the empty partition took very little time to process. Then,
we exchange the newly created empty partition with the full table, and the full table with the empty partition, and that
operation is performed quickly as well. The new data is online.
Looking at our indexes, however, we'll find the following:
EODA@ORA12CR1> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------- --------
PARTITIONED_IDX_LOCAL N/A
PARTITIONED_IDX_GLOBAL UNUSABLE
FY_2014_IDX VALID
FY_2016_IDX VALID
The global index is, of course, unusable after this operation. Since each index partition can point to any table
partition,
and
we just took away a partition and added a partition, that index is invalid. It has entries that point into
the partition we dropped. It has
no
entries that point into the partition we just added. Any query that would make use
of this index would fail and not execute, or, if we skip unusable indexes the query's performance would be negatively
impacted by not being able to use the index:
EODA@ORA12CR1> select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */ count(*)
2 from partitioned
3 where timestamp between to_date( '01-mar-2016', 'dd-mon-yyyy' )
4 and to_date( '31-mar-2016', 'dd-mon-yyyy' );
select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */ count(*)
*
ERROR at line 1:
ORA-01502: index 'EODA.PARTITIONED_IDX_GLOBAL' or partition of such index is in unusable state
EODA@ORA12CR1> explain plan for select count(*)
2 from partitioned
3 where timestamp between to_date( '01-mar-2016', 'dd-mon-yyyy' )
4 and to_date( '31-mar-2016', 'dd-mon-yyyy' );
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC +PARTITION'));
--------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | | |
| 2 | PARTITION RANGE SINGLE| | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITIONED | 2 | 2 |
---------------------------------------------------------------
