"Praveen_db2" <er***********@hotmail.com> wrote in message
news:ef******************************@localhost.ta lkaboutdatabases.com...
Dear All
Db2 version: 8.1
OS: Windows
I have 2 questions:
1) What is the optimizer which db2 uses, rule based or cost based? If any
one can clear out the difference between the two it will be a great help.
DB2 uses a cost based optimizer. That means that DB2 uses statistical data
about the tables, indexes, columns, tablespaces, etc that are captured when
runstats command is run, and information about the configuration (including
disk characteristics speed defined for the tablespace), number of CPU's CPU
speed, size of bufferpools (memory), etc, and then calculates a "relative"
cost estimate of various access path alternatives, and chooses the lowest
cost access path.
Actually, DB2 has a parameter called optimization level that determines how
long DB2 will spend trying calculate all of the various options. The value
ranges from 1-9, and 5 is the default (some levels are not currently
defined, such as 6 or 8). Optimization level 5 is usually good for most SQL
statements, but some very complex decision support queries can benefit from
optimization level of 7.
There are variations on the "rule based" method, but basically it attempts
to figure out the access path based on certain heuristics, and sometimes
explicit hints or instructions by the programmer to use a particular access
path.
I doubt that any database is 100% cost based nor 100 rule based, just like
there are no pure relational databases. But it is safe to say that DB2 is
probably relies more on cost based optimization than other database
products.
