After next extent change, count didn't matching
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: After next extent change, count didn't matching

  1. #1
    Join Date
    Feb 2010
    Posts
    3

    After next extent change, count didn't matching

    After next extent change, my one table become strange and sql work differently with original backup table. I will post sql and schema, can you see why this sql show 0 count instead of about 9500 count.

    This is changed sql and table: count = 0
    SELECT COUNT(*)
    FROM SDAM_ILBI A
    WHERE A.IL_DATE BETWEEN '20100201' AND '20100208'
    AND A.SABUN NOT IN (
    SELECT TM_SABUN
    FROM SKWASO_bk20100106
    GROUP BY TM_SABUN
    )

    And this is backuped original table with query : count =9634

    SELECT COUNT(*)
    FROM SDAM_ILBI A
    WHERE A.IL_DATE BETWEEN '20100201' AND '20100208'
    AND A.SABUN NOT IN (
    SELECT TM_SABUN
    FROM SKWASO
    GROUP BY TM_SABUN
    )

    I think something is wrong with reorg or next extend or index.
    Thank you.
    Here is schema. you can download and put it in DB.
    http://dl.dropbox.com/u/529936/skwaso.zip

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    no, something is wrong with your tables and or query

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Assuming same query runs agaisnt both tables most likely scenario is tables have different data.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    what is it showing when you do count(*) on the table?

    select count(*) from SKWASO_bk20100106;

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Feb 2010
    Posts
    3

    It is because null value in BK20100106

    I found out that column TM_SABUN in SKWASO_BK20100106 has null value.
    That's why count = 0 in this query.

    But the SKWASO table that has no null value in TM_SABUN resulted count = 9634. Thank for your help.

    SQL is basic function, I might study more about how to interpret SQL better.
    Xie Xie.

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    but the script you attached showing that you have inserted and comited some rows in that table ... that means davey is right ...

    anyway, glad to hear that you found the issue.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Feb 2010
    Posts
    3
    select count(*) from SKWASO_bk20100106; this count = 97.
    Something wrong was null value in TM_SABUN.
    It is not related with next extent value.
    This is not PK or Unique, my misunderstanding.
    Thank you.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    so... let me quote myself "most likely scenario is tables have different data"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width