Re: Union Query Improvement

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ever considered using a materialized view? If you do, you could write
the code to load/sync your materialized view using a FOR loop. Note that
PostgreSQL does not support materialized views out of the box, so you'd
need to play around with some triggers and functions. If you're
interested, this is a good starting point:

http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

If you don't change the involved tables all that much, you'll incur in
very little overhead for maintaining the materialized view. If you do
change them quite a bit, you might wanna take a step back and reconsider
parts of your schema design.

BTW, are all your "table/view" the same for each of the SELECTs? I'm
assuming not...

Demian

Ray Bannon wrote:
> There is only one table/view, it's getting one record for each of the
> hundred or so plan ID's that I'm looking for.
>
>
> On 2/13/07 11:29 PM, in article 45D2BA53(dot)2010100(at)cox(dot)net, "Ron Johnson"
> <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:
>
> On 02/13/07 07:46, Ray Bannon wrote:
>>>> I have a query which is running a bit slowly, and I'm wondering if anyone
>>>> has a design improvement. Basically it's a series of unions as follows:
>>>>
>>>> Select ID, plan_name from table/view
>>>> Where plan_name = 'A'
>>>> And rownum = 1
>>>> UNION
>>>> Select ID, plan_name from table/view
>>>> Where plan_name = 'B'
>>>> And rownum = 1
>>>> UNION
>>>> Select ID, plan_name from table/view
>>>> Where plan_name = 'C'
>>>> And rownum = 1
>>>> UNION
>>>>
>>>> Ad infinitum for about 100 iterations.
>>>>
>>>> Any way to write this more efficiently?
> Just out of curiosity: why does your(?) design have 100 tables/views
> with the same (or almost identical) structure?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFF1KJyWs7G5iIp9akRArwOAKCSZTDyfxArgLHJSOozmbopALtfaACfURB0
m4s1oSyNqkQjYcx4//AcfTA=
=l7AK
-----END PGP SIGNATURE-----