Re: Oracle 7.3 Optimizer and Star Schema

In <316C1E0D.3D20_at_dled1.itg.ti.com> Sid Poondla <xpoondla_at_dled1.itg.ti.com> writes:
>
>How does Oracle 7.3 optimizer take advantage of Star Schema design? I
 am
>looking for technical details. Your help is greatly appreciated.
Please

Basically, Oracle 7.3 uses small table optimization. If you consider the simple star schema, this means handling the constraints (WHERE clauses) in the small tables first, then running the cross product of the smallish result sets until finally constructing the compound key you need for the fact table. There are real limitations to this approach:

1. Star schema are rarely this simple. The dimension tables are often quite large.
2. It offers no support for multiple fact table query.
3. The lack of a multi-table index means that the approach often fails, leading to table scans.

-- 
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Neil Raden - nraden_at_ix.netcom.com 
_/ Archer Decision Sciences, Inc.              
_/ Data Warehouse/Decision Support Consultants       
_/ http://www.netmar.com/~nraden 
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/