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

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Tue, 30 Oct 2007 03:17:45 -0700
Message-ID: <1193739465.370652.161440@d55g2000hsg.googlegroups.com>

> Can you post the execution plan?

OK, here is the full query and execution plan - I re-gathered stats on the tables with method_opt => 'FOR ALL COLUMNS SIZE 1'

SQL> explain plan for
SELECT
--count(*)
cpd.customer_ref

   FROM custproductdetails cpd, ACCOUNT a    WHERE cpd.account_num = a.account_num    AND cpd.customer_ref = a.customer_ref    AND a.currency_code = 'GBP'
   AND a.invoicing_co_id = 1 2 3 4 5 6 7 8 9 ;

Explained.

SQL> / Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

---

---

| Id  | Operation            |  Name                   | Rows  | Bytes

| Cost | TQ |IN-OUT| PQ Distrib |

---

|   0 | SELECT STATEMENT     |                         | 86620 |
4652K|  1326 |        |      |            |
|   1 |  NESTED LOOPS        |                         | 86620 |
4652K|  1326 | 89,01  | P->S | QC (RAND)  |
|*  2 |   TABLE ACCESS FULL  | ACCOUNT                 |   855 | 27360
|    43 | 89,00  | S->P | RND-ROBIN  |
|*  3 |   INDEX RANGE SCAN   | CUSTPRODUCTDETAILS_PRF  |   101 |  2323
|     5 | 89,01  | PCWP |            |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - filter("A"."CURRENCY_CODE"='GBP' AND "A"."INVOICING_CO_ID"=1)
   3 - access("CPD"."CUSTOMER_REF"="A"."CUSTOMER_REF" AND
"CPD"."ACCOUNT_NUM"="A"."ACCOUNT_NUM")

Note: cpu costing is off

In this case Oracle is guessing 86K rows (really there will be ~ 60Million for this query), which means its choosing a bad plan here - probably should full scan both tables and not index range scan the big custProductDetails table. Received on Tue Oct 30 2007 - 05:17:45 CDT