[Oracle] oracle patition의 high_value기준으로 조회하기

DBA_TAB_PARTITIONS 뷰의 HIGH_VALUE는 LONG데이터 타입이기 때문에 

ORACLE에서 WHERE절을 사용해 비교할 수 없다.

아래 포스트는 HIGH_VALUE를 비교하기 위해 XML로 변환 후 다시 TABLE로 변환하여 비교하는 SQL문이다.

DBMS_XMLGEN.GETXMLTYPE 은 조회결과를 XML파입으로 반환하는 함수이며

SELECT 
	DBMS_XMLGEN.GETXMLTYPE(
		'SELECT *
		FROM TEST) AS X
FROM DUAL

 

의 형태로 쓸 수 있다.

아래의 with절을 xml형태로 조회한다면

<ROWSET>
	<ROW>
		<TABLE_OWNER>TEST</TABLE_OWNER>
		<TABLE_NAME>TEST</TABLE_NAME>
		..
		..
		..
	</ROW>
	<ROW>
		..
		..
		..
		..
		..
	</ROW>
</ROWSET>

 

과 같은 형태를 띄고 있다.

그리고 XMLTABLE을 통해 XML데이터를 테이블 형식으로 변환할 수 있다.

사용법은 아래와 같으며 test라는 테이블은 xmltable을 통해 얻는 데이터를 포함하고 있어야 합니다.

select *
from test,
	XMLTABLE('/ROWSET/ROW'	PASSING TEST.X
				COLUMNS
					NAME VARCHAR2(50) PATH 'NAME',
					VALUE VARCHAR2(50) PATH 'VALUE'
			);

라고 쓸 수 있다.
이를 활용하여 PARTITION TABLE의 MAXVALUE를 WHERE절에 작성할 수 있는 쿼리를 생성해보자.

 
WITH W_TP AS (SELECT DBMS_XMLGEN.GETXMLTYPE(
										'SELECT 
											TABLE_OWNER,
											TABLE_NAME,
											PARTITION_NAME,
											HIGH_VALUE,
											PARTITION_POSITION
										FROM DBA_TAB_PARTITIONS
										WHERE INTERVAL = ''NO''
										AND TABLE_OWNER IN (SELECT USERNAME
															FROM DBA_USERS
															WHERE ACCOUNT_STATUS = ''OPEN''
															AND USERNAME NOT IN (''SYS'', ''SYSTEM''))') AS X
			FROM DUAL)
SELECT *
FROM DBA_PART_TABLES A,
	(
	SELECT 
		X.TABLE_OWNER,
		X.TABLE_NAME,
		CASE WHEN REGEXP_SUBSTR(MAX(X.HIGH_VALUE), '([0-9]{4}-[0-9]{2}-[0-9]{2})') IS NOT NULL
			THEN TO_DATE(REGEXP_SUBSTR(MAX(X.HIGH_VALUE), '([0-9]{4}-[0-9]{2}-[0-9]{2})'), 'YYYY-MM-DD')
			WHEN REGEXP_SUBSTR(MAX(X.HIGH_VALUE), '([0-9]{4}[0-9]{2}[0-9]{2})') IS NOT NULL
			THEN TO_DATE(REGEXP_SUBSTR(MAX(X.HIGH_VALUE), '([0-9]{4}[0-9]{2}[0-9]{2})'), 'YYYYMMDD')
		END AS HIGH_DATE,
		MAX(PARTITION_NAME) AS PARTITION_NAME
	FROM W_TP P
		, XMLTABLE('/ROWSET/ROW'	PASSING P.X 
					COLUMNS
						TABLE_OWNER 	VARCHAR2(30) PATH '/ROW/TABLE_OWNER',
						TABLE_NAME		VARCHAR2(50) PATH '/ROW/TABLE_NAME',
						PARTITION_NAME 	VARCHAR2(50) PATH '/ROW/PARTITION_NAME',
						HIGH_VALUE		VARCHAR2(50) PATH '/ROW/HIGH_VALUE',
						PARTITION_POSITION	NUMBER	 PATH '/ROW/PARTITION_POSITION') X
	WHERE HIGH_VALUE <> 'MAXVALUE'
	GROUP BY X.TABLE_OWNER , X.TABLE_NAME
	) B
WHERE A.OWNER = B.TABLE_OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.PARTITIONING_TYPE = 'RANGE'
AND A.INTERVAL IS NULL
AND SYSDATE + 30 > HIGH_DATE-1;