JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 13: Using Partitioning.


Partition Elimination on Hash Joins (28th Dec 2002)

Fixed Bugs with Partitions / parallelism (6th Feb 2002)

Fixed Bug in Drop Column (6th Feb 2002)

Improvements to Partitioning (2nd Feb 2002)

Creating unusable indexes (update 3rd Aug 2001)

Skip_unusable_indexes (2nd May 2001)

List Partitions (17th April 2001)

Parallel Query and Partition Elimination (17th April 2001)

SMON and tablespaces (2nd Feb 2001)

Moving indexes online

Partitioning on 'non-existent' data

Back to Book Index




Back to Book Index


Partition Elimination on Hash Joins (28th Dec 2002)

It's hard to say whether this note should be in the Errata or Addenda, as it is a correction to an addition. On 2nd Feb 2002 I added a note about improvements to partitioning, and pointed out that 8.1.7 was now able to handle partition elimination on the fly, provided the join was a nested loop join, and not a hash join. I made this comment after spending some time trying to persuade a recalcitrant system to perform a hash join which was clearly going to be more efficient than a nested loop join - only to find that the nested loop join accessed only 7 partitions from the inner table, whilst the hash join accessed all 700 and refused to do any partition elimination.

A year on, and running, the system has suddenly acquired the ability to eliminate on the hash join. And, according to Metalink note 179518.1 Oracle has been able to do this ever since 8.1.6 - provided the statistics and data selectivity are suitable. The trick lies in run-time optimisation, and Oracle deciding that it is worth running a recursive query on the outer table to decide how many of the inner table partitions will be accessed. This can be seen in the 10128 trace, with information like the following:

