user_ind_expression contains this info:

ops$tkyte@ORA8I.WORLD> create table project (project_ID number primary key,
  2                  teamid number,
  3                  job varchar2(100),
  4                  status number(1));

Table created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> CREATE UNIQUE INDEX project_idx ON project (
  2    DECODE (status, 1, teamid, NULL ),
  3    DECODE (status, 1, job, NULL ));

Index created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select column_expression, column_position from user_ind_expressions
  2  /

COLUMN_EXPRESSION                        COLUMN_POSITION
---------------------------------------- ---------------
DECODE("STATUS",1,"TEAMID",NULL)                       1
DECODE("STATUS",1,"JOB",NULL)                          2 

If you indexed:


create index item_arc_idx on item_arc
( decode( item_serial_num, 
              parent_serial_num, decode(end_date,
                                            NULL,1,
                                            NULL), 
              NULL ) );

and then

update item_arc
   set ....
 where decode( item_serial_num, 
              parent_serial_num, decode(end_date,
                                            NULL,1,
                                            NULL), 
              NULL ) = 1;


then it would most likely use the index (index selects 2-3k rows from the 200 million row table) and the index will only have 2-3k entries in it at any point in time since the values in the function are either 1 or NULL and entirely NULL entries are not recorded in b*tree indexes.

ops$tkyte@ORA920.US.ORACLE.COM> CREATE TABLE item_arc
  2      (
  3       end_date                       DATE,
  4       parent_serial_num              VARCHAR2(30),
  5       item_serial_num                VARCHAR2(30),
  6       item_location                  VARCHAR2(80),
  7       description                       VARCHAR2(30)
  8      );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create index item_arc_idx on item_arc
  2  ( decode( item_serial_num,
  3                parent_serial_num, decode(end_date,
  4                                              NULL,1,
  5                                              NULL),
  6                NULL ) );

Index created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into item_arc select NULL, 'ABC', 'ABC', 'xxx', 'yyy'
  2    from all_objects
  3   where rownum <= 3000;

3000 rows created.
<b>these are indexed...</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into item_arc select NULL, 'XYZ', 'ABC', 'xxx', 'yyy'
  2    from all_objects
  3   where rownum <= 3000;

3000 rows created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into item_arc select sysdate, 'ABC', 'ABC', 'xxx', 'yyy'
  2    from all_objects
  3   where rownum <= 3000;

3000 rows created.

<b>those 6k are not</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> analyze index item_arc_idx validate structure;

Index analyzed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select name, lf_rows from index_stats;

NAME                              LF_ROWS
------------------------------ ----------
ITEM_ARC_IDX                         3000

<b>and that shows that is the case...</b>

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> analyze index item_arc_idx compute statistics;

Index analyzed.

ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'ITEM_ARC', numrows => 200000000, numblks => 2000000 );

PL/SQL procedure successfully completed.

<b>to make it look like your environment -- index with 3k entries, table with 200,000,000 rows....</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter session set query_rewrite_enabled = true;

Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> alter session set query_rewrite_integrity = trusted;

Session altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA920.US.ORACLE.COM> update item_arc
  2     set end_date = sysdate
  3   where decode( item_serial_num,
  4                parent_serial_num, decode(end_date,
  5                                              NULL,1,
  6                                              NULL),
  7                NULL ) = 1;

3000 rows updated.


Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=19 Card=2000000 Bytes=86000000)
   1    0   UPDATE OF 'ITEM_ARC'
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_ARC' (Cost=19 Card=2000000 Bytes=86000000)
   3    2       INDEX (RANGE SCAN) OF 'ITEM_ARC_IDX' (NON-UNIQUE) (Cost=9 Card=800000)




Statistics
----------------------------------------------------------
        109  recursive calls
       8528  db block gets
       1302  consistent gets
          0  physical reads
    1158396  redo size
        791  bytes sent via SQL*Net to client
       1020  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       3000  rows processed

ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM> analyze index item_arc_idx validate structure;

Index analyzed.

ops$tkyte@ORA920.US.ORACLE.COM> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
ITEM_ARC_IDX                         3000        3000

