연속된 년도 구하기 DB에 다음과 같은 데이터가 있다고 가정했을 때, 연속된 년도(혹은 어떤 숫자든)가 얼마나 있는지 확인하는 쿼리 ex ) [인원별 수상내역]테이블이 아래와 같을때 2년이상 연속으로 수상한 사람을 구하시오 수상년도 성명 2014 A 2013 A 2016 B 2020 B 2020 C 2021 C 2019 C => A(2013, 2014 2년연속 수상), B(2019, 2020, 2021 3년 연속 수상)를 출력해야 한다. 방법 핵심 아이디어는 "년도를 오름차순으로 정리하고 순서대로 인덱스를 매기는것" 그 후 현재년도(혹은 수상년도 컬럼에서 가장 max값)에서 수상년도값을 뺀값과 index를 더하는것 1. 수상년도와 이름을 오름차순으로 정렬 수상년도 성명 2013 A 2014 A 2016 ..

foreign 설정 종류 1. ON DELETE/UPDATE CASCADE : 부모 테이블의 값이 지워지면(업데이트되면) 자식 테이블의 해당 row도 지워(업데이트)진다 CREATE TABLE foreign_key_test ( id int NOT NULL, content varchar(2048) DEFAULT NULL, user_id int DEFAULT NULL, product_id int unsigned NOT NULL, PRIMARY KEY (id), FOREIGN KEY (product_id) REFERENCES product (id) on delete cascade ); 2. ON DELETE/UPDATE SET NULL : 부모 테이블의 값이 지워지면(업데이트되면) 자식 테이블의 해당 row의..

SQL update 에러 SQL에서 UPDATE시에 다음과 같은 에러가 나는 경우가 있다. Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 이는 update를 할 때 where 절에 key 컬럼을 사용하지 않았을 때 발생하는 경고 에러이다. 예시) update product set price = price-1000 where name like 'shoes%'; 해결책 key 칼럼을 사용하..

LPAD - 자릿수를 확보하고 빈칸에 원하는 문자열을 채워넣는 함수 - lpad('a',n,'b') => a:우선 입력될 문자열, n자리수, b: 빈칸에 채울 문자열 - ex) lpad('*',length(name)-1,'*')||substr(name,length(OWNER_NM)-1,1) * 3글자일때 : 홍길동 => **동 * 4글자일때 : 재갈공명 => ***명

라이브러리 캐시 - SGA(System Global Area) : 서버 프로세스, 백그라운드 프로세스가 공통으로 엑세스하는 데이터와 제어 구조를 캐싱하는 메모리공간 - 라이브러리 캐시 : SGA에 속해있는 내부 프로시저를 반복 재사용 할 수 있도록 캐싱해 두는 메모리 공간. 소프트파싱과 하드파싱 - SQL문을 실행하면, SQL이 라이브러리 캐시에 존재하는지 확인 - 이때 라이브러리 캐시에서 찾으면 곧바로 실행 => 소프트파싱 - 라이브러리 캐시에서 찾지 못하면, 최적화, 로우 소스 생성단계를 거침 => 하드파싱

사용법 주석( /* */)에 +를 붙이는 방식으로 사용 SELECT /* +INDEX(table col) */ col1, col2, col3, col4 FROM table where col1==option1 주의사항 1. 인자를 나열할 때는 콤마 사용가능, 힌트와 힌트사이에는 사용 불가 아래는 가능 /* INDEX(a,b) INDEX(c,d)*/ 아래는 불가능(첫번째 힌트만 유효) /* INDEX(A), INDEX(B) */ 2. 태이블을 지정할 때 스키마명까지 명시하면 안됨 SELECT /*+ FULL(scima.table) */ FROM table 3. alias를 사용했으면, 힌트에서도 반드시 alias를 사용해야함 SELECT /*+ FULL(EMP) */ FROM EMP E

SQL 최적화 과정 1. SQL파싱 1.1 파싱 트리 생성 : SQL문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성 1.2. syntax 체크 : 문접적 오류가 있는지 확인 (사용 or 순서 바르지 않거나 누락된 키워드 확인) 1.3. semantic체크 : 의미상 오류가 있는지 확인 (존재하지 않는 테이블 사용 or 사용한 오브젝트 권한 있는지) 2. SQL최적화 : 옵티마이저가 통계정보를 바탕으로 다양한 실행경로를 생성하여 비교한 후 효율적인 방법 선택 3. 로우소스 생성 : SQL옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포매팅 하는 단계 SQL 옵티마이저 최적화 단계 1. 사용자가 전달한 쿼리를 수행하는데 후보군이 될 만한 실행계획들을 찾음 2. 데이터 딕셔너..

문제 음식 종류별 즐겨찾기 수가 최대인 값들을 구하는 문제. 오답 SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, A.FAVORITES FROM (SELECT *, max(FAVORITES) as max_favorites FROM REST_INFO group by FOOD_TYPE) AS A order by FOOD_TYPE DESC 위 쿼리가 틀린 이유는 서브쿼리(SELECT *, max(FAVORITES) as max_favorites FROM REST_INFO group by FOOD_TYPE)에서 SELECT문을 "*, max(FAVORITES)"로 하면 max(FAVORITES)컬럼에는 FOOD_TYPE별 최댓값이 들어가지만, 나머지 컬럼들에는 FOOD_TYPE별 ..

