Re: Partition Question (8i)

From: bdick <bdick_at_cox.net>
Date: Tue, 26 Feb 2002 16:13:10 GMT
Message-ID: <quOe8.14331$s03.408346@news2.east.cox.net>

"Alex Vilner" <alex_at_sinoma.com> wrote in message news:22e9f6e0.0202260702.d47e848_at_posting.google.com...

<snip>

> The following are the key
> elements of the table:
> - Geo. location
> - group #
> - trans. id (surrogate key)

<snip>

> If the search that users/automated processes do uses just the group #,
> would the optimizer be smart enough to chose the correct partitions,
> or would it always need the location information?

Hey Alex. What's up?

Do you have queries that specify the loc# without the group#? If so, you can swap the partition columns. Make it group#, loc#?

If you have a mix of queries, some specifying only loc# and some specifying only group#, the partition the table by the most frequently queried column. Then add a partitioned index to the other column. If your other-column index is local, then each index partition will be scanned (faster than table partition scan). If your other-column index is global, then only the pertinent index partitions are scanned (faster yet).

Global partitioned indexes may provide faster access in some situations, but are more difficult to maintain than local partitioned indexes. See chapter 11 Partitioned Tables and Indexes in the Oracle Concepts manual.

--
Later,
BEDick

Received on Tue Feb 26 2002 - 10:13:10 CST