Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    36

    Unanswered: index on one columntable

    a table XYZ with single column of datatype char (3) is involved with join with another table that has a indexed predicate. the table XYX has no index defined on it, and the explain statemen resulted in tablespace scan and would like to know if it is advisable to create unique index on the one column table so that Index space scan is taken. pls advice.
    Rgds
    Roger

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It may be beneficial because index entries are sorted and, thus, DB2 can narrow down the search scope - which it can't do in a table scan, typically. However, if the table only contains a few rows, then it won't give you much benefit (if any).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2003
    Posts
    36
    yes I agree with you, but the table contains just 1 row only and I am just to avoid the ridpool failue by makng a indexable predicate but htis contains just 1 row only. Is there any way one can aoid ridpool getting failed ?

  4. #4
    Join Date
    Dec 2008
    Posts
    76
    Do it for concurrency. Index locks don't usually cause timeouts or deadlocks, row and table locks do.
    Last edited by rdutton; 09-03-09 at 10:44.
    RD

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I haven't tried it myself, but according to what I saw Scott Haas reported you might get a benefit.

    Why not create an index and see what happens. It should take less time then to respond to this

    Please do let us know what you have found
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    sahana, maybe it is just me but what are you using a 1 column, 1 row table for? Have you considered using SYSIBM.SYSDUMMY1 instead if you just need a table to query with (such as when doing Date arithmetic)?

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Cougar8000
    ...according to what I saw Scott Haas reported you might get a benefit.
    Alex, May I ask who Scott Haas is?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2girl
    Alex, May I ask who Scott Haas is?
    I think he means Scott Hayes of DBI.

    Andy

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Thank you Andy.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Maybe I am missing something, but I don't see how DB2 would choose to use an index on a table with one row (unless you altered the cardinality to volatile, or plugged your own numbers in the stats).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Dec 2008
    Posts
    76
    It probably wouldn't - the initial post just said it was a one column table, not a one column/one row table.

    Although I have seen LUW use an index with cardinality so low that zOS definitely wouldn't.
    RD

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Roger,
    What exactly are you after here? Trying to speed up a query? Just research? If it is for a particular query, let us see the SQL, we may be able to offer up some more helpful advice, as was mentioned using sysdummy1 and other items as well.
    Dave

Posting Permissions

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