Even with all the information available to it, the cost-based optimizer did have some
noticeable initial flaws. Aside from the fact that it (like all software) occasionally had
bugs, the cost-based optimizer used statistics that didn??™t provide a complete picture
of the data structures. In the previous example, the only thing the statistics tell the optimizer
about the indexes is the number of distinct values in each index. They don??™t reveal
anything about the distribution of those values. For instance, the larger index can contain
5,000 unique values, but these values can each represent two rows in the associated
table, or one index value can represent 5,001 rows while the rest of the index values represent
a single row. The selectivity of the index can vary wildly, depending on the value
used in the selection criteria of the SQL statement. Fortunately, Oracle 7.3 introduced
support for collecting histogram statistics for indexes to address this exact problem. You
could create histograms using syntax within the ANALYZE INDEX command when
yougathered statistics yourself in Oracle versions prior to Oracle Database 10g.
Pages:
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265