Database Reference
In-Depth Information
We take tablespace
P2
offline, which contains the
PART_2
partition for both the tables and indexes:
EODA@ORA12CR1> alter tablespace p2 offline;
Tablespace altered.
Taking tablespace
P2
offline will prevent Oracle from accessing those specific index partitions. It will be as if
we had suffered media failure, causing them to become unavailable. Now we'll query the table to see what index
partitions are needed by different queries. This first query is written to permit the use of the local prefixed index:
EODA@ORA12CR1> select * from partitioned_table where a = 1 and b = 1;
A B DATA
---------- ---------- --------------------
1 1 x
This query succeeded, and we can see why by reviewing the explain plan. We'll use the built-in package
DBMS_XPLAN
to see what partitions this query accesses. The
PSTART
(partition start) and
PSTOP
(partition stop) columns
in the output show us exactly what partitions this query needs to have online and available in order to succeed:
EODA@ORA12CR1> explain plan for select * from partitioned_table where a = 1 and b = 1;
Explained.
Now access
DBMS_XPLAN.DISPLAY
and instruct it to show the basic explain plan details plus partitioning
information:
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC +PARTITION'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PARTITIONED_TABLE | 1 | 1 |
| 3 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | 1 |
---------------------------------------------------------------------------------------
So, the query that uses
LOCAL_PREFIXED
succeeds. The optimizer was able to exclude
PART_2
of
LOCAL_PREFIXED
from consideration because we specified
A=1
in the query, and we can see that clearly in the plan
PSTART
and
PSTOP
are both equal to 1. Partition elimination kicked in for us. The second query fails, however:
EODA@ORA12CR1> select * from partitioned_table where b = 1;
ERROR:
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/dbfile/ORA12CR1/datafile/o1_mf_p2_9hstdql2_.dbf'
no rows selected
And using the same technique, we can see why:
EODA@ORA12CR1> explain plan for select * from partitioned_table where b = 1;
Explained.