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

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).

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 ?

Do it for concurrency. Index locks don't usually cause timeouts or deadlocks, row and table locks do.

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

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)?

Alex, May I ask who Scott Haas is?

I think he means Scott Hayes of DBI.

Andy

Thank you Andy.

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).

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.

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