| Star query [message #370937] |
Fri, 10 March 2000 02:06  |
Bala
Messages: 205
Registered: 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 #370964 is a reply to message #370937] |
Tue, 14 March 2000 19:13  |
rajeshwar reddy
Messages: 2
Registered: March 2000
|
Junior Member |
|
|
I think u can use hints and
select /*+ USE_MERGE(table_name) */
emp_no,
emp_name
from emp
where
i feel u can slove it by hints, if need clarfication pl contact on my mail id
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
