Discussion Locked

Comparing star and snowflake schemas in OLAP

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This week's Oracle e-newsletter helps you understand the difference between star and snowflake schemas in OLAP (online analytical processing). Did this tip teach you something you didn't know about star and snowflake schemas? Are there any other differences between the two schemas that this tip didn't cover?

If you aren't subscribed to the free Oracle e-newsletter, you can automatically subscribe to it by pasting the following URL into your browser:
http://nl.com.com/MiniFormHandler?brand=builder &subs_channel=bldr_front_door&list_id=e050&tag=fb

* Please delete any extra spaces that appear when you paste this URL into your browser.

2 replies

by b.a.

I did not find this article very helpful. It was far too brief to cover this topic even on a high level. There was no real mention on what types of problems are best solved by choosing one schema type or the other.

There was no discussion of Oracle's star transformations (it must be turned on in init.ora and that older versions of Oracle don't have it thus making star schemas potentially impractical). There was no discussion on how bitmap indexes are crucial to star schemas and how they can sometimes pose limitations on the manner in which you update the tables.

There was no discussion of how in a snowflake schema smaller normalized dimension tables often stay completely pinned in Oracle's cache making de-normalizing them into a star nothing short of a waste of disk space and a maintenance hassle.
There was no example explain plans showing how a query would execute differently on the two schema types.

I also felt the inference that properly normalized schemas inevitably lead to performance problems on large databases was far too broad a statement. The degree of parallelism and other factors determine the validity of this statement on a case-by-case basis. I could also see how these comments as phrased could be taken by the reader as applying to OLTP schemas also and in that context it is probably false.

There was no discussion on a a legitimate third choice; Oracle's materialized views can essentially provide the best of both worlds and that these options should be considered prior to creating a special OLAP schema.

by swstephe

I have a limited amount I can write in these newsletters. OLAP, Star Schemas and Snowflake Schemas are subjects that would normally require entire chapters, each, in books. I had targetted my article toward people who may have heard about "star schema", and I thought I would expand on a general description of what they were. I've been on projects, before, where someone decided on day 1 of planning that they should go with "star schema" without even knowing what it was. I thought it would be good for people who don't have time to get a gist just to be able to answer those questions or when comparing against "snowflake schema". I left off the Oracle specifics and kept to just the general concepts and how they would be implemented. I assume that people who are still interested can find more information in search pages.

Thanks for the information you provided. I'm sure it will be helpful to others who are interested in learning more about OLAP schemas.

This discussion is closed to new replies.