Re: Equivalent to rowid in Oracle...
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 03/22/04
- Next message: Venus: "sp_primarykeys 'myservername' , 'mytabelname' fails to execute?"
- Previous message: Mark Butler: "Best approach to updating remote server?"
- In reply to: Tenaya: "Re: Equivalent to rowid in Oracle..."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Mar 2004 16:16:45 +1100
I actually meant to refer to rownum(), not rowid (sorry!) - just haven't
used it for a while & forgot the difference in the name. I've long felt that
rownum is something tsql should have.
Agree with all your points re arguing over physical / logical model though..
Regards,
Greg Linwood
SQL Server MVP
"Tenaya" <ct@ct.ct> wrote in message
news:u1HoSD8DEHA.3344@tk2msftngp13.phx.gbl...
> Greg,
>
> There are certainly other differences between the Oracle RowID property
and
> the SQL Server Timestamp data type. I was simply concentrating on one of
the
> differences, so I didn't think it was necessary to enumerate a whole bunch
> of them :-)
>
> However, I must admit, I'm not sure that I completely understand just what
> your first sentence means. RowID actually IS the internal Oracle pointer
to
> the physical location of the row. The "advantage" of the Oracle RowID is
> that by specifying it within a WHERE clause it enables Oracle to bypass
> using either indexes or table scans to locate the row. Instead, Oracle is
> able to use the RowID to go directly to where the row resides. I suspect
> that you know all this, and that was the intent of your first sentence.
>
> One can certainly argue seemingly endlessly about the
> advantages/disadvantages of adhering to the logical model of relational
> databases / SQL vs. exposing the internal workings of the database engine.
> One "obvious" advantage of adherence to the logical model is portability
...
> and in fact it would seem that the original poster is probably porting an
> Oracle application to SQL Server and running into portability issues. One
> "obvious" disadvantage of strict adherence to the logical model can be
> performance. Certainly Oracle's decision to expose the physical location
of
> data can permit quicker access to the row in question ... although it can
> reasonably be argued that one has to physically access the row using
indexes
> and/or table scans the first time around in order to get the RowID, and,
> assuming as is probable, that subsequent accesses to that same row occur
> within a "reasonable" time frame, SQL Server will have the necessary index
> pages cached, and thus one is talking about CPU speeds vs. physical I/O
> accesses.
>
> Nevertheless, it still remains true that navigating to the row in
question,
> even given that the necessary pages are cached, will be slower than being
> able to "jump" directly to a row.
>
> Perhaps another way to distinguish between the Oracle RowID and the SQL
> Server Timestamp is, as was implicit in my first posting, that the SQL
> Server Timestamp is intended to be used when doing optimistic locking. To
> the best of my knowledge, the Oracle RowID can not easily be used for
> optimistic locking. In short, they are really two very different
"animals".
>
> Chief Tenaya
>
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uiuPaz5DEHA.2308@tk2msftngp13.phx.gbl...
> > RowId isnt just for physical rows - can also be used in a select
statement
> > which is arguably its most common use in plsql. In tsql, you've got to
> > either use temp tables with identity or inefficient sql to achieve the
> same
> > thing. It's certainly something I wish tsql had..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Tenaya" <ct@ct.ct> wrote in message
> > news:uiaqg52DEHA.3064@tk2msftngp13.phx.gbl...
> > > Greg,
> > >
> > > I believe there is a significant difference between the SQL Server
> > Timestamp
> > > (aka RowVersion) data type and the Oracle RowID property.
> > >
> > > As I understand it, and I am willing to be corrected by anyone with
> > superior
> > > knowledge, the Oracle RowID property is an internal value that allows
> > Oracle
> > > to quickly locate the physical location of a row. As such, it is
> > independent
> > > of the values contained within the row itself. In other words, one can
> > issue
> > > multiple UPDATEs to an Oracle row, and the RowID will not change.
> > >
> > > As I'm confident you are aware, an UPDATE to a row containing the
> > Timestamp
> > > datatype changes the value of the Timestamp column.
> > >
> > > Chief Tenaya
> > >
> > >
> > > "Jaxon" <GregoryAJacksonN0SPAM@hotmail.com> wrote in message
> > > news:eencRb2DEHA.1240@TK2MSFTNGP10.phx.gbl...
> > > > SQL Server does support a RowVersion DataType. Most often referred
to
> as
> > > > TimeStamp.
> > > >
> > > > I dont know JACK about Oracle, but I believe this is what you are
> > looking
> > > > for.
> > > >
> > > >
> > > > cheers,
> > > >
> > > >
> > > > Greg Jackson
> > > > PDX, Oregon
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Venus: "sp_primarykeys 'myservername' , 'mytabelname' fails to execute?"
- Previous message: Mark Butler: "Best approach to updating remote server?"
- In reply to: Tenaya: "Re: Equivalent to rowid in Oracle..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
