Time taken for INSERT into table [message #297513] |
Fri, 01 February 2008 03:48  |
rballal
Messages: 12 Registered: February 2008
|
Junior Member |
|
|
Hi,
Suppose if i have Unique key contraint added for couple of feilds in a table , will time taken by INSERT into the table increase as the number of records in the table increases?
Thanks,
rb
|
|
|
|
Re: Time taken for INSERT into table [message #297516 is a reply to message #297513] |
Fri, 01 February 2008 03:58   |
Frank Naude
Messages: 4590 Registered: April 1998
|
Senior Member |
|
|
Insert time will increase when you add the unique key. As the table grows, the insert time may or may not increase further - depending on if the index depth grows deeper or not. However, if it does, it will be barely noticeable.
|
|
|
Re: Time taken for INSERT into table [message #297521 is a reply to message #297516] |
Fri, 01 February 2008 04:08   |
rballal
Messages: 12 Registered: February 2008
|
Junior Member |
|
|
Thanks a lot for your answer.
If suppose 2 threads are INSERTing data into the same table( with Unique key contraint enabled ) , will this cause deadlock and how i can resolve this?
Thanks,
rb
|
|
|
|
|
Re: Time taken for INSERT into table [message #297641 is a reply to message #297513] |
Fri, 01 February 2008 21:49  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Sorry, I did not really give any information on your original question. Here is some random info:
Oracle does not allow dirty reads to users. There is in fact no way to achieve a dirty read in Oracle. There is no parameter you can set, no isolation level you can use, no command you can invoke, that will let you as a user do a dirty read. Oracle does not support dirty reads by user transactions because it does not need them. Other database do need them, when ever they want to support more than one user.
However, Oracle does not bind itself to the same rules as it holds its users to. Although Oracle will not allow users to do dirty read and thus subject them to incorrect results; Oracle system processes in fact do dirty reads all the time. Consider this scenario:
create table test1 (a number);
create unique index test_uk1 on test1(a);
--
-- start a sqlplus session #1
--
insert into test1 values (1);
--
-- start a sqlplus session #2
--
insert into test1 values (1);
--
-- start a sqllpus session #3
--
insert into test1 values (9);
when you do these steps you will notice that:
session#1 inserts its row fine
session#2 hangs
session#3 inserts its row fine
Q: why is session#2 hanging?
A: it is waiting to see if session#1 is going to commit or rollback. If session#1 commits, then the insert of session#2 is invalid. If session#1 does a rollback, then the insert of session#2 can go forward. But there is a problem here...
How does session#2 know that session#1 has already inserted a row with the same key as it is trying to insert when a transaction is not allowed to see uncommited data and thus session#2 is not supposed to see the yet uncommited insert of session#1?
The answer I remember is: the insert of session#1 is done, and the unique index has been modified. The modified page(s) of this index are now dirty because they hold uncommited changes. Even though users are not allowed to see uncommited data, and answers to their queires can never be based on dirty pages, Oracle behind the scenes is still able to look at the dirty index pages to see that indeed there is a pending insert of the same key value on this unique index and thus session#2 must wait for session#1 to finish with either a commit or rollback. Oracle in fact uses dirty reads behind the curtains to do its "dirty work".
However, this is not a deadlock. This is a block. Session#2 has been "blocked" by session#1. Technicalliy this is not deadlock (though I suppose in the end the result is not too different from a deadlock situation in that one transaction will succeed and one will fail). Also, technically speaking, it is not the sessions that are blocked but rather, the transactions. For it is possible for one session to spawn any number of simultaneous trasactions using AUTONOMOUS TRANSACTIONS.
But you can read up on AUTONOMOUS TRANSACTIONS at your own leasure. I would suggest this to start:
http://www.orafaq.com/node/1915
Lastly, session#3 shows that given multiple transactions, simply inserting into the same index does not by itself mean deadlock or blocking. Indeed, Oracle is different from every other database in that it is designed from the bottom up to be highly concurrent, allowing gobs and gobs of transactions to be happening at the same time. The lack of a need for dirty reads is one positive result of this design. No other database has the design Oracle has when it comes to concurrent processing which is why its performance to connecurt transactions curve is so much better than other databases. Ah but I drag on.
Good luck, hope this was interesting, Kevin
|
|
|