R&D SQL 엔지니어 교육

<< What's Batter >>

1. Measurement tool : TPC-H

[ TPC-H schema ]

* www.tpc.org

* OLAP(On-Line Analysis Processing)

* global online shopping mall

[ TPC-H Query ]

22 Complex Query

• Access many tables
• Access whole data
• Each query requires different optimization techniques

참고 : Scale Factor(SF)

2. TPC-H Scale Factor 10 (SF-10)

[ TPC-H SF-10 data set ]

SF-10 : about 100M rows, 20 GB size

3. DBA dillema : sharding key

[ DBA dillema : sharding key ]

-> 위처럼 3개의 테이블이 있고 Query A,B 처럼 Join Query가 있을 경우 Orders 테이블에 대하여 Shard key를 뭐로 할 것인가?

[ Worst Case 1 ]

--> shard key는 partition key가 아니다. shard key는 실제 물리적인 Data의 저장 공간을 DB에서 구분 시켜 주는

값으로 Partition 테이블의 논리적인 분리 공간하고는 다르다. 실제 Orders에 o_orderdate 컬럼을 partition 테이블의 

partition key로 하여도 문제가 없는 것은 동일한 공간에 논리적으로만 나눈  것으로써 실제 key에 물리적 공간이 구분

되는 것이 아니기에 partition 테이블은 조인데는 컬럼이 partition key가 아니여도 상관이 없지만 Goldilocks Cluster는 

shard key가 데이터가 존재하는 물리적 공간을 구분해 주는 것이므로 해당 key에 따라서 성능 차이가 발생 할 수 있다.

[ worst case2 ]

--> orders 테이블에 o_orderkey를 shard key로 했다면 조인 처리 시 customer도 product도 아닌 어디에도 포함되지 않은 key이기

때문에 일단 Query A, Query B 전부 Orders을 구하기 위하여 Remote SQL이 수행 되어야 하기 때문에 안좋다.

[ workst case 3 ]

drop table customer; drop table orders; drop table product; CREATE TABLE customer (     c_custkey     INTEGER   , c_name        VARCHAR(25)   , c_address     VARCHAR(40)   , c_nationkey   INTEGER   , c_phone       CHAR(15)   , c_acctbal     NATIVE_DOUBLE   , c_mktsegment  CHAR(10)   , c_comment     VARCHAR(117) )sharding by range ( c_custkey ) shard s1 values less than(1000) at cluster group g1, shard s2 values less than(2000) at cluster group g2, shard s3 values less than(MAXVALUE) at cluster group g1; alter table customer add primary key(c_custkey); insert into customer values(1,1,1,1,1,1,1,1); insert into customer values(1001,1,1,1,1,1,1,1); CREATE TABLE orders (     o_orderkey     INTEGER   , o_custkey      INTEGER   , o_orderstatus  CHAR(1)   , o_totalprice   NATIVE_DOUBLE   , o_orderdate     DATE   , o_orderpriority CHAR(15)   , o_clerk        CHAR(15)   , o_shippriority INTEGER   , o_comment      VARCHAR(79) ) sharding by range(o_orderkey, o_custkey) shard s1 values less than(1000,100) at cluster group g1, shard s2 values less than(2000,200) at cluster group g2, shard s3 values less than(MAXVALUE,MAXVALUE) at cluster group g2; alter table orders add primary key(o_orderkey,o_custkey); insert into orders values ( 1,1,1,1,sysdate,1,1,1,1 ); insert into orders values ( 1001,102,1,1,sysdate,1,1,1,1 ); CREATE TABLE product (     p_prodkey     INTEGER   , p_name        char(15)   , p_price       NATIVE_DOUBLE ) sharding by range ( p_prodkey ) shard s1 values less than(1000) at cluster group g1, shard s2 values less than(2000) at cluster group g2, shard s3 values less than(MAXVALUE) at cluster group g1; alter table product add primary key( p_prodkey ); insert into product values(1,1,1); insert into product values(1001,1,1);

-> 조인 관련 되어 복합 Shard Key를 사용 하면 안된다. 위와 같이 조인 처리 시 해당 Key가 어디인지 모르기에 전부

