Database Reference
In-Depth Information
EODA@ORA12CR1> alter table partitioned
2 exchange partition fy_2016
3 with table fy_2016
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.
Note in the following output, the
N/A
status observed for the
PARTITIONED_IDX_LOCAL
index simply means
the statuses are associated with the index
partitions
associated with that index not the index itself. It doesn't make
sense to say the locally partitioned index is valid or not; it is just a container that logically holds the index partitions
themselves:
EODA@ORA12CR1> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------- --------
PARTITIONED_IDX_LOCAL N/A
PARTITIONED_IDX_GLOBAL VALID
FY_2014_IDX VALID
FY_2016_IDX VALID
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 |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX RANGE SCAN| PARTITIONED_IDX_GLOBAL |
---------------------------------------------------
But there is a tradeoff: we are performing the logical equivalent of
DELETE
and
INSERT
operations on the global
index structures. When we drop a partition, we have to delete all of the global index entries that might be pointing to
that partition. When we did the exchange of a table with a partition, we had to delete all of the global index entries
pointing to the original data and then insert all of the new ones that we just slid in there. So the amount of work
performed by the
ALTER
commands was significantly increased.
You should expect with global index maintenance considerations that the approach without index maintenance
will consume fewer database resources and therefore perform faster but incur a measurable period of downtime. The
second approach, involving maintaining the indexes, will consume more resources and perhaps take longer overall,
but will not incur downtime. As far as the end users are concerned, their ability to work never ceased. They might
have been processing a bit slower (since we were competing with them for resources), but
they were still processing,
and they never stopped
.
The index rebuild approach will almost certainly run faster, considering both the elapsed time and the CPU time.
This fact has caused many a DBA to pause and say, “Hey, I don't want to use
UPDATE GLOBAL
INDEXES
—it's slower.”
That is too simplistic of a view, however. What you need to remember is that while the operations overall took longer,
