DB2 optimizer doesn't use index

[JAMES DOAN]

I have one small table with few columns, My query is set up to match all 5
columns of the primary index. But the optimizer doesn't use all 5 columns.
The Plan-table use only two columns of the 5. What could I do to make the
optimzer recognize and match the index?

Thanks

[Ralph Ganszky]

Hi James,

if your table is really small (less than 10000 rows), you can set the
volatile flag of the table
to tell the optimizer that it should prefer index access.
If that don't help, you could also cheat the statistics of your table. If
you increase the
cardinality of the indexed columns the optimizer will use them more
probable.

Regards
Ralph

"JAMES DOAN" <jim...@attbi.com> wrote in message
news:fYED9.109239$NH2.7128@sccrnsc01...

[Mairhtin O'Feannag]

As mentioned earlier, you could cheat the stats and effect the use/non
use of an index.

But don't.

The optimizer knows what it is doing. Believe me. It knows lots of
things that are not ordinarily taken into consideration, such as the
physical layout of data/indexes etc.

TRUST THE OPTIMIZER, LUKE. :)

You *CAN* do an include of non-key columns in an index, but it sounds
like you just want to force DB2 to use the full key value. *shrug* Let
DB2 do what it does best, and that is optimize. :) Remember, these
days, you can almost *NOT* write bad SQL, since it will all be re-written
for you via the optimizer.

Mairhtin

"JAMES DOAN" <jim...@attbi.com> wrote in

news:fYED9.109239$NH2.7128@sccrnsc01:

[JAMES DOAN]

Sorry, But If you have been a programmer, you know how good you have thought
about a process. But at the same time you could also realize that there are
something those you have never thought of. I believe that this is the case
here. Optimizer matches only two colums insytead of a 5 colum index. The
query takes 30% of resources. At the same time, I believe that it should
take only 0.5% or less. After forcesing it to use the right index (a bad
index scan), it actually uses 4%-5% of the resource. I believe that it is
still a bad.

I believe that there are good programmer and desaigner, but they are still
human being....and not GOD, so the do people who coded the Optimizer..

Mairhtin O'Feannag" <irish...@rocketmail.com> wrote in message
news:Xns92D096B2EAD05...@64.164.98.6...

[boco...@ca.ibm.com]

James, you may be right. But, there is not much to go off here ... seeing the
db2exfmt output may provide some clues on why the access plan is not as you
expected.

[PM (pm3iinc-nospam)]

have you tried to unload/reload with pagefreespace=100?
that is if you don't mind about lost space.

not sure it would work but you can test it.
i know it was a technique to avoid locking problems on small tables in the
past
but maybe it can also help here.

i guess 'volatile' is better though. (if you get an index scan and the way
you want it)

saddly, you did not add any new information like the actual cardinality,
total pages, etc.
(maybe you also join with a 1 million rows table, who knows...)
db2 uses different approaches when dealing with small tables.
like if you have only 1 page containing rows and 1 page of index data,
it's not worth it to go to the index first because of the overhead.

If you could really describe your problem with at least some relevant info,
people could probably help more.

you had a tablescan, now you got an index scan that you don't like...
what do you think should be the proper type of index scan for a 20 rows
table?
we've never seen your sql nor any ddl, ... people tried to get you in the
right direction
with the info you gave.

it's sad that you don't provide more valuable feedback.
Other people can benefit from you experiences, your case, and what other
people
have to say about it.

PM

[Mairhtin O'Feannag]

"P".

Since you don't give a real name, I cannot decide if it's "brava"! or
"bravo"!.

But either way, what you say is very important. I've learned a great
deal in the past by trolling this ng. I've taken a more active role
lately, realizing (after a gentle prod by one of the local nettizens)
that I was receiving without giving.

Now, I try to help and answer where I can, even if I'm way off base and
get yelled at for being a dolt. :) Even a wrong answer can generate a
few good comments from others.

Mairhtin

"PM \(pm3iinc-nospam\)" <Pm3iinc...@sympatico.ca> wrote in
news:pjYE9.62503$e%.936797@news20.bellglobal.com:

[The Great One]

You might try different OPTIMIZE FOR clause values.

If this is in a bound program you might try using a hint. Be prepared to be
yelled at by the DBA, since these aren't that easy to administer.

If this is on UDB V7 you may try a higher optimization level.
boco...@ca.ibm.com wrote in message <3DE3156B...@ca.ibm.com>...