Remote SQL를 수행 해야 한다. 복합 Shard Key를 사용 하는 이유는 해당 Key에 대하여 정확하게 어디 인지 구분이 되고

해당 Key 와 and 조건으로 해당 Data가 쌓이는 사이트 상황에서 사용하기 위하여 처리 하는 것이다.

select count(*) from customer, orders where c_custkey = o_custkey and c_nationkey=1;

 

[ DBA dillema : your choise ]

-> 기본 적으로 Shardkey는 Join 조건의 컬럼을 하는 것이 좋다. 다만 위와 같이 Orders 테이블에 각각의 Join 처리가 수행 되야

할 거라면 DBA가 업무 적으로 어느 쪽의 테이블이 Remote Query가 수행 되었을 때 최소한의 Access가 일어 나는지 보고 Orders

테이블의 컬럼에 대한 Shard Key를 선택 해야 한다.

[ Quiz ]

* 왜 partition key 가 sharding key 로 적합하지 않은가?

partition key는 하나의 segment를 여러개로 분할하여 같은 공간안에서 사용 하는 것이고 shard key는 각각의

다른 segment에 각각의 shard key에 맞는 데이터를 갖는 것이여서 구조적으로 의미 자체가 틀리다.

* 왜 composite index 와 같이 composite sharding key 는 적합하지 않은가?

shard key를 composite로 갖게 되면 해당 shard key가 하나가 아닌 여러개의 Member로 나누어지기

때문에 실제 shard key본연의 일을 하지 못한다. 그렇다면 왜 composite shard key를 만들었을까

shard key 중에 conuntry, city( 한국-서울, 미국-뉴욕) 처럼 짝으로 해당 shard 값을 internal node

로 묶고 싶을 때가 있기 때문이다.

* 왜 o_orderkey 는 sharding key 로 적합하지 않은가?

-> customer, product table 모두 포함 되지 않은 shard key이기 때문에 조인 조건 시 무조건 remote sql이

수행 되게 된다.

* 왜 o_custkey 를 선택하면, A 는 좋고 B 는 안좋은가?

-> customer table 과 조인 할 때는 o_custkey를 사용하면 remote가 안일어나고 B를 선택하게 

되면 remote Query가 일어나니 안좋은거지 뭐있겠냐.

* 왜 에러가 아니지? SUM(c_nationkey + c_acctbal) GROUP BY c_nationkey

-> group by가 있다면 select 컬럼에 올 수 있는 것은 aggregation 또는 gruop by에 나열 된

    컬럼만 올 수 있다.

create table t1(c1 int, c2 int);

gSQL> select * from t1;

C1 C2

-- --

1  1

2  2

2  3

3  3

gSQL> select sum(c1+c2) from t1 group by c1;

SUM(C1+C2)

----------

         2

         9

         6

-> c1 group 별로 sum을 해서 데이터 값을 반환 한 것을 알 수 있다.

- Relation

* 흔히 table이라 불리며 row(행)와 column(열)로 구성된 데이터 집합이다.

* row

  * record, tuple로 불리기도 하며 해당 relation의 일련된 데이터 집합이다.

  * 동일한 테이블에서 모든 row는 동일한 구조를 가지고 있다.

* column

  * attribute, field로 불리기도 하며 해당 relation의 각 열을 일컫는다.

  * join, where 조건, 사칙연산, Aggregation 등의 결과는 모두 relation이라 할 수 있다.

- Aggregation

* 특정 연산을 취함으로써 하나의 결과로 반환한다.

  예) SUM, AVG, COUNT, MIN, MAX, ...

- Group by

* GROUP BY keyword 뒤에 기술된 column, column list, value 등을 이용해 그룹화한다.

* 그룹화할 column은 FROM 절에 기술된 table 들의 column중 하나이며 해당 column 값으로 그룹화한다.

* 주로 relation의 특정 column들을 그룹화하여 Aggregation과 같이 사용한다.

* GROUP BY 를 사용하는 쿼리문의 SELECT 뒤에는 다음과 같은 column들만 올 수 있다.

  * GROUP BY keyword 뒤에 기술된 column

  * Aggregation 함수( GROUP BY 뒤에 기술되지 않은 column도 Aggregation에 사용할 수 있다. )

    

