Incrementing Sequence number
596777Sep 7 2007 — edited Sep 7 2007Hi. I am reading up on Oracle's sequence number and I have a couple of questions.
1. From what I've read online, to increment a sequence number we use nextval just like the query below:
insert table_customer
(customer_seq, customer_name, customer_address)
values (select customer_seq.nextval,
'Customer ABC',
'Address XYZ');
My questions is will the insert query below will always produce the same results as above (assuming the DML are adhoc scripts, which are executed only once and no other scripts are executed for the same table).
insert table_customer
(customer_seq, customer_name, customer_address)
values ((select max(customer_seq) + 1 from table_customer),
'Customer ABC',
'Address XYZ');
2. Another question is on how nextval works. If for example I have this sequence:
seq#
1 - Row 1
2 - Row 2
3 - Row 3
5 - Row 4
6 - Row 5
8 - Row 6
There are skips in the sequence number (missing 4 and 7). When I do an insert to the table using the nextval will it insert a record with seq no = 4 (which is one of the missing seq no). Assuming that NOCACHE is set. I believe it will create seq no. 9? but I am not 100% sure.
Thanks a lot for you help. I really appreciate it.