SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Partition pruning with virtual column

user10742038Nov 19 2013 — edited Nov 19 2013

Hi,

using 11.1.0.7 64 bit. I have a table T partitioned monthly on TRANSACTION_DATE with datatype DATE that has date-time values.

I want to create a virtual column DATE_KEY which will be either TRUNC(TRANSACTION_DATE) or TO_NUMBER(TO_CHAR(TRANSACTION_DATE,'YYYYMMDD')). DATE_KEY is a foreign key that will join to D_CALENDAR dimension.

QUESTIONS; 1) If query has D_CALENDAR.DATE_KEY = some date, will partition pruning occur on table T ?

2) Ideally, I would like DATE_KEY to be an INTEGER. Will the TO_NUMBER(TO_CHAR() slow the query down noticeably? If it does, then I'll go with TRUNC(TRANSACTION_DATE).

Thanks!

Adam

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 17 2013

Added on Nov 19 2013

#plsql

3 comments

813 views