create routine = Procedure를 definer 를 설정하여 만들고 정상적으로 작동하지 않는 문제가 발생했다. 상황 1. 일반 usr계정으로 프로시저 생성 후, definer 에 dba사용자권한이 있는 계정을 할당함. 2. 프로시저 내부에 information_schema.table를 이용하여 테이블 존재여부를 체크하는 로직이 존재 3. 그 후 turncate table 수행 결과 위의 2번 스키마 테이블에서 테이블이 존재하지 않는다고 나와, 정상적으로 로직이 수행되지 않음. 문제원인 definer의 계정은 dba권한이 있는 사용자로 excute권한밖에 기본적으로 가지고 있지 않았다. 그렇기때문에 dba권한이 있는 사용자 계정에서 select를 할때는 테이블을 명시적으로 지정해줘야 inf..

현상 Live 운영 테이블에 데이터가 존재하지 않고, 백업용 데이터로 만들어진 테이블에서 데이터를 산출해야하는 경우가 존재했다. 문제점 Live 테이블 데이터의 경우, 기본적으로 index나 partition이 되어있어 대량의 데이터를 산출할때 해당 조건들을 이용해서 산출을 진행했다. 하지만, 백업용 데이터이기때문에 별도로 구성된 index나 partition이 되어있지 않아 index힌트 이용할 경우 추출 시간이 오래 걸려 active session이 오래 살아 있어 문제가 될 가능성이 존재한다. USE_HASH 사용 USE_HASH 힌트를 사용해 산출 시간을 단축시켰다. USE_HASH 힌트는 Oracle에서 사용되는 옵티마이저 힌트 중 하나로, 해시조인을 강제로 사용하도록 지시하는 역할을 한다. 해..

MYSQL vs Oracle 인덱스 차이 MySql과 Oracle은 인덱스 동작 방식에 있어서 약간의 차이가 존재한다. MySql에서는 일반적으로 한번의 쿼리에서는 하나의 인덱스만 사용됩니다. 즉, 여러 인덱스가 걸려있어도 MySQL의 옵티마이저가 쿼리 실행 계획을 결정할때 인덱스의 선택도(카티널리티)를 고려하여 최적의 인덱스 하나만 선택한다. 그렇기때문에 복수 컬럼을 동시에 인덱스를 태우고자 할 경우에는 복합 인덱스 하나만 사용해야 된다. 반면 Oracle은 비교적 더 복잡한 옵티마이저를 갖고 있어, 쿼리 실행 계획을 수립할때 다중 인덱스 조인이나 다중 칼럼 인덱스를 통해 여러 인덱스를 동시에 활영 할 수 있다. 다만, 무조건 Oracle에서도 모든 쿼리에서 여러 인덱스를 동시에 사용하는 것이 아니라 ..

