联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.数据库版本
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production
2.发现含有AND-EQUAL执行计划
SQL> SELECT
2 COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_
3 FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_
4 WHERE (TABXNPRESM0_.COMPANY_ID = 346240)
5 AND (TABXNPRESM0_.EMPLOYEE_ID = 0)
6 AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd'))
7 AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1)
8 AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0))
9 AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0))
10 AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=40)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30)
3 2 NESTED LOOPS (Cost=11 Card=1 Bytes=40)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=8 Card=1 Bytes=25)
5 4 AND-EQUAL
6 5 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_2' (NON-UNIQUE)
7 5 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_1' (NON-UNIQUE) (Cost=4 Card=638)
8 3 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
272188 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3.关于AND-EQUAL解释
If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.
Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.
大概的意思是当where条件后面含有多个列的单列索引时(不超过5个)，会先得到每个条件的rowid，然后这些rowid进行merges，得到一个rowid的结果集，最后根据这些rowid取表中记录。
4.表/列/索引相关信息
--index和列信息
SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
2 FROM USER_IND_COLUMNS
3 WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL');
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ --------------------
IDX_XN_PRESMS_1 TAB_XN_PRESMS COMPANY_ID
IDX_XN_PRESMS_2 TAB_XN_PRESMS EMPLOYEE_ID
IDX_XN_PRESMS_3 TAB_XN_PRESMS PRE_TIME
PK_TAB_XN_PRESMS TAB_XN_PRESMS PRE_ID
IDX_XN_PRESMS_4 TAB_XN_PRESMS SEND_TYPE
IDX_XN_PRESMS_DETAIL TAB_XN_PRESMS_DETAIL PRE_ID
IDX_XN_PRESMS_DETAIL_2 TAB_XN_PRESMS_DETAIL SEND_TIME
PK_TAB_XN_PRESMS_DETAIL TAB_XN_PRESMS_DETAIL DETAIL_ID
8 rows selected
--index的统计信息
SQL> SELECT TABLE_NAME,
2 INDEX_NAME,
3 TO_CHAR(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss')
4 FROM USER_INDEXES
5 WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL');
TABLE_NAME INDEX_NAME TO_CHAR(LAST_ANALYZED,'YYYY-MM
------------------------------ ------------------------------ ------------------------------
TAB_XN_PRESMS IDX_XN_PRESMS_1 2011-12-29 09:25:32
TAB_XN_PRESMS IDX_XN_PRESMS_2 2011-12-29 09:25:35
TAB_XN_PRESMS IDX_XN_PRESMS_3 2011-12-29 09:25:39
TAB_XN_PRESMS IDX_XN_PRESMS_4 2011-12-29 09:25:21
TAB_XN_PRESMS_DETAIL IDX_XN_PRESMS_DETAIL 2011-12-29 09:20:03
TAB_XN_PRESMS_DETAIL IDX_XN_PRESMS_DETAIL_2 2011-12-29 09:20:01
TAB_XN_PRESMS PK_TAB_XN_PRESMS 2011-12-29 09:25:46
TAB_XN_PRESMS_DETAIL PK_TAB_XN_PRESMS_DETAIL 2011-12-29 09:20:02
8 rows selected
--列的唯一度情况
SQL> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
2 FROM USER_TAB_COLS
3 WHERE (TABLE_NAME, COLUMN_NAME) IN
4 (SELECT TABLE_NAME, COLUMN_NAME
5 FROM USER_IND_COLUMNS
6 WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL'))
7 ORDER BY table_name,NUM_DISTINCT DESC;
TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------------------------ ------------------------------ ------------
TAB_XN_PRESMS PRE_ID 1999270
TAB_XN_PRESMS PRE_TIME 1342594
TAB_XN_PRESMS EMPLOYEE_ID 10676
TAB_XN_PRESMS COMPANY_ID 3136
TAB_XN_PRESMS SEND_TYPE 10
TAB_XN_PRESMS_DETAIL DETAIL_ID 3863184
TAB_XN_PRESMS_DETAIL PRE_ID 1996872
TAB_XN_PRESMS_DETAIL SEND_TIME 437526
8 rows selected
通过这些信息可以得出：
1)统计信息是最新收集过的
2)因为有多个单列index，数据库为了使得cost最小，可能选择了不合适的index(IDX_XN_PRESMS_2[EMPLOYEE_ID]/IDX_XN_PRESMS_1[COMPANY_ID]),使得出现AND-EQUAL，从而逻辑读偏高。对于这个sql，应该使用唯一度比较高的IDX_XN_PRESMS_3[PRE_TIME]
3)也可以通过修改index，实现程序高效，但是考虑到会影响启动程序，在没有十足的把握之前遵守hint优先原则
5.增加hint提示
SQL> SELECT /*+ index(TABXNPRESM0_ IDX_XN_PRESMS_3) */
2 COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_
3 FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_
4 WHERE (TABXNPRESM0_.COMPANY_ID = 346240)
5 AND (TABXNPRESM0_.EMPLOYEE_ID = 0)
6 AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd'))
7 AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1)
8 AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0))
9 AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0))
10 AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=641 Card=1 Bytes=40)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30)
3 2 NESTED LOOPS (Cost=641 Card=1 Bytes=40)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=638 Card=1 Bytes=25)
5 4 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_3' (NON-UNIQUE) (Cost=63 Card=22286)
6 3 INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1422 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从这里可以看出，程序的逻辑读下降了很多(从272188下降到1422)，得到了优化效果，提高了程序执行效率
结合上篇:BITMAP CONVERSION FROM ROWIDS,总结一个经验，如果同时使用到了一个表的多个index，效率一般情况下不会太高。同时也给各位提个醒，index并非越多越好，有时候会适得其反(建立index时需要考虑情况)