This discussion is archived
6 Replies Latest reply: Jan 4, 2013 7:51 AM by user10857924 RSS

Query using 'having count' taking forever

587951 Newbie
Currently Being Moderated
I was wondering if someone had some advice for me. In my Oracle 10.2.0.4 database, I have 2 tables called PRDCTN and RQST_225K. The RQST_225K contains RQST_ID, CSTMR_NMBR and PHT_ID. This table contains only the latest PHT_ID for a particular customer. The PRDCTN table contains the same fields with the addition of a RQST_STTS_CODE field, and contains all past PHT_ID as well as the current PHT_ID for a particular customer.

In a nutshell, I want to write a query to select all customer numbers from the PRDCTN table, that exist in the RQST_225K table, that are also in status 400 and that have at least 2 distinct PHT_ID existing for that customer. Here is what I wrote:

select p.cstmr_nmbr from prdctn p where p.rqst_stts_code = 400 and p.cstmr_nmbr in (select r.cstmr_nmbr from rqst_225k r ) having count (distinct p.pht_id) > 2 group by p.cstmr_nmbr;


This query is taking forever to run because it is doing 2 full table scans. Does anyone have any tips on how this could be written to run faster? Thanks everyone
  • 1. Re: Query using 'having count' taking forever
    636309 Newbie
    Currently Being Moderated
    user584948 wrote:
    I was wondering if someone had some advice for me. In my Oracle 10.2.0.4 database, I have 2 tables called PRDCTN and RQST_225K. The RQST_225K contains RQST_ID, CSTMR_NMBR and PHT_ID. This table contains only the latest PHT_ID for a particular customer. The PRDCTN table contains the same fields with the addition of a RQST_STTS_CODE field, and contains all past PHT_ID as well as the current PHT_ID for a particular customer.

    In a nutshell, I want to write a query to select all customer numbers from the PRDCTN table, that exist in the RQST_225K table, that are also in status 400 and that have at least 2 distinct PHT_ID existing for that customer. Here is what I wrote:

    select p.cstmr_nmbr from prdctn p where p.rqst_stts_code = 400 and p.cstmr_nmbr in (select r.cstmr_nmbr from rqst_225k r ) having count (distinct p.pht_id) > 2 group by p.cstmr_nmbr;


    This query is taking forever to run because it is doing 2 full table scans. Does anyone have any tips on how this could be written to run faster? Thanks everyone
    Maybe this is what you need.

    select a.cstmr_nmbr
    (select p.cstmr_nmbr, p.pht_id
    from prdctn p
    where p.rqst_stts_code = 400
    and p.cstmr_nmbr in (select r.cstmr_nmbr from rqst_225k r )
    group by p.cstmr_nmbr
    having count(*) = 1) a
    group by a.cstmr_nmbr
    having count(*) > 1
  • 2. Re: Query using 'having count' taking forever
    Purvesh K Guru
    Currently Being Moderated
    user584948 wrote:
    I was wondering if someone had some advice for me. In my Oracle 10.2.0.4 database, I have 2 tables called PRDCTN and RQST_225K. The RQST_225K contains RQST_ID, CSTMR_NMBR and PHT_ID. This table contains only the latest PHT_ID for a particular customer. The PRDCTN table contains the same fields with the addition of a RQST_STTS_CODE field, and contains all past PHT_ID as well as the current PHT_ID for a particular customer.

    In a nutshell, I want to write a query to select all customer numbers from the PRDCTN table, that exist in the RQST_225K table, that are also in status 400 and that have at least 2 distinct PHT_ID existing for that customer. Here is what I wrote:

    select p.cstmr_nmbr from prdctn p where p.rqst_stts_code = 400 and p.cstmr_nmbr in (select r.cstmr_nmbr from rqst_225k r ) having count (distinct p.pht_id) > 2 group by p.cstmr_nmbr;

    Another way of writing:
    select p.cstmr_nmbr
      from prdctn p
    where p.rqst_stts_code = 400
        and exists (select 'x' from rqst_225k r where p.cstmr_nmbr = r.cstmr_nmbr)
     group by p.cstmr_nmbr
    having count(p.pht_id) > 1;    --> Since you need atleast 2, > 2 would be wrong.
    This query is taking forever to run because it is doing 2 full table scans. Does anyone have any tips on how this could be written to run faster? Thanks everyone
    How long does the query take? And did you check the explain plan if its actually doing a Full Table scan?
    If it does, you are probably missing some indexes, perhaps on prdctn.rqst_stts_code and prdctn.cstmr_nmbr.

    If you have problem with query execution, then I would suggest to read {message:id=3292438} and post the mentioned details.

    To keep the code formatted, use {noformat}
    {noformat} (exactly as written and in smaller case) before and after the code/explain plan. It helps keep the plan and code readable.
    
    Edited by: Purvesh K on Jan 4, 2013 10:10 AM
    Changed Having Count column                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 3. Re: Query using 'having count' taking forever
    jeneesh Guru
    Currently Being Moderated
    Purvesh K wrote:
    Another way of writing:
    group by p.cstmr_nmbr
    having count(distinct p.cstmr_nmbr) > 1;    --> Since you need atleast 2, > 2 would be wrong.
    Count will be always 1, right?
  • 4. Re: Query using 'having count' taking forever
    Purvesh K Guru
    Currently Being Moderated
    jeneesh wrote:
    Count will be always 1, right?
    Yes, Jeneesh. I have modified the query to count the PHT_ID, which as OP says, is not the same for each customer.
  • 5. Re: Query using 'having count' taking forever
    587951 Newbie
    Currently Being Moderated
    Thanks for the replies everyone. Sorry...I meant to say that I want to return at least 3 records, so that is why I had 'having count (distinct p.pht_id) > 2'. I am attaching the explain plans for my original query, as well as the explain plan for your modified query:


    --My original query
    select p.cstmr_nmbr from prdctn p where p.rqst_stts_code = 400 and p.cstmr_nmbr in (select r.screen_cust_no from rqst_225k r where r.pht_id is not null) having count (distinct p.pht_id) > 2 group by p.cstmr_nmbr;
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2223796732
    
    --------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                       |    51 |  1887 |   232K  (1)| 00:46:33 |       |       |
    |*  1 |  FILTER                                |                       |       |       |            |          |       |       |
    |   2 |   SORT GROUP BY                        |                       |    51 |  1887 |   232K  (1)| 00:46:33 |       |       |
    |*  3 |    TABLE ACCESS BY INDEX ROWID         | RQST_225K             |     1 |    16 |     4   (0)| 00:00:01 |       |       |
    |   4 |     NESTED LOOPS                       |                       |  1001 | 37037 |   232K  (1)| 00:46:33 |       |       |
    |*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| PRDCTN                |    37M|   749M| 79547   (1)| 00:15:55 | ROWID | ROWID |
    |   6 |       INDEX FULL SCAN                  | PRDCTN_CSTMR_NMBR_IDX | 80315 |       |   337   (1)| 00:00:05 |       |       |
    |*  7 |      INDEX RANGE SCAN                  | RQST225K_CUST_IDX     |     1 |       |     2   (0)| 00:00:01 |       |       |
    --------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(COUNT(DISTINCT "P"."PHT_ID")>2)
       3 - filter("R"."PHT_ID" IS NOT NULL)
       5 - filter("P"."RQST_STTS_CODE"=400)
       7 - access("P"."CSTMR_NMBR"="R"."SCREEN_CUST_NO")
    
    22 rows selected.
    --Your modified query
    select p.cstmr_nmbr
      from prdctn p
    where p.rqst_stts_code = 400
        and exists (select 'x' from rqst_225k r where p.cstmr_nmbr = r.screen_cust_no)
     group by p.cstmr_nmbr
    having count(p.pht_id) > 2;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 40388322
    
    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                       |    51 |  1581 |   156K  (1)| 00:31:18 |       |       |
    |*  1 |  FILTER                               |                       |       |       |            |          |       |       |
    |   2 |   SORT GROUP BY NOSORT                |                       |    51 |  1581 |   156K  (1)| 00:31:18 |       |       |
    |   3 |    NESTED LOOPS SEMI                  |                       |  1001 | 31031 |   156K  (1)| 00:31:18 |       |       |
    |*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| PRDCTN                |    37M|   749M| 80048   (1)| 00:16:01 | ROWID | ROWID |
    |   5 |      INDEX FULL SCAN                  | PRDCTN_CSTMR_NMBR_IDX | 80821 |       |   339   (1)| 00:00:05 |       |       |
    |*  6 |     INDEX RANGE SCAN                  | RQST225K_CUST_IDX     |  3134 | 31340 |     2   (0)| 00:00:01 |       |       |
    -------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(COUNT("P"."PHT_ID")>2)
       4 - filter("P"."RQST_STTS_CODE"=400)
       6 - access("P"."CSTMR_NMBR"="R"."SCREEN_CUST_NO")
    
    20 rows selected.
    I currently have your query running for the past 20 minutes, and nothing has come back yet. Thanks again.
  • 6. Re: Query using 'having count' taking forever
    user10857924 Journeyer
    Currently Being Moderated
    is your stats are up-todate?

    plan shows 37M rows retrive from PRDCTN table via global index but the nested loop output only 1001 rows means lots of rows not found in other table (rqst_225k).
    oracle is going for full scans in order to avoid sort which is seen in plan by nosort operation.

    I think if you create index on column prdctn.rqst_stts_code that might help.

    also you can try re-write query as below
    select p.cstmr_nmbr
    from prdctn p, rqst_225k r 
    where p.rqst_stts_code = 400 and p.cstmr_nmbr = r.screen_cust_no
    group by p.cstmr_nmbr
    having count(distinct p.pht_id) > 2;
    HTH...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points