Stacy, tom kyte's definitive oracle book references unwise java programmers who adopt the max(id+1) plan.
it is not scalable, requires locking, forces full scans of the entire id column (if it is indexed) or full table scans if it is not. I am sure I am missing many of the myriad reasons he discusses why this is such a bad idea, especially since oracle provides sequences. There are numerous reasons not to use the strategy you have. gaps are the least of your concern. ever consider having two items with the same id? that is what would happen under your approach. we both do that query before an insert or commit of the insert takes place, and we both get the same number back from the database. unless you are locking the table of course and then... pasting from asktom.oracle.com: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4343369880986 another great gap free article: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1508205334476 do a search for gap free on this site for many more explanations of why this is no good. people try to defend it. they use a different approach than you, since i don't believe the dbi can retrieve values from an update .... returning statement (which at least doesn't have to do a full table scan). ******************(from tom kyte)************* well, if you use a gap free approach -- you know what'll happen in this case? NO ONE ELSE IN YOUR ENTIRE SYSTEM WILL BE ABLE TO WORK, that is what. Why? cause this session with uncommitted data will have the id generating table locked. Sigh, I hesitate to show you this, but here goes. Gap free, non-concurrent, highly NON-scalable: ops$[EMAIL PROTECTED]> create table ids ( name varchar2(30), id number ); Table created. ops$[EMAIL PROTECTED]> ops$[EMAIL PROTECTED]> create or replace function get_nextval( p_name in varchar2 ) return number 2 as 3 l_id number; 4 begin 5 update ids set id = id+1 where name = upper(p_name) 6 returning id into l_id; 7 8 if ( sql%rowcount = 0 ) 9 then 10 raise_application_error( -20001, 'No such id name ' || p_name ); 11 end if; 12 return l_id; 13 end; 14 / Function created. ops$[EMAIL PROTECTED]> ops$[EMAIL PROTECTED]> exec dbms_output.put_line( get_nextval( 'foo' ) ) BEGIN dbms_output.put_line( get_nextval( 'foo' ) ); END; * ERROR at line 1: ORA-20001: No such id name foo ORA-06512: at "OPS$TKYTE.GET_NEXTVAL", line 10 ORA-06512: at line 1 ops$[EMAIL PROTECTED]> ops$[EMAIL PROTECTED]> insert into ids values ( 'FOO', 0 ); 1 row created. ops$[EMAIL PROTECTED]> exec dbms_output.put_line( get_nextval( 'foo' ) ) 1 PL/SQL procedure successfully completed. ops$[EMAIL PROTECTED]> exec dbms_output.put_line( get_nextval( 'foo' ) ) 2 PL/SQL procedure successfully completed. Now, when you want to reset, just update ids set id = 0 where name = 'whatever'. To get an autoincrement column, I would: create table T ( x int primary key, .... ); create sequence t_seq; create trigger t_trigger before insert on T for each row begin if ( :new.x is null ) then select t_seq.nextval into :new.x from dual; end if; end; / to automatically populate when a value for X was not supplied (allowing me to easily override the "auto" part of it) or I would: create table T ( x int primary key, .... ); create sequence t_seq; and then: insert into t ( x, ... ) values ( t_seq.nextval, .... ); The nice thing about sequences is when you are populating a parent/child table -- you have immediate access to t_seq.CURRVAL which returns the value of the last NEXTVAL you selected. __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com
