We have a query

select *
from B
where br_id = 'COMPANY'
and id in (select id from id_list_table A)
and reasn_cd = '1'

Table B has 48 million records, table A has 52,000 records.

1. Scenario:
Table B has runstats information in ( with cards, indexes, no distribution). table A no runstats. ( -1)

do explain plan and get Total cost: 1.03042e+06 , optimizer consider 1000 rows as default for table A. Real run time 4 hours.

2. Scenario:

Table B has runstats information in ( with cards, indexes, no distribution). table A has runstats. ( 52,000 cards )

do explain plan and get Total Cost: 8.50476e+06 , even though this time total cost much lager than first scenario, the query run much faster that no runstats, only take about 40 minutes. Strange???

Question: What make db2 optimizer pick up path and decided total cost, why cost higher but real run faster?

Thanks in advance.

GY

If there are no stats, then DB2 might make the wrong decision about which access path to use. Also, the estimated cost cannot be accurate if there are no stats and are based on default assumptions about the number of rows.

It's probably not going to help you much to speculate about why DB2 does something when there are no valid statistics. I am not sure why you are not getting distribution stats. You only need to run them once after a reorg, and not every single time.

I would be curious if the following performs any better (with full runstats on both tables):

select *
from Table_B B
where br_id = 'COMPANY'
and reasn_cd = '1'
and exists (select id from id_list_table A where id.A = id.B)

When we look at the explain plan output, the DB2 optimizer give the optimized statement like you mentioned with the table join on ID column.

It give me the same performance I will say.

Thanks,

GY

Whether the Marcus' query performs better or not may depend on various other factors ....

But, it is a good practice to follow that style ... Many queries do benefit ..

Cheers
Sathyaram