JPL_DAY repartitioned using partitioning of VISIT_TX on the RANGE dimension
Partition Iterator Information:
  partition level = PARTITION
  call time = START
  order = ASCENDING
  Partition iterator for level 1:
   iterator = SQL {count= 5, text =
   index = 0
  current partition: part# = 226, subp# = 65535, abs# = 226
  current partition: part# = 227, subp# = 65535, abs# = 227
  current partition: part# = 228, subp# = 65535, abs# = 228
  current partition: part# = 229, subp# = 65535, abs# = 229
  current partition: part# = 230, subp# = 65535, abs# = 230

Note the appearance of the function tbl$or$idx$part$num() which is also used by Oracle in a validated partition exchange - this looks worthy of a little extra investigation as a possible mechanism for determining which partition an item of data is supposed to belong to. Be that as it may, we see in this trace file that Oracle first identifies the necessary partitions from the inner table by accessing the outer table. In the tests I was running, this then allowed Oracle to produce a synthetic partition-wise join between the jpl_day table and the visit_tx table.

There are a couple of hidden parameters referenced in the note


This presumably adjust the weighting and costing in some way that affects the optimizer's decision on whether to execute recursive subquery or whether to blast through the inner table without worrying about partition elimination.

It is also possible that judiciious use of the pq_distribute hint may also encourage Oracle to try to find a pseudo-partitionwise join, and therefore perform this subquery.


Top of page


Fixed bugs with Partitions / parallelism (6th feb 2002)

This note appears as a consequence of reading the bug fixes note that came with a recent upgrade to Version There are bugs in 8.1.7 that are fixed in this release, so if you are running an earlier release, be careful. Note - At the time of writing this addendum, I have not visited Metalink to read up the details of any bugs reported.

Bug 1387406 - Unnecessary partition access can occur during parallel queries if thereis partition pruning based on a nested loops join. (In other words, expected partition elimination may not occur when running parallel queries).

Bug 1548495 - Wrong results are possible from parallel execution of full or partial partition-wise hash joins on composite partitioned table. The workaroundi is to adjust a pair of parameters, and then re-parse the query. The relevant (hidden) parameters should be set as follows:

	alter session set "_full_pwise_join_enabled"=FALSE;
	alter session set "_partial_pwise_join_enabled"=FALSE;

Bug 1700705 - Unnecessary partition access is possible in parallel queries if there is a single-row subquery against the partition key. (i.e. another case of expected partition elimination not occurring).

Bug 1832457 - If a table has an index parallelized and a query is issued which generates a plan for slave sql where index-fast full scan is chosen (for the specified index) as the right hand side access path of nested loops join . Now if the index is global partitioned then we might produce incorrect results .This can also happen if the user supplies INDEX_FFS as hint. (On the other hand, I did make the point in my book that I would be hard pushed to use global indexes if I thought that partitioning was going to be of benefit, so you may never have to face this bug).

Bug 1920770 - Parallel partition elimination may not eliminate all partitions that it could. (Note - it seems likely that there are still examples where a parallel version of a query cannot eliminate partitions as effectively as the corresponding serial query)

Top of page


Fixed Bug in Drop Column (6th Feb 2002)

This note appears as a consequence of reading the bug fixes note that came with a recent upgrade to Version There are bugs in 8.1.7 that are fixed in this release, so if you are running an earlier release, be careful. Note - At the time of writing this addendum, I have not visited Metalink to read up the details of any bugs reported.

Bug 1949786 - Data may be inserted into the wrong partition of a partitioned table after a column has been dropped if the dropped column appears before the partition key column(s) in the table definition. The workaround is to ensure that all partitioned tables are created with the partition key column(s) first in the column list in the 'create table' statement. Fortunately, this is simple rule of thumb ordering is likely to be something that most people will have chosen to do 'by accident', but it is of course possible that some code generators may simply build tables with colums listed in alphabetical order.

Top of page


Improvements to Partitioning (2nd Feb 2002):

This note has been delayed a very long time, awaiting some experience and experiments. Oracle Corp. has been enhancing their partitioning strategies with every release of the database and this has, inevitably, made various sections of my book not only out of date, but completely wrong.

The main point to note is that where I say "partitioning does not work like this" in my book, you should not believe it until you have tested it. (One important exception to this guideline is the bit about two-dimensional partitioning; if you partition on two columns, this is not the same as a two-dimensional partitioning).

The most significant change is perhaps the join. I gave an example (p.284) of sales data partitioned by day, using a date column; and a reference table to convert financial weeks into a start and end date. In Oracle 8.1.5, if you joined these two tables together on the date (the partitioning column), partition elimination would not occur, Oracle was not coded to deal with such partition identification and elimination on the fly and this could be proved by setting event 10128. If you try the same experiment with Oracle 8.1.7 then Oracle will do partition elimination and can select just from those partitions that fall within the referenced week provided that the execution path is a nested loop join from the week table to the sales table. If the join turns into a hash join, then partition elimination will not take place. Again, this can be proved using event 10128 (or for the bloody-minded, by putting a couple of unwanted partitions into tablespaces that are then put offline).

This dichotomy between the nested loop and the hash join may be a bit of an issue with big systems, as such queries are often driven to be parallel queries and it is almost inevitable that such a parallel query will take the hash join option. Consequenly you may find that a serial version of a query runs much faster than a parallel version of the 'same' query, because the serial query uses a nested loop with partition elimination and the parallel query uses a hash join and loses the benefit of partition elimination.

It is one of the irritiating, but stimulating, features of our time as DBA/designers that improvements in the Oracle code often require us to rethink our strategies.

Top of page


Creating unusable indexes:

I suggested a strategy (p.281) for creating a new index on a very large partitioned table in this chapter. When I posted this as a suggestion on the 'Server Enterprise Edition' forum of Metalink, an Oracle employee responded with the following:

This is an interesting method, but it is not supported. The option to create an index unrecoverably is for internal use only and is intentionally undocumented.

You have been warned - however, if it does work, I am struggling to invent a theory of how it might go wrong after the event.

3rd August 2001 - Just as a warning about the difficulties of keeping up with the manuals (even when you work at Oracle on the Metalink support team), Connor McDonald sent me an email a little while ago pointing out that this intentionally undocumented and unsupported feature is quoted referenced in the PL/SQL Supplied Packages manual as the method for creating an index definition on a partitioned table before using the dbms_pclxutil package to build the index in parallel.

Top of page



This parameter also appears twice under the addenda to Chapter 7 (Enhanced Indexing) and Chapter 12 (Introducing Partitioning). The specific point of raising it for this chapter is to expand on my notes on creating very large partitioned indexes (p.282).

Under Oracle 8.1.5, if any partition of a partitioned index was unusable, then Oracle would still use the index, even with the session-level parameter skip_unusable_indexes was set to true. In Oracle 8.1.6+, when you set this parameter, Oracle will not even consider using such an index unless it can determine absolutely at parse-time that it will not need to touch the unusable partition, which means that the following two queries could behave differently:

	select count(*) from partitioned_table where partitioning_column = 33;
	select count(*) from partitioned_table where partitioning_column = :b1;

The first query would be able to use an index on partitioning_column provided the target partition is not the one in an unusable state. The second query will not use the index, even if the bind variable when it arrives is 33 - Oracle cannot determine at parse time that you are going to hit a safe partition.

How does this affect my sugggested strategy for building a new, large, partitioned index. After some thought, I decided it was a bit of a nuisance, but didn't really matter. The strategy is still sound - the index will still be built, but there is one plus point and one minus point to consider. On the plus side, it will no longer be possible for queries to fail because they hit an index partition that has not yet been built; on the minus side, the index will not actually be available for use until the whole thing is built.

This gives you two options: set skip_unusable_indexes = true and you will be able to change the data whilst the index is being built, but you won't be able to use the index until all partitions are built, or leave skip_unusable_indexes = false and you will be able to get some queries speeding up as soon as 'their' index partitions are built - but until all index partitions are built you will have queries (perhaps most of them, especially if you use bind variables) crashing with error

	ORA-01502: index XXX.YYY or partition of such index is in unusable state.

and you will only be able to change data in the partitions where the index partition has been built.

Top of page


List Partitions

Word has been leaking out of a useful new form of partitioning to be introduced with Oracle 9. There is a brief note under the Addenda to Views that relates the gossip I have picked up so far.

Top of page


Parallel Query and Partition elimination

I have recently come across a case where upgrading a data warehouse from 8.0.5 to resulted in a catastrophic collapse in performance and a rapid downgrade. The problem was a failure in partition elimination. Following this problem up with Oracle support, I discovered that there are apparently a number of classes of query where partition elimination stops happening when the query is switched to parallel execution.

In particular, my favourite trick - the single row sub-query (p.287)- seems to fail in almost all cases when the query goes parallel. In these cases you may be more successful with the single row in-line view. There is a fairly generic bug listed on Metalink (1700705) if you want to track the success Oracle is having with fixing this problem..

Top of page


SMON and tablespaces

I have mention here (p.276) and in chapter 8 that smon executes a check every five minutes to search for tablespaces with free space that could be coalesced. There is a paper on this web site that goes into some detail about the behaviour of smon under version 7.3 which mentions, in particular, event 10269 which can be used to stop smon from doing this scan. With the increasing appearance of locally managed tablespaces, it certainly seems to make some sense to use this event if you want to build a database with a large number (1000+ tablespaces).

Top of page


Moving indexes online:

There is a mention of rebuilding indexes online (p.282,3) in this chapter. There is a fairly serious bug with this feature, described in the addenda to chapter 7, that is not fixed until version

Top of page


Partitioning on 'non-existent' data:

One of the topics of discussion that followed on from my comments information on partitioning was the issue of partitioning on some data value that was not supplied by the user - a specifica example of this was the request to partition 'by day of week', although the column value available was actually the date.

The quick answer to this requirement is that the partitioning item must actually exist as a pure column in the table: you cannot partition on an expression such as to_number(to_char(date_col,'d')). However, there is a partial work-around to meet the requirement. It is possible to partition on a column that is maintained by before-row triggers, for example:

	create or replace trigger pt_bri
	before insert on partitioned_table
	for each row
	    :new.day_col := to_number(to_char(date_col,'d'));

It will work, but it is probably not a good idea - apart from the usual problem of array inserts turning into rwo-at-a-time processing, it also eliminates any possibility of array inserts being able to use an optimal code path to mimise the cost of identifying the target partition for an insert.

Top of page




Top of page

Back to Book Index