안녕하세요.
아래 테이블 처럼, USER_ID 별로 data를 저장할때 SEQUENCE_ID를 증가 시켜야하는 테이블이 있습니다.
제가 구현한 쿼리는, USER_ID별로 ID가 가장 큰 값을 가져와서 + 1을 한 후에 더하는 것이었습니다.
그런데 데이터의 많아지면서, DB 성능의 큰 저하를 가져오게 되었습니다.
이런 경우에, 뭔가 trigger라던지 sequence 관리 테이블이라던지를 추가하여 쉽게 관리할 방법 없을까요?
조언을 듣고 싶습니다. 그리고 SEQUENCE_ID 증가 시킬때, 16진수로 계산하도록 DB단에서 처리 할 수 있을까요?
또한 이 SEQUENCE_ID는 0부터 65535까지 증가하고 다시 0부터 시작해야 합니다.
현재는 서버단에서 16진수 변환작업을 하지만, Oracle DB에서도 가능한지 궁금합니다.
ID | USER_ID | SEQUENCE_ID |
9 | USER_A | 5 |
8 | USER_B | 2 |
7 | USER_C | 2 |
6 | USER_A | 4 |
5 | USER_A | 3 |
4 | USER_B | 1 |
3 | USER_C | 1 |
2 | USER_A | 2 |
1 | USER_A | 1 |
WITH T (ID , USER_ID ) AS ( SELECT '9' ,'USER_A' FROM DUAL UNION ALL SELECT '8' ,'USER_B' FROM DUAL UNION ALL SELECT '7' ,'USER_C' FROM DUAL UNION ALL SELECT '6' ,'USER_A' FROM DUAL UNION ALL SELECT '5' ,'USER_A' FROM DUAL UNION ALL SELECT '4' ,'USER_B' FROM DUAL UNION ALL SELECT '3' ,'USER_C' FROM DUAL UNION ALL SELECT '2' ,'USER_A' FROM DUAL UNION ALL SELECT '1' ,'USER_A' FROM DUAL ) SELECT ID , USER_ID , ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY ID ) RN FROM T ORDER BY ID DESC
적절한 인덱스(user_id, id)가 없어서, 대체 인덱스(user_id) 만 이용하겠네요.
신규ID 에 대한 처리도 없네요? 이것도 별도 프로그램에서 처리하나보네요.
(user_id, id) 만드는것 보다는 (user_id, sequence_id)인덱스 만들고
다음과 같이 MAX 쿼리 이용하세요.
-- 숫자형 저장된 경우 -- SELECT NVL(MAX(sequence_id), 0) + 1 AS sequence_id_new FROM t WHERE user_id = #{value} ;
아. 그런데 이 쿼리는 10진수 숫자형일때 이야기 입니다.
16진수가 문자형으로 저장되어 있나요?
혹시 자리수 4자리 고정(0001)인가요? 가변자리(1)인가요?
고정자리를 사용하기를 권장합니다. 가변자리의 경우엔 MAX 로 가져오기가 어렵습니다.
가변 자리의 경우 문자열에 대함 MAX 를 가져올 수 없습니다.
또한, 초기화가 된다면? max 쿼리를 어차피 못 쓰겠네요.
인덱스를 (user_id, id, sequence_id) 로 해야겠네요.
(user_id, id, sequence_id) 보단 못하지만 (user_id, id) 도 괜찮습니다.
WITH t AS ( SELECT 9 id, 'USER_A' user_id, '5' sequence_id FROM dual UNION ALL SELECT 8, 'USER_B', '2' FROM dual UNION ALL SELECT 7, 'USER_C', '2' FROM dual UNION ALL SELECT 1, 'USER_D', 'FFFF' FROM dual ) SELECT TO_CHAR(NVL(MOD(TO_NUMBER(MAX(sequence_id), 'XXXX') + 1, 65536), 0), 'fmXXXX') x FROM (SELECT sequence_id , ROW_NUMBER() OVER(ORDER BY id DESC) rn FROM t WHERE user_id = 'USER_D' ) WHERE rn = 1 ;
그런데
굳이 시퀀스ID를 만들어 쓰는 이유는 뭔가요?
굳이 16진수를 쓰는 이유가 뭔가요?
굳이 사이클을 돌리는 이유가 뭔가요?
시퀀스ID를 통해서 또 다른 값(서버 로직에서)을 계산해서 같이 insert를 합니다..(line data check value)
16진수를 쓰는 이유는 저도 모르지만, 데이터가 순차적으로 문제 없이 생성 되었는지를 판단하는 수단인것 같습니다.
16진수를 쓰는 시스템을 처음 접합니다..@_@
사이클을 돌리는 이유는 저도 모르겠습니다 @_@...
다만 이렇게 생성 된 데이터들을 요구사항을 만든 서버쪽으로 전송하게 되는데, 이떄 sequence나 data check value가 맞지 않으면, error가 발생하게 되는 상황이었습니다.
이 데이터들은 저희 회사에서 사용하려고 쌓는 데이터는 아니고, 요구사항을 만든측에 유효한 데이터를 전송하기 위한 기능중 하나 입니다.(validator가 좀 깐깐합니다.)