timing and auto tracing a select statement with group : GROUP BY « Select Query « Oracle PL / SQL






timing and auto tracing a select statement with group

   
SQL>
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'wvelasq@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL>
SQL>  set autotrace on
SQL>  set timing on
SQL>
SQL>  select employee_id,
  2           count(*) total_orders
  3      from employees
  4      group by employee_id
  5    /

EMPLOYEE_ID TOTAL_ORDERS
----------- ------------
       1003            1
       1006            1
       1001            1
       1002            1
       1007            1
       1004            1
       1005            1
       1008            1

8 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     8 |   104 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |     8 |   104 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |     8 |   104 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        574  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL>  set autotrace off
SQL>  set timing off
SQL>
SQL> drop table employees;

Table dropped.

SQL>

   
    
  








Related examples in the same category

1.Use avg, sum, max and count functions with group
2.Using the GROUP BY Clause
3.Must include a nonaggregate column in the SELECT list in the GROUP BY clause
4.Use group by and avg
5.GROUP BY may be used on a column without the column name appearing in the result set
6.Grouping at Multiple Levels: group by more than one column
7.Column sequence in the group by impacts the ordering
8.Using the ORDER BY Clause to Sort Groups
9.You don't have to include the columns used in the GROUP BY clause in your SELECT clause
10.GROUP BY and HAVING clauses
11.Using a Column Multiple Times in a GROUP BY Clause
12.Group joined tables
13.Group by case
14.Group by course name then by begin date
15.Group and count employeem and display only if its count is more than 4
16.Count all employees by even/odd employee id