Database Reference
In-Depth Information
Other types of queries, however, will not (and cannot) function at this point in time:
EODA@ORA12CR1> select empno, job, loc from emp where job = 'CLERK';
select empno, job, loc from emp where job = 'CLERK'
*
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/dbfile/ORA12CR1/datafile/o1_mf_p2_9hx10fqv_.dbf'
The
CLERK
data is in all of the partitions, and the fact that three of the tablespaces are offline does affect us. This is
unavoidable unless we had partitioned on
JOB
, but then we would have had the same issues with queries that needed
data by
LOC
. Anytime you need to access the data from many different
keys
, you will have this issue. Oracle will give
you the data whenever it can.
Note, however, that if the query can be answered from the index, avoiding the
TABLE ACCESS BY ROWID
, the fact
that the data is unavailable is not as meaningful:
EODA@ORA12CR1> select count(*) from emp where job = 'CLERK';
COUNT(*)
----------
4
Since Oracle didn't need the table in this case, the fact that most of the partitions were offline doesn't affect this
query (assuming the index isn't in one of the offline tablespaces of course). As this type of optimization (i.e., answer
the query using just the index) is common in an OLTP system, there will be many applications that are not affected
by the data that is offline. All we need to do now is make the offline data available as fast as possible (restore it and
recover it).
Partial Indexes
Starting with Oracle 12
c
, you can create either local or global indexes on a subset of partitions in a table. You may want
to do this if you've pre-created partitions and don't yet have data for range partitions that map to future dates—the
idea being that you'll build the index after the partitions have been loaded (at some future date).
You set up the use of a partial index by first specifying
INDEXING ON|OFF
for each partition in the table. In this next
example,
PART_1
has indexing turned on and
PART_2
has indexing turned off:
EODA@ORA12CR1> CREATE TABLE p_table (a int)
2 PARTITION BY RANGE (a)
3 (PARTITION part_1 VALUES LESS THAN(1000) INDEXING ON,
4 PARTITION part_2 VALUES LESS THAN(2000) INDEXING OFF);
Table created.
Next, a partial local index is created:
EODA@ORA12CR1> create index pi1 on p_table(a) local indexing partial;
Index created.
