Home » SQL & PL/SQL » SQL & PL/SQL » Not able to understand this Query
Not able to understand this Query [message #327589] Tue, 17 June 2008 00:14 Go to next message
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
I have a table say T1 with a column of unique sequential numbers that begin at 1. When one inserts a new row, one must use a sequence number that is not currently in the column, that is Fill the gaps. If there are no gaps, then and only then one can use the next highest integer in the sequence.

create table T1
          CHECK (seq > 0);
junk CHAR(5) NOT NULL);

VALUES (1,'Tom'), (2,'Dick'), (4,'Harry'), (5,'Moe');

Here is the INSERT statement,i came across, where i am not able to understand how logic is working?

INSERT INTO T1 (seq_nbr,junk)
VALUES (CASE WHEN EXISTS              -- no gaps
                  ( SELECT 'no gaps'
                        FROM T1
                    HAVING COUNT(*) = MAX(seq_nbr))
              THEN ( SELECT MAX(seq_nbr) FROM T1 ) + 1
              ELSE ( SELECT MIN(seq_nbr)  -- gaps
                      FROM T1
                      WHERE (seq_nbr -1)
                          NOT IN (SELECT seq_nbr FROM T1)
                        AND seq_nbr > 0) - 1, 'Jen');

the thing is i am not able to understand how this query is executing and the logic.. Can someone please explain this to me?

Thank You,

[Updated on: Tue, 17 June 2008 00:15]

Report message to a moderator

Re: Not able to understand this Query [message #327599 is a reply to message #327589] Tue, 17 June 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64717
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't care it does not work when there is more than 1 user.
No gap does not exist unless you lock the whole table each time you want to insert a new row.

For the query, if you want to know how it works, analyze each part alone.

Re: Not able to understand this Query [message #327745 is a reply to message #327589] Tue, 17 June 2008 07:33 Go to previous message
Messages: 5
Registered: September 2007
Junior Member
As Michael said, Gaps in sequences cannot be avoided if performance and scalability (concurrent users) are both required. Either you lock entire table and go gap-free(performance goes for hit) or have gaps.

The logic is not hard to understand. If the count of records is same as the max number (since sequence starts with 1), there are no gaps in which case, next number should be max_seq_no + 1.

If there are gaps, then the count will be of course less than the max_sequence_no, in which case,select the lowest number-gap.
Previous Topic: VARRAY
Next Topic: Compare the value with previous row
Goto Forum:

Current Time: Mon Apr 24 00:42:57 CDT 2017

Total time taken to generate the page: 0.21369 seconds