Chance of duplicate on single select max into statement

best practicesoracleprimary-keysequence

Against my will, I am told to use the following method to generate a new pk value.

INSERT  
    INTO
        SERVER_MONITERING(SM_SEQ, CPU_USE, MEMORY_BASE, MEMORY_USE, DISK_BASE, DISK_USE, SERVER_IP, SERVER_NAME, OBSTACLE_YN, CHECK_TIME)
    VALUES((SELECT MAX(SM_SEQ)+1 FROM SERVER_MONITERING), #cpuStat.combinedUsed#, #memStat.total#, #memStat.used#, #diskStat.size#, #diskStat.used#, #serverIP#, 'KW', 'N', TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'))

I told him it still looks like there could be duplicate SM_SEQ values.

However, I don't know for sure whether this approach will eliminate duplication or not.

Will the approach above eliminate the chance of duplicate values ?
Is this a good practice ?
Will a sequence.nextval eliminate the chance of duplicate values ?
What is the best practice ?

Edit

Sorry, I forgot to mention.
There are multiple devs and their own code that inserts into this table and they all use select max()+1. I am a "guest" dev at this company. So I have little influence on their decision. If I switch to using a sequence only by myself, other devs will still select max()+1 and there will be collision between the two approaches.
Probably, I would have to tell all of them why even nesting a select max()+1 won't solve their problem, if it truly would not.

Best Answer

Assuming that you allow multiple users in your application, max()+1 will guarantee that you will get duplicate values. Therefore, it is a bad practice. It's also less efficient but that is a lesser concern. If you have two sessions, session 1 gets the max() + 1, and session 2 tries to get the max() + 1 before session 1 commits (or rolls back), session 2 will get the same value that was given to session 1.

Using a sequence will eliminate the possibility of duplicates. Use a sequence.

Related Solutions

Combine multiple queries into single query

You can use UNION [ALL] to combine rows from multiple queries, as long as the number of columns are the same and the columns are compatible, if not of the exact same types.

SELECT 'Driver' as SourceTable, Driver_No, Name, Available 
  FROM Driver
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Freight', Freight_No, Freight_Wagon_Type_Name, Available
  FROM Freight
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Locomotive', Loco_No, Loco_Name, Available
  FROM Locomotive
 WHERE Available = 'Y';

(added SourceTable column per a_horse_with_no_name's suggestion)

Note that the column names are taken from the first query in the UNION, which means you end up with (SourceTable, Driver_No, Name, Available). You can always alias them as required.

Select which has max date or latest date

Your current query is not giving the desired result because you are using a GROUP BY clause on the PERSON_ID column which has a unique value for both entries. As a result you will return both rows.

There are a few ways that you can solve this. You can use a subquery to apply the aggregate function to return the max(LAST_UPDATE_DATE_TIME) for each SCHOOL_CODE:

select s1.LAST_UPDATE_DATE_TIME,
  s1.SCHOOL_CODE,
  s1.PERSON_ID
from SCHOOL_STAFF s1
inner join
(
  select max(LAST_UPDATE_DATE_TIME) LAST_UPDATE_DATE_TIME,
    SCHOOL_CODE
  from SCHOOL_STAFF
  group by SCHOOL_CODE
) s2
  on s1.SCHOOL_CODE = s2.SCHOOL_CODE
  and s1.LAST_UPDATE_DATE_TIME = s2.LAST_UPDATE_DATE_TIME;

See SQL Fiddle with Demo

Or you can use use a windowing function to return the rows of data for each school with the most recent LAST_UPDATE_DATE_TIME:

select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
  select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
    row_number() over(partition by SCHOOL_CODE 
                        order by LAST_UPDATE_DATE_TIME desc) seq
  from SCHOOL_STAFF
  where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;

See SQL Fiddle with Demo

This query implements row_number() which assigns a unique number to each row in the partition of SCHOOL_CODE and placed in a descending order based on the LAST_UPDATE_DATE_TIME.

As a side note, the JOIN with aggregate function is not exactly the same as the row_number() version. If you have two rows with the same event time the JOIN will return both rows, while the row_number() will only return one. If you want to return both with a windowing function, then consider using the rank() windowing function instead as it will return ties:

select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME
from
(
  select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME,
    rank() over(partition by SCHOOL_CODE 
                        order by LAST_UPDATE_DATE_TIME desc) seq
  from SCHOOL_STAFF
  where STAFF_TYPE_NAME='Principal'
) d
where seq = 1;

See Demo

Best Answer

Related Solutions

Combine multiple queries into single query

Select which has max date or latest date

Related Question