If the maximum current value of an ID generated by a sequence is N, does that guarantee that all future rows will have index > N

oracle-11g-r2sequence

I'm debugging some legacy code and have found what might be a flaw in how it interacts with the database. A simplified version of it is as follows:

TableA is acting as a queue. It has data inserted into it from multiple users/sessions at pretty high velocity. There are never any updates or deletes except the one I am about to describe. This table has an integer ID which is populated by a 'before insert' trigger on the table that draws values from a sequence.

A consumer application repeatedly selects the whole table, processes the data, and stores the maximum index value of the resultset each time. It then executes

DELETE FROM TableA WHERE id<=:maxSelectedID

My question is, is this a reliable way to delete all the data that was previously selected (and only that data)? I feel like with the volume of inserts on this table that it is quite possible that a higher ID might get committed (and thus be available to select) before a lower one. In this case the lower one could potentially be deleted without ever having been selected (or if we are lucky it would also miss the delete get caught in the next select).

Best Answer

If the database is running on a RAC cluster, each node in the cluster would have a separate sequence cache. That would cause the values returned to be out of order unless the sequence was created with ORDER specified (i.e. CREATE SEQUENCE seq_foo START WITH 1 ORDER).

If the database is running on a standalone instance, sequence numbers will always be generated in order. If you're depending on this behavior for functional reasons, I'd be pedantic and specify ORDER when creating the sequence even on a standalone database. But it isn't necessary to do so.

From the documentation

ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.

The other potential issue would be if the sequence reached the MAXVALUE and was set to CYCLE by starting over at the minimum value. NOMAXVALUE and NOCYCLE are the defaults, though, so it would be pretty unusual that someone using a sequence like this would create the sequence in that way.

Related Solutions

Migrating to Oracle Flashback for Historization on Oracle 11g

It sounds like Oracle Total Recall may fit your needs:

Regulatory oversight such as Sarbanes-Oxley, HIPAA, and Basel-II, as well as internal audits, require companies to keep historical data available for long periods of time. Oracle Total Recall, part of Oracle's comprehensive portfolio of database security solutions, works with Oracle Database 11g, Enterprise Edition to help companies store their data in secure, tamper-proof databases while keeping it accessible to existing applications

The documentation states that it can support refactoring:

In Oracle Database 11g Release 1, the Add Column DDL operation is supported with Flashback Data Archive. With Oracle Database 11g Release 2, the following DDL operations are supported, with full support for Flashback queries across all associated changes:

• Add, Drop, Rename, Modify Column

• Drop, Truncate Partition

• Rename, Truncate, Drop Table

For more complex DDL – upgrades, split table, etc. – the Disassociate and Associate PL/SQL procedures can be used to temporarily disable Flashback Data Archive on specified tables.

Simple query with date type requires explicit conversion to_date of date

Your belief that this should not be necessary is correct based on your assumptions. Normally this means the assumptions should be rechecked (unless there is data corruption or a bug). If you run the following you should get the same results from the first query as the second:

DROP TABLE t1;
CREATE TABLE t1 AS (
   SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn 
   FROM dual CONNECT BY level <=120);

select * from t1 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

select myDateColumn from t1 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

To check some of the assumptions, can you show the results of the following query?

SELECT myDateColumn, to_date(myDateColumn)
   , to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY') 
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

The first three columns should all show identical information and the last should verify that the year is correct.

Best Answer

Related Solutions

Migrating to Oracle Flashback for Historization on Oracle 11g

Simple query with date type requires explicit conversion to_date of date

Related Question