Oracle Scratchpad

November 25, 2009


Filed under: Oracle,Partitioning — Jonathan Lewis @ 7:10 pm GMT Nov 25,2009

In one of those little coincidences that seem to happen so often a question came up on the server news group that has an answer in a blog note I’d written just a few of days earlier . The question was simply asking for a way of counting the number of rows in each partition of a partitioned table.


November 21, 2009

ora_hash function

Filed under: Oracle,Partitioning,Performance,Troubleshooting — Jonathan Lewis @ 10:59 am GMT Nov 21,2009

A few weeks ago I wrote a note demonstrating the way in which Oracle’s strategy for hash partitioning is engineered to give an even data distribution when the number of partitions is a power of two. In one of the comments, Christo Kutrovsky pre-empted my planned follow-up by mentioning the hashing function ora_hash() that appeared in 10g.


September 21, 2009

Hash Partitions

Filed under: Infrastructure,Partitioning,Performance — Jonathan Lewis @ 5:57 pm GMT Sep 21,2009

I made a throwaway comment in a recent posting about using powers of two for the number of partitions when using hash partitioning. The article in question was talking about globally partitioned indexes, but the “power of 2” principle was first associated with tables.

Here’s a simple demonstration of hash partitioning in action demonstrating why Oracle adopted this “power of 2” rule. We start by creating a table that doesn’t obey the rule – with six partitions – and collect stats on it to see how many rows go into each partition:

December 19, 2008


Filed under: Indexing,Infrastructure,Partitioning,Performance — Jonathan Lewis @ 10:22 pm GMT Dec 19,2008

I wrote a short reply this morning to a question from someone on the OTN forum who wanted a little help with indexing strategies after partitioning one of his larger tables.

The comments I made there capture the key points you need to consider when comparing the costs and benefits of locally partitioned indexes with those of global, or globally partitioned indexes – so I thought I’d post a pointer to the thread.

August 20, 2007

Intelligent Stats

Filed under: CBO,Partitioning,trace files,Troubleshooting — Jonathan Lewis @ 5:43 pm GMT Aug 20,2007

Experiences like this one [Ed: Nov 2008 – the blog has become private since I wrote this note]are always worth reading about to remind yourself what you can do with the dbms_stats package when it’s really necessary.

And while I’m pointing to other URLs, here’s another one worth knowing about – event “Cursor: pin S wait on X”.  It’s not surprising to see this wait event occasionally in a busy 10g system, but if you’re losing a significant amount of time, it could be a bug.

May 8, 2007

More of a hash

Filed under: Partitioning,Performance,Tuning — Jonathan Lewis @ 8:46 pm GMT May 8,2007

No matter how simple a topic you pick, a few minutes thought invariably allow you to conjure up some new anomalies that could appear in the right (or possibly wrong) circumstances.

Yesterday I made a few comments about hash partitioning and performance. Today I suddenly realised that global indexes on hash-partitioned tables could exhibit an unfortunate behaviour pattern that would make them pretty useless – unless hacked or hinted. Consider the following table: (more…)

May 7, 2007

Hash Partitions

Filed under: Infrastructure,Partitioning,Tuning — Jonathan Lewis @ 9:06 pm GMT May 7,2007

A question recently appeared on an article I wrote about partitioning a few months ago:

We are planning to create 8 HASH partitions. Looking only at PERFORMANCE would be there be any improvements, if we go for 16 or 32 partitions (maintainance and availability is not a problem in our case). There are only 2 indexes on our 350M table – one is LOCAL, another non-partitioned index.


January 29, 2007

Small Partitions

Filed under: Infrastructure,Partitioning,Performance — Jonathan Lewis @ 8:09 pm GMT Jan 29,2007

Here’s a little example of how technology catches you out.

You have a table of 1,000,000 blocks in your system and from time to time it is necessary to run a tablescan through it. It’s not nice, but it only happens once a day, and it’s not worth building an index to create an alternative access path. (more…)

January 2, 2007

Partition Count

Filed under: Partitioning,Performance — Jonathan Lewis @ 11:28 am GMT Jan 2,2007

A little while ago, I received the following question by email:

What is the optimum level of partitioning in Oracle 10g, as in what is an efficient number of partitions that you can have in a single table? What are the advantages and disadvantages of having more than 3,000 partitions in a table?

These are actually questions I addressed seven years ago in my first book. The answers you might come up with for any specific occasion may vary, but the analysis does not change.


December 28, 2006

Parallel Execution

Filed under: Infrastructure,Oracle,Parallel Execution,Partitioning,Performance — Jonathan Lewis @ 7:21 pm GMT Dec 28,2006

Looking back at some of my previous posts I was reminded how easy it is to overlook one important feature when trying to comment on another.  In particular, in this case, a short posting on indexed access paths omitted any mention of parallel execution until a comment from David Aldridge jogged my memory. So here’s an important thought about parallel execution.


December 10, 2006

Drop parent partition

Filed under: Infrastructure,Partitioning — Jonathan Lewis @ 9:01 pm GMT Dec 10,2006

Here’s a dirty trick for those of you who are having problems with “partition0-wise” referential integrity on partitioned tables.

If you’ve created a pair of partitioned tables, parent and child say, with referential integrity declared between them, then you’ve probably discovered that you have to set the foreign key constraint on the child table to novalidate if you want to use “partition exchange loading” (as Oracle now calls it) to get data into the child table.

If you’ve got  to the point where you decide to drop a pair of old partitions, you may have tried the following:

November 16, 2006

11g benefits

Filed under: CBO,Infrastructure,Partitioning,Statistics — Jonathan Lewis @ 8:22 am GMT Nov 16,2006

Posting from the UKOUG (UK Oracle User Group) conference. A couple of useful details from Tom Kyte’s technical keynote on “Things which might be in 11g but we’re not making any promises and you can’t hold us to it”.

The optimizer will be extended to allow us to collect some statistical information about correlated columns. This should help the optimizer to deal with combining predicates like: “Orders made in the last two weeks” with “Orders that have not yet been delivered” – if they’re recent orders, they’re more likely to be undelivered. I’m looking forward to seeing how far the Optimizer team has got with handling this rather difficult problem.

Partition handling: one of the current irritations for partitioning is that you have to disable referential integrity between partitioned tables if you want to drop old partitions. (Drop child partition Jan2001, followed by an attempt to drop the “obviously matching” parent partition Jan2001 currently results in Oracle error “ORA-02266: unique/primary keys in table referenced by enabled foreign keys”). 11g will give us the ability to declare that the partitioning of the child table is dependent on the partitioning of the parent table, and therefore guaranteed to be in-synch with the parent table. Apart from handling the drop partition problem, this should also help to avoid accidents that manage to disable partition-wise joins.

October 30, 2006

Non-execution plans

Filed under: CBO,Execution plans,Partitioning — Jonathan Lewis @ 4:15 pm GMT Oct 30,2006

It’s a funny feature of execution plans that they don’t need to execute in their entirety. There are many reasons why this can be so, but the underlying reason comes from the strategy of sharing SQL.

In an ideal system, optimization is done once per unique SQL statement, even when the statement involves bind variables. This means the optimizer has to find a single execution path that will work even when the bound values change.

As a consequence, the optimizer can produce a plan that gives the run-time engine some leeway in bypassing redundant stages of execution. (Hence the discusion in the article on my website: When 2 + 2 = 5).


« Previous Page

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 5,954 other followers