Using the
index values to select the desired rows involves less I/O and is therefore more efficient
than retrieving all the data from the table and then imposing the selection
conditions.
Another factor in determining the optimal query execution plan is whether there is
an ORDER BY condition in the query that can be automatically implemented by the
presorted index. Alternatively, if the table is small enough, the optimizer may decide
to simply read all the blocks of the table and bypass the index since it estimates the
cost of the index I/O plus the table I/O to be higher than just the table I/O.
The query optimizer has to make some key decisions even with a query on a single
table. When a more involved query is submitted, such as one involving many tables
that must be joined together efficiently or one that has complex selection criteria and
multiple levels of sorting, the query optimizer has a much more complex task.
Prior to Oracle Database 10g, you could choose between two different Oracle query
optimizers, a rule-based optimizer and a cost-based optimizer; these are described in
the following sections.
Pages:
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257