帮别人看了个案例,远程看的,没深入追究具体原因,问题是一个查询出的记录为0,但是在加上限制条件后,查询出来的记录就不为0了,一听说有这样的问题,也感到奇怪,决定先看下执行计划,看是否能在执行计划中看出什么问题,版本是10.2.0.4
下面是有问题的查询的执行计划
select /*+ gather_plan_statistics */ count(*) from ic_general_b b left join ic_general_h h on b.cgeneralhid = h.cgeneralhid
left join ts_batchcode c on b.vbatchcode = c.vbatchcode left join ts_batchcode_b d on c.pk_batchcode = d.pk_batchcode where
h.cbilltypecode = '47' and b.dr = 0 and h.dr = 0 and d.dr = 0 and d.leibie in ('1', '2', '3', '4', '0') and b.dbizdate
= '2016-02-04' -- and h.vbillcode='WW1602040014'
Plan hash value: 525439825
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.05 | 7522 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | TS_BATCHCODE_B | 1 | 1 | 0 |00:00:00.05 | 7522 | | | |
| 3 | NESTED LOOPS | | 1 | 1957 | 1 |00:00:00.05 | 7522 | | | |
|* 4 | HASH JOIN | | 1 | 1732 |