(8.0.22)
調べた限り方法なし
(19c)
-- 1. テストデータ作成
drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(2000) );
declare
begin
for i in 1..1000 loop
insert into tab1 values(i,rpad('X',200,'X') );
commit;
end loop;
end;
/
create index ind1 on tab1(col2);
exec dbms_stats.gather_table_stats(user,'TAB1');
-- 2. クラスタリングファクタ算出
set serveroutput on
declare
cursor cur1 is
select dbms_rowid.rowid_block_number(rowid)
,dbms_rowid.rowid_to_absolute_fno(rowid,'TEST','TAB1')
from tab1
where col2 is not null
order by col2;
l_clf int := 0;
l_bno int := 0;
l_fno int := 0;
l_bno2 int := 0;
l_fno2 int := 0;
begin
open cur1;
loop
fetch cur1 into l_bno,l_fno;
exit when cur1%notfound;
if (l_bno2 != l_bno or l_fno2 != l_fno) then
l_clf := l_clf + 1;
end if;
l_bno2 := l_bno;
l_fno2 := l_fno;
end loop;
dbms_output.put_line('l_clf = ' || l_clf);
close cur1;
end;
/
-- 3. ディクショナリの値と比較
select index_name,clustering_factor
from user_indexes
where table_name = 'TAB1'
order by index_name;
(14)
前提: pg_filedumpインストール済
-- 1. テストデータ作成
drop table tab1;
create table tab1(col1 int,col2 varchar(2000) ) with ( fillfactor = 10 );
insert into tab1 select g,g from generate_series(1,100) g;
alter table tab1 add constraint tab1pk primary key(col1) with ( fillfactor = 10 );
create index ind1 on tab1(col2) with ( fillfactor = 10 );
checkpoint;
\d+ tab1
export PATH=$PATH:/usr/pgsql-14/bin
oid2name
oid2name -d test -i
pg_filedump -i /var/lib/pgsql/14/data/base/16384/17359
pg_filedump -i /var/lib/pgsql/14/data/base/16384/17358 | grep Block
pg_filedump -D int,varchar /var/lib/pgsql/14/data/base/16384/17353
-- データの並び替えによるデータ配置変化確認
cluster tab1 using ind1;
cluster tab1 using tab1pk;
-- 2. クラスタリングファクタ算出
clf=0
bno2=0
mode=0
OLDIFS=$IFS
IFS=$'\n'
for line in $( pg_filedump -i /var/lib/pgsql/14/data/base/16384/17358 | grep -e Block -e 'Flags: 0x' | grep -v '*' | tac ) ; do
Flags=$( echo "${line}" | grep 'Flags: 0x' | wc -l)
LEAF=$( echo "${line}" | grep 'Flags: 0x' | grep LEAF | wc -l)
Block=$( echo "${line}" | grep Block | grep linp | wc -l)
# echo Flags="${Flags}"
# echo LEAF="${LEAF}"
# Flags: 0xの行の場合、LEAFを含むなら処理続行
if [ "${Flags}" -eq 1 ]; then
if [ "${LEAF}" -eq 1 ]; then
mode=1
else
mode=0
fi
fi
# echo mode="${mode}"
if [ "${mode}" -eq 1 ]; then
if [ "${Block}" -eq 1 ]; then
bno=$( echo "${line}" | sed 's/linp.*$//' | sed 's/Block Id://' )
if [ "${bno}" -ne "${bno2}" ]; then
clf=$(("${clf}" + 1) )
fi
bno2="${bno}"
fi
fi
done
IFS=$OLDIFS
echo "${clf}"
-- 3. ディクショナリの値と比較
analyze tab1;
select schemaname,tablename,attname,correlation
from pg_stats
where tablename = 'tab1'
order by schemaname,tablename,attname
;
(2019)
-- 1. テストデータ作成
drop table tab1;
create table tab1(col1 int not null ,col2 char(1000) );
alter table tab1 add constraint tab1pk primary key(col1) with( fillfactor = 10);
create index ind1 on tab1(col2) with( fillfactor = 10);
declare @i int = 1;
set nocount on
while (@i <= 300)
begin
insert into tab1 values(@i,@i);
set @i = @i + 1;
end
select count(*) from tab1;
update statistics tab1;
DBCC SHOW_STATISTICS ('test.dbo.tab1','tab1pk')
DBCC SHOW_STATISTICS ('test.dbo.tab1','ind1')
-- 2. クラスタリングファクタ算出
declare @clf int = 0;
declare @file_id int = 0;
declare @page_id int = 0;
declare @file_id2 int = 0;
declare @page_id2 int = 0;
declare cur1 cursor for select fplc.file_id,fplc.page_id
from dbo.tab1
cross apply sys.fn_PhysLocCracker(%%physloc%%) as fplc
where dbo.tab1.col2 is not null
order by dbo.tab1.col2;
open cur1;
fetch next from cur1 into @file_id,@page_id;
while @@fetch_status = 0
begin
if ( @file_id != @file_id2 or @page_id != @page_id2 )
begin
set @clf = @clf + 1;
end;
set @file_id2 = @file_id;
set @page_id2 = @page_id;
fetch next from cur1 into @file_id,@page_id;
end
print '@clf = ' + cast(@clf as varchar)
close cur1;
deallocate cur1;
GO
