Oracle 19c: Automatic Indexing
Oracle 19c 新特性/Oracle 19c New Features
Core aims: Long Term Stability 19c
New Features
自动索引
drop table hr.big1 purge;
drop table sh.sales1 purge;
drop tablespace ts001 including contents and datafiles;
exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', NULL);
exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'null, allow => TRUE);
select con_id, parameter_name, parameter_value from cdb_auto_index_config;
exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT')
option: OFF, REPORT_ONLY, IMPLEMENT
alter session set container=orclpdb1;
select con_id, parameter_name, parameter_value from cdb_auto_index_config;
exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'ts001');
exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', allow => TRUE);
exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'OE', allow => FALSE);
create table hr.big1 as select rownum id, t.* from dba_objects t;
declare
a varchar2(2000) := '';
begin
for x in 1.. 10000 loop
select object_name into a from hr.big1 where id =x;
end loop;
end;
/
column task_name format a30
column advisor_name format a30
select task_name, advisor_name
from dba_advisor_tasks where owner='SYS' order by task_id;
task_name advisor_name
---------------------- ---------------------
SYS_AI_SPM_EVOLVE_TASK SPM Evolve Advisor
SYS_AI_VERIFY_TASK SQL Performance Analyzer
SYS_AUTO_INDEX_TASK SQL Access Advisor
... ....
column owner format a20
column index_type format a10
column index_name format a20
column table_name format a20
column table_owner format a10
select owner, idnex_type, index_name, table_name, table_owner
from dba_indexes where auto='YES' order by owner, index_name;
index_name
---------------
SYS_AI_gzmy94nq3vhgq
实时统计收集
sqlplus sh/sh@localhost:1521/orclpdb1
create table sales1 as select * from sales;
set pagesize 5000
set linesize 200
col column_name format a13
col low_value format a14
col high_value format a14
col notes format a40
col partition_name format a13
select column_name, low_value, high_volue, sample_size, notes
from user_tab_col_statistics where table_name ='SALES1'
order by 1, 5;
select nvl(partitio_name, 'global') partitio_name, num_rows, block, notes
from user_tab_statistics where table_name='SALES1' order by 1,4;
insert into sales1(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
select prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold*3, amount_sold*3 from sales;
commit;
select * from table(dbms_xplan.display_cursor(format=>'TYPICAL'));
select count(*) from sales1;
exec dbms_stats.flush_database_monitoring_info;
select nvl(partitio_name, 'global') partitio_name, num_rows, block, notes
from user_tab_statistics where table_name='SALES1' order by 1,4;
select count(*) from sales1 where quantity_sold >50;
select * from table(dbms_xplan.display_cursor(format=>'TYPICAL'));
--Scripts--
https://oracle.zoom.com.cn/j/4382854932
autoindex:
COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A20
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
#EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
#EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
alter session set container=orclpdb1;
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
alter session set container=orclpdb1;
create tablespace ts001 datafile '/opt/ts001.dbf' size 500m;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','ts001');
查看AI的默认表空间
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);
schema：
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', allow => TRUE);
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'OE', allow => FALSE);
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);
SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;
create table hr.big1 as select rownum id,t.* from dba_objects t;
declare
a varchar2(2000) := '';
begin
for x in 1.. 10000 loop
select object_name into a from hr.big1 where id=x;
end loop;
end;
/
COLUMN task_name FORMAT A30
COLUMN advisor_name FORMAT A30
select task_name,advisor_name from dba_advisor_tasks where owner='SYS' ORDER BY TASK_ID;
COLUMN OWNER FORMAT a10
col INDEX_TYPE format a10
col INDEX_NAME format a20
col TABLE_NAME format a20
col TABLE_OWNER format a10
SELECT OWNER,INDEX_TYPE,INDEX_NAME,TABLE_NAME,TABLE_OWNER FROM DBA_INDEXES WHERE AUTO='YES' ORDER BY OWNER,INDEX_NAME;
环境清理：
drop table hr.big1 purge;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
drop tablespace ts001 including CONTENTS and datafiles;
=========================================================================================================================
R-T-S
sqlplus sh/sh@localhost:1521/orclpdb1
create table sales1 as select * from sales;
set pagesize 5000
set linesize 200
col column_name format a13
col low_value format a14
col high_value format a14
col notes format a40
col partition_name format a13
select column_name,low_value,high_value,sample_size,notes from user_tab_col_statistics where table_name='SALES1' order by 1,5;
select nvl(partition_name,'global') partition_name,num_rows,blocks,notes from user_tab_statistics where table_name='SALES1' order by 1,4;
insert into sales1(prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold)
select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold*3,amount_sold*3 from sales;
commit;
select * from table(dbms_xplan.display_cursor(format=>'TYPICAL'));
select count(*) from sales1;
exec dbms_stats.flush_database_monitoring_info;
select nvl(partition_name,'global') partition_name,num_rows,blocks,notes from user_tab_statistics where table_name='SALES1' order by 1,4;
select count(*) from sales1 where quantity_sold>50;
select * from table(dbms_xplan.display_cursor(format=>'TYPICAL'));
清理环境：
drop table sh.sales1 purge;--End of File--
Comments
Post a Comment