Timeline for Partition pruning with multiple date columns
Current License: CC BY-SA 3.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 17, 2012 at 13:40 | history | edited | Chris Saxon | CC BY-SA 3.0 |
removed incorrect answer; replaced with info regarding functions on partition columns
|
| Dec 16, 2012 at 15:19 | comment | added | Chris Saxon |
@sjk - I think you need to post your code with dates that isn't pruning, rather than the integer/year example that is. Although I've called the columns year in my example, they are just dates and partition pruning is working (for me). So I'm struggling to see why it's not working for you.
|
|
| Dec 14, 2012 at 13:15 | comment | added | sjk | The view above contains (partition_year=start_year and partition_year=end_year), if start_year would instead be start_date, the first half of view would not match to most rows any more. I tried to change the view to include (extract year), but then I could not get pruning any more (it did a full table scan). I could of course also change partition_year to partition_date, but then most of the rows would end to overflow partition (since typical date range is more than one day). | |
| Dec 14, 2012 at 13:00 | comment | added | Chris Saxon | @sjk - yep, you have to trade off increased storage vs. improved performance. You could always create the partitioned MVs on subset of your data if, for example, most of the queries only cover dates in the past two years. I'm not sure what you mean when you say that that equality predicates would miss many rows - could you give a more detail please? | |
| Dec 14, 2012 at 10:18 | comment | added | sjk | Materialized view with query rewrite might indeed work. Of course it would require multiple copies of data, so it's not an ideal solution, but worth considering. The date values in original data have granularity of one day. If the scheme above is used, equality predicate would miss many of the rows that span multiple days. | |
| Dec 13, 2012 at 12:39 | history | answered | Chris Saxon | CC BY-SA 3.0 |