Home » Developer & Programmer » Data Integration » Star Query
Star Query Mon, 20 March 2000 02:15
 Bala Messages: 205Registered: November 1999 Senior Member
Star query testing
The fact table has 1 million records. The SQL query that we ran was a join of all dimension tables with the fact table (in a star schema).
The explain plan output which was taken before creating the bitmap indexes and before analyzing tables is provided in Appendix A

The following activities were performed after that

1) Create bitmap indexes on all the Foreign key columns in the Fact table. Basically a concatenated index.
2) Create bitmap index on all Foreign key columns in the Fact table individually.
3) Analyze table compute statistics on all the dimension tables.
4) Analyze table compute statistics on all the Foreign Key columns on the Fact table.

The explain plan output taken after carrying out the above activities is given in Appendix B

Question
1) Can we conclude that the query is using the Star information by seeing the Explain statement and comparing it with the first plan?
2) Are the activities performed alright?

---------------------------------------------------------------------
Appendix A
SELECT STATEMENT Cost = 83086
SORT GROUP BY
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL FCT_CUSTOMER
INDEX UNIQUE SCAN SYS_C006882
INDEX UNIQUE SCAN SYS_C006883
INDEX UNIQUE SCAN SYS_C006884
INDEX UNIQUE SCAN SYS_C006886
INDEX UNIQUE SCAN SYS_C006881

Appendix B
SELECT STATEMENT Cost = 384397343984
NESTED LOOPS
MERGE JOIN CARTESIAN
MERGE JOIN CARTESIAN
MERGE JOIN CARTESIAN
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DIM_BRANCH_TEMP
SORT JOIN
TABLE ACCESS FULL DIM_GENDER_TEMP
SORT JOIN
TABLE ACCESS FULL DIM_GROUP_TEMP
SORT JOIN
TABLE ACCESS FULL DIM_OCC_TEMP
SORT JOIN
TABLE ACCESS FULL DIM_CITY_TEMP
TABLE ACCESS BY INDEX ROWID FCT_CUSTOMER
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX RANGE SCAN BITFCTFKCOLSMISDATE
Re: Star Query [message #366931 is a reply to message #366930] Wed, 22 March 2000 20:26
 Prashanth Subbakrishna Messages: 1Registered: March 2000 Junior Member
Have u created the indexes
on the columns of the dimension tables
that are part of the where clause in the query.
If ur explain plan shows those dimension table
indexes,u may conclude that it is a star query.
Re: Star Query [message #366932 is a reply to message #366931] Fri, 24 March 2000 05:11
 Bala Messages: 205Registered: November 1999 Senior Member
Thanks Prashanth.. I will check this out and let you know
 Previous Topic: Data Warehouse Jobs - NYC Next Topic: ODBC Error
Goto Forum:

Current Time: Fri Dec 02 06:00:12 CST 2022