使用函数索引优化查询一例
发布时间：2010/7/5 15:54:27 来源：城市学习网 编辑：ziteng
整个过程基本上没有什么什么新意，仅为留一下一个脚印。
在一个非常空闲的系统中，用AWR发现一条语句的consistent gets比较高：
01 SELECT * FROM
02 (
03 SELECT RESOURCES.CATALOGID, ARTICLE.TITLE AS TITLE, ARTICLE.ID AS ID,
04 ARTICLE.HOTDESC AS BRIEF, ARTICLE.CREATORTIME AS TIME, ARTICLE.PIC AS PICTURE,
05 ARTICLE.ISPICARTICLE AS ISPICARTICLE, RESOURCES.URL AS FILEURL, ARTICLE.TYPE AS TYPE,
06 ARTICLE.LINKURL AS LINKURL, RESOURCES.SITE, RESOURCES.ORDERLINE AS L_ORDERS,
07 TO_CHAR(ARTICLE.CREATORTIME, 'YYYY') AS YEAR, TO_CHAR(ARTICLE.CREATORTIME, 'MM') AS MONTH,
08 TO_CHAR(ARTICLE.CREATORTIME, 'DD') AS DAY, TO_CHAR(ARTICLE.CREATORTIME, 'HH24') AS HOUR,
09 TO_CHAR(ARTICLE.CREATORTIME, 'MI') AS MINUTE, TO_CHAR(ARTICLE.CREATORTIME, 'SS') AS SECOND,
10 ARTICLE.ISNEW, ARTICLE.ISHOT, ARTICLE.TITLEFONTCOLOR, ARTICLE.TITLEFONTNAME,
11 ARTICLE.TITLEBOLD , ARTICLE.ORDERS, ARTICLE.ISINSTANCY
12 FROM ARTICLE, RESOURCES
13 WHERE 1=1
14 AND RESOURCES.STATE=1
15 AND TO_CHAR(ARTICLE.ID)=TRIM(RESOURCES.SOURCEID)
16 AND RESOURCES.CATALOGID=20794
17 ORDER BY ISINSTANCY DESC, L_ORDERS DESC
18 ) WHERE ROWNUM<31
执行计划及统计信息如下：
01 --------------------------------------------------------------------------------------
02 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
03 --------------------------------------------------------------------------------------
04 | 0 | SELECT STATEMENT | | 30 | 38790 | 2972 (1)|
05 | 1 | COUNT STOPKEY | | | | |
06 | 2 | VIEW | | 126 | 159K| 2972 (1)|
07 | 3 | SORT ORDER BY STOPKEY | | 126 | 18018 | 2972 (1)|
08 | 4 | HASH JOIN | | 126 | 18018 | 2972 (1)|
09 | 5 | TABLE ACCESS BY INDEX ROWID| RESOURCES | 126 | 7560 | 48 (0)|
10 | 6 | INDEX RANGE SCAN | IDX_RESOURCES | 126 | | 1 (0)|
11 | 7 | TABLE ACCESS FULL | ARTICLE | 60606 | 4912K| 2923 (1)| [NextPage] 12 --------------------------------------------------------------------------------------
13 Statistics
14 ----------------------------------------------------------
15 0 recursive calls
16 0 db block gets
17 13175 consistent gets
18 0 physical reads
19 0 redo size
20 2654 bytes sent via SQL*Net to client
21 469 bytes received via SQL*Net from client
22 2 SQL*Net roundtrips to/from client
23 1 sorts (memory)
24 0 sorts (disk)
25 6 rows processed
在内存查询where子句中的 RESOURCES.CATALOGID=20794 ，估计就是引起对 IDX_RESOURCES 的 RANGE SCAN ，而HASH JOIN 应该是由于 TO_CHAR(ARTICLE.ID)=TRIM(RESOURCES.SOURCEID) 这个条件引起的，由于ARTICLE.ID被TO_CHAR函数包裹着，所以，一般的索引是不会被使用的：
01 select
02 i.index_name,i.index_type,c.column_name
03 from user_indexes i,user_ind_columns c
04 where i.table_name='ARTICLE'
05 and i.index_name=c.index_name
06 order by i.index_name
07
08 INDEX_NAME INDEX_TYPE COLUMN_NAME
09 ------------------------------ --------------------------- ------------------------------
10 PK_ARTICLE NORMAL ID[NextPage] 于是乎给ARTICLE.ID建一个TO_CHAR的函数索引：
1 create index fidx__to_char_ARTICLE_ID on ARTICLE(TO_CHAR(id));
执行计划改变了：
01 -------------------------------------------------------------------------------------------------
02 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
03 -------------------------------------------------------------------------------------------------
04 | 0 | SELECT STATEMENT | | 30 | 38790 | 1561 (1)|
05 | 1 | COUNT STOPKEY | | | | |
06 | 2 | VIEW | | 76140 | 93M| 1561 (1)|
07 | 3 | SORT ORDER BY STOPKEY | | 76140 | 10M| 1561 (1)|
08 | 4 | NESTED LOOPS | | 76140 | 10M| 1561 (1)|
09 | 5 | TABLE ACCESS BY INDEX ROWID| RESOURCES | 126 | 7560 | 48 (0)|
10 | 6 | INDEX RANGE SCAN | IDX_RESOURCES | 126 | | 1 (0)|
11 | 7 | TABLE ACCESS BY INDEX ROWID| ARTICLE | 606 | 50298 | 12 (0)|
12 | 8 | INDEX RANGE SCAN | FIDX__TO_CHAR_ARTICLE_ID | 12 | | 1 (0)|
13 -------------------------------------------------------------------------------------------------
14 Statistics
15 ----------------------------------------------------------
16 0 recursive calls
17 0 db block gets
18 21 consistent gets
19 0 physical reads
20 0 redo size
21 2654 bytes sent via SQL*Net to client
22 469 bytes received via SQL*Net from client
23 2 SQL*Net roundtrips to/from client
24 1 sorts (memory)
25 0 sorts (disk)
26 6 rows processed
大大减少了 consistent gets ，这系统变得更加空闲了。
