Books for you

In a complex database, a query can easily involve several tables, each with several
indexes and complex selection conditions and ordering. This complexity means
that there were a lot of options, and the simple set of rules used by the rule-based
optimizer might not differentiate the choices well enough to make the best choice.
The rule-based optimizer assigned an optimization score to each potential execution
path and then took the path with the best optimization score. Another weakness in
the rule-based optimizer was resolution of optimization choices made in the event of
a ???tie??? score. When two paths presented the same optimization score, the rule-based
optimizer looked to the syntax of the SQL statement to resolve the tie. The winning
execution path was based on the order in which the tables occur in the SQL
statement.
Youcan understand the potential impact of this type of tie-breaker by looking at a
simple situation in which a small table with 10 rows, SMALLTAB, is joined to a large
table with 10,000 rows, LARGETAB, as shown in Figure 4-4.


Pages:
235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259