
42 2.2 Methods for Tuning a Data Warehouse
There are a few more points to note about star transformation queries.
Even if you expect a star transformation to occur, even if you use the
STAR_TRANSFORMATION hint, the optimizer may not necessarily use
a star query. Why?
If a query accesses more than, say, 10% of a fact table’s rows, then the
optimizer automatically executes a full table scan of the fact table. A
query retrieving more than 10% of a table’s rows is automatically
faster reading the entire table, according to the optimizer.
In the past the use of a hint has only been a suggestion to the optimizer
and has not forced the optimizer to pick ...