simple question about indexes

From: Ruslan A Dautkhanov <>
Date: 24 Oct 2003 05:38:57 -0700
Message-ID: <>

Hello !

I create the index

        CREATE INDEX details_loaded ON details (loaded);   where loaded - CHAR(1) DEFALUT 'N'.

The column details.loaded used to determine is this row processed or not, so index details_loaded used to speed up request when my program fetch not loaded records.

In a first release I use
  UPDATE details SET loaded='Y' WHERE ...

but I hear later that Oracle do not index really rows with NULL keys, so I birn my second variant:
  UPDATE details SET loaded=NULL WHERE ...

as far as details table is huge and volatile, by this method I tries minimize system overhead by this index - number of rows WHERE loaded='N' is much less total number of rows.

Please let me know really this technique save my space and speed up my request??

P.S. The RDBMS PostgreSQL have `conditional indexes` -

     this way I can write                 

		CREATE INDEX details_loaded ON details (loaded) WHERE loaded='N'
     and DBMS will index only pointed rows (loaded='N'), but we have Oracle..

best regards,
Ruslan A Dautkhanov
Received on Fri Oct 24 2003 - 14:38:57 CEST

Original text of this message