Re: Newbie's Oracle 9i impression: it sucks

Actually, you misunderstand what Howard is stating. What Howard is saying is:(chronologically down the page)

Howard (starts a transaction)
Howard updates the emp table changing all the males to females and females to males.(assume sex is not null)

SQLJoe reads the emp table and does not see Howard's change. (Also SQLJoe can read the entire emp table because even though Howard updated the emp table - every row in this case - readers don't block writers and writers don't block readers)

Howard issues a select * from emp and notices that he made the change he wanted to.

Howard issues a commit; (thus making the change visible)

SQLJoe issues a commit and does a select * from emp. SQLJoe notices that all the males are now females and the females are now males.(He sees Howard's change)

MS SQL Server does not work that way. In Oracle it is called multiversioning.

Jim

"SQLJoe" <sqljoe_at_aol.com> wrote in message news:20020520171940.09238.00000763_at_mb-mq.aol.com...
>
> >If I update the emp table, in Oracle, you can read the emp table, and you
> >will see the data as it was before I started updating it. That's the
> >rollback mechanism being used to generate read-consistent images of data.
> >
> >Try that in SQL Server (or Sybase etc etc), and you will discover that,
> >unless you force the issue, your select is blocked by my update. Only
when I
> >commit can you read.
>
>
> Actually, you are factually incorrect. MS SQL does provide INSERTED and
DELETED
> tables which allow you to view either inserted or deleted data BEFORE it
is
> comitted.
>
> As far as rollback is concerned, MS SQL does allow partial rollbacks and
have
> savepoints etc.
>
>
>
> >That's an architectural issue of the rdbms itself, and no amount of O/S
> >improvement or hardware will deal with it.
> >
> >Also architecturally-related is SQL Server 2000's insistence on 8Kb
blocks
> >("pages" if you prefer). That's performance limiting. Also it has a
problem
> >with long-running transactions (as does Informix amongst others) because
the
> >same mechanism is used for both transaction recovery and transaction
> >rollback. If your transaction takes up more than half the log, it
> >automatically fails, because it needs as much room to roll the
transaction
> >back as it took to generate it in the first place. Oracle's separation of
> >redo from rollback means that this simply isn't an issue for it.
>
>
> I bet this is one of the 20-30% of features in Oracle that most Oracle DBA
and
> Developers never end up changing or using. Chaning the page (or block)
size
> rarely makes much difference. But I will admit Oracle allows you to
configure
> the page (or block) size while MS SQL does not (set at 8KB).
>
> Jinsoo
> MCDBA, MCSD, MCSE+I
Received on Mon May 20 2002 - 22:16:58 CDT