Question: I have a table that
contains columns titled last_row, first_row and
latest,. These columns are
intended to help track the last row inserted, the first row inserted and
the latest row updated.
Each of these columns has a value of 1 if the condition
is met and 0 otherwise (i.e. the most recently inserted row will
have 1 in both the last_row and latest columns.
Is it possible to
get multiple entries in the latest column? If so, how do I protect
against it?
Answer: You only want one row at a time to
have the latest column set to 1.
What you will need to accomplish
this is a function-based index on some identifier and decode for
latest. For example:
create table vehicle_positions (vehicle_id
number, longitude number,
latitude number, insert_time date, latest
number);
create unique index
i
on vehicle_positions
(
vehicle_id,
decode(latest,1,1)
);
This will ensure that there exists one, and only one, vehicle_id
associated with a latest column equal to 1; otherwise, Oracle will
throw a constraint violation error. The constraint violation
error should be caught and handled with an PL/SQL stored procedure.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
