Re: Find most recent rows, generically?

Mark,

Thanks for sharing your insight into Oracle 'sequence'.

DL

Mark Townsend <markbtownsend_at_attbi.com> wrote in message news:<B8986C21.136B3%markbtownsend_at_attbi.com>...
> Assignment of the current sequence value to a column is done in application
> code (either in the application, or via a trigger or stored procedure in the
> DB). Note that sequences themselves are not 'bound' to a column in the way
> that 'identity' is bound to a column in SQLServer. Also not that sequences
> don't necessarily increment (they can be defined to decrement also).
>
> So there is no data dictionary managed way to determine which column is
> actually using a given sequence, or vice versa.
>
> And to go back to your original question (which John answered), you simply
> cannot count on a column containing values generated from a sequence to
> determine the most recent rows.
>
> If you have this as a requirement in your data model, the easiest thing is
> to add a timestamp column (DATE or DATETIME) to your table. And it's
> portable across all database platforms !!
>
> FYI - Even though it appears that you could, you simply cannot rely on a
> primary key to identify the most recent rows in ANY database (whether it's a
> true primary key, or a surrogate key based on an identity/assigned from a
> sequence). Even timestamp columns can be overloaded in the application with
> earlier values.
>
> The only 'real' potential solution to the problem space of finding the most
> recent rows in a table is surprisingly only supported in Oracle9i - use
> flashback query to diff the current table with it's older version from your
> given reference point in time. And even then this diff would have to be
> keyed on the primary key to determine what had been changed.
>
> in article 604bb90c.0202192008.53621721_at_posting.google.com, DL at
> donlcs_at_yahoo.com wrote on 2/19/02 8:08 PM:
>
> > Hi all,
> >
> > First of all, some clarification is in order:
> > With MS SQL Server, 'identity' serves as an attribute to integer or
> > other numberic data type, it IS NOT a data type by itself, and I never
> > said 'identity' is a data type. Secondly, again, assume we don't know
> > if there's a date column in the table. The question was really, how
> > to determine if Oracle 'sequence' exists for a given column, say, PK
> > column (EMPNO) for EMPLOYEE table, and if so, where to find the
> > 'sequence' numbers for that column (EMPNO)? Thanks.
> >
> > DL
> >
> >
> > "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> > news:<EJgc8.5529$S01.352296_at_sccrnsc02>...
> >> There are sequences in Oracle and you could store them as integers, numbers,
> >> strings, but there is not a column type of identity. That is you cannot
> >> look at the column type and determine that it is a sequence. Sequences are
> >> nice because if I have a table with a bunch of child tables I can insert a
> >> record in the parent and then use that value (mySequence.currentval ) for
> >> the related keys in the child tables.
> >>
> >> If you need to know what the most recent rows are I think a date column is a
> >> much better use. Then you could know when. You might be interested in how
> >> many rows are generated per day which a sequence won't tell you.
> >> Jim
> >>
> >> "DL" <donlcs_at_yahoo.com> wrote in message
> >> news:604bb90c.0202172222.a375f10_at_posting.google.com...
> >>> Thanks for the response, Jim, let me re-phrase my question, with MS
> >>> SQL Server, when I add a column and define it as integer, identity,
> >>> later I can find out if that column is of data type int etc., what's
> >>> Oracle's equivalent of "Identity" if any? and if yes, which system
> >>> table do I go to determine it? Thanks.
> >>>
> >>> DL
> >>>
> >>> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
 news:<9qhb8.431$Ot2.153407_at_sccrnsc01>...
> >>>> You can't. If it is important to know if it was recent then you need a
> >>>> column to model that attribute.
> >>>> Jim
> >>>> "DL" <donlcs_at_yahoo.com> wrote in message
> >>>> news:604bb90c.0202150930.49ef0fc5_at_posting.google.com...
> >>>>> Hi,
> >>>>>
> >>>>> First of all, I thank those who have been very helpful in the past.
> >>>>> How to find most recent rows, say, 10, (for insert and update
> >>>>> transactions), generically?
> >>>>>
> >>>>> For insert:
> >>>>> Well, one can find PK column of a given table, suppose, single column,
> >>>>> then determine data type of that column, if it is of integer or
> >>>>> similar type data,
> >>>>> 1(a) how would one know that it uses "identity" attribute for it
> >>>>> (thus, incremental)?
> >>>>> 1(b) and if it does not, how to proceed?
> >>>>> 2, what if it is of random alphnumeric?
> >>>>> 3, what if it is of composite keys?
> >>>>>
> >>>>> For update:
> >>>>> ???
> >>>>>
> >>>>> * Question category 1 is most important to me for now.
> >>>>>
> >>>>> Thanks.
> >>>>>
> >>>>> DL
Received on Fri Feb 22 2002 - 06:41:01 CET