关于索引和SQL解释

1、查询一张表里面索引

 select * from user_indexes where table_name = 'AUSER';

 

2、查询被索引字段

select * from user_ind_columns where index_name like  'ST%';

 

3、查看sql的执行计划

explain plan for select * from auser where usercode = '4AB6002';

select * from table(dbms_xplan.display);

 

4、分析查询表

SQL> exec dbms_stats.gather_table_stats(user,'auser');

 

5、设定优化器类型

SQL> select value from v$parameter where name='optimizer_mode';

SQL> alter session set optimizer_mode=first_rows;

SQL> alter session set optimizer_mode=all_rows;

SQL> alter system set optimizer_mode=first_rows;

SQL> alter system set optimizer_mode=all_rows;

 

6、判断该表是否修改分析过

SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name = 'T';
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T'

 

7、直方图

做直方图分析

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true');

 删除直方图信息

SQL> exec dbms_stats.delete_column_stats(user,'T','id');

 

8、分区表

SQL> select partition_name,num_rows,avg_row_len,last_analyzed from user_tab_partitions where table_name='T1';

SQL> select num_rows,avg_row_len,last_analyzed from user_tables where table_name='T1';

SQL> select blevel,leaf_blocks,last_analyzed from user_indexes where index_name='IND_T1';

SQL> exec dbms_stats.gather_table_stats(user,'T1',partname=>'pm',granularity=>'partition'); ----对分区进行直方

SQL> exec dbms_stats.gather_table_stats(user,'T1',granularity=>'GLOBAL');  --- 对该表全图分析

SQL> exec dbms_stats.gather_table_stats(user,'T1',granularity=>'GLOBAL',method_opt=>'for all columns size 1'); -----method_opt=>for all columns size 1 不对列进行直方