아래 IQ 테이블 사이즈 구하는 프로시져를 수정을 약간하다가 발견한 사항입니다.
1. create table #tablesize_temp 로 생성해서 다음 쿼리를 날리면..
결과값이 제대로 나옵니다.
select T.OwnerTable as OwnerTable,
sum(T.Columns) as Columns,
sum(T.KBytes) as KBytes,
sum(T.Pages) as Pages,
sum(T.CompressedPages) as CompressedPages,
sum(T.NBlocks) as NBlock
from (select Ownername||'.'||Tablename as OwnerTable,
cast(Columns as bigint) as Columns,
cast(KBytes as bigint) as KBytes,
cast(Pages as bigint) as Pages,
cast(CompressedPages as bigint) as CompressedPages,
cast(NBlocks as bigint) as NBlocks
from #tablesize_temp ) T
group by rollup(T.OwnerTable);
결과값:
OwnerTable Columns KBytes Pages CompressedPages NBlocks
DBA.sybase 1 768 5 4 24
DBA.sybase2 1 768 5 4 24
DBA.x 1 1952 10 7 61
132 3014720 12695 11963 94210 <--rollup 으로 나온값
2. Declare local Temporary Table tablesize_temp 로 생성해서 다음의 쿼리를 날리면
rollup 된 값이 나오지 않습니다.
select T.OwnerTable as OwnerTable,
sum(T.Columns) as Columns,
sum(T.KBytes) as KBytes,
sum(T.Pages) as Pages,
sum(T.CompressedPages) as CompressedPages,
sum(T.NBlocks) as NBlock
from (select Ownername||'.'||Tablename as OwnerTable,
cast(Columns as bigint) as Columns,
cast(KBytes as bigint) as KBytes,
cast(Pages as bigint) as Pages,
cast(CompressedPages as bigint) as CompressedPages,
cast(NBlocks as bigint) as NBlocks
from tablesize_temp ) T
group by rollup(T.OwnerTable);
결과값:
OwnerTable Columns KBytes Pages CompressedPages NBlocks
DBA.sybase 1 768 5 4 24
DBA.sybase2 1 768 5 4 24
DBA.x 1 1952 10 7 61
쿼리 구조는 모두 똑같은데 테이블의 생성관계가 다를 뿐입니다..
현재 시스템의 버전은 sp_iqstatus 로 확인한바로는
12.5.0/050425/P/ESD 16/RS6000MP/AIX 5.1.0/64bit/2005-04-25 17:02:55
최신 패치가 아니라서 그런건지도 모르겠네요.
|