LYNXEYED: 데이터 필터링의 효율

2011년 3월 13일 일요일

일단SQL 튜닝에서는모든상황에항상맞는것은없다.

즉, SQL 튜닝엔왕도가없다는말이다. 수학공식외우듯이외워서튜닝을하는것은아니며그때그때데이터의분포, 서버의상태, 인덱스의유무및SQL TRACE나TKPROF결과등의각종참조가능한수치들을분석하여튜닝방향을정한다.

상황에따라다른모든경우엔가장안좋던방법이특정경우엔최적의솔루션이될수있다.

A 집합에서B집합의데이터를제외한나머지만구하는방법은질문의3가지를포함하여상황에따라보통크게5가지정도를주로쓰게된다. 하나씩특징만간단히적겠다...

1. NOT IN ...

SELECT * FROM A WHERE A.KEY NOT IN (SELECT B.KEY FROM B)

형태의구문이며, B쪽을먼저ACCESS하여B.KEY로A.KEY에공급자역할을하는서브쿼리로쓰고싶을때주로사용한다.

2. NOT EXISTS ...

SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE B.KEY = A.KEY)

형태의구문이며, A쪽을먼저ACCESS하고나서A의각ROW들을NOT EXISTS로조사하여FILTERING하는처리를할때주로사용한다. 즉, B를ACCESS하기전에A쪽의전체범위가먼저ACCESS된다.

이때의서브쿼리는공급자가아닌확인자역할만해줄수있다.

3. MINUS ...

SELECT KEY, COL1, COL2 FROM A

MINUS

SELECT KEY, COL1, COL2 FROM B

형태의구문이며, 테스트해보면아시겠지만MINUS는특성상SORT와중복제거수행을동반한다.

그러므로가장이해하기는간단하나대용량에서는사용시주의해야한다.

A나B집합의ACCESS대상이대량인경우대량의SORT와중복제거가발생하므로이들처리에많은시간이소요될수있는쿼리다.

4. OUTER + NULL CHECK ...

SELECT * FROM A, B WHERE A.KEY = B.KEY(+) AND B.KEY IS NULL

형태의구문이며, 위의NOT IN이나NOT EXISTS가주로NESTED LOOP JOIN 또는NESTED LOOP ANTI JOIN 방법을수행하는데비해대용량의경우HASH JOIN이나MERGE JOIN을유도하여성능을보장받을수있는방법이다.

단, 각DBMS 마다A LEFT OUTER JOIN B ON ~ , (*)등으로아우터조인에대한표현은약간씩다르다.

5. UNION ALL + GROUP COUNT 또는COUNT() OVER() 분석함수이용등...

SELECT *

FROM(

SELECT A.*

, COUNT(DISTINCT GBN) OVER(PARTITION BY KEY) AS CNT

, COUNT(DISTINCT DECODE(GBN, 'A', 1)) OVER(PARTITION BY KEY) AS A_CNT

FROM(

SELECT 'A' AS GBN, KEY, COL1, COL2 FROM A UNION ALL

SELECT 'B' AS GBN, KEY, COL1, COL2 FROM B

) A

)

WHERE CNT < 2 AND A_CNT = 1

형태의구문이며, UNION ALL은MINUS와달리SORT나중복제거를하지않고별다른조인도없기때문에양쪽집합에SCAN할마땅한인덱스가없거나하는상황에서위력을발휘할수있는솔루션이다.

GROUP BY와COUNT 함수로도위의의미를그대로만들수있다...분석함수나통계함수를지원하지않는DBMS들은COUNT() OVER() 대신GROUP BY / COUNT로변경해야한다.

LYNXEYED