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.