为何查询条件中多了　OR 选项，索引就走全扫描？

ZALBB · 发表于 2017-8-17 15:25

newkid 发表于 2017-8-16 23:51
那么索引列到底是不是非空呢？只有你自己知道，我们是看不到的。
如果不是非空，那么你可以构造一个用例， ...

为避免排序，用　INDEX FULL SCAN 作全扫描后回表，我觉得这种说法不可接受，

排序的数据量未必很大，满足条件的记录可能也就几条（实际上只有3条），但全索引扫描，有6M条记录，此时作这种选择，显然不是最佳方法，

ZALBB · 发表于 2017-8-17 15:31

newkid 发表于 2017-8-16 23:26
另外一贴有人回答你了，加上OR之后放松了条件，所以RANGE扫描出来的记录会有遗漏的，已经靠不住了，必须全 ...

我去掉　ROWNUM <= 5 这条件后，得到的执行计划如下：

SQL>SELECT * FROM
  2       (SELECT t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTID
FROM T_BD_LOTMASTER T0, (select FLOTID,FNAME from T_BD_LOTMASTER_L where FLocaleId = 2052) T0_L
WHERE t0.FLOTID = t0_L.FLOTID(+)
AND ( 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) 3 4 5 6  ;

Elapsed: 00:00:18.97

Execution Plan
----------------------------------------------------------
Plan hash value: 3437293865

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                      | 862 | 80166 |    | 48921  (14)| 00:00:55 |
| 1 |  SORT ORDER BY                |                      | 862 | 80166 |    | 48921  (14)| 00:00:55 |
| 2 | CONCATENATION                |                      |    |    |    |          |       |
|*  3 | FILTER                      |                      |    |    |    |          |       |
|*  4 |    HASH JOIN OUTER          |                      | 431 | 40083 | 106M| 46219  (14)| 00:00:52 |
|*  5 |    TABLE ACCESS FULL       | T_BD_LOTMASTER       |  1860K| 85M|    | 31159  (13)| 00:00:35 |
|*  6 |    TABLE ACCESS FULL       | T_BD_LOTMASTER_L    |  6196K| 265M|    |  6448  (20)| 00:00:08 |
|*  7 | FILTER                      |                      |    |    |    |          |       |
| 8 |    NESTED LOOPS OUTER       |                      | 431 | 40083 |    |  2701 (1)| 00:00:04 |
|*  9 |    TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER       | 431 | 20688 |    |  1401 (1)| 00:00:02 |
|* 10 |    INDEX RANGE SCAN       | IDX_BD_LOTMASTER_NO |  1435 |    |    | 15 (0)| 00:00:01 |
|* 11 |    TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER_L    |    1 | 45 |    |    3 (0)| 00:00:01 |
|* 12 |    INDEX RANGE SCAN       | IDX_BD_LOTMASTER_L_ID |    1 |    |    |    2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("FNAME" LIKE U'8%')
4 - access("T0"."FLOTID"="FLOTID"(+))
5 - filter("T0"."FUSEORGID"=100132 AND "T0"."FBIZTYPE"='1')
6 - filter("FLOCALEID"(+)=2052)
7 - filter(LNNVL("FNAME" LIKE U'8%'))
9 - filter("T0"."FUSEORGID"=100132 AND "T0"."FBIZTYPE"='1')
  10 - access("T0"."FNUMBER" LIKE U'8%')
   filter("T0"."FNUMBER" LIKE U'8%')
  11 - filter("FLOCALEID"(+)=2052)
  12 - access("T0"."FLOTID"="FLOTID"(+))

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   295749  consistent gets
      2  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
      1  sorts (memory)
      0  sorts (disk)
      3  rows processed

从上面可以看到，OR 之前的条件，走索引范围扫描来实现，OR 之后的条件，对两表作全表扫描，加了 rownum <= 5 后，优化器把这两个操作合在一起，先索引全扫描，获取ROWID后，回表，因为 fnumber 字段非空，因此，这种执行计划也能满足查询的要求，但显然，执行计划不是最佳，，，

ZALBB · 发表于 2017-8-17 15:42

solomon_007 发表于 2017-8-16 13:00
下面不用OR，和原来等价：

SELECT t0.FNUMBER FNUMBER,

为何要写3段，把　OR 前后条件，分成2段，不就可以了？

solomon_007 · 发表于 2017-8-17 15:45

ZALBB 发表于 2017-8-17 15:42
为何要写3段，把　OR 前后条件，分成2段，不就可以了？

改成两段也可以，那么 union all 就要改成 union !

solomon_007 · 发表于 2017-8-17 15:56

select t1.FNUMBER,
      t2.FNAME,
      t1.FLOTID
from (
      select t0.FNUMBER,
               t0.FLOTID
         from T_BD_LOTMASTER T0
         where t0.FUSEORGID IN (100132)
         AND t0.FBIZTYPE = '1'
      ) t1,
LEFT OUTER JOIN
   (
      select t0_L.FLOTID,
            t0_L.FNAME
         from T_BD_LOTMASTER_L T0_L
      where t0_L.FLocaleId = 2052
   ) t2
on t1.FLOTID = t2.FLOTID
where t1.FNUMBER LIKE N'8%' or t2.FNAME LIKE N'8%'

ZALBB · 发表于 2017-8-17 16:33

solomon_007 发表于 2017-8-17 15:56
select t1.FNUMBER,
t2.FNAME,
t1.FLOTID

耗时很久，比起 UNION 的写法，这写法作全表扫描，没用上索引，
SQL>select * from display;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1827126747

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                | 785 | 73005 |    | 47144  (14)| 00:00:53 |
|*  1 |  FILTER          |                |    |    |    |          |       |
|*  2 | HASH JOIN OUTER |                | 785 | 73005 | 104M| 47144  (14)| 00:00:53 |
|*  3 | TABLE ACCESS FULL| T_BD_LOTMASTER |  1833K| 83M|    | 32092  (13)| 00:00:36 |
|*  4 | TABLE ACCESS FULL| T_BD_LOTMASTER_L |  6214K| 266M|    |  6451  (20)| 00:00:08 |
------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T0"."FNUMBER" LIKE U'8%' OR "T0_L"."FNAME" LIKE U'8%')
2 - access("T0"."FLOTID"="T0_L"."FLOTID"(+))
3 - filter("T0"."FUSEORGID"=100132 AND "T0"."FBIZTYPE"='1')
4 - filter("T0_L"."FLOCALEID"(+)=2052)

19 rows selected.

Elapsed: 00:00:00.06
SQL>select t1.FNUMBER,
  2          t2.FNAME,
      t1.FLOTID
from (
      select t0.FNUMBER,
               t0.FLOTID
         from T_BD_LOTMASTER T0
         where t0.FUSEORGID IN (100132)
         AND t0.FBIZTYPE = '1'
      ) t1
LEFT OUTER JOIN
   (
      select t0_L.FLOTID,
            t0_L.FNAME
         from T_BD_LOTMASTER_L T0_L
      where t0_L.FLocaleId = 2052
   ) t2
on t1.FLOTID = t2.FLOTID
where t1.FNUMBER LIKE N'8%' or t2.FNAME LIKE N'8%';  3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

FNUMBER             FNAME                   FLOTID
-------------------- -------------------- ----------
89648964          89648964             1022270
897856             897856                1022291
8578                8578                   1050182

Elapsed: 00:00:14.48
SQL>

solomon_007 · 发表于 2017-8-17 16:50

select t1.FNUMBER,
      t2.FNAME,
      t1.FLOTID
from (
      select t0.FNUMBER,
               t0.FLOTID
         from T_BD_LOTMASTER T0
         where t0.FUSEORGID IN (100132)
         AND t0.FBIZTYPE = '1'
      ) t1,
LEFT OUTER JOIN
   (
      select t0_L.FLOTID,
            t0_L.FNAME
         from T_BD_LOTMASTER_L T0_L
      where t0_L.FLocaleId = 2052
   ) t2
on t1.FLOTID = t2.FLOTID
where t1.FNUMBER LIKE N'8%'
union
select t1.FNUMBER,
      t2.FNAME,
      t1.FLOTID
from (
      select t0.FNUMBER,
               t0.FLOTID
         from T_BD_LOTMASTER T0
         where t0.FUSEORGID IN (100132)
         AND t0.FBIZTYPE = '1'
      ) t1,
LEFT OUTER JOIN
   (
      select t0_L.FLOTID,
            t0_L.FNAME
         from T_BD_LOTMASTER_L T0_L
      where t0_L.FLocaleId = 2052
   ) t2
on t1.FLOTID = t2.FLOTID
where t2.FNAME LIKE N'8%'

--业务化简
--如果业务上可以确定 T_BD_LOTMASTER.FNUMBER 与 T_BD_LOTMASTER_L.FNAME 的字段是同一来源，不存在一个 LIKE N'8%' ，另一个不是 LIKE N'8%' ，那么可以直接这样简化：
select t1.FNUMBER,
      t2.FNAME,
      t1.FLOTID
from (
      select t0.FNUMBER,
               t0.FLOTID
         from T_BD_LOTMASTER T0
         where t0.FUSEORGID IN (100132)
         AND t0.FBIZTYPE = '1'
   AND t0.FNUMBER LIKE N'8%'
      ) t1,
LEFT OUTER JOIN
   (
      select t0_L.FLOTID,
            t0_L.FNAME
         from T_BD_LOTMASTER_L T0_L
      where t0_L.FLocaleId = 2052
   and t0_L.FNAME LIKE N'8%'
   ) t2
on t1.FLOTID = t2.FLOTID

gdutllf2006 · 发表于 2017-12-15 16:58

接楼主的问题，最近一直想类似的问题，
需求是很简单每个月1号的时候通过java的定时任务把这个累计值归零．
desc T_RISK_BANK_SUM
BANK_SUM_ID                                                                                                                                        NUMBER(12)
DAY_SUM                                                                                                                                              NUMBER(17,2)
MONTH_SUM                                                                                                                                           NUMBER(17,2)

当前的SQL是这样:
update t_risk_bank_sum set day_sum=0,month_sum=0 where day_sum >0 or month_sum>0;

查了下上个月的记录数据的分布是这样的.
SQL> select cnt, cnt_day_sum/cnt, cnt_month_sum/cnt from (select count(*) cnt ,sum(decode(day_sum,0,0,1)) cnt_day_sum,sum(decode(month_sum,0,0,1)) cnt_month_sum from t_risk_bank_sum  a );

   CNT CNT_DAY_SUM/CNT CNT_MONTH_SUM/CNT
---------- --------------- -----------------
  19999880    .049746348       .04993345
现在的索引是复合索引:
OWNER          TABNAME                INDNAME                               UNIQ    STATUS COLNAME
--------------- ------------------------- ---------------------------------------- --------- -------- -------------------------
BPEP_RISK    T_RISK_BANK_SUM          IDX_T_RISK_BANK_DAY_MONTH             NONUNIQUE VALID DAY_SUM
BPEP_RISK    T_RISK_BANK_SUM          IDX_T_RISK_BANK_DAY_MONTH             NONUNIQUE VALID MONTH_SUM

一直在想怎么优化这个SQL或者改写SQL.　
or条件的时候，只能走FTS
SQL> explain plan for update bpep_risk.t_risk_bank_sum set day_sum=0,month_sum=0 where day_sum >0 or month_sum>0;

Explained.

SQL> @plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1136648923

--------------------------------------------------------------------------------------
| Id  | Operation       | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT |                | 903K|  4409K| 87215 (6)| 00:07:36 |
| 1 |  UPDATE          | T_RISK_BANK_SUM |    |    |          |       |
|*  2 | TABLE ACCESS FULL| T_RISK_BANK_SUM | 903K|  4409K| 87215 (6)| 00:07:36 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

2 - filter("DAY_SUM">0 OR "MONTH_SUM">0)

14 rows selected.

请教下各位专家有啥好的方案．

我想的有如下几个
1 直接用SQL:  truncate table bpep_risk.t_risk_bank_sum . 这是效率最高的SQL，但在实际环境中我们是不允许业务代码SQL中使用DDL语句的．
2 通过Oracle存储过程. -- 同样被禁用的.
3 对day_sum,month_sum分别建立单列索引 ?

newkid · 发表于 2017-12-15 23:46

merge into t_risk_bank_sum t (
using (select rowid rid from t_risk_bank_sum where day_sum >0 union select rowid rid from t_risk_bank_sum where month_sum >0) d
on (d.rid=t.rowid)
when matched then update set day_sum=0,month_sum=0;

gdutllf2006 · 发表于 2017-12-19 22:06

谢谢，嗯，用Merge来做．

为何查询条件中多了 OR 选项，索引就走全扫描？

为何查询条件中多了　OR 选项，索引就走全扫描？