단일 쿼리로 테이블 카운트하기

오라클의 getxmltype 함수를 사용해서 테이블별 레코드수를 구하는 쿼리가 있더군요.

인포믹스에서도 가능한지 테스트를 해보았습니다.

우선 오라클에서 getxmltype 함수를 사용한 예제입니다

SELECT table_name

     , num_rows -- 통계정보 건수

     , TO_NUMBER(

       dbms_xmlgen.getxmltype('SELECT COUNT(*) c FROM ' || table_name).Extract('//text()')

       ) num_rows2 -- 실제측정 건수

  FROM user_tables

;

샘플 데이터인 SCOTT 계정에서 위와 같은 쿼리를 실행하면, 아래와 같은 결과를 보여줍니다.

TABLE_NAME                       NUM_ROWS  NUM_ROWS2

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

DEPT                                    4          4

EMP                                    14         14

BONUS                                   0          0

SALGRADE                                5          5

TB_DF_STAT                             24         24

인포믹스에서 XML 함수를 사용하려면 idsxmlvp 클래스의 Virtual Processor가 작동중이어야 합니다.

이는 onconfig의 VPCLASS 설정이나 onmode 명령으로 동적으로 설정할 수 있습니다.

인포믹스의 샘플 데이터베이스인 stores_demo 에서 xml함수를 활용해서 아래의 쿼리문장을 만들었습니다.

사용한 함수는 extractvalue, genxmlquery 입니다. 기본내장된 함수들입니다.

select tabname,

       nrows::int num_rows,

       extractvalue(genxmlquery('set','SELECT count(*) c FROM '||tabname),'/set/row/c')::int num_rows2 

from   systables where tabid > 99 and tabtype = 'T';

tabname     customer

num_rows    28

num_rows2   28

tabname     orders

num_rows    23

num_rows2   23

tabname     manufact

num_rows    9

num_rows2   9

tabname     stock

num_rows    74

num_rows2   74

tabname     items

num_rows    67

num_rows2   67

단일 쿼리로 건수를 검증할 수 있어 편리하네요. 용도에 맞게 사용하시면 좋을 것 같습니다.

대신 건수가 너무 많은 경우는 메모리나 디스크등 자원 소모량이 많아 시스템에 무리가 갈수도 있겠네요.

위와 같이 COUNT하는 용도외에도 활용할 수 있겠네요.

참조:

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.xml.doc/ids_xpconfig.htm

http://www.gurubee.net/article/55486

http://www.gurubee.net/article/61164