Re: Union Query Improvement

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

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