Home » SQL & PL/SQL » SQL & PL/SQL » Time taken for INSERT into table
Time taken for INSERT into table [message #297513] Fri, 01 February 2008 03:48 Go to next message
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 #297514 is a reply to message #297513] Fri, 01 February 2008 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
With or without index the answer is the same, single row insert does not change with number of rows in the table.

Regards
Michel
Re: Time taken for INSERT into table [message #297516 is a reply to message #297513] Fri, 01 February 2008 03:58 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 Go to previous messageGo to next message
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 #297522 is a reply to message #297521] Fri, 01 February 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the process.

Generally speaking, always insert in the same order of the keys.

Regards
Michel
Re: Time taken for INSERT into table [message #297640 is a reply to message #297513] Fri, 01 February 2008 21:15 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You should do a google on ORACLE FREELISTS. You will get some good reads and a little deeper understanding of what happens during most inserts.

But be aware, freelists on indexes is not quite the same as freelists on tables, because of the basic nature of what an index is and where a row must go in an index vs. a table.

Also, other related topics you might want to read up on for additional learning in this area would include:

HIGH WATER MARK
INSERT APPEND
MONOTONICALLY INCREASING KEYS (eg. use of sequence numbers for primary keys)

Good luck, have fun, Kevin
Re: Time taken for INSERT into table [message #297641 is a reply to message #297513] Fri, 01 February 2008 21:49 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: Lag function
Next Topic: how to pass a set of values to a procedure
Goto Forum:
  


Current Time: Mon Dec 05 11:20:33 CST 2016

Total time taken to generate the page: 0.05108 seconds