| Auto increment performance [message #460998] |
Wed, 16 June 2010 01:12  |
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 #461002 is a reply to message #461001] |
Wed, 16 June 2010 01:26  |
 |
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 #461004 is a reply to message #460998] |
Wed, 16 June 2010 01:29 |
 |
Michel Cadot
Messages: 68767
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 #461076 is a reply to message #461013] |
Wed, 16 June 2010 08:03 |
 |
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
|
Senior Member |
|
|
manjukmoorthi wrote on Wed, 16 June 2010 09:08I 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 |
cookiemonster
Messages: 13968
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Frank wrote on Wed, 16 June 2010 13:47I 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 |
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 |
 |
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 #461244 is a reply to message #461155] |
Thu, 17 June 2010 04:35 |
|
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
|
Senior Member |
|
|
mikerault wrote on Wed, 16 June 2010 22:36And 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 #461339 is a reply to message #461244] |
Thu, 17 June 2010 08:14 |
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 #461437 is a reply to message #460998] |
Fri, 18 June 2010 00:09 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
