This discussion is archived
6 Replies Latest reply: Jan 3, 2013 10:47 PM by Purvesh K RSS

Explain rows returned in a plan for group by

User477708-OC Journeyer
Currently Being Moderated
I have a (select statement) that returns 18 rows.

wrap a nested select with a group by around it

select col_a, col_b from
(select statement)
group by col_a, col_b

still returns 18 rows as expected

explain it and I see the 18 rows returned at Id 2 and the hash group by and select showing 1 rows.
                    
------------------------------------------------------------------------------------------------------------------------------------------------------                              
| Id  | Operation                                   | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                              
------------------------------------------------------------------------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT                            |                                |     1 |    95 |       | 90897  (75)| 00:00:02 |       |       |                              
|   1 |  HASH GROUP BY                              |                                |     1 |    95 |       | 90897  (75)| 00:00:02 |       |       |                              
|   2 |   VIEW                                      |                                |    18 |  1710 |       | 90896  (75)| 00:00:02 |       |       |        
reason Im asking is I rewrote the SQL in the inner statement for tuning purposes and I still get 18 rows as expected but now I have 2 rows returned to hash group by.
-----------------------------------------------------------------------------------------------------------------------------------------------------                               
| Id  | Operation                                  | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                               
-----------------------------------------------------------------------------------------------------------------------------------------------------                               
|   0 | SELECT STATEMENT                           |                                |     2 |   190 |       | 82657  (80)| 00:00:02 |       |       |                               
|   1 |  HASH GROUP BY                             |                                |     2 |   190 |       | 82657  (80)| 00:00:02 |       |       |                               
|   2 |   VIEW                                     |                                |    18 |  1710 |       | 82656  (80)| 00:00:02 |       |       |                               
..
..
In terms of the above plans what does "rows" mean for the hash group by.
  • 1. Re: Explain rows returned in a plan for group by
    moreajays Pro
    Currently Being Moderated
    HI,

    How many columns you specified in group by function before & after query modification

    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 2. Re: Explain rows returned in a plan for group by
    Purvesh K Guru
    Currently Being Moderated
    961469 wrote:
    reason Im asking is I rewrote the SQL in the inner statement for tuning purposes and I still get 18 rows as expected but now I have 2 rows returned to hash group by.
    And in fact, how many rows does your entire query return?
    Does the output of two of your queries differ?

    If they do not, then I do not see any issue.


    From the Cost and the Rows, posted in Explain plan, it looks like some different plan is being used.

    Were the statistics pertaining to the tables in use, collected between execution of two queries?

    I will suggest to read the {message:id=3292438} and post the relevant details.

    Also, do not forget to mention the Table/Index details (actual and with statistics) and the Query.
  • 3. Re: Explain rows returned in a plan for group by
    moreajays Pro
    Currently Being Moderated
    Hi,

    ROWS of explain plan for HASH GROUP BY operation is nothing but the rows processed by the optimizer to retrieve result by joining two columns

    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 4. Re: Explain rows returned in a plan for group by
    User477708-OC Journeyer
    Currently Being Moderated
    Purvesh K wrote:
    961469 wrote:
    reason Im asking is I rewrote the SQL in the inner statement for tuning purposes and I still get 18 rows as expected but now I have 2 rows returned to hash group by.
    And in fact, how many rows does your entire query return?
    Does the output of two of your queries differ?
    If they do not, then I do not see any issue.
    No, the output doesnt differ, and its not a problem for me, just something I wanted explained.


    >
    >
    From the Cost and the Rows, posted in Explain plan, it looks like some different plan is being used.
    Yes, there is, I rewrote it, but only on the inner select statement but I don't have a question on that. If take the inner select statement from both I get the 18 rows correctly.

    Were the statistics pertaining to the tables in use, collected between execution of two queries?
    no but that last select is only on the outer select, ie shouldnt be affected by the stats, anyway, The queries are run straight after each other.

    I will suggest to read the {message:id=3292438} and post the relevant details.

    Also, do not forget to mention the Table/Index details (actual and with statistics) and the Query.
    I can, buts its VERY lengthy as theres a lot of nested tables/views and for me not relevant as if I run the inner select on both queries the datasets match. I was hoping for a simpler answer like Ajays which was my understanding of it too but as the outer select and group by hasnt changed, I cant understand the difference in the rows.

    thanks for your reply though.
    >
    How many columns you specified in group by function before & after query modification
    I thought this too and was my understanding but the group by clause hasnt changed, only the inner select.

    Edited by: 961469 on Jan 3, 2013 7:42 AM
  • 5. Re: Explain rows returned in a plan for group by
    Dom Brooks Guru
    Currently Being Moderated
    These are just estimates, not actual rows returned.

    Sometimes you can make such a small change and some funny sort of optimizer calculation can change.

    But it's just an estimate.
  • 6. Re: Explain rows returned in a plan for group by
    Purvesh K Guru
    Currently Being Moderated
    961469 wrote:
    I can, buts its VERY lengthy as theres a lot of nested tables/views and for me not relevant as if I run the inner select on both queries the datasets match. I was hoping for a simpler answer like Ajays which was my understanding of it too but as the outer select and group by hasnt changed, I cant understand the difference in the rows.
    Alright, for a short opinion, looking at your Explain Plans,

    Earlier query "estimated", the Inline View will retrieve a Single Record, meaning it was already grouped or inline view fetched just one record.
    However, the Later query "estimates", the Inline view will retrieve 2 records which are to be grouped.

    This is my understanding by looking at the explain plan you posted. Unless the entire explain plan is posted, all of us will be left to play a guessing game. And yes, Dom has already suggested that the Explain Plan has estimated to process 2 records with your modified query, but it might be incorrect.

Legend

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