Re: Partition Indexes

I've remembered more of the original problem - which was first

    SQL 1 takes 100 times as long as SQL 2 then it turned out that on retesting

   SQL 1 takes 10 times as long as SQL 2 then it turned into something like

    SQL 1 takes 0.04 seconds, and SQL 2 takes     0.01 seconds.

which is perfectly reasonable if you allow that you shouldn't convert a query that takes 0.01 seconds to run in parallel because the overhead of starting 8 (in this case) PX slaves is quite large.

In your case -
What is the exact Oracle version ?
How many partitions ?
What are the partitioning columns ?
What is the index definition ?
Are there any non-null columns in the index ?

Idea 1: A query that is known to use more than one partition uses the global table statistics; a query that is known to use a specific single partition uses the statistics from that one partition. (At least, that's what I read in a manual somewhere on a recent release).
Is there something about the column value that makes it very scarce per partition, but relatively across the whole table ?

Idea 2: Obviously the performance difference comes from the fact that the PX query is using fast-full scans, therefore doing a massive amount of I/O. Is there anything about the column, value that lets Oracle discover very quickly that that column value is not relevant to particular partitions ?

Idea 3: Send me a full execution plan (set long 20000, set linesize 180 etc. use the explain plan off my web site if necessary) of the two queries by direct mail as a flat text file.

--
Regards

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

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

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








Billy Verreynne wrote in message ...
Jonathan Lewis wrote:



> Haven't we already seen this one once in the

> last 4 weeks ?  Or was it on metalink ?


First time posting on the subject from  me, Jon.

I had a look at MetaLink and only saw the following on my bug notification
list:
NO PRUNING ON CONCAT KEY PARTITIONING WITH INLIST AND RANGE ON KEYS

Not really the same problem, though it seems to point to a problem with how
local partitioned indexes are used.



> And did set autotrace statistics show that

> one plan did 58 physical reads and the other

> did 32,845 ?


Nope. I did a search on Google and could not find any related articles in
comp.database.oracle.server (using keywords such as performance, partition,
index and so on).

Plainly put Jon, this is my problem:

SQL1:
select count(*) from partition_table where partition_index_colum = 'foo'

vs.

SQL2:
select sum(x) from
(
select count(*) "X" from partition_table partition(p1)
where partition_index_colum = 'foo'
union all
select count(*) from partition_table partition(p2)
where partition_index_colum = 'foo'
.. etc..
)

SQL1 = 11+ minutes execution time

SQL2 = below 1 second execution time

Both SQL1 and SQL2's utlxpls say they are using the same local partition
index. Of course, SQL1 does a partition interation.

Does not make a lot of sense to me.

--
Billy