<b>and the index is for all intents and purposes "empty" - just waiting to be filled with new data tomorrow...</b>


 

never say "won't work for me".  there are many ways to use this.  In "Effective Oracle by Design", I showed at least three:

What if you need more than one column from the related table? Suppose we needed not only the COUNT(*), but also the AVG(OBJECT_ID). We have four choices:

0 Go back to the outer join.
0 Use two scalar subqueries.
0 Use a trick with a single scalar subquery.
0 Use an object type.

Since the first option is pretty obvious, we won't demonstrate that. We will take a look at the other choices, and demonstrate why the third and fourth options may be worthwhile. 

(4)Use Two Scalar Subqueries

First, we'll look at using two scalar subqueries:

select a.username, (select count(*)
                      from all_objects b
                     where b.owner = a.username) cnt,
                   (select avg(object_id )
                      from all_objects b
                     where b.owner = a.username) avg
  from all_users a

....

That effectively doubled the work (look at the QUERY column and compare its values to the previous results). We can get back to where we were, however, just by using a small trick.

(4)Use a Single Scalar Subquery

Instead of running two scalar subqueries, we will run one that will encode all of the data of interest in a single string. We can use SUBSTR then to pick off the fields we need and convert them to the appropriate types again.

select username,
       to_number( substr( data, 1, 10 ) ) cnt,
       to_number( substr( data, 11 ) ) avg
  from (
select a.username, (select to_char( count(*), 'fm0000000009' ) ||
                           avg(object_id)
                      from all_objects b
                     where b.owner = a.username) data
  from all_users a
       )

...

So, in the inline view, we formatted the COUNT(*) in a ten-character wide, fixed-width field. The format modifier (FM) in the TO_CHAR format suppressed the leading space that a number would have, since we know the count will never be negative (so we do not need a sign). We then just concatenate on the AVG() we want. That does not need to be fixed width, since it is the last field. I prefer to use fixed-width fields in all cases because it makes the SUBSTR activity at the next level much easier to perform. The outer query then just must SUBSTR off the fields and use TO_NUMBER or TO_DATE as appropriate to convert the strings back to their native type. As you can see, in this case, it paid off to do this extra work. 

One note of caution on this technique though: Beware of NULLs. On fields that allow NULLs, you will need to use NVL. For example, if COUNT(*) could have returned a NULL (in this case, it cannot), we would have coded this way:
nvl( to_char(count(*),'fm0000000009'), rpad( ' ', 10 ) )
That would have returned ten blanks, instead of concatenating in a NULL, which would have shifted the string over, destroying our results.

(4)Use an Object Type

Lastly, we can use an object type to return a "scalar" value that is really a complex object type. We need to start by creating a scalar type to be returned by our subquery:

ops$tkyte@ORA920> create or replace type myScalarType as object
  2  ( cnt number, average number )
  3  /

Type created.

That maps to the two numbers we would like to return: the count and the average. Now, we can get the result using this query:
select username, a.data.cnt, a.data.average
  from (
select username, (select myScalarType( count(*), avg(object_id) )
                    from all_objects b
                   where b.owner = a.username ) data
  from all_users a
       ) A
...

Here, we get the same results without needing to encode the data using TO_CHAR and decode the data using SUBSTR and TO_NUMBER. Additionally, the presence of NULLs would not further complicate the query.

Using the object type is convenient to reduce the query complexity, but it does involve the extra step of creating that type, which some people are hesitant to do. So, while this technique is easier to use, I find most people will use the encode/decode technique rather than the object type approach. The performance characteristics are very similar with either technique.

<b>for the "what is the difference between your model and my model"... I'd say

