9 Replies Latest reply: Feb 11, 2013 12:25 AM by pawii RSS

    group by execution

    pawii
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        seems so..... so what???
                        • 9. Re: group by execution
                          pawii
                          yeahhh it is a biography
                          i want aal ov u to focus on queries instead of biographies