Oracle Partition

pts.
Tags:
Application software
Development
Oracle
Hi, I have a query that looks like select * from fact, dim_time where fact.my_dt = dim_time.clndr_dt and fact.my_dt between '1-jan-2005' and '10-mar-2005' this query eliminates partitions which are based on week. Now, if i replace the literal strings in the between clause with some kind of sub-queries that provide the values at run time, the partitions dont get eliminated. any ideas, clues, suggestions, advices? all are welcome and appreciated. Regards,

Answer Wiki

Thanks. We'll let you know when a new response is added.

Correct me if I am wrong, but I am wondering if your example is working correct in the first place. Your example shows a character conparison on date: date between ’1-jan-2005′ and ’10-mar-2005′.

Consider these entries in your file/table/database:
1-jan-2005
10-apr-2005
10-mar-2005
2-jan-2005
22-feb-2005
3-mar-2005
5-jan-2005

If the date field is a character field, then the first 3 entries get selected, because of the fact that they are sorted in this order.

It would be a different story if your entries in the file/table/database are filled in like this:
20050101
20050102
20050105
20050222
20050303
20050310
20050410

In this case you would select: date between ’20050101′ and ’20050310′, which would select 6 entries. If your routine builds the dates like this, then the dynamic selection will work.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Apontey
    What does your subquery look like?
    0 pointsBadges:
    report
  • Hipcheck
    Nothing personal, but it doesn't look like the first respondant is familar with Oracle date format. It could be that your sub-queries don't return what you think they do. By that I mean the BETWEEN is looking for dates and the queries may be returning characters. Check out the Oracle TO_DATE function. May have to change your clause to fact.my_dt BETWEEN TO_DATE(sub-query1) and TO_DATE(sub-query2). Hope this helps, ~ M
    0 pointsBadges:
    report
  • Hipcheck
    Nothing personal, but it doesn't look like the first respondant is familar with Oracle date format. It could be that your sub-queries don't return what you think they do. By that I mean the BETWEEN is looking for dates and the queries may be returning characters. Check out the Oracle TO_DATE function. May have to change your clause to fact.my_dt BETWEEN TO_DATE(sub-query1) and TO_DATE(sub-query2). Hope this helps, ~ M
    0 pointsBadges:
    report
  • Gsentveld
    Hi What you are assuming is that Oracle will convert the literal date strings to the datatype of you fact.my_dt column. Since you say the subquery works, and the 'hardcoded' string literals does not, I assume that in your subquery you compare the fact.my_dt with a DATE, and NOT a varchar2. Put a to_date around your literals and the query should pick up your partitions. For comparison try: select * from dual where dummy = 1. You will get an 'invalid number' error. Why? Because Oracle converts the value in dummy to a number, not the number to a varchar2. (you can create a test table with a varchar and only put numbers in it, and the exception will not be thrown) Now try: select * from dual where dummy = to_char(1). Works like a charm In your example Oracle will convert the value in fact.my_dt to varchar2, which works so no exception is thrown, but then you loose the concepts of weeks, so the partition can not be used.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following