BEGIN
DBMS_STATS.GATHER_TABLE_STATS( 'test','tab2',
METHOD_OPT => 'FOR COLUMNS (col1,col2)');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS( 'test','tab2',
METHOD_OPT => 'FOR COLUMNS (LOWER(col2))');
END;
/
SELECT * FROM USER_STAT_EXTENSIONS;
未対応の模様
https://www.postgresql.jp/document/10/html/sql-createstatistics.html
https://qiita.com/nuko_yokohama/items/b5f7146416dc34e78b9a
(10)
CREATE TABLE tab3 (
col1 int,
col2 int
);
INSERT INTO tab3 SELECT i/100, i/500
FROM generate_series(1,1000000) s(i);
ANALYZE tab3;
-- マッチする行の数は非常に低く見積もられる
EXPLAIN ANALYZE SELECT * FROM tab3 WHERE (col1 = 1) AND (col2 = 0);
CREATE STATISTICS sta1 (dependencies) ON col1, col2 FROM tab3;
ANALYZE tab3;
-- 行数の見積もりがより正確になる
EXPLAIN ANALYZE SELECT * FROM tab3 WHERE (col1 = 1) AND (col2 = 0);
create table tab10 (col1 int,col2 int);
go
insert into tab10 values(1,2),(3,4);
go
select * from tab10;
go
CREATE STATISTICS sta1
ON dbo.tab10 (col1, col2)
WITH SAMPLE 5 PERCENT;
go
