조인 칼럼의 low_value와 high_value는 조인 카디널리티 계산에 영향을 줍니다. 조인 칼럼의 high_value가 다른 조인 칼럼의 low_value와 값이 겹치지 않으면 조인 카디널리티가 1로 계산되어 비효율적인 실행 계획이 생성될 수 있습니다.
테스트를 위해 아래와 같이 t1, t2 테이블을 생성하겠습니다. 두 테이블 모두 2021-01-01부터 2022-12-31까지 데이터가 존재하고 2021-12-31 이후 통계를 수집하지 않았다고 가정합니다.
-- 1-1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1
AS
SELECT ROWNUM AS c1
, DATE '2020-12-31' + CEIL (ROWNUM / 100) AS c2
FROM XMLTABLE ('1 to 36500');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c1);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'TUNA', tabname => 'T1', method_opt => 'FOR ALL COLUMNS SIZE 1');
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'TUNA', tabname => 'T2', method_opt => 'FOR ALL COLUMNS SIZE 1');
END;
/
-- 1-2
INSERT ALL
INTO t1
INTO t2
SELECT ROWNUM AS c1
, DATE '2021-12-31' + CEIL (ROWNUM / 100) AS c2
FROM XMLTABLE ('1 to 36500');
COMMIT;
t3 테이블은 2022-01-01부터 2022-12-31까지 데이터가 존재합니다.
-- 2
DROP TABLE t3 PURGE;
CREATE TABLE t3
AS
SELECT ROWNUM AS c1
, DATE '2021-12-31' + CEIL (ROWNUM / 100) AS c2
FROM XMLTABLE ('1 to 1000');
CREATE INDEX t3_x1 ON t3 (c1);
EXEC BMS_STATS.GATHER_TABLE_STATS (ownname => 'TUNA', tabname => 'T3', method_opt => 'FOR ALL COLUMNS SIZE 1');
t1, t2 테이블은 stale 상태이며 c2 칼럼에 대한 high_value는 2021-12-31 00:00:00입니다. t3 테이블의 c2 열에 대한 low_value는 2022-01-01 00:00:00으로 t2 테이블의 c2 칼럼에 대한 high_value 값과 겹치지 않습니다.
-- 3-1
SELECT table_name, num_rows, stale_stats
FROM user_tab_statistics
WHERE table_name IN ('T1', 'T2', 'T3');
TABLE_NAME NUM_ROWS STALE_STATS
---------- -------- -----------
T1 36500 YES
T2 36500 YES
T3 1000 NO
3 행이 선택되었습니다.
-- 3-2
CREATE OR REPLACE FUNCTION fnc_raw_to_date (i_rawval IN RAW)
RETURN DATE
IS
v_resval DATE;
BEGIN
DBMS_STATS.CONVERT_RAW_VALUE (i_rawval, v_resval);
RETURN v_resval;
END;
/
-- 3-3
SELECT table_name
, num_distinct
, fnc_raw_to_date (low_value) AS low_value
, fnc_raw_to_date (high_value) AS high_value
FROM user_tab_columns
WHERE table_name IN ('T1', 'T2', 'T3')
AND column_name = 'C2';
TABLE_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE
---------- ------------ ------------------- -------------------
T1 365 2021-01-01 00:00:00 2021-12-31 00:00:00
T2 365 2021-01-01 00:00:00 2021-12-31 00:00:00
T3 10 2022-01-01 00:00:00 2022-01-10 00:00:00
3 행이 선택되었습니다.
아래 쿼리는 t1 테이블의 c2 칼럼에 대한 high_value와 t3 테이블의 c2 칼럼에 대한 low_value가 겹치지 않아 조인 카디널리티가 1로 계산되었고 이로 인해 t2 테이블을 NL 조인합니다.
-- 4
SELECT *
FROM t1 a, t3 b, t2 c
WHERE b.c2 = a.c2
AND c.c1 = b.c1;
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200K|00:00:00.43 | 210K| |
| 1 | NESTED LOOPS | | 1 | 1 | 200K|00:00:00.43 | 210K| |
| 2 | NESTED LOOPS | | 1 | 1 | 200K|00:00:00.20 | 10280 | |
|* 3 | HASH JOIN | | 1 | 1 | 100K|00:00:00.04 | 1192 | 1317K (0)| -- !
| 4 | TABLE ACCESS FULL | T3 | 1 | 1000 | 1000 |00:00:00.01 | 4 | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 36500 | 73000 |00:00:00.01 | 1188 | |
|* 6 | INDEX RANGE SCAN | T2_X1 | 100K| 1 | 200K|00:00:00.12 | 9088 | |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 200K| 1 | 200K|00:00:00.17 | 200K| |
-----------------------------------------------------------------------------------------------------------
아래와 같이 t1, t2 테이블의 통계 정보를 수집하겠습니다.
-- 5
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'TUNA', tabname => 'T1', method_opt => 'FOR ALL COLUMNS SIZE 1', no_invalidate => FALSE);
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'TUNA', tabname => 'T2', method_opt => 'FOR ALL COLUMNS SIZE 1', no_invalidate => FALSE);
END;
/
쿼리를 다시 수행하면 조인 카디널리티가 제대로 계산되어 해시 조인으로 조인이 수행되는 것을 확인할 수 있습니다.
-- 6
SELECT *
FROM t1 a, t3 b, t2 c
WHERE b.c2 = a.c2
AND c.c1 = b.c1;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200K|00:00:00.04 | 1384 | |
|* 1 | HASH JOIN | | 1 | 198K| 200K|00:00:00.04 | 1384 | 1409K (0)|
|* 2 | HASH JOIN | | 1 | 1988 | 2000 |00:00:00.01 | 195 | 1571K (0)|
| 3 | TABLE ACCESS FULL| T3 | 1 | 1000 | 1000 |00:00:00.01 | 4 | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 73000 | 73000 |00:00:00.01 | 190 | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 73000 | 73000 |00:00:00.01 | 1188 | |
-------------------------------------------------------------------------------------------------