Re: Union Query Improvement

On Tue, 2007-02-13 at 05:46 -0800, Ray Bannon wrote:

Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION

...

Ad infinitum for about 100 iterations.

Any way to write this more efficiently?

I assume that "table/view" in your OP really refers to different tables or views; otherwise, this is a trivial query that can be collapsed to "select ... where plan_name in ('A','B','C') ..." or, perhaps just "select ... where rownum=1".

As Tom said, UNION ALL may be more appropriate for your needs. See
http://www.postgresql.org/docs/8.1/interactive/queries-union.html

You should consider inherited tables and table partitioning. See
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html

Partitioning offers two administrative benefits: consistency of child tables and an implied UNION ALL of children when one selects on the parent.

-Reece

-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter.