ORACLE Execution Plan (7)-partitions cannot be determined using the TRUNC (SYSDATE)-1 Method

In the Execution Plan of the partition, it indicates that the field has different partition operations, including two

PARTITION RANGE ITERATOR
Partition range single

The cost of ITERATOR is higher than that of SINGLE. It seems that ITERATOR is cross-partition.

There is also a partition range iterator all, which is to access ALL partitions.

However, I don't like PARTITION RANGE ITERATOR very much.

Select PROVCODE, AREACODE, to_char (CREATETIME, 'yyyymmdd ')
From sms_mms_send_his

Where status = 100
And CREATETIME> = trunc (sysdate)-1
And CREATETIME <trunc (sysdate)

SELECT STATEMENT REMOTE, GOAL = ALL_ROWS Depth=0  Operation=SELECT STATEMENT Cost=20295   CPU cost=3824735837 IO cost=20128 FILTER Depth=1  Operation=FILTER  Filter predicates=TRUNC(SYSDATE@!)-1<TRUNC(SYSDATE@!)     PARTITION RANGE ITERATOR Depth=2  Operation=PARTITION RANGE Cost=20295   CPU cost=3824735837 IO cost=20128   TABLE ACCESS FULL Depth=3  Operation=TABLE ACCESS Cost=20295 Filter predicates="STATUS"=100 AND "CREATETIME">=TRUNC(SYSDATE@!)-1 AND "CREATETIME"<TRUNC(SYSDATE@!)  CPU cost=3824735837 IO cost=20128

If I specify a partition

select PROVCODE,AREACODE,to_char(CREATETIME,'yyyymmdd')from sms_mms_send_his_info  partition(SMS_MMS_SEND_HIS_INFO_201210421)where status = 100

SELECT STATEMENT, GOAL = ALL_ROWS Depth=0  Operation=SELECT STATEMENT Cost=2   CPU cost=7121 IO cost=2 PARTITION RANGE SINGLE Depth=1  Operation=PARTITION RANGE Cost=2   CPU cost=7121 IO cost=2  TABLE ACCESS FULL Depth=2  Operation=TABLE ACCESS Cost=2 Filter predicates="STATUS"=100  CPU cost=7121 IO cost=2

From the execution plan, ITERATOR is higher than SINGLE, and createtime is the partition keyword, but it is not an ACCESS predicate, or a predicate condition for positioning, but instead a filter condition.

To avoid this situation

and CREATETIME >= trunc(sysdate)-1and CREATETIME < trunc(sysdate)

Change

and CREATETIME >= to_date('','yyyymmdd')

and CREATETIME < to_date('','yyyymmdd') 

Method

+/* + Full () */prompt to avoid using the partition Local index. although our DBA said that trunc (sysdate) is not executed across partitions, it is better to take control of itself.