Because these queries are typical
in a data warehouse, the recognition of the star schema by Oracle??™s cost-based optimizer
can deliver enormous performance benefits.
Figure 10-2. Typical star schema
Product
Channel Time
Geography
Fact Table
Sales
Transactions
Category
Type
Brand
Model
Manufacturer
Distributor
Store Chain
Store
Region
District
State
City
Year
Quarter
Month
Week
Day
230 | Chapter 10: Oracle Data Warehousing and Business Intelligence
Query Optimization
Oracle first provided the ability to recognize a star schema in the query optimizer in
Oracle7 and has focused on making its cost-based query optimizer smarter in
response to business intelligence queries in subsequent database releases. Further
improving optimizer prediction accuracy, since Oracle Database 10g, optimizer
predictions are compared to actual runtime performance, and any errors are subsequently
corrected automatically. The optimizer also can provide query rewrite
transparently to summary levels commonly deployed with star schema through
materialized views.
Pages:
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506