Oracle Partition
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,
Software/Hardware used:
Software/Hardware used:
ASKED:
May 27, 2005 8:09 PM
UPDATED:
June 6, 2005 1:38 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
What does your subquery look like?
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
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
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.