This discussion is archived
9 Replies Latest reply: Feb 10, 2013 10:25 PM by pawii RSS

group by execution

pawii Newbie
Currently Being Moderated
in this query how the execution will take place??



select department_id,job_id,sum(salary)
from employees
group by department_id,job_id;

how the oracle will execute this??
first employees table will be selected thn ??
  • 1. Re: group by execution
    sb92075 Guru
    Currently Being Moderated
    SQL> set autotrace on explain
    SQL> select department_id,job_id,sum(salary)
    from employees
    group by department_id,job_id;
      2    3  
    DEPARTMENT_ID JOB_ID     SUM(SALARY)
    ------------- ---------- -----------
              110 AC_ACCOUNT        8300
               90 AD_VP            34000
               50 ST_CLERK         55700
               80 SA_REP          243500
               50 ST_MAN           36400
               80 SA_MAN           61000
              110 AC_MGR           12000
               90 AD_PRES          24000
               60 IT_PROG          28800
              100 FI_MGR           12000
               30 PU_CLERK         13900
    
    DEPARTMENT_ID JOB_ID     SUM(SALARY)
    ------------- ---------- -----------
               50 SH_CLERK         64300
               20 MK_MAN           13000
              100 FI_ACCOUNT       39600
                  SA_REP            7000
               70 PR_REP           10000
               30 PU_MAN           11000
               10 AD_ASST           4400
               20 MK_REP            6000
               40 HR_REP            6500
    
    20 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1192169904
    
    --------------------------------------------------------------------------------
    | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |           |   107 |  3531 |     4  (25)| 00:00:01 |
    |   1 |  HASH GROUP BY     |           |   107 |  3531 |     4  (25)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |  3531 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> 
  • 2. Re: group by execution
    stefan nebesnak Journeyer
    Currently Being Moderated
    Or..
    EXPLAIN PLAN FOR 
    select department_id,job_id,sum(salary)
    from employees
    group by department_id,job_id;
  • 3. Re: group by execution
    ranit B Expert
    Currently Being Moderated
    select department_id,job_id,sum(salary)
    from employees
    group by department_id,job_id;

    how the oracle will execute this??
    first employees table will be selected thn ??
    Adding/Simplifying to what others already suggested( Explain Plan actually shows how Oracle executed a query) -

    First, the table is selected,
    then grouped according to the specified columns,
    finally the Selected column's data is printed.

    Why it is Grouped first is coz without grouping how will it apply a SUM function. Just think practically, to SUM anything you need to have multiple things first and then only you can find it. Obviously, you won't SUM a single item... right? ;)
    That's why a Group by always needs an Aggregate function like SUM, MAX, etc...

    Adding l'il extra content :
    the ORDER BY clause will be the last to get executed coz it is just used for ordering the already Selected rows... i.e. it only serves a cosmetic purpose and so is done at last.
  • 4. Re: group by execution
    ranit B Expert
    Currently Being Moderated
    stefan nebesnak wrote:
    Or..
    EXPLAIN PLAN FOR 
    select department_id,job_id,sum(salary)
    from employees
    group by department_id,job_id;
    This is incomplete and not of much use, unless you tell how to read the generated plan.

    Do this -
    SELECT *
     FROM
    TABLE(DBMS_XPLAN.display);
    From - <b>Generating Explain Plans</b>
    >
    The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

    -- table_name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
    -- statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
    -- format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'. There is also an undocumented 'ADVANCED' setting.
  • 5. Re: group by execution
    stefan nebesnak Journeyer
    Currently Being Moderated
    ranit B wrote:
    This is incomplete and not of much use, unless you tell how to read the generated plan.
    The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right.
    The next step is the parent of that line.
    If two lines are indented equally, then the top line is normally executed first.

    Each step of the execution plan returns a set of rows that either is used by the next step or, in the last step, is returned to the user or application issuing the SQL statement. A set of rows returned by a step is called a row set.
    ------------------
    EXPLAIN PLAN FOR 
    select * from employees;
    SELECT *  FROM TABLE(DBMS_XPLAN.display);
    ╔═══════╤═══════════════╤═══════╤═══════╗
    ║     │DEPARTMENT_ID     │JOB_ID     │SALARY ║
    ╠═══════╪═══════════════╪═══════╪═══════╣
    ║1     │4          │2     │500000     ║
    ║2     │4          │2     │50000     ║
    ║3     │3          │1     │40000     ║
    ║4     │2          │2     │20000     ║
    ║5     │1          │1     │10000     ║
    ║6     │3          │1     │5000000║
    ╚═══════╧═══════════════╧═══════╧═══════╝
    
    --------------------------------------------------------------------
    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)|
    --------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |     6 |   234 |     3   (0)|
    |   1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   234 |     3   (0)|
    --------------------------------------------------------------------
    
    OPERATIONS:
       (SELECT STATEMENT) --return result
          ▲
     TABLE ACCESS (option:"FULL") --retrieval of all rows from a table EMPLOYEES.
    ------------------
    EXPLAIN PLAN FOR 
    select department_id,job_id,sum(salary)
    from employees
    group by department_id,job_id
    having sum(salary) > avg(salary)
    order by sum(salary);
    SELECT *  FROM TABLE(DBMS_XPLAN.display);
    ╔═══════╤═══════════════╤═══════╤════════════╗
    ║     │DEPARTMENT_ID     │JOB_ID     │sum(salary) ║
    ╠═══════╪═══════════════╪═══════╪════════════╣
    ║1     │4          │2     │550000          ║
    ║2     │3          │1     │5040000     ║
    ╚═══════╧═══════════════╧═══════╧════════════╝
    
    
    -----------------------------------------------------------------------
    | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)|
    -----------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |           |     6 |   234 |     5  (40)|
    |   1 |  SORT ORDER BY       |           |     6 |   234 |     5  (40)|
    |   2 |   FILTER             |           |       |       |            |
    |   3 |    HASH GROUP BY     |           |     6 |   234 |     5  (40)|
    |   4 |     TABLE ACCESS FULL| EMPLOYEES |     6 |   234 |     3   (0)|
    -----------------------------------------------------------------------
    
    OPERATIONS:
       (SELECT STATEMENT) --return result
          ▲
        SORT (option:"ORDER BY") --sorting a set of rows for a query with an ORDER BY clause
          ▲    
         FILTER --accepting a set of rows, eliminates some of them, and returns the rest.
          ▲
        HASH (option:"GROUP BY") --hashing a set of rows into groups for a query with a GROUP BY clause
          ▲
     TABLE ACCESS (option: "FULL") --retrieval of all rows from a table EMPLOYEES.
    Edited by: stefan nebesnak on Feb 10, 2013 11:42 AM
  • 6. Re: group by execution
    ranit B Expert
    Currently Being Moderated
    Much better Stefan!!!
    OP must be feeling really good with this... ;)

    Btw, whaaaat is this...? Biography???
    >
    Handle:      987018
    Status Level:      Newbie
    Registered:      Feb 8, 2013
    Total Posts:      9
    Total Questions:      6 (3 unresolved)
    Name      pawii
    Location      jaipur
    Occupation      student
    Biography      ohh **** ppl r reading biographies in oracle sql forum!! go get a lyf dumbozzz start reading queries instead
  • 7. Re: group by execution
    stefan nebesnak Journeyer
    Currently Being Moderated
    ranit B wrote:
    Btw, whaaaat is this...? Biography???

    Biography      ohh **** ppl r reading biographies in oracle sql forum!! go get a lyf dumbozzz start reading queries instead
    >
    It seems so. (͡° ͜ʖ ͡°)
  • 8. Re: group by execution
    pawii Newbie
    Currently Being Moderated
    seems so..... so what???
  • 9. Re: group by execution
    pawii Newbie
    Currently Being Moderated
    yeahhh it is a biography
    i want aal ov u to focus on queries instead of biographies

Legend

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