Re: Star Queries causing full table scans

Is this Oracle 9 - the plan looks like one that doesn't happen in Oracle 8.

You are NOT doing a full scan of the fact table, examine this line:

>1000000 TABLE ACCESS BY INDEX ROWID FACT_TABLE
Note how it says that you are accessing FACT_TABLE by INDEX ROWID.

Have you got primary keys declared on the DIM_TABLE1.DIM_INDEX1 and the rest ?

You are correct in thinking that STAR_TRANSFORMATION is the hint you need, not STAR, and I think you are probably getting a modified star transformation here.

However, since you have not restricted the selection from either dimension table, Oracle has probably worked out that you need the entire fact table and that it is cheaper to carry the one required column inwards from the dimension table, rather than go back outward for every single row in the fact table.

If you want to test things bit by bit, I would

    get rid of the CUBE temporarily
    add another column to the dimensions     restricted on one of the dimension columns     select the other dimension.

I would guess that you would then see a more recognisable star_transformation plan.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Cheng-Jih Chen wrote in message ...


>Hi, I'm trying to set up a simple star schema and my query against it

>is causing a full table scan against the fact table.  Can anyone tell

>me what I'm doing wrong?

>

>The fact table contains 1000000 rows and looks like this:

>

>SQL> desc fact_table

> Name                                      Null?    Type

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

-----


> MEASURE1                                           NUMBER

> DIM1_INDEX                                         NUMBER

> DIM2_INDEX                                         NUMBER

> DIM3_INDEX                                         NUMBER

> DIM4_INDEX                                         NUMBER

> DIM5_INDEX                                         NUMBER

> DIM6_INDEX                                         NUMBER

>

>There are bitmap indexes against each of the DIM*_INDEX columns.

>

>The dimension tables look like:

>

>SQL> desc dim_table1

> Name                                      Null?    Type

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

-----


> DIM_COL1                                           VARCHAR2(4)

> DIM1_INDEX                                         NUMBER(38)

>

>The query I'm running is:

>

>select /*+ STAR_TRANSFORMATION */ dim_col1, dim_col2, sum(measure1) as

measure


>from fact_table, dim_table1, dim_table2

>where fact_table.dim1_index = dim_table1.dim1_index

>  and fact_table.dim2_index = dim_table2.dim2_index

>group by cube (dim_col1, dim_col2);

>

>We've also tried it with hit /*+ STAR */.

>

>The tkprof output looks like:

>

>

>Rows     Row Source Operation

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

>    121  SORT GROUP BY

>    400   GENERATE CUBE

>    100    SORT GROUP BY

>1000000     TABLE ACCESS BY INDEX ROWID FACT_TABLE

>1000101      NESTED LOOPS

>    100       MERGE JOIN CARTESIAN

>     10        TABLE ACCESS FULL DIM_TABLE1

>    100        BUFFER SORT

>     10         TABLE ACCESS FULL DIM_TABLE2

>1000000       BITMAP CONVERSION TO ROWIDS

>    100        BITMAP AND

>    399         BITMAP INDEX SINGLE VALUE (object id 4833)

>    300         BITMAP INDEX SINGLE VALUE (object id 4834)

>

>

>Rows     Execution Plan

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

>      0  SELECT STATEMENT   GOAL: CHOOSE

>    121   SORT (GROUP BY)

>    400    GENERATE (CUBE)

>    100     SORT (GROUP BY)

>1000000      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF

>                 'FACT_TABLE'

>1000101       NESTED LOOPS

>    100        MERGE JOIN (CARTESIAN)

>     10         TABLE ACCESS   GOAL: ANALYZED (FULL) OF

>                    'DIM_TABLE1'

>    100         BUFFER (SORT)

>     10          TABLE ACCESS   GOAL: ANALYZED (FULL) OF

>                     'DIM_TABLE2'

>1000000        BITMAP CONVERSION (TO ROWIDS)

>    100         BITMAP AND

>    399          BITMAP INDEX (SINGLE VALUE) OF 'FACT_TABLE$DIM1'

>    300          BITMAP INDEX (SINGLE VALUE) OF 'FACT_TABLE$DIM2'

>

>

>All rows in the fact table are being pulled in, which is what I though

>the star and star_transformation hints were supposed to prevent.

>

>Statistics for all tables have been computed.  Anyone know what's

>going on?  Thanks.

>