Re: Can a Function-Based Index do this?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 4 Jun 2002 06:58:44 -0700
Message-ID: <adih2k0960@drn.newsguy.com>

In article <3cfc2d0d$1_3_at_nopics.sjc>, "contrapositive" says...
>
>"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?
>

yes, you just need a bigger decode, or use CASE (more readable)

Consider:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( order_whse int, cust_id int, po_num int, order_type varchar2(1), order_status int, delete_ind int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create UNIQUE index t_idx on

2 t ( (case when order_type = 'A' and order_status <> -1 and delete_ind = 0 then po_num else null end),
3 (case when order_type = 'A' and order_status <> -1 and delete_ind = 0 then cust_id else null end),
4 (case when order_type = 'A' and order_status <> -1 and delete_ind = 0 then order_whse else null end) );

Index created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1, 1, 'b', 1, 0 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1, 1, 'b', 1, 0 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1, 1, 'A', 1, 1 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1, 1, 'A', 1, 0 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1, 1, 'A', 1, 0 ); insert into t values ( 1, 1, 1, 'A', 1, 0 ) *
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated

An index like:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create UNIQUE index t_idx on   2 t ( (case

  3          when order_type = 'A'
  4               and order_status <> -1
  5               and delete_ind = 0
  6          then po_num||'/'||cust_id||'/'||order_whse
  7          else null
  8          end)
  9     );

Index created.

would work as well (take the three fields and glue them together into a string once instead of having three cases)

>Thanks!
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Jun 04 2002 - 08:58:44 CDT