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

In article <K_iG8.75146$Po6.120173_at_rwcrnsc52.ops.asp.att.net>, "Jim says...
>
>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)

Not sure I'd like to work for a company than enforces a global sex change! ;)

>
>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
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Tue May 21 2002 - 07:24:41 CDT