Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Partitioning

• Partitioning allows applications to take advantage of "rolling window" data operations (refer to figure 2). Rolling windows allow administrators to roll off (and un-plug data using Oracle’s transportable tablespace feature) that are no longer needed. For example, a DBA may roll off the data in the tablespace containing last April’s data as they add this year’s data for April. If the data is ever needed again, administrators are able to pull the data from tape and plug the data back into the database using the transportable tablespace.

Figure 2: Rolling windows data operations.

• Increases performance - Partitioning allows you to distribute data and balance the I/O load across several devices. The Oracle optimizer is partition aware and will create query plans that access only those partitions and subpartitions needed to satisfy the query's request (partition pruning). Partition pruning is critical in providing quick access to data that is logically grouped together (i.e. date, customer id, etc.).

Partition pruning (see Figure 3) allows administrators to create large data stores and still provide fast access to the data. There is no difference in query performance between a 20 GIG database and a 200 GIG database if the optimizer prunes the data to create access paths to only those partitions required to solve the query. Partitioned tablespaces also increase the performance of bulk data loads. Oracle’s SQL*Loader supports concurrent loading of individual partitions and entire partitioned tables.
 

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.