OraFAQ Forum: Performance Tuning » Multiple SELECT statement help

Home » RDBMS Server » Performance Tuning » Multiple SELECT statement help (TOAD 9)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Multiple SELECT statement help [message #502746]

Fri, 08 April 2011 13:44

gitarooman83
Messages: 17
Registered: March 2011
Location: Louisville

Junior Member

Hello, I'm working on a query that will show how many differents SKUs we have on-hand, how many of those SKUs have been cycle-counted, and how many we have yet to cycle-count.

I've prepared a sample table and data:

CREATE TABLE SKU
(
  ABC             VARCHAR2(1 CHAR),
  SKU             VARCHAR2(32 CHAR)             NOT NULL,
  Lastcyclecount  DATE,
)
CREATE TABLE wcc
(
  yrweekno  VARCHAR2(12),
  sku       VARCHAR2(32)
)
CREATE TABLE LOT
(
  sku  VARCHAR2(32)                             NOT NULL,
  qty  INTEGER,
  loc  VARCHAR2(12)
)
INSERT INTO SKU
VALUES ('A', '31103A','03/25/2011')
INSERT INTO SKU
VALUES ('A', '23110B','03/20/2011')
INSERT INTO SKU
VALUES ('C', '50001A','06/01/2010')
INSERT INTO SKU
VALUES ('C', '50001B','12/30/2010')
INSERT INTO SKU
VALUES ('B', '60100A',NULL)
INSERT INTO SKU
VALUES ('C', '99999B',NULL)
INSERT INTO SKU
VALUES ('B', '40010A','03/01/2011')
INSERT INTO SKU
VALUES ('A', '10001B','01/20/2011')
INSERT INTO SKU
VALUES ('A', '20102A',NULL)
INSERT INTO SKU
VALUES ('A', '23110B',NULL)

INSERT INTO WCC
VALUES ('20110325', '31103A')
INSERT INTO WCC
VALUES ('20110207', '31103A')
INSERT INTO WCC
VALUES ('20110320', '23110B')
INSERT INTO WCC
VALUES ('20110110', '23110B')
INSERT INTO WCC
VALUES ('20100601', '50001A')
INSERT INTO WCC
VALUES ('20101230', '50001B')
INSERT INTO WCC
VALUES ('20110301', '40010A')
INSERT INTO WCC
VALUES ('20110103', '40010A')
INSERT INTO WCC
VALUES ('20110120', '10001B')
INSERT INTO WCC
VALUES ('20100620', '10001B')
INSERT INTO WCC
VALUES ('20110208', '12345B')
INSERT INTO WCC
VALUES ('20110209', '12345A')

INSERT INTO LOT
VALUES ('31103A', 12, 'AA01A')
INSERT INTO LOT
VALUES ('31103A', 10, 'AH10A')
INSERT INTO LOT
VALUES ('23110B', 1, 'AB03A')
INSERT INTO LOT
VALUES ('50001A', 102, 'BA13A20')
INSERT INTO LOT
VALUES ('50001B', 8, 'AA03A')
INSERT INTO LOT
VALUES ('60100A', 13, 'AD01A')
INSERT INTO LOT
VALUES ('99999B', 19, 'BA01A10')
INSERT INTO LOT
VALUES ('40010A', 75, 'AF05D')
INSERT INTO LOT
VALUES ('10001B', 2, 'AC10A')
INSERT INTO LOT
VALUES ('20102A', 12, 'AA01B')
INSERT INTO LOT
VALUES ('23110B', 15, 'AG01D')
INSERT INTO LOT
VALUES ('23110B', 2, 'AM03B')
INSERT INTO LOT
VALUES ('50001A', 100, 'BA14A20')
INSERT INTO LOT
VALUES ('50001B', 6, 'AC07A')
INSERT INTO LOT
VALUES ('12345B', 25, 'AK02D')
INSERT INTO LOT
VALUES ('12345A', 8, 'AC52D')
INSERT INTO LOT
VALUES ('10001B', 0, 'BA10B10')
INSERT INTO LOT
VALUES ('20102A', 12, 'AA01B')
INSERT INTO LOT
VALUES ('23110B', 15, 'AG01D')
INSERT INTO LOT
VALUES ('23110B', 2, 'AM03B')
INSERT INTO LOT
VALUES ('50001A', 100, 'BA14A20')
INSERT INTO LOT
VALUES ('50001B', 0, 'AM02B')
INSERT INTO LOT
VALUES ('12345B', 0, 'AH21E')
INSERT INTO LOT
VALUES ('12345A', 0, 'AG52E')

Brief explanation of what I'm trying to do:
Query by wcc.yrweekno (yrweekno > &1, yrweekno <&2), group by sku.abc, count the total A, B, and C SKUs where lot.qty is > 0.

I've got that part down:

SELECT   s.abc AS "STRATA",
           COUNT (DISTINCT s.sku) AS "counted"
    FROM   sku s,
           (SELECT   sku
              FROM   lot
             WHERE   qty > 0) item,
           wcc
   WHERE       wcc.yrweekno >= &1
           AND wcc.yrweekno <= &2
           AND s.lastcyclecount IS NOT NULL
           AND wcc.sku = item.sku(+)
           AND item.sku = s.sku(+)
GROUP BY   s.abc

What I also want to do is select another column that will group by sku.abc and count the total number of A, B, and C SKUs where the lot.qty is > 0:

SELECT   sk.abc AS "STRATA",
           COUNT (DISTINCT sk.sku) AS "Total"
    FROM   sku sk,
           (SELECT   sku
              FROM   lot
             WHERE   qty > 0) item
   WHERE   item.sku = sk.sku(+)
GROUP BY   sk.abc

Finally, I need the last column to display the DIFFERENCE between the two totals from the queries above (the difference between the "counted" and the "total"):

COUNT (DISTINCT sk.sku) - COUNT (DISTINCT s.sku)

Any insight would be appreciated as this one is getting to me. Let me know if I can provide any further details.

Report message to a moderator

Re: Multiple SELECT statement help [message #502747 is a reply to message #502746]

Fri, 08 April 2011 13:54

gitarooman83
Messages: 17
Registered: March 2011
Location: Louisville

Junior Member

As far as results go, I'm looking for the following:

STRATA      COUNTED      TOTAL      DIFFERENCE
A           4            6          2
B           2            4          2
C           2            6          4

**These are not the actual totals

Report message to a moderator

Re: Multiple SELECT statement help [message #502763 is a reply to message #502747]

Fri, 08 April 2011 22:57

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

You could just use the two quereis as inline views, provide the appropriate join, and subtract the total from the counted, as demonstrated below.

SCOTT@orcl_11gR2> SELECT   t1."STRATA",
  2  	      t1."counted",
  3  	      t2."Total",
  4  	      t1."counted" - t2."Total" difference
  5  FROM     (SELECT	s.abc AS "STRATA",
  6  			COUNT (DISTINCT s.sku) AS "counted"
  7  	       FROM	sku s,
  8  			(SELECT   sku
  9  			 FROM	  lot
 10  			 WHERE	  qty > 0) item,
 11  			wcc
 12  	       WHERE	wcc.yrweekno >= &1
 13  	       AND	wcc.yrweekno <= &2
 14  	       AND	s.lastcyclecount IS NOT NULL
 15  	       AND	wcc.sku = item.sku(+)
 16  	       AND	item.sku = s.sku(+)
 17  	       GROUP	BY s.abc) t1,
 18  	      (SELECT	sk.abc AS "STRATA",
 19  			COUNT (DISTINCT sk.sku) AS "Total"
 20  	       FROM	sku sk,
 21  			(SELECT   sku
 22  			 FROM	  lot
 23  			 WHERE	  qty > 0) item
 24  	       WHERE	item.sku = sk.sku(+)
 25  	       GROUP	BY sk.abc) t2
 26  WHERE    t1."STRATA" = t2."STRATA"
 27  /
Enter value for 1: 20110101
old  12:           WHERE    wcc.yrweekno >= &1
new  12:           WHERE    wcc.yrweekno >= 20110101
Enter value for 2: 20111231
old  13:           AND      wcc.yrweekno <= &2
new  13:           AND      wcc.yrweekno <= 20111231

S    counted      Total DIFFERENCE
- ---------- ---------- ----------
A          3          4         -1
B          1          2         -1

2 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Multiple SELECT statement help [message #507519 is a reply to message #502763]

Mon, 16 May 2011 10:18

gitarooman83
Messages: 17
Registered: March 2011
Location: Louisville

Junior Member

Barbara, this still didn't work for me; it just times out. I've tried several variations but I can't get it to work.

Is there another JOIN syntax that could be used to join the two subqueries?

The problem seems to be that there is no common ground between the first and second subqueries. That seems to be why it's timing out.

Report message to a moderator

Re: Multiple SELECT statement help [message #507525 is a reply to message #507519]

Mon, 16 May 2011 11:41

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

I will move this thread to the "Performance Tuning" forum. It would help if you provide a copy and paste of exactly what you ran, as I did. Also, please provide what indexes are available on what columns, make sure statistics are current, and post an execution plan. There is a separate guide at the top of the tuning forum that suggests what additional information to provide.

Report message to a moderator

Re: Multiple SELECT statement help [message #507541 is a reply to message #507525]

Mon, 16 May 2011 13:57

gitarooman83
Messages: 17
Registered: March 2011
Location: Louisville

Junior Member

I ran the code exactly as it was provided, but with my actual tables. The actual tables are much larger than the samples I provided but the results would not populate in the 10 minutes for which I let it run.

Each individual subquery runs on the actual tables in about 6 seconds and the result I wish to display is the combination of both queries, exactly as Barbara has it in her results.

Report message to a moderator

Re: Multiple SELECT statement help [message #507544 is a reply to message #507541]

Mon, 16 May 2011 14:12

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

You could try adding "AND ROWNUM > 0" to each sub-query to try to materialize them, as shown below. Please read the tuning guide and provide the information previously requested. We need to see an explain plan to see what it is trying to do.

SELECT   t1."STRATA", 
         t1."counted",
         t2."Total",
         t1."counted" - t2."Total" difference
FROM     (SELECT   s.abc AS "STRATA",
                   COUNT (DISTINCT s.sku) AS "counted"
          FROM     sku s,
                   (SELECT   sku
                    FROM     lot
                    WHERE    qty > 0) item,
                   wcc
          WHERE    wcc.yrweekno >= &1
          AND      wcc.yrweekno <= &2
          AND      s.lastcyclecount IS NOT NULL
          AND      wcc.sku = item.sku(+)
          AND      item.sku = s.sku(+)
          AND      ROWNUM > 0
          GROUP    BY s.abc) t1,
         (SELECT   sk.abc AS "STRATA",
                   COUNT (DISTINCT sk.sku) AS "Total"
          FROM     sku sk,
                   (SELECT   sku
                    FROM     lot
                    WHERE    qty > 0) item
          WHERE    item.sku = sk.sku(+)
          AND      ROWNUM > 0
          GROUP    BY sk.abc) t2
WHERE    t1."STRATA" = t2."STRATA"
/

Report message to a moderator

Previous Topic:	Question regarding performance impact on disabling foreign key
Next Topic:	Query tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:47:43 CDT 2024