Notes in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Anke Heinrich" <Anke.Heinrich_at_marconi.com> wrote in message
news:bm70qg$je1hf$1_at_ID-70722.news.uni-berlin.de...
>
> Am I so wrong to think that a good select strategy for a specific
statement
> will stay good even when more data are added (presumed that size
relations
> between tables are known for the application and won't change)?
Thinking in terms of strategies for specific statements
is a good starting point - and the knowing the size relationships
between the tables, and their evolution of time puts you ahead
of the crowd. But this requires one of two conditions -
a) you ignore the SQL text, and decide the strategy
based on the business intent of the SQL text
or
b) you start with a realistic volume of realistic data.
and the growth rate is not extreme
Consider a very simplistic case:
A tablescan may be a very good strategy for a small table,
but when the table grows, an indexed access path may
become the better strategy.
Exactly the same issues appear with more realistic cases
> Even if hints are not that easy to understand, I got the impression
that
> they are the best way to keep things under control.
"Best" in terms of eliminating surprises - if you can put enough of
them in place to ensure that the optimizer can't find a way of
putting (some of) them out of context.
The optimizer operates under several misconceptions:
a) A single block read costs as much as a multiblock read
(addressed by optimizer_index_cost_adj, partly)
b) There is no cache
(addressed by optimizer_index_caching, partly)
c) Within a table, different columns are independent
variables. (addressed by dynamic sampling, partly)
and finally, the optimizer uses a rather naive algorithm to
determine the quality of an index.
Received on Sat Oct 11 2003 - 02:23:23 CDT
