Skip to Main Content
  • Questions

  • Mixing clusters and partitioned tables.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Scott.

Asked: July 31, 2003 - 2:40 pm UTC

Last updated: February 07, 2005 - 10:40 am UTC

Version: 9

Viewed 1000+ times

You Asked

Tom,

I have been looking at clustered tables to help get better performance for a key query in one of the applications I look after and I was wondering if combining partitioning and clustered tables make sense.

If I create an indexed cluster wouldn't that really be a global index? Now suppose I roll or drop a partition wouldn't that make the clustered index unusable? (if you have never done it don't worry about creating an example, I could do that myself )

If I use a hash cluster I don't think I would have the same problem, however, I don't know off hand how many hashkeys will be required for this table which is why I was thinking of a indexed cluster.

While on the topic of hash clusters, does it make sense to use the PK column as the hash function instead of letting Oracle choose a hash function?



Thanks,
Scott.

and Tom said...

you haven't tried to partition a cluster yet have you ;)

partitioning and clusters do not go together.

</code> http://docs.oracle.com/cd/B10501_01/server.920/a96524/c12parti.htm#464767 <code>






Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How can you tell :-)

A reader, August 01, 2003 - 11:14 am UTC

As you deduced, I had not tried to cluster a partitioned table yet. I was brainstorming different approaches before putting fingers to keyboard and got the idea of using a cluster based on the examples in your book.

Thanks for the info and link.

Scott.

Domain indexes?

Fabio, February 07, 2005 - 9:36 am UTC

It could be possible, theorically, to define a "partitioned domain index" over a clustered table?

Would it be a way to overcome the incompatibility between partitioning and clustering or the incompatibility is deeper (i.e. it is not just a "missing feature": there are conceptual and deep reasons for not having partitioned indexes on a cluster)?

Thanks,
Fabio

Tom Kyte
February 07, 2005 - 10:40 am UTC

they were trying to partition a clustered table -- that won't work. but the indexing won't work either, the two features are not supported together.

A reader, September 26, 2006 - 5:57 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.