타 업체 혹은 외부에서 데이터를 가져와서 사용할때 인코딩 방식이 다른 경우가 존재하기때문에, 내부적으로 쿼리에서 인코딩을 맞출 필요가 존재합니다. 그럴때 사용하는게 CONVER함수입니다. CONVER함수는 CAST와 비슷하지만, 문자열 인코딩을 설정할 수 있어 조금 더 MYSQL에서 특화된 함수라고 볼 수 있습니다. CONVERT 사용법 기본적으로 CONVERT함수는 CONVERT(EXPRESSION , DATA_TYPE) 으로 표현식이 구성되어있습니다. 하지만, 문자열 인코딩 값을 설정하는 경우에는 콤마 대신 USING 키워드를 사용해 데이터 타입을 구분합니다. CONVERT(EXPRESSION USING DATA_TYPE) SELECT CONVERT('안녕하세요 123 ABC' USING 'euckr..

MYSQL에서 WITH 구문절에 RECURSIVE 키워드를 이용해 재귀 CTE를 구성할 수 있습니다. 기본 재귀 코드 이러한 재귀 CTE는 보통 연속된 값들을 구현하기 위해서 사용합니다. WITH RECURSIVE CTE1 AS ( SELECT 0 RNUM FROM DUAL UNION ALL SELECT RNUM+1 FROM CTE1 WHERE RNUM < 20 ) SELECT * FROM CTE1 위의 쿼리를 수행하게 되면 0부터 20까지의 연속된 숫자를 가지고 있는 데이터를 생성 할 수 있습니다. 타 컬럼 추가 재귀 코드 추가적으로 연속된 값 말고도, 다른 컬럼을 생성할 수 있습니다. 대신에 타 컬럼을 추가할때는 크기 값을 주의 해서 사용해야됩니다!! WITH RECURSIVE CTE1 AS ( SE..

Mysql에서는 WITH 구문 내부안에서 ORDER BY절을 이용하여 정렬을 할 수가 없습니다. 하지만, 임시테이블 내에서 정렬된 순서라든지 값이 필요한 경우가 존재하기때문에 ROW_NUMBER() 함수를 이용해 정렬에 사용합니다. 기본적으로 테이블 데이터들은 PK값이나 인덱스 값을 기준으로 오름차순 정렬되어 있는 경우가 많습니다. 그로인해 정렬을 지정안하고 WITH 구문절에서 데이터를 가지고 와도 오름차순 정렬되어 있는 경우가 존재하지만, 내림차순 정렬을 지정할 경우에는 아래와 같이 ROW_NUMBER 함수를 이용합니다. ROW_NUMBER 이용하기 WITH TEMP_TABLE ( SELECT COL1 , COL2 , ROWNUMBER() OVER(ORDER BY COL2) AS ROW_NUM -- 순..

CTE란? Common Table Expression 의 약어로, 일반적으로 공통 테이블 표현식이라고 번역됩니다. sql 쿼리내에서 임시로 정의된 이름이 지정된 집합으로 간주되는 가상의 테이블로 복잡한 쿼리를 간결하게 작성하고, 재사용 가능한 부분을 정의하며 가독성을 향상시킬 수 있습니다. 이러한 CTE를 Oracle과 Mysql8.0 이상에서는 WITH 구문절로 제공하고 있습니다. WITH(CTE) 구문절 사용법 WITH TABLE_NAME AS ( SELEECT COL1,COL2 FROM TABLE WHERE CONDITION ) SELECT * FROM TABLE_NAME WHERE COL1 = VALUE; 위의 형식으로 ' WITH 테이블명 AS (SELECT ~) ' 의 형식으로 Oracle과 ..

Mysql에서 insert문을 수행했을때 컬럼값과 지정된 value의 갯수가 일치하지 않을때 아래의 오류가 발생한다. Column count doesn't match value count at row 1 주로 ' insert into table select * from table2 ' ~ 를 수행했을 때, 테이블의 갯수와 value의 갯수가 동일시되지 않아 발생하는 오류이므로 아래의 쿼리를 통해 table 컬럼 개수를 통해 확인 후 해결 할 수 있다. SELECT COUNT(*) FROM information_schema.columns WHERE table_name='테이블명';

SELECT * TABLE 로 쿼리를 조회 했을때 COLUMN 들을 확인 할 수 있는데, 몇개의 컬럼이 존재하는지 확인 하고싶을 때가 존재합니다. 아래의 쿼리를 통해 TABLE COLUMN 갯수를 확인 할 수 있습니다. SELECT COUNT(*) FROM information_schema.columns WHERE table_name='테이블명';

Mysql에서 제공하는 DATE_FORMAT 함수는 'DATE' , 'DATETIME' , 'TIMESTAMP' 등의 데이터 타입을 원하는 형식으로 출력하고자 할때 사용하는 함수입니다. 출력형식은 아래와 같이 다양하게 존재하여 날짜값을 다양한 형식으로 출력이 가능합니다. %Y : 4자리 연도를 출력합니다. ex) 2022 %y : 2자리 연도를 출력합니다. ex) 22 %m : 월을 2자리 숫자로 출력합니다. ex) 01 %M : 월의 이름을 출력합니다. ex) February %d : 일을 숫자로 출력합니다. ex) 15 %D : 일을 출력하되, 해당 일이 몇번째 일인지 영어로 출력합니다. ex) 15st %h : 12시간 단위로 시(hour)를 출력합니다. ex) 14 %H : 24시간 단위로 시(h..