Oracle autotrace – pstart and pstop always the same

execution-planoraclepartitioning

I have been executing autotrace against a partitioned table. The table is partitioned by month range. For the data I have so far there are 4 partitions – however, when I execute autotrace against query in which WHERE clause uses partitioning key, the pstart is always 1, and pstop is 1048575. Does that mean that it doesn't utilize the partition pruning properly? Shouldn't it come back as something like pstart=1 and pstop=1 (or 2 and 2 etc) if I'm looking to retrieve data of a single month?

Statement used to create partitioning

PARTITION BY RANGE(event_time) 
INTERVAL (numtomyinterval(1, 'month'))
(PARTITION part_01 values less than (to_date('2010-01-01', YYYY-MM-DD)));

SQL query issues that was analyzed by autotrace

SELECT * FROM EXAMPLE
WHERE TO_DATE(SUBSTR(event_time, 1, 10), 'dd-MM-yy' BETWEEN TO_DATE('06-JUL-2020', 'dd-MON-yy')
AND TO_DATE('06-JUL-2020', 'dd-MON-yy');

Autotrace results in
enter image description here

Best Answer

Sure it should, given that you wrote proper SQL. But you used a derived value in the filter conditions instead of the actual column.

Actual condition in SQL should be something like this (I also changed the date format string and the values to make some sense):

where event_time between BETWEEN TO_DATE('06-JUL-2020', 'dd-MON-yyyy')
AND TO_DATE('07-JUL-2020', 'dd-MON-yyyy')
Related Question