0

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

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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