索引 & 优化

SQL> select index_name from user_indexes where table_name='G302T';

 

INDEX_NAME-----------------------------------------------------------

G302T_IDX1

G302T_IDX2

G302T_IDX3

G302T_IDX4

 

select dbms_metadata.get_ddl('INDEX','G302T_IDX2') from dual;

 select dbms_metadata.get_ddl('TABLE','G302T') from dual;

//查询某个表的已建立索引项

select * from user_ind_columns where index_name in (select index_name from user_indexes where table_name ='G808M')

//为某个字段建立索引

create index g808m_ind5 on g808m(A100_MPLT_CD);

//删除索引

drop index g808m_ind5

//大量数据SQL优化

    1，查询相关表的数据量，对数据量较大的表

    2，对数据量较大的表，对字段添加索引！

//查询是否为i'G302T'表的'SIZKK_CD'字段建立了索引

select *
  from (select *
          from user_ind_columns
         where index_name in (select index_name
                                from user_indexes
                               where table_name = 'G302T')) A where A.COLUMN_NAME ='SIZKK_CD'

-------------------------------------------------------------------------------------

select
  a.table_name,
  b.records,
  index_name,
  column_name,
  column_position,   --索引的列号
  count(*) over (partition by a.table_name) index_nums,  --每个表的索引数
  count(*) over (partition by a.table_name,index_name) column_nums  --每个索引的列数
  from user_ind_columns A,   --统计每个表的数据量
  (select 'G302T' as table_name, count(*) as records from G302T union all
  select 'G808M' as table_name, count(*) as records from G808M union all
  select 'G103T' as table_name, count(*) as records from G103T union all
  select 'G824M' as table_name, count(*) as records from G824M union all
  select 'G805M' as table_name, count(*) as records from G805M union all
  select 'G903M' as table_name, count(*) as records from G903M union all
  select 'G104T' as table_name, count(*) as records from G104T union all
  select 'G103T' as table_name, count(*) as records from G103T) B
 where a.table_name = b.table_name and
  index_name in
       (select index_name
          from user_indexes
         where table_name in ('G302T', 'G808M', 'G103T', 'G824M', 'G805M',
                'G903M', 'G104T', 'G103T', ''))
 order by A.table_name,A.INDEX_NAME, A.COLUMN_POSITION

--------------

Excel 中技巧

="select '"&A1&"' as table_name, count(*) as records from "&A1&" union all "

 

分析表
BEGIN
 DBMS_STATS.gather_table_stats(ownname => 'GAU01E',tabname => 'G302T',cascade => TRUE);
 END;
建立有序索引
create index G302T_P_IND2 on g302t(KIJ_YM_TO DESC)

排出不常用的条件
CREATE INDEX XXX IN TAB(DECODE(COL,'1', NULL))
使用该索引，必须再引用DECODE(COL,'1', NULL)
使用该索引
CREATE VIEW V_TAB
AS
SELECT * FROM TAB WHERE DECODE(COL,'1', NULL) = '0'

SELECT * FROM V_TAB