Do have any schematics of an Oracle data warehouse and its infrastructure? Any examples of frozen, archived or
historical files and how the infrastructure should look for individual data marts? Any help with this would be greatly appreciated, or a resource site that I can look at... Entire books have been written on this topic. Generally a data warehouse will utilize a star schema. A star schema consists of centralized fact tables and outlying dimension tables the dimension tables are usually only a couple of columns while the fact tables may contain hundreds. The dimension tables relate back tom the facts through one-to-many relationships. A single warehouse can have many of these star schemas. Generally the dimension table will contain the columns you use to slice the data in the fact table with. You search on the dimension tables and then move in to the fact tables. In 9i and later releases the indexes on the fact table are bitmap indexes to allow the optimal bitmap merge resolution of the star join. Oracle also provides the concept of dimension objects. A dimension object is a description of the hierarchies inherent in the fact table and is used by Oracle to optimize execution plans. Oracle also provides for materialized views (also called summaries).
Dig deeper on Oracle data warehousing
Related Q&A from Mike Ault, Senior Oracle Consultant, Burleson Consulting
I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to...continue reading
How to find the definition or structure of a dropped table? I know the table's name but I don't know the columns and datatypes. It no longer exists.continue reading
I want to find the length of a numeric datatype field in my table. How can I find it?continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.