- Error

  SELECT

       c_nationkey + c_acctbal

     , SUM( c_acctbal )

  FROM customer

  GROUP BY c_nationkey;

----------------------------------------------------

위에서 언급했듯, Group by를 사용하면 SELECT 에는 Group by 뒤에 온 컬럼 혹은 aggregation만 올 수 있다.

하지만 현재 쿼리문에는 c_nationkey와 c_acctbal의 합을 기술했으므로 에러를 출력한다.

- Success

  SELECT

       SUM( c_nationkey + c_acctbal )

  FROM customer

  GROUP BY c_nationkey;

----------------------------------------------------

비록 SUM 안에 c_acctbal 컬럼이 있지만

이는 결국 aggregation 이므로 정상적으로 동작한다.

* COUNT(c_nationkey) 의 결과가 왜 25 건, 왜 1건, 결과값의 의미는?

aggregate function(집계함수)는 본래, 여러개의 input으로 하나의 output을 발생시킨다.

이 aggragate function이 group by와 함께 동작할 경우 처리 방법이 다르다.

count나 sum는 전체 테이블을 포함하는 값이 아니라 각 그룹을 포함하는 값을 생성한다.

max나 min과 같은 함수는 group 내의 가장 작은 값이나 가장 큰 값만 찾기 때문에, 최대/최소치 1개만 출력한다..

max가 없는 구문에서는 group별로 출력 하게되고,

max가 있는 구문에서는 group내 가장 큰 sum값과 count(총 갯수)를 출력한다.

SELECT COUNT(c_nationkey) , SUM(c_acctbal) FROM customer GROUP BY c_nationke;

-> customer 테이블에 대해 c_nationke을 그룹으로 각 group의 c_nationkey의 갯수와 c_acctbal의 합을 출력하라.

SELECT COUNT(c_nationkey) , MAX( SUM(c_acctbal) ) FROM customer GROUP BY c_nationkey;

-> customer 테이블애 대해 c_nationke을 그룹으로 group 내 c_nationkey 의 갯수와 c_acctbal의 sum 중 가장 큰 값을 출력하라

* UNION ALL 과 ORDER BY error 와 success 인가? (고객이 원하는 결과를 만드세요.)

order by 절은 항상 select 문의 제일 마지막에 위치하여 select 된 데이터를 지정된 기준에 따라 정렬한다.

첫번째 쿼리에서 order by 절 이후에 union 을 사용하여 syntax error 발생 두번째 쿼리는 두 select 문을

union all 한 이후에 order by 로 정렬 하기 때문에 정상 적으로 출력 된다.

정상적인 형태로 출력 되기 위해서는 서브쿼리를 이용하여 각 select 문에서 order by 로 정렬 이후에 union all

구문으로 합친거나 혹은 그냥 괄호로 쿼리를 분할 한다.

select r_name from

(select r_name from region order by 1)

union all

select r_name from

(select r_name from region order by 1)

(select r_name from region order by 1)

union all

(select r_name from region order by 1)

* SELECT .. "SELECT" .. ... 왜 parser 에서 error 가 안나지?(no rows 가 나오도록 CREATE TABLE 하시오.)

identifier 는 ordinary identifier 와 delimited identifier로 나뉜다. ordinary identifier는 내부적으로 모든 문자를

대문자로 치환하여 사용되기 때문에 입력시 대소문자를 구분하지 않는다. delimited identifier는 " "로 감싸서 사용하며

내부적으로 " "에 쓰인 그대로 사용, 대소문자 구분. select, from 등의 token과 동일한 컬럼, 테이블 명을 사용하면

parser에서 identifier가 아닌 token으로 인식하기 때문에 syntax error 발생. -> " "를 사용하여 토큰과 동일한 컬럼 및

테이블명을 delimited indentifier로 정의 하였기 때문에 syntax error가 발생하지 않음.

따라서 Quiz 4-2 와 같은 결과를 얻기 위해서는 테이블을 생성 할 때 테이블명 및 컬럼명의 syntax error를 피하기 위해서

