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