Re: Bookish Questions need Ideas

arijitchatterjee123_at_yahoo.co.in (Arijit Chatterjee) wrote in message news:<ea01504d.0410010136.7ed30d95_at_posting.google.com>...
> Thanks Sir,
> Thanks for your reply.I got your points.But one thing is there.
> I am explaining through an example
> =========================================
> Table1
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |1 |A |
> |----------|--------------|
> |2 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
>
> Step1: Now I am updating the table
> ------------------------------------
> Update table Table1 set col1=col1+1
> ------------------------------------
>
> So before Commit
> ****************
>
> Rollback Segment Stores
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |1 |A |
> |----------|--------------|
> |2 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> Database Table Store
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |2 |A |
> |----------|--------------|
> |3 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> Whenever any user asking for any record
> he/she is getting for Rollback segment.
>
> After Commit
> ****************
>
> Database Table Remains
> But Users are now getting data from
> Database table only.
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |2 |A |
> |----------|--------------|
> |3 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
>
> But
> Rollback Segment remain storing
> these records or releasing this.
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |1 |A |
> |----------|--------------|
> |2 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> ==================================
> As per above scenario
> Q1.If rollback segment releasing then fine if not then why it is
> storing it because we can take the information from Redo Logs
> only.And user and do any undo operation.

The rollback segment is keeping the information for the person who is performing the transaction, so he can roll it back if need be. It is also keeping it for other people who look at that data, so they can see the data as it was at the beginning of _their_ transactions. The amount of rollback is adjustable by the DBA, so if it is insufficient (or misconfigured), people may see ORA-15xx errors because the information necessary to reconstruct the data as viewed by others is no longer there when the rollback is written over by another transaction.

> ==================================
> ----------------------------------
> RedoLog is storing
> ==================
> New information + Date
>
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |2 |A |
> |----------|--------------|
> |3 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> as well as previous information
> with the date because I can take
> retrive my previous data through
> Redo Logs.
> ====================
> Old Information + Date
> |----------|--------------|
> |Col1 |Col2 |
> |----------|--------------|
> |1 |A |
> |----------|--------------|
> |2 |B |
> |----------'--------------|
> |... 100 records |
> '-------------------------'
> =========================================
> Q2.Why Oracle immediately updates tables in original database
> and keep the data in rollback segments.As a little programmer's
> point of view if it store the data temporarily in Rollback
> segment and after "commiting" it updates that orginal
> database what is the low sound in this concept.Because oracle
> is used for robust data storage.why it is making the prototype
> of huge data in Rollback segments.
> =========================================

Because for the types of applications Oracle is used for, it is much more efficient. Imagine 500 people ordering widgets, 1000 people editing videos in the database, and 1 person running reports on widgets that have been ordered. If Oracle had to go through many gigabytes of irrelevant redo logs to reconstuct the widget information at a particular time, rather than just the few megabytes of widget information that had changed.

And since most things added are probably committed rather than rolled back, it makes sense for the database to be optimistic and keep the changes first while allowing the capability to undo them. The downside is if you have lots of people wanting to read what is being written, you have to understand what is going on and deal with it, through large rollbacks, short transactions, sending the readers to another database, or whatever. It's still better than the way most db's work, since you lessen the blocking between readers and writers.

> On the above expample if I am wrong any where
> please guide me.Thanks once again for your
> great guidances.
> Regards
> Arijit Chatterjee

So once again, rollback and redo have different purposes. You need to understand what those purposes are. If all your database did was your example, you probably don't need or want Oracle.

jg

--
@home.com is bogus
http://www.signonsandiego.com/uniontrib/20040930/news_1b30ams.html