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.
 
Search WWH ::




Custom Search