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

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

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


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')
  • I forgot to mention that event_time is a timestamp - using your query doesn't return any results. – S. Lokis Oct 25 '20 at 16:22
  • @S.Lokis Then use TO_TIMESTAMP instead of TO_DATE. – Balazs Papp Oct 25 '20 at 16:30
  • I understand your point though, changed SQL to work and now pstart and pstop both have KEY. – S. Lokis Oct 25 '20 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.