| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: An easier way to write this SQL
A copy of this was sent to "Juan Carlos" <jc_va_at_hotmail.com>
(if that email address didn't require changing)
On Wed, 28 Jul 1999 20:19:07 -0700, you wrote:
>We have a table containing an Employee ID in the format 'E00000001'. It is
>monotonically incremented. I want to write a SQL statement to get the next
>available number .e.g 'E00000002'. We can't use a sequence for reasons I
>won't bore you with. The following works, but is there a more elegant way
>to write it?
>
>SELECT 'E' || LPAD(MAX(TO_NUMBER(SUBSTR(EXTERNAL_ID,2,9))+1),9,'0')
>FROM EXTID
>
>
thats ugly -- it'll have to full scan the table to find the max(to_number(substr(external_id,2,9))+1),9,'0') each time. as you get more records, this will slow down.
Ok, say you cannot use a sequence number for some reason. A better way to do this would be to have a table with 1 row and 1 column in it.
Create table EmpNos ( id number );
insert into empnos values (1);
The logic to get the next sequence would be:
update empnos set id = id+1;
select 'E' || to_char(id,'fm00000000') into :myNewIdfield from empnos;
That locks the row (update) and increments it. If you are using Oracle8.0, the syntax:
SQL> declare
2 myNewempno varchar2(25);
3 begin
4 update empnos set id = id+1 5 returning 'E' || to_char(id,'fm00000000') into myNewempno; 6 6 dbms_output.put_line( myNewempno );7 end;
PL/SQL procedure successfully completed.
can do the update and fetch in one call.
Say you have to keep the existing table structure (no new single row table) then you should consider rewriting the query as follows:
SQL> create table empnos( empno varchar2(25) primary key , data char(2000) );
Table created.
SQL>
SQL> begin
2 for i in 1 .. 1000 loop 3 insert into empnos values 4 ( 'E'|| to_char(i,'fm00000000'), 'x' ); 5 end loop;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
SQL> alter session set sql_trace=true
2 /
Session altered.
SQL>
SQL> SELECT 'E' || LPAD(MAX(TO_NUMBER(SUBSTR(empno,2,9))+1),9,'0')
2 FROM empnos
3 /
'E'||LPAD(
SQL>
SQL> select 'E' || LPAD((TO_NUMBER(SUBSTR(max(empno),2,9))+1),9,'0')
2 from empnos
3 /
'E'||LPAD(
The second query is the same as the first BUT since the max is way inside -- we can use an index on empno to find it fast. The tkprof report on the trace file shows me:
SELECT 'E' || LPAD(MAX(TO_NUMBER(SUBSTR(empno,2,9))+1),9,'0') FROM empnos
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 351 3 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.02 0 351 3 1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 TABLE ACCESS (FULL) OF 'EMPNOS'
********************************************************************************
select 'E' || LPAD((TO_NUMBER(SUBSTR(max(empno),2,9))+1),9,'0') from empnos
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 2 0 1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 INDEX (FULL SCAN) OF 'SYS_C0048272' (UNIQUE)
********************************************************************************
The original query reads the whole table (351 blocks in query), while the second query just reads the last block in the index basically - same answer much faster.
Either query should have:
update empnos set empno = empno where empno = ( select max(empno) from empnos );
to lock that last row (serialize access to the empnos table).
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 29 1999 - 06:58:18 CDT
 |
 |