Re: Oracle partitioning bug (Bug 2111373)

I've just read the bug, and I think that the example has managed to blur the issue about what constitutes correct, generic, behaviour.

The example is a table partitioned by (week, city) and built from partitions:

[1,ADE1]
[1,BNE1]
[1,CAN1]
[1,MEL1]
[2,ADE1]
[2,BNE1]
[2,CAN1]
[2,MEL1].

The query suggested was:

    SELECT count(*) FROM TEST_PART_ELIMINATION     WHERE City= 'ADE1' and Week BETWEEN 1 and 2

The theory was that the query should access only two partitions, [1,ADE1] and [2,ADE1], but actually accessed all of the first five partitions.

In fact, given the way in which partition boundaries are defined, the values (1,ADE1) and (2,ADE1) have to be in the partitions bounded by [1,BNE1] and [2,BNE1], but that is a minor detail.

The problem with claiming that only two partitions should be touched is a variation on the theme of assuming that two-column partitioning is the same as two-dimensional partitioning. You are also adding to the confusion because YOU KNOW that the values for week are integer only - Oracle does not.

Imagine (ignoring the minor error in boundaries already mentioned) that you want to change the query to:

    SELECT count(*) FROM TEST_PART_ELIMINATION     WHERE City= 'BNE1' and Week BETWEEN 1 and 2

Should Oracle examine just two partitions because you know that the answer can only be in two partitions, viz: [1,BNE1] and [2,BNE1] ? Except that there could be an answer (1.7, BNE1) which would belong in partition [2,ADE1]. So two very similar queries require a different resolution - your example is NOT a generic case. You might like to compare it with a two-column index - If your query was on a single table with a two-column index, would you expect your query with a between on the first column and an equality on the second column to scan along leaf blocks, and keep jumping to find the next chunk of the index that might match on the second column ?

(In fact, in Oracle 9 Oracle has a skip-scan on indexes which does exactly that - so your demand for the same technology to be applied to partiitoning is not unreasonable - but it's still early days).

One thought - if you have a reasonably large number of cities, you might consider composite partitioning - ranging by week, and hashing by city. With a small number of cities the hashing will not be particularly uniform, but overall the elimintaiton benefit may be rather better than the effect you get from the double-column ranging.

When I have a moment, I'll also comment on a couple of your other questions on partitions.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Vsevolod Afanassiev wrote in message
<4f7d504c.0201281542.63bc2d3f_at_posting.google.com>...


>I found a bug in Oracle partitioning (Bug 2111373 on Metalink, has

>public access), and they don't want to fix it!

>It affects all versions (I tried on 8.1.6, 8.1.7, and 9.0.1).

>While the results returned by the query are correct, there is a severe

>impact on performance. It affects queries agains tables with range

>partitioning on two columns, when BETWEEN condition is specified for

>the first

>column, "=" (equal) condition is specified for the second column,

>and access is by full partition scan (no indexes).

>Instead of scanning partitions specified in the WHERE clause,

>Oracle scans ALL partitions between the first and the last

>partitions in the WHERE clause by Partition_Position.

>

>Please see metalink for more info on this bug.

>

>Regards,

>Sev