Re: Can a Function-Based Index do this?

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:adfl1i031bu_at_drn.newsguy.com...

> so, you want order_whse, cust_id, po_num to be unique when order_type is
A.
> That would be:
>
> create unique index t_idx on t( decode(order_type,'A',order_whse,null),
> decode(order_type,'A',cust_id,null),
> decode(order_type,'A',po_num,null) );
>
> that would create an index that would only have entries for order_type 'A'
(so
> if 100 out of 10,000 rows have A, the cardinality of the index would be
100 --
> not 10,000) and order_whse,cust_id,po_num would have to be unique (so
po_num is
> unique within order_whse,cust_id)
>

This looks good, but I forgot something in the WHERE clause! This only applies to orders that aren't voided and aren't "deleted" so add AND order_status != -1 AND delete_ind = 0 to the query. Is it still possible?

Thanks! Received on Mon Jun 03 2002 - 21:42:11 CDT