ORACLE 11204, SOLARIS

如下：原始寫法，where 條件中，有 OR 條件，這時，索引　IDX_BD_LOTMASTER_NO　走全索引掃描，導致效率非常低，

-bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 16 09:17:30 2017

Copyright (c) 1982, 2013, Oracle.??All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>set autot trace exp stat;

SQL>SELECT * FROM

2??(SELECT t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTID

FROM T_BD_LOTMASTER T0

LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L ON (t0.FLOTID = t0_L.FLOTID AND t0_L.FLocaleId = 2052)

WHERE (((t0.FUSEORGID IN (100132)) AND t0.FBIZTYPE = '1')

AND ((t0.FNUMBER LIKE N'8%') OR

(t0_L.FNAME LIKE N'8%') ) ) ORDER BY t0.FNUMBER ASC) WHERE ROWNUM <= 5? ?3? ? 4? ? 5? ? 6? ? 7??;

Elapsed: 00:00:30.64

Execution Plan

----------------------------------------------------------

Plan hash value: 3360367673

---------------------------------------------------------------------------------------------------------

| Id??| Operation? ?? ?? ?? ?? ?? ?? ???| Name? ?? ?? ?? ?? ?? ?| Rows??| Bytes | Cost (%CPU)| Time? ???|

---------------------------------------------------------------------------------------------------------

|? ?0 | SELECT STATEMENT? ?? ?? ?? ?? ? |? ?? ?? ?? ?? ?? ?? ???|? ???5 |??2635 | 67582? ?(1)| 00:01:16 |

|*??1 |??COUNT STOPKEY? ?? ?? ?? ?? ?? ?|? ?? ?? ?? ?? ?? ?? ???|? ?? ? |? ?? ? |? ?? ?? ?? ?|? ?? ?? ? |

|? ?2 |? ?VIEW? ?? ?? ?? ?? ?? ?? ?? ???|? ?? ?? ?? ?? ?? ?? ???|? ???5 |??2635 | 67582? ?(1)| 00:01:16 |

|*??3 |? ? FILTER? ?? ?? ?? ?? ?? ?? ???|? ?? ?? ?? ?? ?? ?? ???|? ?? ? |? ?? ? |? ?? ?? ?? ?|? ?? ?? ? |

|? ?4 |? ???NESTED LOOPS OUTER? ?? ?? ? |? ?? ?? ?? ?? ?? ?? ???|? ???5 |? ?465 | 67582? ?(1)| 00:01:16 |

|*??5 |? ?? ?TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER? ?? ???|??1860K|? ? 85M| 35032? ?(1)| 00:00:40 |

|? ?6 |? ???INDEX FULL SCAN? ?? ?? ???| IDX_BD_LOTMASTER_NO | 35968 |? ?? ? |? ?309? ?(2)| 00:00:01 |

|*??7 |? ?? ?TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER_L? ?? ?|? ???1 |? ? 45 |? ???3? ?(0)| 00:00:01 |

|*??8 |? ?? ? INDEX RANGE SCAN? ?? ?? ? | IDX_BD_LOTMASTER_L_ID |? ???1 |? ?? ? |? ???2? ?(0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=5)

3 - filter("T0"."FNUMBER" LIKE U'8%' OR "T0_L"."FNAME" LIKE U'8%')

5 - filter("T0"."FUSEORGID"=100132 AND "T0"."FBIZTYPE"='1')

7 - filter("T0_L"."FLOCALEID"(+)=2052)

8 - access("T0"."FLOTID"="T0_L"."FLOTID"(+))

Statistics

----------------------------------------------------------

1??recursive calls

0??db block gets

9352435??consistent gets

113??physical reads

724??redo size

809??bytes sent via SQL*Net to client

520??bytes received via SQL*Net from client

2??SQL*Net roundtrips to/from client

0??sorts (memory)

0??sorts (disk)

3??rows processed

SQL>

--去掉 OR 條件后，IDX_BD_LOTMASTER_NO　走范圍掃描，效率高多了，問：為何多了　OR??條件后，索引就走全掃描？SQL>SELECT * FROM

2? ? (SELECT t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTID

FROM T_BD_LOTMASTER T0

LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L ON (t0.FLOTID = t0_L.FLOTID AND t0_L.FLocaleId = 2052 )

WHERE (((t0.FUSEORGID IN (100132)) AND t0.FBIZTYPE = '1')

AND ( (t0.FNUMBER LIKE N'8%')) ) ORDER BY t0.FNUMBER ASC)??3? ? 4? ? 5? ? 6??;

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 1298050334

------------------------------------------------------------------------------------------------------

| Id??| Operation? ?? ?? ?? ?? ?? ???| Name? ?? ?? ?? ?? ?? ?| Rows??| Bytes | Cost (%CPU)| Time? ???|

------------------------------------------------------------------------------------------------------

|? ?0 | SELECT STATEMENT? ?? ?? ?? ? |? ?? ?? ?? ?? ?? ?? ???|? ?431 | 40083 |??2701? ?(1)| 00:00:04 |

|? ?1 |??NESTED LOOPS OUTER? ?? ?? ? |? ?? ?? ?? ?? ?? ?? ???|? ?431 | 40083 |??2701? ?(1)| 00:00:04 |

|*??2 |? ?TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER? ?? ???|? ?431 | 20688 |??1401? ?(1)| 00:00:02 |

|*??3 |? ?INDEX RANGE SCAN? ?? ?? ? | IDX_BD_LOTMASTER_NO? ?|??1435 |? ?? ? |? ? 15? ?(0)| 00:00:01 |

|*??4 |? ?TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER_L? ?? ?|? ???1 |? ? 45 |? ???3? ?(0)| 00:00:01 |

|*??5 |? ? INDEX RANGE SCAN? ?? ?? ? | IDX_BD_LOTMASTER_L_ID |? ???1 |? ?? ? |? ???2? ?(0)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("T0"."FUSEORGID"=100132 AND "T0"."FBIZTYPE"='1')

3 - access("T0"."FNUMBER" LIKE U'8%')

filter("T0"."FNUMBER" LIKE U'8%')

4 - filter("T0_L"."FLOCALEID"(+)=2052)

5 - access("T0"."FLOTID"="T0_L"."FLOTID"(+))

Statistics

----------------------------------------------------------

1??recursive calls

0??db block gets

22??consistent gets

0??physical reads

0??redo size

809??bytes sent via SQL*Net to client

520??bytes received via SQL*Net from client

2??SQL*Net roundtrips to/from client

0??sorts (memory)

0??sorts (disk)

3??rows processed

總結

以上是生活随笔為你收集整理的oracle中or会使索引,为何查询条件中多了　OR 选项，索引就走全扫描？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。