Re: Oracle 7 - Indexes ?

In article <49jv32$3ee_at_pheidippides.axion.bt.co.uk> mc_at_pst.bt.co.uk "M C" writes:

: A colleague has brought an interesting question to me :-
:
: He creates a table and inserts a number of rows without committing the
: insert, a index already exists on the table. Does anyone know if the index
: is used by that session when querying without actually doing the commit ?
:

Yes.
Tell your colleague to try doing two inserts that violate a unique key index without doing an insert. Oracle will return an 'ORA-00001 Duplicate Key' error.

More interestingly, if two separate session try to insert the same duplicate key item without committing, the second session will hang until the first commits or rolls back.

Furthermore: it is Oracle's ability to read uncommitted index entries which allows the 'parent lock' problem of primary/foreign key constraints to be bypassed if the foreign key is represented by an index on the child table.

-- 
Jonathan Lewis