关于分区表中的全partition扫描问题

Oracle 10.2.0.4 ,  3 nodes RAC , Linux AS 5.3  64bit .  


具体执行计划见图片，    wip_d_sn_detail 是一个分区表 ，半年一个分区，其中 20100101 ～ 20100701 是第三个分区。
查询发现partition 3 的总数据量是 4100多万，  报表需要查询的时间段(一天) 数据量只有 52 万 。  



SELECT ROWNUM AS ID, plant_code , serial_number , work_order , batch_no ,
model_name , product_line ,
TO_CHAR ( in_station_time , 'YYYY/MM/DD HH24:MI:SS' ) AS in_station_time ,
op_no
FROM wip_d_sn_detail
WHERE ck_flag = 'N'
AND work_group = 'KIT1'
AND plant_code IN ( 'MI02' , 'MI03' , 'MI04' )
AND product_line = 'J302'
AND in_station_time >= TO_DATE ( '20100322 14:12:00' , 'YYYYMMDD HH24:MI:SS' )
AND in_station_time < TO_DATE ( '20100323 14:12:00' , 'YYYYMMDD HH24:MI:SS' )








SELECT /*INDEX(wip_d_sn_detail,IDX_WIP_D_SN_DETAIL_TIME)*/   
ROWNUM AS ID, plant_code , serial_number , work_order , batch_no ,
model_name , product_line ,
TO_CHAR ( in_station_time , 'YYYY/MM/DD HH24:MI:SS' ) AS in_station_time ,
op_no
FROM wip_d_sn_detail
WHERE ck_flag = 'N'
AND work_group = 'KIT1'
AND plant_code IN ( 'MI02' , 'MI03' , 'MI04' )
AND product_line = 'J302'
AND in_station_time >= TO_DATE ( '20100322 14:12:00' , 'YYYYMMDD HH24:MI:SS' )
AND in_station_time < TO_DATE ( '20100323 14:12:00' , 'YYYYMMDD HH24:MI:SS' )






查询partition 3 的总数据量是 4133万 （说明： 这里是半年设置一个partition）。

SELECT count(1)
FROM wip_d_sn_detail
WHERE in_station_time >= TO_DATE ('20100101 00:00:00', 'YYYYMMDD HH24:MI:SS')
AND in_station_time < TO_DATE ('20100701 00:00:00', 'YYYYMMDD HH24:MI:SS') ;






查询partition 3 中 0322～0323 这一天内的数据量是 52 万 。

SELECT count(1)
FROM wip_d_sn_detail
WHERE  in_station_time >= TO_DATE ('20100322 14:12:00', 'YYYYMMDD HH24:MI:SS')
AND in_station_time < TO_DATE ('20100323 14:12:00', 'YYYYMMDD HH24:MI:SS')










addhint.jpg

no-hint.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-659776/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-659776/