Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    55

    Unanswered: explain plan cost and real run ?

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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)

  3. #3
    Join Date
    May 2002
    Posts
    55
    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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

    Originally posted by guyang2000
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •