Re: PL/SQL statement for QUICKLY getting the "next available value"

From: Kathinka Diehl <kathinka_at_rrr.de>
Date: 2000/07/17
Message-ID: <8kuf66$3bgqj$1@ID-6887.news.cis.dfn.de>#1/1

Derrian Jones <derrian.jones_at_doit.wisc.edu> schrieb: >
> This works fine, but the execution time is over 1 minute! Is there any
way we
> can optimize this select?

We had a nearly similar problem (not just 'Y' oder 'N' but a status-flag between 1 and 6). First we have used an index but this didn't work well. Now we have a trigger, that will fill another table.

You can create a trigger, which will fill another table, insert the PVI of all rows, whitch have USED_FLAG = 'N' and delete the row if the USED_FLAG change to 'Y'. Make a unique index onto you new table an you will have the min(PVI) in less than a second.

The disadvatage is: this solution needs more space and makes insert an updates slower.

HTH, Kathinka Received on Mon Jul 17 2000 - 00:00:00 CDT