*** Comments by BECKER, BILL Wed Jun 25, 2003 -- 07:51:18 AM Hi Paula,
Oracle offers both a STAR and a STAR_TRANSFORMATION hint, and each results in a different optimizer path. The STAR hint builds a cartesian product across the dimension tables, then joins that virtual table to the fact table using a composite index on the fact table. STAR_TRANSFORMATION reads the fact table first using the bitmap indexes, then joins that product to the dimension tables. Bitmap indexes usually belong to the fact table, if you are trying to use STAR_TRANSFORMATION. There was a white paper floating around a while back, entitled "STAR QUERY VERSUS STAR TRANSFORMATION QUERY: WHICH TO CHOOSE". The author is Michael Janesch. I don't recall exactly where I found it, but you may be able to find it by a google search. It's a bit dated now, but as far as I know, the information is still accurate, and he provides a more detailed explanantion of the differences between the two plans. Hope this helps. ____________________________________________________________________________ *** Original message by [EMAIL PROTECTED] Guys, My understanding is that Oracle's Star Transformation Algorithm does bitmapped merges on the dimensions tables to pull out the FACTS in the FACT table rather efficiently and that you would use bitmapped indexes on your dimensions tables rather than btree. However, noted a posted on data warehousing listserve which said "Oracle Expert" was strongly recommending b-tree. I haven't used Oracle Expert very much - I don't trust it as much as the various traces, running queries and testing yourself. I guess I feel like Oracle Expert would give you rule-of-thumb stuff and have lots of inherent problems in its "recommendations" - like exactly what you feed it. I don't trust this over the testing and tracing to see what is happening with the CBO myself. Any opinions? -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
