Oracle 索引优化

 一般来说，查询sql很慢，大部分都可以通过添加索引来解决，日常开发中

这个方法可以解决 90%以上的问题（甚至是 100% 哈哈）。对于ETL（数仓）开发的同学来说，还能通过 表分区、索引、索引+分区索引 来对sql进一步优化。
索引能力，可以说想当强悍了，那么问题来了，索引如何选择，如何创建，这是个不算问题的问题；对于一个程序开发人员，比如我，是一个java程序员，我所要查询的表，所要用到的查询字段，我肯定是相当熟悉的。索引创建时，我用到的查询条件、多表查询时，关联条件这是索引的首选，而且这样选是没有任何问题的（不绝对哈）；但是有时候索引选择的有问题，就会难以达到预期效果，甚至白白造成空间浪费（毕竟索引也是占用空间的，有时甚至占用的比数据本身占用空间还大）。喏，下面这条sql可以检查你索引创建的是否合理，声明下哈，这个sql需要使用拥有dba权限的用户才可以执行，很多表是dba角色才可以访问，如果没有dba权限的同学，不要抱怨，你这啥破sql，直接报“对象不存在”。

-- 索引优化
SELECT b.TABLE_NAME,--表名称
       b.COLUMN_NAME, --字段名称
       c.data_type, --字段类型
       ROUND((DECODE(b.num_distinct, 0, 1, (a.num_rows / b.num_distinct))),0) chose,--选择性
       b.NUM_DISTINCT,--重复性
       a.NUM_ROWS,--条数
       b.NUM_NULLS,--为空行数
       a.BLOCKS,--大小
       d.INDEX_NAME --索引名称
  FROM dba_tables a,     
       DBA_TAB_COL_STATISTICS b,       
       dba_tab_columns c, 
       DBA_INDEXES d,
       DBA_IND_COLUMNS e
WHERE 
b.OWNER ='用户名'
AND b.TABLE_NAME ='表名'
AND a.OWNER = b.OWNER
AND b.OWNER= C.OWNER
AND C.OWNER = d.OWNER
AND d.OWNER= e.table_owner
AND a.TABLE_NAME = b.TABLE_NAME 
AND b.TABLE_NAME = C.TABLE_NAME
AND C.TABLE_NAME = d.TABLE_NAME
AND d.TABLE_NAME = e.TABLE_NAME
AND d.INDEX_NAME = e. INDEX_NAME
AND b.COLUMN_NAME = C.COLUMN_NAME
AND C.COLUMN_NAME = e.COLUMN_NAME;

具体这些表、字段代表啥含义，请自行百度，不再一一解释。
现在我说下表中哪个列适合创建索引，chose,–选择性 值越大，NUM_DISTINCT,–重复性 与 a.NUM_ROWS,–条数 值越接近，并且 NUM_NULLS,–为空行数 越小。则这个列 作为索引的优先级越高，当
然你要结合自己实际业务场景，对索引列进行进一步筛选。

如果你是dba，或者新员工，对业务表并不熟悉，建议使用以下sql，对
表进行初步筛选，再根据业务进一步定位要给哪列创建索引，选用原则同上。
 

--粗略分析,可做索引的字段
SELECT
	b.TABLE_NAME ,
	b.COLUMN_NAME ,
	a.num_rows,
	b.num_distinct, 
	ROUND ( (DECODE (b.num_distinct,0,1,(a.num_rows / b.num_distinct)) ) ,0) chose
FROM
	dba_tables a,
	DBA_TAB_COL_STATISTICS b
WHERE
	b.OWNER = ' SCOTT'--用户名
	AND b.TABLE_NAME = 'EMP'--表名
	AND a.OWNER = b.OWNER
	AND a.TABLE_NAME = b.TABLE_NAME
ORDER BY num_DISTINCT DESC, ROUND ( (DECODE (b.num_distinct,0,1,(a.num_rows / b.num_distinct)) ) ,0)

总结：索引创建准则：
1.业务经常使用的筛选条件
2.表间关联条件
3.空值（null值）较少的列
4.选择性较强的列 chose值大的列，通俗点就是 重复值较少的列

补充：以上sql执行时，依赖于 oracle已经对 数据库进行过自动/手动
进行了统计信息的收集，如果没有进行收集，需要手动收集（需在业务较少时段进行）。

exec dbms_stats.gather_schema_stats('SCOTT');--对用户进行统计收集
exec dbms_stats.gather_table_stats('scott','work_list') ; --指定用户，表

这些内容，都是生产出过问题，或由我们生产dba老师总结的。由于我本地并没有多少数据，且数据库版本较低 11g，无法演示具体案例，请谅解。

————————————————
版权声明：本文为CSDN博主「雪夜明月」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_40152902/article/details/122644177

最近准备了解下索引，下篇文章可能介绍DBA相关及索引的原理，它们为什么会让你的sql变快，以及工作流程是怎样的。