Re: partitioning and execution plans?

We really need an idea of the total volume of data and the variation in data volumes per account.

One starting thought - you say you have partitioned over 10 tablespaces, does this also mean 10 partitions ? You should always hash partition over power(2,N) partitions to maximise you chances of equal sized partitions.

In general, 10 partitions will lead to 4 small and 6 large partitions, where the large partitions are twice the size of the small partitions.

If you partition by hash (account_id), then any query of the form:

    account_id = {constant}
will automatically eliminate all but one partition, whether or not the account exists at all - Oracle will simply access the partition where it would be if it existed.

Your best bet for getting the best access path all the time to analyze the index for the column account_id to get histograms generated.

One thought, however, for handling historical account information - you might consider composite partitioning -  range partitioning by date, and hash subpartitioning by account_id.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

gdas wrote in message <004e2dc6.f97dbd35_at_usw-ex0101-006.remarq.com>...


>I found an interesting issue today...

>

>I have a large table that is hash partitioned across 10

>tablespaces by an account_id.  This was done because a user will

>always be viewing information for their own account.  However,

>the information for each account can have different amounts of

>data and thus varied cardinalities.

>

>I've experimenting with analyzing this table with different

>levels of sampling.  I've even done the same for all the

>individual partitions.

>

>The problem is that the execution plan is highly data

>dependent.  An execution plan for one account that works well,

>doesn't work well for another account if the amount of data is

>greatly varied.

>

>Additionally, if an account does not yet exist in this

>table...an incredibly slow query is sent out which I have to

>kill after 2 minutes.  This table is tracking history.  And new

>accounts have no history yet... accounts that do have history

>come back in anywhere from 2 - 60 seconds (due to problem above).

>

>I can logically deduce that since there is no record for the

>account in the history table yet, that there is no hash bucket

>yet assigned.  So oracle might be doing a full table scan here.

>(I do have local indexes created for the account_id).

>Also, I did an explain plan on a simple query for an account

>that did not exist yet "Select count(*) from table where

>account_id = n  "...to see what partitions were being scanned.

>The output showed a start and stop on just a single partition.

>(Why it is scanning just one partition I don't know...but I

>suppose if it scanned all 10, the query would be ten times as

>slower).

>

>For manageability, I don't want to have to create 1 partition

>per account.

>

>

>Is there a better way to gather statistics in order to account

>for the variances in cardinalities for individual accounts that

>exist within a single partition.

>

>Any ideas?

>

>

>

>-----------------------------------------------------------

>

>Got questions?  Get answers over the phone at Keen.com.

>Up to 100 minutes free!

>http://www.keen.com

>

>