Обсуждение: Union Query Improvement

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?

Thanks!

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.

I'm sure I'm missing something but wouldn't this give the same results:

select id, plan_name from table where rownum=1 and plan_name in ('A',
'B', 'C');

?

Or do the rownum's change later on?

--
Postgresql & php tutorials
http://www.designmagick.com/

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

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.6 (GNU/Linux)

iD8DBQFF0rpTS9HxQb37XmcRAtH5AJ4zz5NPM5rBsNWLrKC+/Md6GhxCNgCfZBHf
AeMbTRNKp4guK81pGwfU5wc=
=t9y+
-----END PGP SIGNATURE-----

Ray Bannon <bannonr@comcast.net> writes:
> Any way to write this more efficiently?

UNION -> UNION ALL, perhaps?  Do you really need UNION's duplicate-row-
elimination behavior?

            regards, tom lane

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?

-- 
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.

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.2010100@cox.net, "Ron Johnson"
<ron.l.johnson@cox.net> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> 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.6 (GNU/Linux)
>
> iD8DBQFF0rpTS9HxQb37XmcRAtH5AJ4zz5NPM5rBsNWLrKC+/Md6GhxCNgCfZBHf
> AeMbTRNKp4guK81pGwfU5wc=
> =t9y+
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

-----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.2010100@cox.net, "Ron Johnson"
> <ron.l.johnson@cox.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-----

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

On 02/14/07 06:43, Ray Bannon wrote:
> There is only one table/view, it's getting one record for each of the

Clarity in question-asking always helps.

> hundred or so plan ID's that I'm looking for.

One table, huh?  Then why all the references to "ROWNUM = 1"?  To us
who don't know your system, ROWNUM looks a whole lot like a PK.

Anyway...

SELECT ID, PLAN_NAME
FROM SOME_TABLE
WHERE PLAN_NAME IN ('A', 'B', 'C', etc, etc)
  AND ROWNUM = 1l

Making a lookup table with just a list of PLAN_NAME values in it
would also work, but then you'd have to join the lookup table to
SOME_TABLE on PLAN_NAME.

> On 2/13/07 11:29 PM, in article 45D2BA53.2010100@cox.net, "Ron Johnson"
> <ron.l.johnson@cox.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.6 (GNU/Linux)

iD8DBQFF1KJ4S9HxQb37XmcRAs+AAKCad0JDoZgwXXpxXHNvXm/8BG1QRgCeLW+H
bAFQXlbtYfq27z4WDjsKahY=
=4JYC
-----END PGP SIGNATURE-----