不可視索引

• Oracle

select table_name,index_name,visibility from all_indexes where index_name = 'IND1';

alter index ind1 visible;
alter index ind1 invisible;

• MySQL

(8.0)から実装

show index from tab1 where key_name = 'ind1'\G

alter table tab1 alter index ind1 visible;
alter table tab1 alter index ind1 invisible;

• PostgreSQL

HypoPG
https://media.readthedocs.org/pdf/hypopg/latest/hypopg.pdf
https://blog.dbi-services.com/can-i-do-it-with-postgresql-15-invisible-indexes/
https://dev.classmethod.jp/server-side/db/guess-postgresql-index-with-dexter/

--インストール
export PATH=$PATH:/usr/pgsql-9.4/bin/
wget https://github.com/HypoPG/hypopg/archive/1.1.2.tar.gz
tar xvzf 1.1.2.tar.gz
cd hypopg-1.1.2
make

su
make install
exit

create extension hypopg;
\dx

--動作確認

create table tab1 ( col1 int );
with generator as
( select a.*
from generate_series ( 1, 5000000 ) a
order by random()
)
insert into tab1 ( col1 )
select a
from generator;

analyze tab1;
select * from pg_size_pretty ( pg_total_relation_size ('tab1') );

explain select * from tab1 where col1 = 5;
explain analyze select * from tab1 where col1 = 5;

SELECT * FROM hypopg_create_index('CREATE INDEX ON tab1 (col1)');

explain select * from tab1 where col1 = 5;
explain analyze select * from tab1 where col1 = 5;

select * from hypopg_list_indexes();
select * from hypopg_drop_index(57960);
select * from hypopg_list_indexes();

• SQL Server

仮定のインデックス

https://blog.engineer-memo.com/2018/11/25/sql-server-%E3%81%AE%E4%BB%AE%E5%AE%9A%E3%81%AE%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6%E3%81%BE%E3%81%A8%E3%82%81%E3%81%A6%E3%81%BF%E3%82%8B/

select object_name(object_id) object_name,* from sys.indexes
where object_name(object_id) = 'tab1'

CREATE INDEX ind1
ON dbo.tab1(col1)
WITH STATISTICS_ONLY = -1

DBCC TRACEON(2588)
DBCC HELP ('AUTOPILOT')

SELECT
name,
0 AS typeid,
DB_ID() AS database_name,
object_id,
index_id
FROM sys.indexes
WHERE object_id = OBJECT_ID('tab1')
AND is_hypothetical = 1

DBCC AUTOPILOT (0,13,314952694,2)

SET AUTOPILOT ON

select * from tab1 where col1 = 1;