Home » SQL & PL/SQL » SQL & PL/SQL » Auto increment performance (Oracle)
Auto increment performance [message #460998] Wed, 16 June 2010 01:12 Go to next message
manjukmoorthi
Messages: 20
Registered: February 2010
Junior Member
Hello All,

Please let me klnow which method is more efficient in terms of performance to generate an auto increment number in oracle.

Step1

CREATE TABLE test 
(id NUMBER PRIMARY KEY, 
name VARCHAR2(30)); 


Step2
CREATE SEQUENCE test_sequence 
START WITH 1 
INCREMENT BY 1; 


Method1

Follow Step1 and Step2 and create a Trigger as below :

CREATE OR REPLACE TRIGGER test_trigger 
BEFORE INSERT 
ON test 
REFERENCING NEW AS NEW 
FOR EACH ROW 
BEGIN 
SELECT test_sequence.nextval INTO :NEW.ID FROM dual; 
END; 

/


Method 2

Follow Step1 and Step2 and directly have an insert statement as below:

INSERT INTO test (id, name) VALUES (test_sequence.nextval , 'Jon343');


Thank you ..!!
Re: Auto increment performance [message #461001 is a reply to message #460998] Wed, 16 June 2010 01:21 Go to previous messageGo to next message
Kumarvijay
Messages: 25
Registered: June 2010
Location: India
Junior Member
I feel Method 2 should be more efficient . Isn't it Xperts..?
Re: Auto increment performance [message #461002 is a reply to message #461001] Wed, 16 June 2010 01:26 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I would say so: a trigger is PL/SQL, which means a context switch from SQL (the insert statement) to PL/SQL (the trigger). Within the trigger, a SQL statement is executed. This is another context switch.

Edit: but I'm not sure that the impact would be that large. A select from dual is a special case. Furthermore, in 11g you can access the nextval directly from PL/SQL without having to select from dual.

MHE

[Updated on: Wed, 16 June 2010 01:28]

Report message to a moderator

Re: Auto increment performance [message #461003 is a reply to message #460998] Wed, 16 June 2010 01:28 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Not sure which is better in terms of performance but in terms of maintainability the 1st method i would prefer

Regards
Ved
Re: Auto increment performance [message #461004 is a reply to message #460998] Wed, 16 June 2010 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Method 2 is more efficient but it requires that all your INSERT statements call the sequence.
Unless you have many INSERTs per second, it is recommend you hide the use of the sequence this will prevent someone inserting with dumb id value.

Regards
Michel

[Updated on: Wed, 16 June 2010 01:29]

Report message to a moderator

Re: Auto increment performance [message #461007 is a reply to message #461004] Wed, 16 June 2010 01:32 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I'd like to add that was looking at this pure from a performance point of view. A trigger is, like Michel said, a way of enforcing the sequence to be used.

MHE
Re: Auto increment performance [message #461013 is a reply to message #460998] Wed, 16 June 2010 02:08 Go to previous messageGo to next message
manjukmoorthi
Messages: 20
Registered: February 2010
Junior Member
I am little confused here. The insert statements would be more per second. It is a bulk insert of data. So which can I still prefer to use?
Re: Auto increment performance [message #461016 is a reply to message #461013] Wed, 16 June 2010 02:19 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Read the answers again: looking only at performance, the method without a trigger might be faster. But the drawback is that you can't be sure that anyone else will use the same sequence to fill your column. So, that method isn't fail safe. A valid trigger enforces the use of the sequence, no matter from where the insert originates. To be safe, use a trigger. And it has been said before: the performance impact is minimal.

What Oracle version are you using?

MHE
Re: Auto increment performance [message #461041 is a reply to message #461016] Wed, 16 June 2010 04:24 Go to previous messageGo to next message
manjukmoorthi
Messages: 20
Registered: February 2010
Junior Member
I understand!! The version is Oracle 9i
Re: Auto increment performance [message #461073 is a reply to message #461016] Wed, 16 June 2010 07:47 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I would use a form in which the user still is able to insert a specific id if he wants to: only assign a value in the trigger if ID is null.
Re: Auto increment performance [message #461076 is a reply to message #461013] Wed, 16 June 2010 08:03 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
manjukmoorthi wrote on Wed, 16 June 2010 09:08
I am little confused here. The insert statements would be more per second. It is a bulk insert of data. So which can I still prefer to use?


If there is no concurrency in the context I prefer to use MAX(id)+rownum and drop the sequence.

Max/Min over a primary key is very fast.


[EDIT]

One more hint: for a bulk load it's better to use INSERT/SELECT instead of INSERT/VALUES



Bye Alessandro

[Updated on: Wed, 16 June 2010 08:06]

Report message to a moderator

Re: Auto increment performance [message #461077 is a reply to message #461073] Wed, 16 June 2010 08:25 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Frank wrote on Wed, 16 June 2010 13:47
I would use a form in which the user still is able to insert a specific id if he wants to: only assign a value in the trigger if ID is null.


I would have thought that would just be a recipe for random ORA-0001 errors.
What happens when the users enter a value that the sequence hasn't reached yet?
Re: Auto increment performance [message #461155 is a reply to message #461076] Wed, 16 June 2010 15:36 Go to previous messageGo to next message
mikerault
Messages: 21
Registered: June 2010
Location: Alpharetta, Ga
Junior Member
And what happens when you get 10 million or 10 billion rows in your table? using a max(id) would at the least generate some sort of index lookup, at worst a full table scan. I have yet to find some home grown method that beats Oracle's built in, kernel optimized method. To paraphrase Cuba Gooding Jr., "Show me the numbers" or else you are just waving your hands around and making noise.

Mike
Re: Auto increment performance [message #461170 is a reply to message #461155] Wed, 16 June 2010 20:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
If I am not mistaken, triggers (either table level, or instead-of-trigger variety) disable use of parallel DML (but not parallel query). [above edited, thanks to clarification from MIke].

Additionally triggers of any kind require extra processing.

Thus it is clear that the method without the trigger is much faster. This can easily be benchmarked if you care to take the time so please do and tell us what you find.

But unless your database has a real tough performance SLA to meet, then the question of which way to go (trigger or app defined) depends upon other goals. Performance takes a second seat to design goals. You have to answer some questions. This was hinted at by all the posters to this thread when they started to point out things like manageability issues etc. So, you have some design sole searching to do. It really comes down to various philosophies on using a database. If you are not sure, ask your seasoned professionals around you. Questions like these:

1) what do you think about using database triggers in general
2) what do you think about using sequences in general
3) what is your position on hiding the existence of a sequence
4) is a table trigger the best place for this, or should you consider and instead of trigger (big philosophy thing with this one)
5) how much work would you put into making your database "OPEN" in the sense that it should be able to handle an application that expects to supply the value vs. an application that does not want to see it ever?

With respect to #5 for example, if you are using an Oracle Forms application, you would let forms get the sequence number and supply it in its insert (or update or delete). If you were using a MS-ACCESS front end, you would wrap a view around your table that hides the ID fields and somehow make your database fill in the sequences.

Consider though that if you are using a table level trigger to supply this ID on insert, how does the app that inserted the row know what it is? If it does not know, how can it use it in subsequent steps as in for example you create a dept row and then want to add several employees to it? It is in this kind of "thinking" that you discover where your preferences are for using triggers and other database advanced features. Sometimes you have to live with it for a while to figure out if you like it or not. Then again I presume you are being very smart because you realize this and are thus looking to those who have used both methods for some time and have thus formulated an opinion.

My opinion is this:

1) if you have a database design team that thinks about more than just implementing a data model (that is OPEN-NESS of a database, and how database features affect application development and ultimate application success), then you should consider using advanced techniques. This would include use of table level triggers to hide sequences or more to my preferences, figuring out how instead-of-triggers could do the same but offer more functionality for other things at the same time.

2) if you have a corporate environment that does not foster long term management of databases (they have the build and it should not need any care and feeding kine of mentality) then don't use triggers at all. Make your apps do the work.

Good luck, Kevin

Kevin

[Updated on: Thu, 17 June 2010 09:01]

Report message to a moderator

Re: Auto increment performance [message #461178 is a reply to message #461170] Wed, 16 June 2010 22:03 Go to previous messageGo to next message
mikerault
Messages: 21
Registered: June 2010
Location: Alpharetta, Ga
Junior Member
Triggers need to be disabled for parallel DML but not parallel query.

Mike
Re: Auto increment performance [message #461183 is a reply to message #461178] Thu, 17 June 2010 00:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes you are correct. Thanks very much for the clarification. I forget the details all the time. Best that someone with a younger brain is around to pick up after me.

Thanks Mike.

Kevin
Re: Auto increment performance [message #461244 is a reply to message #461155] Thu, 17 June 2010 04:35 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
mikerault wrote on Wed, 16 June 2010 22:36
And what happens when you get 10 million or 10 billion rows in your table? using a max(id) would at the least generate some sort of index lookup, at worst a full table scan.


On a primary key it consists of scanning the final leaf on the index B-TREE. An action that could be done reading no more than 10 blocks even for tables of terabytes.

Do know how the size of the table impacts the MIN/MAX search on an index?
I'll tell you that... IT'S LESS THAN NOTHING

http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/indexiot.htm#CNCPT1170


Quote:

I have yet to find some home grown method that beats Oracle's built in, kernel optimized method.


What home grown method for doing what?
And what built in, kernel optimized method for doing what?


As I already said before when there is no concurrency, in particular when nobody else is trying to load data in the same table in the same time as you, but just in that case, it's better to use max+rownum method. You evaluate the max once and then that value plus one for every loaded row. It's really simple, any CPU can do an increment with a single clock step.

The use of a sequence involves a database object, it handles concurrency, the first time you call the nextval and every time you consume the cache the RDBMS executes a sort of SELECT/UPDATE on that object. This extra tasks are executed by the database just with the purpose to handle concurrency but they are not as simple as a variable increment.


Quote:

To paraphrase Cuba Gooding Jr., "Show me the numbers" or else you are just waving your hands around and making noise.


What should be demonstrated?

That a MAX or MIN costs less than nothing?
That a CPU executes the increment with a single clock step?
That a sequence executes extra tasks to handle concurrency?

They're all obvious things, but I want to post you some trace-stats and the explain plan to do a MAX compared to a COUNT to show you that a MAX costs nothing; for the rest I point you to some trusted docs that may be in your interest to get a basic culture on the topics.

binary integer arithmetic

Overview of Sequences
they also say here:
Quote:

The sequence generator is useful in multiuser environments for generating unique numbers without the overhead of disk I/O or transaction locking. For example, two users simultaneously insert new rows into the orders table. By using a sequence to generate unique numbers for the order_id column, neither user has to wait for the other to enter the next available order number. The sequence automatically generates the correct values for each user.





SQL> select count(*) from XXXXX
  2  /

  COUNT(*)
----------
  60433806

Elapsed: 00:00:08.84

Execution Plan
----------------------------------------------------------
Plan hash value: 4050400992

---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 | 32262   (6)| 00:01:41 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| XXXXXXXXX |    59M| 32262   (6)| 00:01:41 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     142804  consistent gets
        287  physical reads
          0  redo size
        226  bytes sent via SQL*Net to client
        235  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select max(XXX) from XXXXX
  2  /

MAX(XXX)
----------
  82831129

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1243661939

----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |     6 |   185K  (5)| 00:09:41 |
|   1 |  SORT AGGREGATE            |           |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| XXXXXXXXX |    59M|   340M|            |          |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        228  bytes sent via SQL*Net to client
        235  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>





Bye Alessandro

[Updated on: Thu, 17 June 2010 04:54]

Report message to a moderator

Re: Auto increment performance [message #461337 is a reply to message #461178] Thu, 17 June 2010 08:07 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I am honored to have Mr. Mike Ault participate here.
Re: Auto increment performance [message #461339 is a reply to message #461244] Thu, 17 June 2010 08:14 Go to previous messageGo to next message
mikerault
Messages: 21
Registered: June 2010
Location: Alpharetta, Ga
Junior Member
I believe you have struck the nail on the head, if it is a single user database (no concurrency issues) then your method is fine, as soon as you get more than one user you are setting yourself up for many problems and performance issues.

How, using your method, do you be sure that two users don't get the same value without locking the entire table? That is the problem with the MAX() methods as well as the "We just create a table and store a value for each sequence" method, locking bites you every time. It is impossible for a database using those methods to scale beyond just a couple of users.

Go ahead and use your method in your single-user system, however, I doubt anyone is going to spend the bucks for an Oracle system and restrict its use to one person.
Re: Auto increment performance [message #461342 is a reply to message #461337] Thu, 17 June 2010 08:15 Go to previous messageGo to next message
mikerault
Messages: 21
Registered: June 2010
Location: Alpharetta, Ga
Junior Member
joy_division wrote on Thu, 17 June 2010 08:07
I am honored to have Mr. Mike Ault participate here.


Thanks! glad to be here!

Mike
Re: Auto increment performance [message #461437 is a reply to message #460998] Fri, 18 June 2010 00:09 Go to previous messageGo to next message
manjukmoorthi
Messages: 20
Registered: February 2010
Junior Member
I am very happy that I am able to get lot of suggestions from the senior members.
Though my initial purpose was to get a confirmation on the quoted methods now I can understand there are so many factors.

Being a front end developer, it is little tough for me to follow all the inputs in depth.

Now I think having concurency issues in multi user system, I should opt for the triggers. And also for maintainability. Hope I am correct on those.

I would definitely take all the suggestions and queries to the DBA and get it clarified.Thanks one and again
Re: Auto increment performance [message #461459 is a reply to message #461339] Fri, 18 June 2010 02:40 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
mikerault wrote on Thu, 17 June 2010 15:14

Go ahead and use your method in your single-user system, however, I doubt anyone is going to spend the bucks for an Oracle system and restrict its use to one person.


Yeah you got it! Laughing

I compare your first response with this one and I just notice that your only target is to be right in front of the others. It didn't matter that my first statement in the thread about the method was "If there is no concurrency in the context" (something different form being single user).

You may be a stated Oracle professional, but your idea that one method should fit in any faced situation is something I can't understand.

There are many complex cases where being the only one doing a job is an opportunity to reach great performance and it has nothing to do with single user. But you should know that.
Re: Auto increment performance [message #461524 is a reply to message #461459] Fri, 18 June 2010 08:01 Go to previous message
mikerault
Messages: 21
Registered: June 2010
Location: Alpharetta, Ga
Junior Member
No, my purpose was to stress to the OP that they need to consider concurrency in any solution they choose.
Previous Topic: redirect output of a command to variable or file
Next Topic: Left Join with fixed string failed?
Goto Forum:
  


Current Time: Wed Aug 20 11:00:47 CDT 2025