By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

IGNORE NULLS possible fro CREATE INDEX? DB2 UDB v8.1.9 Linux

P: n/a
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?
Apr 12 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 12 '06 #2

P: n/a
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.
Apr 12 '06 #3

P: n/a
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
Apr 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.