Database Reference
In-Depth Information
The output shows a full table scan of
PART_2
was required, as there is no usable index with entries pointing at
data in
PART_2
. We can instruct Oracle to create index entries pointing to data in
PART_2
by rebuilding the index
partition associated with the
PART_2
partition:
EODA@ORA12CR1> alter index pi1 rebuild partition part_2;
Index altered.
Re-running the previous select query shows that the optimizer is now utilizing the local partitioned index
pointing to the
PART_2
table partition:
------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE SINGLE| | 2 | 2 |
| 2 | INDEX RANGE SCAN | PI1 | 2 | 2 |
------------------------------------------------------
In this way, partial indexes allow you to disable the index while the table partition is being loaded (increasing the
loading speed), and then later you can rebuild the partial index to make it available.
Partitioning and Performance, Revisited
Many times I hear people say, “I'm very disappointed in partitioning. We partitioned our largest table and
it went
much slower
. So much for partitioning being a performance increasing feature!”
Partitioning can do one of the following three things to overall query performance:
•
Make your queries go faster
•
Not impact the performance of your queries at all
•
Make your queries go much slower and use many times the resources as the nonpartitioned
implementation
In a data warehouse, with an understanding of the questions being asked of the data, the first bullet point is
very much achievable. Partitioning can positively impact queries that frequently full scan large database tables by
eliminating large sections of data from consideration. Suppose you have a table with 1 billion rows in it. There is a
timestamp attribute. Your query is going to retrieve one years' worth of data from this table (and it has 10 years of
data). Your query uses a full table scan to retrieve this data. Had it been partitioned by this timestamp entry—say, a
partition per month—then you could have full scanned one-tenth the data (assuming a uniform distribution of data
over the years). Partition elimination would have removed the other 90 percent of the data from consideration. Your
query would likely run faster.
Now, take a similar table in an OLTP system. You would never retrieve 10 percent of a 1 billion row table in that
type of application. Therefore, the massive increase in speed seen by the data warehouse just would not be achievable
in a transactional system. You are not doing the same sort of work, and the same possible improvements are just not
realistic. Therefore, in general, in your OLTP system the first bullet point is not achievable, and you won't be applying
partitioning predominantly for increased performance. Increased availability—absolutely. Administrative ease of
use—very much so. But in an OLTP system, I say you have to work hard to make sure you achieve the second point:
that you do not impact the performance of your queries at all, negatively or positively. Many times, your goal is to
apply partitioning without affecting query response time.
