[EMAIL PROTECTED] ("Simon Riggs") writes:
> Well, its fairly straightforward to auto-generate the UNION ALL view, and
> important as well, since it needs to be re-specified each time a new
> partition is loaded or an old one is cleared down. The main point is that
> the constant placed in front of each table must in some way relate to the
> data, to make it useful in querying. If it is just a unique constant, chosen
> at random, it won't do much for partition elimination. So, that tends to
> make the creation of the UNION ALL view an application/data specific thing.Ah, that's probably a good thought. When we used big "UNION ALL" views, it was with logging tables, where there wasn't really any meaningful distinction between partitions. So you say that if the VIEW contains, within it, meaningful constraint information, that can get applied to chop out irrelevant bits? That suggests a way of resurrecting the idea... Might we set up the view as: create view combination_of_logs as select * from table_1 where txn_date between 'this' and 'that' union all select * from table_2 where txn_date between 'this2' and 'that2' union all select * from table_3 where txn_date between 'this3' and 'that3' union all select * from table_4 where txn_date between 'this4' and 'that4' union all ... ad infinitum union all select * from table_n where txn_date > 'start_of_partition_n'; and expect that to help, as long as the query that hooks up to this has date constraints? We'd have to regenerate the view with new fixed constants each time we set up the tables, but that sounds like it could work... -- "cbbrowne","@","acm.org" http://www3.sympatico.ca/cbbrowne/x.html But what can you do with it? -- ubiquitous cry from Linux-user partner. -- Andy Pearce, <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
