Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> partitioning and execution plans?

partitioning and execution plans?

From: gdas <gdas1NOgdSPAM_at_yahoo.com.invalid>
Date: 2000/08/11
Message-ID: <004e2dc6.f97dbd35@usw-ex0101-006.remarq.com>#1/1

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 Received on Fri Aug 11 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US