delimited identifier를 사용하여 테이블을 생성한다.

* seq.nextval > 0 는 왜 에러인가?

- 정의

sequence pseudo 컬럼은 실제 수행 시 동적으로 생성되는 컬럼인데 아직 생성 되지도 않은 컬럼의

값에 조건을 먼저 비교 할 수 는 없기 때문이다. 

- oracle에서도 동일하게 오류가 발생 함.

 ORA-02287: 시퀀스 번호는 이 위치에 사용할 수 없습니다.

  ORA-02287: sequence number not allowed here

- 오류 나는 원인

오라클에서 INSERT 구문 작성 시, 위와 같은 에러를 보는 경우가 있습니다. 데이터를 INSERT할 때,

SELECT 한 결과를 바로 INSERT할 때 발생하는 데, 만날 수 있는 오류입니다.(즉 INSERT INTO SELECT 구문)

아래 쿼리는 SELECT GROUP BY 한 결과를 바로 INSERT 할 때 발생한 프로시져의 일부를 발췌한 예시입니다.

(설명 이해를 돕기위해 임의로 작성한 예시 코드입니다.)

GROUP BY 한 SELECT 결과에서는 시퀀스(Sequence) 를 함께 사용할 수 없습니다. 시퀀스 생성을 허용하는

범위를 넘어서는 경우가 몇가지 있는데, 먼저 GROUP BY절을 포함한 SELECT의 결과 컬럼으로 사용하는 경우입니다.

단일 SELECT 하는 경우 시퀀스를 얻어오는데에 문제가 없지만, GROUP BY 가 적용되는 경우에는 논리적으로 시퀀스

값을 얻어올 수 없습니다.

INSERT INTO tbl_test1 (data_id, name_id, company_id, cost_value, date_write,data_status)

SELECT tbl_test1_seq.nextval, name_id, company_id, MAX(cost_value), date_create, db_sts

FROM tbl_test2

WHERE work_number = p_work_id    -- p_work_id 는 프로시져 내 변수

AND db_sts IS NOT NULL

GROUP BY name_id;

문제를 해결하기 위한 간단한 방법으로는 GROUP BY - SELECT 결과와 시퀀스를 분리하면 됩니다.

INSERT INTO tbl_test1 (data_id, name_id, company_id, cost_value, date_write, data_status)

SELECT tbl_test1_seq.nextval, name_id, company_id, good_cost_value, date_create, db_sts

FROM (SELECT name_id AS name_id, company_id AS company_id, MAX(cost_value) AS good_cost_value, date_create, db_sts

FROM tbl_test2

WHERE work_number = p_work_id    -- p_work_id 는 프로시져 내 변수

AND db_sts IS NOT NULL

GROUP BY name_id

)

GROUP BY - SELECT 된 결과를 먼저 구하고, 그 결과를 다시 SELECT 할 때, 시퀀스와 함께 SELECT 하면 됩니다.

위와 같은 시퀀스 오류가 발생하는 이유를 간단히 설명하면 시퀀스는 오라클에서 동적으로 생성 해주는 키 값을 얻어오는

것인데, 그 값이 생성되기도 전에, 그 결과 집합을 GROUP BY로 얻는 것 자체가 논리적으로 맞지 않기 때문입니다. 그러므로

GROUP BY가 끝난 결과 데이터를 받아와 순차적으로 시퀀스를 붙여주도록 만든 쿼리가 바로 위의 쿼리입니다.

마지막으로 한가지만 더 덧붙이자면..

그럼 오라클은 왜 알아서 먼저 시퀀스를 할당하고, Group By하면 되는데 그렇지 않는냐 할 수도 있는데, 최종적인 결과 집합이

Group By 에 의해 결정되는데, Group By 하기 이전의 임시적인 SELECT 집합은 결과 집합도 아닌데, 해당 집합을 대상으로 시퀀스를

먼저 할당하는 것은 논리적으로 모순입니다.

* @ DML 은 에러이고, WHERE pseudo DML 은 success 이다.(R&D 는 무슨 생각으로 이랬을까?)

