Database Reference
In-Depth Information
Now, we take one of the tablespaces offline (simulating, for example, a disk failure), thus making unavailable the
data in that partition:
EODA@ORA12CR1> alter tablespace p1 offline;
Tablespace altered.
Next, we run a query that hits every partition, and we see that this query fails:
EODA@ORA12CR1> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/u01/dbfile/ORA12CR1/datafile/o1_mf_p1_9gck8ndv_.dbf'
However, a query that does not access the offline tablespace will function as normal; Oracle will eliminate the
offline partition from consideration. I use a bind variable in this particular example just to demonstrate that even
though Oracle does not know at query optimization time which partition will be accessed, it is nonetheless able to
perform this elimination at runtime:
EODA@ORA12CR1> variable n number
EODA@ORA12CR1> exec :n := 7844;
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select * from emp where empno = :n;
EMPNO ENAME
---------- --------------------
7844 TURNER
In summary, when the optimizer can eliminate partitions from the plan, it will. This fact increases availability for
those applications that use the partition key in their queries.
Partitions also increase availability by reducing downtime. If you have a 100GB table, for example, and it is
partitioned into 50 2GB partitions, then you can recover from errors that much faster. If one of the 2GB partitions is
damaged, the time to recover is now the time it takes to restore and recover a 2GB partition, not a 100GB table. So
availability is increased in two ways:
•
Partition elimination by the optimizer means that many users may never even notice that
some of the data was unavailable.
•
Downtime is reduced in the event of an error because of the significantly reduced amount of
work that is required to recover.
Reduced Administrative Burden
The administrative burden relief is derived from the fact that performing operations on small objects is inherently
easier, faster, and less resource intensive than performing the same operation on a large object.
For example, say you have a 10GB index in your database. If you need to rebuild this index and it is not
partitioned, then you will have to rebuild the entire 10GB index as a single unit of work. While it is true that you could
rebuild the index online, it requires a huge number of resources to completely rebuild an entire 10GB index. You'll
need at least 10GB of free storage elsewhere to hold a copy of both indexes, you'll need a temporary transaction log
table to record the changes made against the base table during the time you spend rebuilding the index, and so on.
