select B.AREACODE as domain,D.ITEMTYPECODE,sum(A.SALEAMT) saleAmt
from serp01.mag_customer_sale_state_type A,
     serp01.pub_area_code B,
     serp01.pub_area_code_explode C,
    serp01.pub_item_type_code D,
     serp01.pub_item_type_explode E
where
     a.enterprisecode = e.enterprisecode
and a.enterprisecode = d.enterprisecode
and a.enterprisecode = c.enterprisecode
and a.enterprisecode = b.enterprisecode
and A.EnterpriseCode = 'sygs'

and a.ITEMTYPECODE = e.ChildItemTypeCode  
and e.parentitemtypecode = d.itemtypecode
and d.parentitemtypecode is null
and a.areacode = c.childareacode
and c.parentareacode = b.areacode
and b.parentareacode is null
and A.SALEDATE >= '2002/01/01'
明明5个表有4个连接条件，但是仍然有cartersian奇怪
group by B.AREACODE, D.ITEMTYPECODE



SELECT STATEMENT, GOAL = CHOOSE                        1385        1        150       
SORT GROUP BY                        1385        1        150       
  NESTED LOOPS                        1371        1        150       
   NESTED LOOPS                        1371        50        6700       
    MERGE JOIN CARTESIAN                        1371        993        122139       
     MERGE JOIN CARTESIAN                        6        1        49       
      TABLE ACCESS FULL        SERP01        PUB_AREA_CODE        5        1        28       
      SORT JOIN                        1        1        21       
       INDEX RANGE SCAN        SERP01        PUB_ITEM_TYPE_CODE_IDX_004        2        1        21       
     SORT JOIN                        1370        993        73482       
      TABLE ACCESS FULL        SERP01        MAG_CUSTOMER_SALE_STATE_TYPE        1365        993        73482       
    INDEX UNIQUE SCAN        SERP01        PK47                3        33       
   INDEX UNIQUE SCAN        SERP01        PK326                39        624       

呵呵

What is Star Query Optimization ?
You'll probably see an execution plan like this
Merge Join Cartesian

fullscan A
fullscan B
This is an effect of the Star Query Optimization. Star Query states that if table A and B are small, then it's more efficient to do a direct Cartesian Product (i.e. try all the combinaisons) than to do a regular Join. It's very efficient when the tables are actually small. The time to query such a join is proportional to NumberOfLine(Table A) * NumberOfLine(Table B). The only problem is that Oracle considers a table as small if it fits in the SGA. If you have a big SGA (let's say 500 MO) then any couple of table smaller than 500 MO is candidate to such query optimisation. Actually in alot of cases, you don't want such Star Query.

是主键的索引次序不对，我现在太忙，等完了，可以好好写一下

老哥你也忒不小心了