1. sort aggregate - 전체 로우를 대상으로 집계를 수행할 때 - sort라는 표현을 썼지만 실제로 정렬하진 않는다 - sum, max, min, avg 등의 값을 구할 때 사용 - 집계당(sum, max, min, count 등) 변수를 만들고 데이터를 하나씩 넣으면서 집어 넣으면서 집계를 수행 ex) 값을 하나씩 넣으면서 sum은 더하고, max는 큰게 나오면 교치하고, min은 작은게 나오면 교체하고, count는 1씩 더해준다 2.sort order by - sort order by : 데이터를 정렬할 때 발생 - ex select colA from tableA order by colB 3. sort group by - 그룹별 집계를 수행할 때 나타난다. - ex) select c..

메모리 공간 종류 1. 메모리 소트(in memory sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것 2. 디스크 소트(to-dist sort) : 할당받은 sort area내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우 과정 [SGA] -> [PGA] -> [temp tablespace] --(merge)--> [PGA] 1. 소트할 대상 집합을 SGA버퍼캐시를 통해 읽어들인다. 2. sort area에서 정렬을 시도한다. 3. 양이 많을 때는 정렬된 중간집합을 temp 태이블스페이스에 임시 세그먼트로 저장한다. 4. 최종 결과집합을 얻기 위해 merge한다

인덱스 엑세스 조건 - 인덱스 스캔 범위를 결정하는 조건절 - 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치는 조건절 - 또한 인덱스 리프 블록을 스캔하다가 어디서 멈출지 결정하는데 영향을 미치는 조건절 - 첫번째 범위검색 조건절 이전까지 조건절이 인덱스 엑세스 조건이다 인덱스 필터 조건 - 테이블로 엑세스를 할지 결정하는 조건절 - 첫번째 범위검색 조건절 이후부터가 인덱스 필터 조건이다 그림

인덱스 탐색과정 - 인덱스 tree가 위와 같고, 조건절이 where c1='B' and c2=3이라고 가정하자 - 루트 블록에서 조건에 맞는건 (B,3)이지만 그 직전 데이터인 (A,3)의 리프블록에서 부터 스캔을 시작 해야 조건에 맞는 모든 데이터를 찾을 수 있다. - 스캔시작구간은 리프블록2의 네번째 데이터(B,3), 종료구간은 리프블록3의 두번째(B,3) - 루트블록속 데이터들의 하위블록들은, 루트블록속 데이터들보다 항상 크거나 같다. - 루트블록속 가장 왼쪽 레코드를 LMC(LeftMost Child)라고 한다.

부분범위 처리 - SQL 수행 후 전체 쿼리 결과 집합을 쉼 없이 연속적으로 전송하지 않고 사용자로 부터 "Fectch Call"이 있을 때 마다 나누어 전송하는 것 - 전체 결과 집합 중 아직 전송하지 않은 분량이 남아 있더라도 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 잠을 잠 - Array Size : 한번 전송할 때 데이터의 양 - array size 쿼리 show arraysize

인덱스 손익 분기점이란 - index range scan이 table full scan보다 느려지는 시점 - table full scan은 추출 건수와 상관없이 어차피 테이블을 전체 스캔하는 것이기 때문에 소요 시간이 항상 같다 - index range scan은 추출 건수가 많아짐에 따라 소요시간이 늘어난다.(인덱스 스캔 + 테이블 랜덤 엑세스, 테이블 랜덤엑세스로 인한 시간 소요가 주요원인) - 이때 CF(clustering factor)가 나쁘면 손익분기점이 빨리 오고, 좋으면 손익분기점이 늦게 온다

인덱스 클러스터링 팩터 - 클러스터링 팩터(clustering Factor) : 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여 있는 정도 - 높은 클러스터링 팩터의 예 - 낮은 클러스터링 팩터의 예

index range scan - 루트 인덱스에서 리프 블록까지 수직적으로 탐색 후 필요한 범위만 스캔하는 것 - 선두 칼럼을 가공하지 않은 상태로 조건절에 사용해야 함(그렇지 않으면 인덱스를 타지 않음) index full scan * 모든 인덱스를 수평적으로 탐색 * table full scan과 비교시 - 테이블면적이 크면 table full scan이 시간이 많이 걸리므로 index full scan이 유리 - 찾는 개수가 많으면 테이블 엑세스가 많아지므로 애초부터 table full scan이 유리 index unique scan - "=" 조건으로 탐색하는 경우에 작동 index skip scan - 다중 조건에서 첫번째 조건으로 skip할 수 있는 구간을 skip하며 찾는것 - 선두 칼..

자동 형변환 - SQL에서 형이 다른 두 변수를 비교할 때 자동으로 한 변수의 형태를 다른 변수에 맞게 변환해 주는 것 - 숫자와 문자를 비교하면 숫자가 된다(LIKE와 같이 문자를 비교하는 연산자를 사용하면 숫자가 문자가 된다) - 문자와 날짜를 비교하면 날짜가 된다 - 자동 형 변환에 의지하는 것 보다, 정확한 형태를 명시해 주는것이 좋음

수직적 탐색과 수평적 탐색 수직적 탐색 - 정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정 - 즉 인덱스 스캔 시작지점을 찾는 과정 - 루트 노드부터 시작해서 아래로 내려온다 수평적 탐색 - 수직적 탐색 후 찾고자 하는 데이터가 더 나타나지 않을 때 까지 인덱스 리프 블록을 수평적으로 스캔 - 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖음 (double linked list 구조) - 인덱스 스킨을 끝낸 후 테이블 스캔을 하기 위해 ROWID를 얻는다.