Partitioning - Expert Oracle Database Architecture

Database Reference

In-Depth Information

get this row. This index access will be as effective as on a nonpartitioned table and perform the same amount of I/O

to do so. It is just a simple, single index unique scan followed by “get this row by rowid.” Now, let's look at one of the

other global indexes, the one on JOB :

EODA@ORA12CR1> explain plan for select empno, job, loc from emp where job = :x;

Explained.

EODA@ORA12CR1> select * from table(dbms_xplan.display);

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | |

---------------------------------------------------------------------------------

Sure enough, we see a similar effect for the INDEX RANGE SCAN . Our indexes are used and can provide high-

speed OLTP access to the underlying data. If they were partitioned, they would have to be prefixed and enforce index

partition elimination; hence, they are scalable as well, meaning we can partition them and observe the same behavior.

In a moment, we'll look at what would happen if we used LOCAL indexes only.

Lastly, let's look at the area of availability. The Oracle documentation claims that globally partitioned indexes

make for less available data than locally partitioned indexes. I don't fully agree with this blanket characterization.

I believe that in an OLTP system they are as highly available as a locally partitioned index. Consider the following:

EODA@ORA12CR1> alter tablespace p1 offline;

Tablespace altered.

EODA@ORA12CR1> alter tablespace p2 offline;

Tablespace altered.

EODA@ORA12CR1> alter tablespace p3 offline;

Tablespace altered.

EODA@ORA12CR1> select empno, job, loc from emp where empno = 7782;

EMPNO JOB LOC

---------- --------- -------------

7782 MANAGER NEW YORK

Here, even though most of the underlying data is unavailable in the table, we can still gain access to any bit of

data available via that index. As long as the EMPNO we want is in a tablespace that is available, and our GLOBAL index

is available, our GLOBAL index works for us. On the other hand, if we had been using the highly available local index

in the preceding case, we might have been prevented from accessing the data! This is a side effect of the fact that we

partitioned on LOC but needed to query by EMPNO . We would have had to probe each local index partition and would

have failed on the index partitions that were not available.

Search WWH ::

Custom Search

Home