半瓶
{ Keywords : 互联网 数据库 网站架构 伪文艺 }
对大数据量查询时，合理使用索引能极大的提高查询效率，以下几则案例是前一段时间优化系统时做的，简单记录一下。
案例一，优化前的查询执行计划：
SQL> set autotrace traceonly
SQL> set timing on
SQL> set linesize 200
SQL>
SQL> select count(*) from busilink
2 where (wpcode='074' or wpcode='078' or wpcode='099' ) and wcode ='0795'
3 or (wcode in (select rwcode from ratifyauth where wcodeto = '0795') and (wpcode='074' or wpcode='078' or wpcode='099' ) )
4 /Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 4220767725------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 131 (3)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX FAST FULL SCAN| BUSILINK_PRIMARYKEY | 1824 | 16416 | 131 (3)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | RATIFYAUTH_PRIMARYKEY | 1 | 11 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("WCODE"='0795' OR EXISTS (SELECT 0 FROM "RATIFYAUTH" "RATIFYAUTH" WHERE
"WCODETO"='0795' AND "RWCODE"=:B1))
3 - filter("WPCODE"='074' OR "WPCODE"='078' OR "WPCODE"='099')
4 - access("RWCODE"=:B1 AND "WCODETO"='0795')Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
818 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
经分析后在wpcode,wcode两列上建立复合索引：
SQL> create index idx_busilink_wpcode_wcode on busilink(wpcode,wcode) tablespace erp_index;
Index created.
Elapsed: 00:00:00.36
SQL>
SQL> select count(*) from busilink
2 where (wpcode='074' or wpcode='078' or wpcode='099' ) and wcode ='0795'
3 or (wcode in (select rwcode from ratifyauth where wcodeto = '0795') and (wpcode='074' or wpcode='078' or wpcode='099' ) )
4 /Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2874463383-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | FILTER | | | | | |
| 3 | INLIST ITERATOR | | | | | |
|* 4 | INDEX RANGE SCAN| IDX_BUSILINK_WPCODE_WCODE | 1824 | 16416 | 7 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | RATIFYAUTH_PRIMARYKEY | 1 | 11 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("WCODE"='0795' OR EXISTS (SELECT 0 FROM "RATIFYAUTH" "RATIFYAUTH" WHERE
"WCODETO"='0795' AND "RWCODE"=:B1))
4 - access("WPCODE"='074' OR "WPCODE"='078' OR "WPCODE"='099')
5 - access("RWCODE"=:B1 AND "WCODETO"='0795')Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
244 consistent gets
7 physical reads
0 redo size
514 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
逻辑读有大幅度的减少，效果明显。
案例二，单列索引，建立索引前走全表扫描：
SQL> select count(*)
2 from
3 ccode where groupccode='9010000000'
4 /Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 2879317460----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 901 (2)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| CCODE | 1 | 11 | 901 (2)| 00:00:11 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("GROUPCCODE"='9010000000')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4036 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
根据查询条件建立索引：
SQL> create index idx_ccode_groupccode on ccode(groupccode) tablespace erp_index;
Index created.
Elapsed: 00:00:00.45
SQL>
SQL> alter session set events = 'immediate trace name flush_cache';Session altered.
Elapsed: 00:00:01.40
SQL> select count(*) from ccode where groupccode='9010000000';Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2074245817------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | INDEX RANGE SCAN| IDX_CCODE_GROUPCCODE | 1 | 11 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("GROUPCCODE"='9010000000')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
2 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
案例三，在不同列上建立复合索引的效果比对：
优化之前：
SQL> alter session set events = 'immediate trace name flush_cache';
Session altered.
Elapsed: 00:00:00.15
SQL>
SQL> SELECT IHCODE, HCODE, ETRDCODE, HSTATUS, SUBSTR(BCODE, 1, 5) AS BCODE1, BCODE,
2 RMB, CCODE, GCODELIST, QTYLIST, QTYUNITLIST, BUSIMODE1, BUSIMODE2,
3 INOUTMODE, ECODE, VPREPARE, EDITION, predate,'*', BCODE, HSTATUS, HTYPE,
4 BUSIMODE1, INOUTMODE, ECODE,CONTRACTTBL.predate
5 FROM
6 CONTRACTTBL WHERE SBCODE <> '200994' AND (IHCODE LIKE '________________'
7 OR IHCODE LIKE '________________xx') AND (BCODE LIKE '01%' OR BCODE
8 LIKE '02%' OR BCODE LIKE '03%' OR BCODE LIKE '04%' OR BCODE LIKE '05%' OR
9 BCODE LIKE '06%' OR BCODE LIKE '07%' OR BCODE LIKE '08%' OR BCODE LIKE
10 '09%' OR BCODE LIKE '10%' OR BCODE LIKE '11%' OR BCODE LIKE '90%' OR BCODE
11 LIKE '91%' OR BCODE LIKE '01%' OR BCODE LIKE '02%' OR BCODE LIKE '03%' OR
12 BCODE LIKE '04%' OR BCODE LIKE '05%' OR BCODE LIKE '06%' OR BCODE LIKE
13 '07%' OR BCODE LIKE '08%' OR BCODE LIKE '09%' OR BCODE LIKE '10%' OR BCODE
14 LIKE '11%' OR BCODE LIKE '90%' OR BCODE LIKE '91%' OR BCODE LIKE '02%' OR
15 BCODE LIKE '03%' OR BCODE LIKE '05%' OR BCODE LIKE '07%' OR BCODE =
16 '01100040049') AND HTYPE ='190301' AND INOUTMODE ='01' AND PREDATE>=
17 TO_DATE('2009-01-01', 'yyyy-mm-dd') ORDER BY IHCODE DESC
18 /15815 rows selected.
Elapsed: 00:00:37.96
Execution Plan
----------------------------------------------------------
Plan hash value: 693144140------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 280 | 3640 (1)| 00:00:44 |
| 1 | SORT ORDER BY | | 2 | 280 | 3640 (1)| 00:00:44 |
|* 2 | TABLE ACCESS BY INDEX ROWID | CONTRACTTBL | 2 | 280 | 3639 (1)| 00:00:44 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 4 | BITMAP OR | | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 6 | SORT ORDER BY | | | | | |
|* 7 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 9 | SORT ORDER BY | | | | | |
|* 10 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 11 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 12 | SORT ORDER BY | | | | | |
|* 13 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 14 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 15 | SORT ORDER BY | | | | | |
|* 16 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 17 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 18 | SORT ORDER BY | | | | | |
|* 19 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 20 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 21 | SORT ORDER BY | | | | | |
|* 22 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 23 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 24 | SORT ORDER BY | | | | | |
|* 25 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 26 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 27 | SORT ORDER BY | | | | | |
|* 28 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 29 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 30 | SORT ORDER BY | | | | | |
|* 31 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 32 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 33 | SORT ORDER BY | | | | | |
|* 34 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 35 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 36 | SORT ORDER BY | | | | | |
|* 37 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 38 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 39 | SORT ORDER BY | | | | | |
|* 40 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 41 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 42 | SORT ORDER BY | | | | | |
|* 43 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
| 44 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 45 | SORT ORDER BY | | | | | |
|* 46 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("HTYPE"='190301' AND "PREDATE">=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"INOUTMODE"='01' AND "SBCODE"<>'200994' AND ("IHCODE" LIKE '________________' OR "IHCODE" LIKE
'________________xx'))
7 - access("BCODE"='01100040049')
filter("BCODE"='01100040049')
10 - access("BCODE" LIKE '03%')
filter("BCODE" LIKE '03%' AND "BCODE" LIKE '03%')
13 - access("BCODE" LIKE '06%')
filter("BCODE" LIKE '06%' AND "BCODE" LIKE '06%')
16 - access("BCODE" LIKE '09%')
filter("BCODE" LIKE '09%' AND "BCODE" LIKE '09%')
19 - access("BCODE" LIKE '02%')
filter("BCODE" LIKE '02%' AND "BCODE" LIKE '02%')
22 - access("BCODE" LIKE '10%')
filter("BCODE" LIKE '10%' AND "BCODE" LIKE '10%')
25 - access("BCODE" LIKE '08%')
filter("BCODE" LIKE '08%' AND "BCODE" LIKE '08%')
28 - access("BCODE" LIKE '05%')
filter("BCODE" LIKE '05%' AND "BCODE" LIKE '05%')
31 - access("BCODE" LIKE '11%')
filter("BCODE" LIKE '11%' AND "BCODE" LIKE '11%')
34 - access("BCODE" LIKE '07%')
filter("BCODE" LIKE '07%' AND "BCODE" LIKE '07%')
37 - access("BCODE" LIKE '04%')
filter("BCODE" LIKE '04%' AND "BCODE" LIKE '04%')
40 - access("BCODE" LIKE '01%')
filter("BCODE" LIKE '01%' AND "BCODE" LIKE '01%')
43 - access("BCODE" LIKE '90%')
filter("BCODE" LIKE '90%' AND "BCODE" LIKE '90%')
46 - access("BCODE" LIKE '91%')
filter("BCODE" LIKE '91%' AND "BCODE" LIKE '91%')Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
92614 consistent gets
92574 physical reads
0 redo size
2269387 bytes sent via SQL*Net to client
12063 bytes received via SQL*Net from client
1056 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
15815 rows processed
建立一个单列索引：
SQL> create index idx_contracttbl_predate on CONTRACTTBL(PREDATE) tablespace erp_index;
Index created.
Elapsed: 00:00:13.51
SQL> alter session set events = 'immediate trace name flush_cache';Session altered.
Elapsed: 00:00:00.14
SQL> SELECT IHCODE, HCODE, ETRDCODE, HSTATUS, SUBSTR(BCODE, 1, 5) AS BCODE1, BCODE,
2 RMB, CCODE, GCODELIST, QTYLIST, QTYUNITLIST, BUSIMODE1, BUSIMODE2,
3 INOUTMODE, ECODE, VPREPARE, EDITION, predate,'*', BCODE, HSTATUS, HTYPE,
4 BUSIMODE1, INOUTMODE, ECODE,CONTRACTTBL.predate
5 FROM
6 CONTRACTTBL WHERE SBCODE <> '200994' AND (IHCODE LIKE '________________'
7 OR IHCODE LIKE '________________xx') AND (BCODE LIKE '01%' OR BCODE
8 LIKE '02%' OR BCODE LIKE '03%' OR BCODE LIKE '04%' OR BCODE LIKE '05%' OR
9 BCODE LIKE '06%' OR BCODE LIKE '07%' OR BCODE LIKE '08%' OR BCODE LIKE
10 '09%' OR BCODE LIKE '10%' OR BCODE LIKE '11%' OR BCODE LIKE '90%' OR BCODE
11 LIKE '91%' OR BCODE LIKE '01%' OR BCODE LIKE '02%' OR BCODE LIKE '03%' OR
12 BCODE LIKE '04%' OR BCODE LIKE '05%' OR BCODE LIKE '06%' OR BCODE LIKE
13 '07%' OR BCODE LIKE '08%' OR BCODE LIKE '09%' OR BCODE LIKE '10%' OR BCODE
14 LIKE '11%' OR BCODE LIKE '90%' OR BCODE LIKE '91%' OR BCODE LIKE '02%' OR
15 BCODE LIKE '03%' OR BCODE LIKE '05%' OR BCODE LIKE '07%' OR BCODE =
16 '01100040049') AND HTYPE ='190301' AND INOUTMODE ='01' AND PREDATE>=
17 TO_DATE('2009-01-01', 'yyyy-mm-dd') ORDER BY IHCODE DESC
18 /15815 rows selected.
Elapsed: 00:00:14.21
Execution Plan
----------------------------------------------------------
Plan hash value: 567454617---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 280 | | 2277 (2)| 00:00:28 |
| 1 | SORT ORDER BY | | 2 | 280 | | 2277 (2)| 00:00:28 |
|* 2 | TABLE ACCESS BY INDEX ROWID | CONTRACTTBL | 2 | 280 | | 2276 (2)| 00:00:28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 4 | BITMAP AND | | | | | | |
| 5 | BITMAP OR | | | | | | |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 7 | SORT ORDER BY | | | | | | |
|* 8 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 9 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 10 | SORT ORDER BY | | | | | | |
|* 11 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 13 | SORT ORDER BY | | | | | | |
|* 14 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 16 | SORT ORDER BY | | | | | | |
|* 17 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 19 | SORT ORDER BY | | | | | | |
|* 20 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 21 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 22 | SORT ORDER BY | | | | | | |
|* 23 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 24 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 25 | SORT ORDER BY | | | | | | |
|* 26 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 27 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 28 | SORT ORDER BY | | | | | | |
|* 29 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 30 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 31 | SORT ORDER BY | | | | | | |
|* 32 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 33 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 34 | SORT ORDER BY | | | | | | |
|* 35 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 36 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 37 | SORT ORDER BY | | | | | | |
|* 38 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 39 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 40 | SORT ORDER BY | | | | | | |
|* 41 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 42 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 43 | SORT ORDER BY | | | | | | |
|* 44 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 45 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 46 | SORT ORDER BY | | | | | | |
|* 47 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | | | | 5 (0)| 00:00:01 |
| 48 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
| 49 | SORT ORDER BY | | | | 5240K| | |
|* 50 | INDEX RANGE SCAN | IDX_CONTRACTTBL_PREDATE | | | | 448 (1)| 00:00:06 |
---------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("HTYPE"='190301' AND "INOUTMODE"='01' AND "SBCODE"<>'200994' AND ("IHCODE" LIKE '________________'
OR "IHCODE" LIKE '________________xx'))
8 - access("BCODE"='01100040049')
filter("BCODE"='01100040049')
11 - access("BCODE" LIKE '03%')
filter("BCODE" LIKE '03%' AND "BCODE" LIKE '03%')
14 - access("BCODE" LIKE '06%')
filter("BCODE" LIKE '06%' AND "BCODE" LIKE '06%')
17 - access("BCODE" LIKE '09%')
filter("BCODE" LIKE '09%' AND "BCODE" LIKE '09%')
20 - access("BCODE" LIKE '02%')
filter("BCODE" LIKE '02%' AND "BCODE" LIKE '02%')
23 - access("BCODE" LIKE '10%')
filter("BCODE" LIKE '10%' AND "BCODE" LIKE '10%')
26 - access("BCODE" LIKE '08%')
filter("BCODE" LIKE '08%' AND "BCODE" LIKE '08%')
29 - access("BCODE" LIKE '05%')
filter("BCODE" LIKE '05%' AND "BCODE" LIKE '05%')
32 - access("BCODE" LIKE '11%')
filter("BCODE" LIKE '11%' AND "BCODE" LIKE '11%')
35 - access("BCODE" LIKE '07%')
filter("BCODE" LIKE '07%' AND "BCODE" LIKE '07%')
38 - access("BCODE" LIKE '04%')
filter("BCODE" LIKE '04%' AND "BCODE" LIKE '04%')
41 - access("BCODE" LIKE '01%')
filter("BCODE" LIKE '01%' AND "BCODE" LIKE '01%')
44 - access("BCODE" LIKE '90%')
filter("BCODE" LIKE '90%' AND "BCODE" LIKE '90%')
47 - access("BCODE" LIKE '91%')
filter("BCODE" LIKE '91%' AND "BCODE" LIKE '91%')
50 - access("PREDATE">=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
filter("PREDATE">=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
25260 consistent gets
25225 physical reads
0 redo size
2269387 bytes sent via SQL*Net to client
12063 bytes received via SQL*Net from client
1056 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
15815 rows processed
建立复合索引：
SQL> drop index idx_contracttbl_predate
2 /Index dropped.
Elapsed: 00:00:00.83
SQL>
SQL> create index idx_contracttbl_htype_inout_predate on CONTRACTTBL(HTYPE, INOUTMODE,PREDATE) tablespace erp_index;
create index idx_contracttbl_htype_inout_predate on CONTRACTTBL(HTYPE, INOUTMODE,PREDATE) tablespace erp_index
*
ERROR at line 1:
ORA-00972: identifier is too longElapsed: 00:00:00.00
SQL> create index idx_contract_htiopre on CONTRACTTBL(HTYPE, INOUTMODE,PREDATE) tablespace erp_index;Index created.
Elapsed: 00:00:13.32
SQL> alter session set events = 'immediate trace name flush_cache';Session altered.
Elapsed: 00:00:38.90
SQL>
SQL> SELECT IHCODE, HCODE, ETRDCODE, HSTATUS, SUBSTR(BCODE, 1, 5) AS BCODE1, BCODE,
2 RMB, CCODE, GCODELIST, QTYLIST, QTYUNITLIST, BUSIMODE1, BUSIMODE2,
3 INOUTMODE, ECODE, VPREPARE, EDITION, predate,'*', BCODE, HSTATUS, HTYPE,
4 BUSIMODE1, INOUTMODE, ECODE,CONTRACTTBL.predate
5 FROM
6 CONTRACTTBL WHERE SBCODE <> '200994' AND (IHCODE LIKE '________________'
7 OR IHCODE LIKE '________________xx') AND (BCODE LIKE '01%' OR BCODE
8 LIKE '02%' OR BCODE LIKE '03%' OR BCODE LIKE '04%' OR BCODE LIKE '05%' OR
9 BCODE LIKE '06%' OR BCODE LIKE '07%' OR BCODE LIKE '08%' OR BCODE LIKE
10 '09%' OR BCODE LIKE '10%' OR BCODE LIKE '11%' OR BCODE LIKE '90%' OR BCODE
11 LIKE '91%' OR BCODE LIKE '01%' OR BCODE LIKE '02%' OR BCODE LIKE '03%' OR
12 BCODE LIKE '04%' OR BCODE LIKE '05%' OR BCODE LIKE '06%' OR BCODE LIKE
13 '07%' OR BCODE LIKE '08%' OR BCODE LIKE '09%' OR BCODE LIKE '10%' OR BCODE
14 LIKE '11%' OR BCODE LIKE '90%' OR BCODE LIKE '91%' OR BCODE LIKE '02%' OR
15 BCODE LIKE '03%' OR BCODE LIKE '05%' OR BCODE LIKE '07%' OR BCODE =
16 '01100040049') AND HTYPE ='190301' AND INOUTMODE ='01' AND PREDATE>=
17 TO_DATE('2009-01-01', 'yyyy-mm-dd') ORDER BY IHCODE DESC
18 /15815 rows selected.
Elapsed: 00:00:05.90
Execution Plan
----------------------------------------------------------
Plan hash value: 2443244798-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 280 | 117 (15)| 00:00:02 |
| 1 | SORT ORDER BY | | 2 | 280 | 117 (15)| 00:00:02 |
|* 2 | TABLE ACCESS BY INDEX ROWID | CONTRACTTBL | 2 | 280 | 116 (14)| 00:00:02 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 4 | BITMAP AND | | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 6 | SORT ORDER BY | | | | | |
|* 7 | INDEX RANGE SCAN | IDX_CONTRACT_HTIOPRE | 3294 | | 16 (0)| 00:00:01 |
| 8 | BITMAP OR | | | | | |
| 9 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 10 | SORT ORDER BY | | | | | |
|* 11 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 13 | SORT ORDER BY | | | | | |
|* 14 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 16 | SORT ORDER BY | | | | | |
|* 17 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 19 | SORT ORDER BY | | | | | |
|* 20 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 21 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 22 | SORT ORDER BY | | | | | |
|* 23 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 24 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 25 | SORT ORDER BY | | | | | |
|* 26 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 27 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 28 | SORT ORDER BY | | | | | |
|* 29 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 30 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 31 | SORT ORDER BY | | | | | |
|* 32 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 33 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 34 | SORT ORDER BY | | | | | |
|* 35 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 36 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 37 | SORT ORDER BY | | | | | |
|* 38 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 39 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 40 | SORT ORDER BY | | | | | |
|* 41 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 42 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 43 | SORT ORDER BY | | | | | |
|* 44 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 45 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 46 | SORT ORDER BY | | | | | |
|* 47 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
| 48 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 49 | SORT ORDER BY | | | | | |
|* 50 | INDEX RANGE SCAN | CONTRACTTBL_BCODE_RATIFYDATE | 3294 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("SBCODE"<>'200994' AND ("IHCODE" LIKE '________________' OR "IHCODE" LIKE
'________________xx'))
7 - access("HTYPE"='190301' AND "INOUTMODE"='01' AND "PREDATE">=TO_DATE('2009-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
filter("PREDATE">=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "INOUTMODE"='01' AND
"HTYPE"='190301')
11 - access("BCODE"='01100040049')
filter("BCODE"='01100040049')
14 - access("BCODE" LIKE '03%')
filter("BCODE" LIKE '03%' AND "BCODE" LIKE '03%')
17 - access("BCODE" LIKE '06%')
filter("BCODE" LIKE '06%' AND "BCODE" LIKE '06%')
20 - access("BCODE" LIKE '09%')
filter("BCODE" LIKE '09%' AND "BCODE" LIKE '09%')
23 - access("BCODE" LIKE '02%')
filter("BCODE" LIKE '02%' AND "BCODE" LIKE '02%')
26 - access("BCODE" LIKE '10%')
filter("BCODE" LIKE '10%' AND "BCODE" LIKE '10%')
29 - access("BCODE" LIKE '08%')
filter("BCODE" LIKE '08%' AND "BCODE" LIKE '08%')
32 - access("BCODE" LIKE '05%')
filter("BCODE" LIKE '05%' AND "BCODE" LIKE '05%')
35 - access("BCODE" LIKE '11%')
filter("BCODE" LIKE '11%' AND "BCODE" LIKE '11%')
38 - access("BCODE" LIKE '07%')
filter("BCODE" LIKE '07%' AND "BCODE" LIKE '07%')
41 - access("BCODE" LIKE '04%')
filter("BCODE" LIKE '04%' AND "BCODE" LIKE '04%')
44 - access("BCODE" LIKE '01%')
filter("BCODE" LIKE '01%' AND "BCODE" LIKE '01%')
47 - access("BCODE" LIKE '90%')
filter("BCODE" LIKE '90%' AND "BCODE" LIKE '90%')
50 - access("BCODE" LIKE '91%')
filter("BCODE" LIKE '91%' AND "BCODE" LIKE '91%')Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15474 consistent gets
15439 physical reads
0 redo size
2269387 bytes sent via SQL*Net to client
12063 bytes received via SQL*Net from client
1056 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
15815 rows processed
除了改写明显有问题的SQL语句，针对索引的优化是提高查询性能的最快最简单的办法。
分享这篇文章
技术组织
