Partition Pruning

  Hi,

I have a table with 2 billion rows range partitioned (monthly) with timestamp data type. Please see the sample column data output.

Quote:

15-APR-10 09.09.54.000000 PM 22-APR-10 03.46.53.000000 PM 18-APR-10 12.25.01.000000 PM 20-APR-10 12.45.12.000000 PM 15-APR-10 11.46.47.000000 PM 21-APR-10 11.53.44.000000 PM 19-APR-10 02.05.53.000000 PM 18-APR-10 09.41.38.000000 PM 19-APR-10 03.37.56.000000 AM

Please find the where clause predicate from the query I am using. Actual partition column is renamed as COLUMN_NAME in the below query.

Quote:

to_number(TO_CHAR(A.COLUMN_NAME,'YYYYMM')) between to_number(to_char(ADD_MONTHS(to_date(to_char('20101130'),'YYYYMMDD'),-3),'YYYYMM')) and to_number(to_char(ADD_MONTHS(to_date(to_char('20101130'),'YYYYMMDD'),-1),'YYYYMM'))