Star Queries causing full table scans

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

    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. Received on Thu May 02 2002 - 13:43:40 CDT