Oracle Database 11g added query rewrite for the OLAP Option
as well as improved solving of queries containing inline views.
How does the optimizer handle a query against a star schema? First, it finds a sales
transactions fact table (shown in Figure 10-2) with a lot more entries than the surrounding
dimension tables. This is the clue that a star schema exists. As Oracle7
evolved, the optimizer began to produce much smarter plans. The optimizer for a
standard relational database typically would have tried to join each of the dimension
tables to the fact table, one at a time. Because the fact table is usually very large,
using the fact table in multiple joins takes a lot of time.
Cartesian product joins were added to Oracle7 to first join the dimension tables,
with a subsequent single join back to the fact table in the final step. This technique
works relatively well when there are not many dimension tables (typically six or
fewer, as a rule of thumb, to keep the Cartesian product small) and when data is relatively
well populated.
Pages:
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507