@구문은 select만 지원하고 i/u/d 구문은 지원 하지 않는다. 

delete from lilneitem@G2N1은 에러 처리

delete from lineitem WHERE lineitem.CLUSTER_MEMBER_NAME = 'G2N1'은 정상 처리

-> 왜 그런지는 정확히 모르겠음.

[ 참고 / Pseudo-column ]

* pseudo 컬럼 정의

pseudo 컬럼은 가상 컬럼으로 실제 테이블에 저장 된 컬럼이 아닌 수행 시 생성 되는 가상 컬럼을 의미한다.

* pseudo point

가상 컬럼 종류 : sequnece, ROWNUM, ROWID 등이 있다.

* pseudo column 정의

의사컬럼Pseudo-column이란 테이블의 컬럼처럼 동작하지만 실제로 테이블에 저장되지는 않는 컬럼을 말한다. SELECT 문에서는

의사컬럼을 사용할 수 있지만, 의사컬럼 값을 INSERT, UPDATE, DELETE 할 수는 없다. 2장의 ‘06.시퀀스’ 절에서 학습했던

NEXTVAL, CURRVAL도 의사컬럼의 일종이다. 대표적인 의사컬럼의 종류를 열거하면 다음과 같다.

CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, LEVEL

NEXTVAL, CURRVAL

시퀀스에서 사용하는 의사컬럼이다.

ROWNUM, ROWID

ROWNM은 쿼리에서 반환되는 각 로우들에 대한 순서 값을 나타내는 의사컬럼이다.

* pseudo column 사용 이유

테이블에 데이터가 많으면 SELECT만 하더라도 결과가 나올 때까지 시간이 많이 소요된다. 예를 들어, sales 테이블에 어떤 컬럼이 있는지

보고 싶어 ‘SELECT * FROM sales;’ 문을 실행하면 전체 로우가 반환되며 결과를 보기까지 시간이 오래 걸린다(물론 여러 가지 툴에서는

한 번에 몇 건만 화면에 출력되도록 하는 옵션이 있다. SQL Developer는 디폴트로 화면에 50건씩 뿌려진다). 이때 다음과 같이 ROWNUM이라는

의사컬럼을 사용하면 매우 편리하다.

입력    SELECT ROWNUM, employee_id      FROM employees     WHERE ROWNUM < 5; 결과    ROWNUM  EMPLOYEE_ID    ------ -----------        1         100        2         101        3         102        4         103

ROWID는 테이블에 저장된 각 로우가 저장된 주소 값을 가리키는 의사컬럼이다. 각 로우를 식별하는 값이므로 ROWID는 유일한 값을 가진다.

4. TPC-H 모델

[ TPC-H model A ]

[ TPC-H model B ]

[ TPC-H model C ]

5. Query Processor

[ Query Processing Phase ]

[ Query Processing Phase ]

• Checker
• Parser : syntax check
• validator : semantic check

• optimizer
• Rewriter : query transformation
• Enumerator : enumerator various plans
• Code planner : make code plan(text area)
• Data planner : make data plan(data area)

• Executor
• Executor : execute plan(code plan + data plan)

[ Parser : Input & Output ]

woong -> clinet가 수행 시킨 sql에 대하여 parset tree 생성 하여 syntax check를 한다.

[ parser : lex & yacc ]

woong : 들어온 SQL에 대하여 lex,yacc 이용하여 정규 표현식 체크를 한다.

[ Validator ]

> Query

woong -> validator에서는 parser tree이용하여 plan정보를 생성 하고 문법 체크 및 Data가 없는 상태에서

테이블 및 index 정보를 가지고 와 옵티마이저에서 사용 할 정보를 생성한다.

[ Rewriter ]

> Rewriter : equivalence relation(등가 연결)

Filter Push-Down	View Projection Pruning
DISTINCT elimination	Transitive Predicate
ORDER BY elimination	Join Transitive Closure
Simple View Merging	Subquery Unnesting
Remove Outer Join Table	Semi-Join to Inner-Join
Outer Join to Inner Join	Scalar Subquery Unnesting
Quantifier elimination	Complex View Merging