Re: CBO rows estimate way out in 2 table join - what to do?



Hi Stephen,

Do you have 101 distinct values in whatever column(s) is/are indexed
by the CUSTPRODUCTDETAILS_PRF index? What is your setting for
cursor_sharing?

It looks like the stats suggest that there are only 101 matches in the
customerproductdetails table for whatever rows in account have a
currency_code of GBP and an invoicing_co_id of 1. Does that sound
like the right number?

Out of the 10K rows, are there 855 rows in account with the query
parameters you have?

Have you run a 10053 trace (also detailed in Jonathans book)?
Although cryptic, I usually find the cardinality estimates so
important in joins can be extracted from that trace file without a
*ton* of effort.

HTH,

Steve

Cursor sharing is off on this database.

The account table has 10K rows - those with currency code GBP and
Invoicing_co_id = 1 makes up about 9000 or more of those rows (very
skewed data, histograms on these two column on account did help things
somewhat).

In the CustProductDetails table, each of the ~70M rows must have a
matching account row, meaning if you join account and
CustProductDetails the result will be about 70M rows. In this case we
can expect it to be nine tenths of that give or take.

I was hunting through the 10053 trace file, and was able to extract
the numbers (and formulae) that Oracle was using to get these
estimates, so I understand why it is doing what it is doing - its
figuring out how to make it do something else without putting an
'evil' hint in there that has me stumped!


.

Relevant Pages

  • Scraping data off an application window and processing it
    ... We have a need to scrap (extract) data supplied only to ... Read an input text file one by one with the account ... Wait for a response and check for any error messages ...
    (microsoft.public.windowsxp.general)
  • Re: Append/Update
    ... extract) to a 2nd table (historical backup). ... The typical customer name, account number, account type, balances and ... Because I need the history, I will NOT be removing the 1,000 ...
    (microsoft.public.access.queries)
  • Re: Append/Update
    ... I am wondering if there is a simple way to backup one table (a monthly ... Each month I get an extract containing customer account information. ... Because I need the history, I will NOT be removing the 1,000 ...
    (microsoft.public.access.queries)
  • Re: Append/Update
    ... extract) to a 2nd table. ... The typical customer name, account number, account type, balances and ... Because I need the history, I will NOT be removing the 1,000 ... find to do it is to first to an append query, then do an update query, ...
    (microsoft.public.access.queries)
  • CBO rows estimate way out in 2 table join - what to do?
    ... I have two tables - account and custProductDetails. ... I know that my query with filters will return close to 10K rows on the ... Using the formula in Cost Base Oracle, ...
    (comp.databases.oracle.server)