Mine is "correct" (i usually don't say that but I firmly believe it).  

Mine can use the database to enforce integrity *(which is the only way to enforce it, I'll break your trigger or application based approach in seconds unless you lock tables!).  

Mine does not encode N values into a single column.  

Mine is safer, less confusing.

And the scalar subquery will work for you.

</b>

 

so, basically, when col2 is not null -- col1, col2 will be in the index and if col2 is not null AND col3 is not null -- col3 will be there as well.....


that is actually simplified to

"create a unique index on col1, col2, col3 when col2 is not null"






ops$tkyte@ORA9IR2> create table t ( c1 int not null, c2 int, c3 int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create unique index t_idx on t(
  2  case when c2 is not null then c1 end,
  3  case when c2 is not null then c2 end,
  4  case when c2 is not null then c3 end
  5  )
  6  /
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, 1 );
insert into t values ( 1, 1, 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, null );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, null );
insert into t values ( 1, 1, null )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, null, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, null, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, null, null );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, null, null );
 
1 row created.
 
 

ops$tkyte%ORA10GR2> create table p1(x int primary key);

Table created.

ops$tkyte%ORA10GR2> create table p2(y int primary key);

Table created.

ops$tkyte%ORA10GR2> insert into p1 values (1);

1 row created.

ops$tkyte%ORA10GR2> insert into p2 values (1);

1 row created.

ops$tkyte%ORA10GR2> create table c
  2  ( x references p1,
  3    y references p2,
  4    constraint x_y_check check( nvl2(x,0,1)+nvl2(y,0,1) = 1 )
  5  )
  6  /

Table created.
<b>a short hand approach if you have lots more than two mutually exclusive things, would be deserving of a comment of course</b>

ops$tkyte%ORA10GR2> insert into c values(null,null);
insert into c values(null,null)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.X_Y_CHECK) violated


ops$tkyte%ORA10GR2> insert into c values(1,1);
insert into c values(1,1)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.X_Y_CHECK) violated


ops$tkyte%ORA10GR2> insert into c values(1,null);

1 row created.

ops$tkyte%ORA10GR2> insert into c values(null,1);

1 row created.
 

ops$tkyte%ORA10GR2> create table test
  2  (col1 varchar2(10) ,
  3   col2 number,
  4   constraint check_when_a check ( case when col1 = 'A' and col2 = 0 then 1
  5                                        when col1 <> 'A' or col1 is null then 1
  6                                        else 0
  7                                    end = 1 )
  8  )
  9  ;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'A', 0 );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'A', null );
insert into test (col1, col2) values ( 'A', null )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_WHEN_A) violated


ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'A', 1 );
insert into test (col1, col2) values ( 'A', 1 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_WHEN_A) violated


ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'b', 0 );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'b', null );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'b', 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( null, 0 );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( null, null );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( null, 1 );

1 row created.

    When Others then
        Lv_x := 'N';
        Null;
End;

begin
    .... your code ....
when others
   then null;
end;

ops$tkyte%ORA10GR2> CREATE UNIQUE INDEX contacts_uk ON contacts i_am_a_correlation_name(email);

Index created.

ops$tkyte%ORA10GR2> CREATE UNIQUE INDEX contacts_uk ON contacts (upper(email));

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> ANALYZE TABLE contacts COMPUTE STATISTICS;

Table analyzed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into contacts values (dacr_seq.nextval, 'dave@hello.com');

1 row created.

ops$tkyte%ORA10GR2> insert into contacts values (dacr_seq.nextval, 'DAVE@hello.com');
insert into contacts values (dacr_seq.nextval, 'DAVE@hello.com')
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.CONTACTS_UK) violated

create table t (
  c1 int, c2 int,
  unique ( c1 ) 
    using index (
      create index i 
        on t ( c1, c2 )
    )
);

insert into t values ( 1, 1 );
insert into t values ( 1, 2 );

ORA-00001: unique constraint (CHRIS.SYS_C0014846) violated

insert into t values ( 2, 1 );
insert into t values ( 2, 2 );

ORA-00001: unique constraint (CHRIS.SYS_C0014846) violated

set serveroutput off

select c2 from t
where  c1 = 1;

C2   
    1 

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

---------------------------------    
| Id  | Operation        | Name |    
---------------------------------    
|   0 | SELECT STATEMENT |      |    
|   1 |  INDEX RANGE SCAN| I    |    
---------------------------------