Indexes are nothing but the ordered set of pointers to your actual data. It's main advantage is that , you can create it on DB2 columns to speed up the query processing.
The inedexes created can be either unique or non unique.When you create unique index on a column , it can contain only the unique values and exactly one null if the column is defined as NULL.
What if you want to allow multiple NULL for the same.
This new clause WHERE NOT NULL is use to enable multiple NULLs to exist in Unique Index.This is useful when an index contain at least one nullable column, but all non null entries must be unique.
The inedexes created can be either unique or non unique.When you create unique index on a column , it can contain only the unique values and exactly one null if the column is defined as NULL.
What if you want to allow multiple NULL for the same.
This new clause WHERE NOT NULL is use to enable multiple NULLs to exist in Unique Index.This is useful when an index contain at least one nullable column, but all non null entries must be unique.
3 comments:
Hi Nitin,
First of all thanks for creating such a amazing blog which is helping many like me understand different DB2 concepts.
In order to visualize how UNIQUE indexes work I created a test table 'ORDERS' and a UNIQUE INDEX.
CREATE TABLE orders
(order_num INTEGER NOT NULL,
buyer_name VARCHAR(35),
amount NUMERIC(5,2));
CREATE UNIQUE INDEX idx_orderno ON orders (order_num);
I inserted three records in the below order -
insert into orders values (2,'NAMITA',10.00);
insert into orders values (3,'SARANYA',20.00);
insert into orders values (1,'ABHIJIT',50.00);
Then I queried the table with a SELECT statement .
select * from orders.
As per the concept of UNIQUE INDEX the records should be fetched in the increasing order of order_num. But I am getting the records in the order I have inserted.
Please help me understand what is amiss.
Please share syntax of creating unique index with "WHERE NOT NULL" clause.
thanks.. nice info
Post a Comment