Re: Union Query Improvement

On Feb 14, 9:59 am, "Ray Bannon" <raymond.w.ban..._at_boeing.com> wrote:
> Howdy, I've got a query which runs forever and am wondering if there's a
> more efficient way to write it.
>
> Select id, plan_name from table/view1 where plan_name = 'A' and rownum = 1
> union
> Select id, plan_name from table/view1 where plan_name = 'B' and rownum = 1
> union
> Select id, plan_name from table/view1 where plan_name = 'C' and rownum = 1
> union.....etc
>
> There are about 100 plan names, and I want one ID/owner for each plan_name.

Maybe I am not reading the question correctly, but this seems to be very easy to accomplish. You can speed it up a bit by changing UNION to UNION ALL. Or, you can write the SQL statement so that it may be executed efficiently. Example:
CREATE TABLE T3 (
  ID NUMBER(10),
  PLAN_NAME VARCHAR2(10)); Fill with a known sequence of numbers from 1 to 100: INSERT INTO
  T3
SELECT
  ROWNUM,
  TO_CHAR(ROWNUM)
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<=100;

Fill in a second set of 100 numbers with somewhat random values: INSERT INTO
  T3
SELECT
  TRUNC(ROWNUM*DBMS_RANDOM.VALUE(0,5)),
  TO_CHAR(ROWNUM)
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<=100;

Now, a simple GROUP BY:
SELECT
  MAX(ID) ID,
  PLAN_NAME
FROM
  T3
GROUP BY
  PLAN_NAME
ORDER BY
  PLAN_NAME;         ID PLAN_NAME
========== ==========

	 2 1
	10 10
       385 100
	41 11
	12 12
	18 13
	21 14
	17 15
	58 16
	51 17
	23 18
	19 19
	 5 2
	83 20
	27 21
	22 22
	73 23
	71 24
	49 25
	49 26
       124 27
       113 28
	29 29
	 6 3
	30 30
	40 31
	32 32
	33 33
       149 34
       168 35
       177 36
	37 37
	39 38
	39 39
	16 4
       168 40
	57 41
       209 42
       104 43
       123 44
	57 45
	69 46
       116 47
	48 48
	56 49
	 7 5
	77 50
       172 51
       212 52
	53 53
	84 54
       232 55
       254 56
       275 57
       107 58
       181 59
	22 6
       151 60
       287 61
       172 62
       287 63
	98 64
       289 65
       238 66
	67 67
       145 68
       178 69
	10 7
       147 70
       204 71
       321 72
	95 73
       211 74
       147 75
       196 76
       163 77
	78 78
       276 79
	35 8
       250 80
       278 81
       244 82
       261 83
       102 84
       219 85
	86 86
       407 87
	88 88
       112 89
	 9 9
       259 90
       222 91
	92 92
       357 93
	94 94
	95 95
	96 96
       350 97
       268 98
       490 99

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Feb 14 2007 - 09:45:35 CST