Hotsos Home Page

Document Properties

Title: The Cost-Based Optimizer and Partitioning Range Partitioning
Author: Gary Propeck
Get File:
Abstract: Partitioning of tables was introduced in Oracle 8, around 1997. With each upgrade Oracle has made to the RDBMS engine, there have been enhancements to the use of partitioning within the database. The original intent of this paper was to examine the use of the statistics gathered by the Oracle Cost-Based Optimizer, as the CBO calculates the cost for using partitioned tables and partitioned indexes. In the course of gathering data for this paper, it was discovered that certain statistics calculations tend to drive the CBO away from using an index for an index range scan when the table is partitioned. The observations were made using both a global non-partitioned index and a local range-partition index on a rangepartitioned table. Why does the CBO’s Cost calculation cause the optimizer to not use an index on a partitioned table?
Document type: article
Updated: Mon 20 Feb 2012 (2.2 years ago)
File size: 331,536 bytes
MD5 checksum: 2993583d51e1ba42396f0a7491a4b1e9
File type: .pdf
Authentication: registered — Registered guest (free registration)