Re: Analytic function insight wanted

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 3 Mar 2007 08:38:45 -0800
Message-ID: <1172939924.826911.29050@z35g2000cwz.googlegroups.com>

On Mar 3, 11:04 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> Now, let's return the answer:
> SELECT
> MY_YEAR,
> SUM(DECODE(CATEGORY,'A1',L_VALUE1,'A2',L_VALUE1,0)) A_SUM,
> SUM(DECODE(CATEGORY,'B1',L_VALUE1,'B2',L_VALUE1,0)) B_SUM,
> SUM(DECODE(CATEGORY,'C',L_VALUE1,'C2',L_VALUE1,0)) C_SUM
> FROM
> (SELECT
> MY_YEAR,
> CATEGORY,
> NVL(VALUE1,
> NVL(LEAD(VALUE1,1,NULL) OVER (PARTITION BY CATEGORY ORDER BY
> MY_YEAR DESC),
> NVL(LEAD(VALUE1,2,NULL) OVER (PARTITION BY CATEGORY ORDER BY
> MY_YEAR DESC),0)
> )
> ) L_VALUE1
> FROM
> (SELECT
> C.MY_YEAR,
> C.CATEGORY,
> T1.VALUE1
> FROM
> (SELECT
> YRS.MY_YEAR,
> CAT.CATEGORY
> FROM
> (SELECT
> FIRST_YEAR+(ROWNUM-1) MY_YEAR
> FROM
> (SELECT
> MAX(YEAR)-MIN(YEAR)+1 YEARS,
> MIN(YEAR) FIRST_YEAR
> FROM
> T1),
> DUAL
> CONNECT BY ROWNUM<=YEARS) YRS,
> (SELECT DISTINCT
> CATEGORY
> FROM
> T1) CAT) C,
> T1
> WHERE
> C.MY_YEAR=T1.YEAR(+)
> AND C.CATEGORY=T1.CATEGORY(+)))
> GROUP BY
> MY_YEAR
> ORDER BY
> MY_YEAR;

>


>    MY_YEAR      A_SUM      B_SUM      C_SUM

> ---------- ---------- ---------- ----------

>       2000         11         22         50

>       2001         13         24         50

>       2002         63         28          7

>

> Did I mention that I don't like this problem? There might be other
> ways to solve the problem.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

DBMS_XPLAN:
PLAN_TABLE_OUTPUT

---

| Id  | Operation			   | Name | Starts | E-Rows | A-Rows
|   1 |  WINDOW SORT			   |	  |	 1 |	 13 |	  15
|*  2 |   HASH JOIN OUTER		   |	  |	 1 |	 13 |	  15
|   3 |    VIEW 			   |	  |	 1 |	 10 |	  15
|   4 |     MERGE JOIN CARTESIAN	   |	  |	 1 |	 10 |	  15
|   5 |      VIEW			   |	  |	 1 |	  1 |	   3
|   6 |       COUNT			   |	  |	 1 |	    |	   3
|   7 |        CONNECT BY WITHOUT FILTERING|	  |	 1 |	    |	   3
|   8 | 	COUNT			   |	  |	 1 |	    |	   1
|   9 | 	 NESTED LOOPS		   |	  |	 1 |	  1 |	   1
|  10 | 	  VIEW			   |	  |	 1 |	  1 |	   1
|  11 | 	   SORT AGGREGATE	   |	  |	 1 |	  1 |	   1
|  12 | 	    TABLE ACCESS FULL	   | T1   |	 1 |	 10 |	  10
|  13 | 	  FAST DUAL		   |	  |	 1 |	  1 |	   1
|  14 |      BUFFER SORT		   |	  |	 3 |	 10 |	  15
|  15 |       VIEW			   |	  |	 1 |	 10 |	   5
|  16 |        HASH UNIQUE		   |	  |	 1 |	 10 |	   5
|  17 | 	TABLE ACCESS FULL	   | T1   |	 1 |	 10 |	  10
|  18 |    TABLE ACCESS FULL		   | T1   |	 1 |	 10 |	  10

   2 - access("C"."MY_YEAR"="T1"."YEAR" AND "C"."CATEGORY"="T1"."CATEGORY") DBMS_XPLAN with ORDERED hint:
PLAN_TABLE_OUTPUT

---

| Id  | Operation			     | Name | Starts | E-Rows | A-Rows
|   1 |  SORT GROUP BY			     |	    |	   1 |	   13 |      3
|   2 |   VIEW				     |	    |	   1 |	   13 |     15
|   3 |    WINDOW SORT			     |	    |	   1 |	   13 |     15
|*  4 |     HASH JOIN OUTER		     |	    |	   1 |	   13 |     15
|   5 |      VIEW			     |	    |	   1 |	   10 |     15
|   6 |       MERGE JOIN CARTESIAN	     |	    |	   1 |	   10 |     15
|   7 |        VIEW			     |	    |	   1 |	    1 |      3
|   8 | 	COUNT			     |	    |	   1 |	      |      3
|   9 | 	 CONNECT BY WITHOUT FILTERING|	    |	   1 |	      |      3
|  10 | 	  COUNT 		     |	    |	   1 |	      |      1
|  11 | 	   NESTED LOOPS 	     |	    |	   1 |	    1 |      1
|  12 | 	    VIEW		     |	    |	   1 |	    1 |      1
|  13 | 	     SORT AGGREGATE	     |	    |	   1 |	    1 |      1
|  14 | 	      TABLE ACCESS FULL      | T1   |	   1 |	   10 |     10
|  15 | 	    FAST DUAL		     |	    |	   1 |	    1 |      1
|  16 |        BUFFER SORT		     |	    |	   3 |	   10 |     15
|  17 | 	VIEW			     |	    |	   1 |	   10 |      5
|  18 | 	 HASH UNIQUE		     |	    |	   1 |	   10 |      5
|  19 | 	  TABLE ACCESS FULL	     | T1   |	   1 |	   10 |     10
|  20 |      TABLE ACCESS FULL		     | T1   |	   1 |	   10 |     10

   4 - access("C"."MY_YEAR"="T1"."YEAR" AND "C"."CATEGORY"="T1"."CATEGORY") Three full tablescans of T1 in both cases.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Mar 03 2007 - 10:38:45 CST