If the optimizer chose
to read SMALLTAB first, the Oracle database will read the 10 rows and then read
LARGETAB to find the matching rows for each of the 10 rows. If the optimizer chose
to read LARGETAB first, the database read 10,000 rows from LARGETAB and then
read SMALLTAB 10,000 times to find the matching rows. Of course, the rows in
SMALLTAB would probably be cached, reducing the impact of each probe, but you
could see a dramatic difference in performance.
Differences like this could occur with the rule-based optimizer as a result of the
ordering of the table names in the query. In the previous situation the rule-based
optimizer returned the same results for the query, but it used widely varying amounts
of resources to retrieve those results.
Cost-Based Optimization
To improve the optimization of SQL statements, Oracle introduced the cost-based
optimizer in Oracle7. As the name implies, the cost-based optimizer does more than
simply look at a set of optimization rules; instead, it selects the execution path that
requires the least number of logical I/O operations.
Pages:
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260