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
具体执行计划见图片， 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博客 ” ，链接：https://blog.itpub.net/35489/viewspace-659776/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
