Bob Stearns wrote:
Serge Rielau wrote:
Bob Stearns wrote:
I am creating an index on a column which is 40% NULLS. The process
seems to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?
No. DB2 indexes NULLs. I presume you mean the CREATE INDEX statement
right? It's not the queries that are slow...
Yes. An IBM DB that I previously used (used to be called Universe, I
can't remember the IBM name, it's IBM's mv offering) had such a clause
for indices; I'd hoped IBM had done an internal IP transfer. It also has
powerful capabilities in the 'GENERATED VIRTUAL' (including referring to
other tables, albeit only by primary key) area that I wish would migrate
into DB2. Some of my tables had more virtual columns than real ones.
Universe is still around under this name.
Looking at index requirements I know of these:
Larger index names: Fixed in DB2 Viper
More columns: Fixed in Viper
Wider index: Fixed in Viper
Index on expression: Under consideration
Allow more than one NULL in Unique indexes: Under consideration
Selective indexes (not necessarily limited to NULL): Under consideration
IP tends to be the least of our problems ;-)
W.r.t. virtual columns we call these "GENERATED BY REFERENCE".
Thing is it's hard to see their usage compared to expressions added to a
VIEW over the base table. Care to elaborate?
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab