SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(main) */ employees.*
FROM HR.employees, 
 ( SELECT /*+ QB_NAME(iv1) */
     trunc(hire_date, 'YYYY'), MAX(employee_id) employee_id
   FROM HR.employees
   GROUP BY trunc(hire_date, 'YYYY')) x
WHERE employees.employee_id=x.employee_id
/

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE           JOB_ID         SALARY MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- ---------- -------------
        206 William              Gietz                     WGIETZ                    515.123.8181         2002-06-07 00:00:00 AC_ACCOUNT       8300        205           110
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         2001-01-13 00:00:00 AD_VP           17000        100            90
        197 Kevin                Feeney                    KFEENEY                   650.507.9822         2006-05-23 00:00:00 SH_CLERK         3000        124            50
        201 Michael              Hartstein                 MHARTSTE                  515.123.5555         2004-02-17 00:00:00 MK_MAN          13000        100            20
        199 Douglas              Grant                     DGRANT                    650.507.9844         2008-01-13 00:00:00 SH_CLERK         2600        124            50
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         2003-09-17 00:00:00 AD_ASST          4400        101            10
        198 Donald               OConnell                  DOCONNEL                  650.507.9833         2007-06-21 00:00:00 SH_CLERK         2600        124            50
        202 Pat                  Fay                       PFAY                      603.123.6666         2005-08-17 00:00:00 MK_REP           6000        201            20

8 rows selected.

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |       |     4 (100)|      8 |00:00:00.01 |      19 |       |       |          |
|   1 |  NESTED LOOPS                |               |      1 |    107 | 15622 |     4  (25)|      8 |00:00:00.01 |      19 |       |       |          |
|   2 |   NESTED LOOPS               |               |      1 |    107 | 15622 |     4  (25)|      8 |00:00:00.01 |      11 |       |       |          |
|   3 |    VIEW                      |               |      1 |    107 |  1391 |     4  (25)|      8 |00:00:00.01 |       7 |       |       |          |
|   4 |     HASH GROUP BY            |               |      1 |    107 |  2354 |     4  (25)|      8 |00:00:00.01 |       7 |  1116K|  1116K|  894K (0)|
|   5 |      TABLE ACCESS FULL       | EMPLOYEES     |      1 |    107 |  2354 |     3   (0)|    107 |00:00:00.01 |       7 |       |       |          |
|*  6 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |      8 |      1 |       |     0   (0)|      8 |00:00:00.01 |       4 |       |       |          |
|   7 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      8 |      1 |   133 |     0   (0)|      8 |00:00:00.01 |       8 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAIN
   3 - IV1  / X@MAIN
   4 - IV1
   5 - IV1  / EMPLOYEES@IV1
   6 - MAIN / EMPLOYEES@MAIN
   7 - MAIN / EMPLOYEES@MAIN

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("EMPLOYEES"."EMPLOYEE_ID"="X"."EMPLOYEE_ID")



SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(iv1) */
     trunc(hire_date, 'YYYY'), MAX(employee_id) employee_id
   FROM HR.employees
   GROUP BY trunc(hire_date, 'YYYY')
/

TRUNC(HIRE_DATE,'YY EMPLOYEE_ID
------------------- -----------
2002-01-01 00:00:00         206
2001-01-01 00:00:00         102
2006-01-01 00:00:00         197
2004-01-01 00:00:00         201
2008-01-01 00:00:00         199
2003-01-01 00:00:00         200
2007-01-01 00:00:00         198
2005-01-01 00:00:00         202

8 rows selected.

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |       |     4 (100)|      8 |00:00:00.01 |       7 |       |       |          |
|   1 |  HASH GROUP BY     |           |      1 |    107 |  2354 |     4  (25)|      8 |00:00:00.01 |       7 |  1116K|  1116K|  892K (0)|
|   2 |   TABLE ACCESS FULL| EMPLOYEES |      1 |    107 |  2354 |     3   (0)|    107 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------



--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |       |     4 (100)|      8 |00:00:00.01 |      19 |       |       |          |
|   1 |  NESTED LOOPS                |               |      1 |    107 | 15622 |     4  (25)|      8 |00:00:00.01 |      19 |       |       |          |
|   2 |   NESTED LOOPS               |               |      1 |    107 | 15622 |     4  (25)|      8 |00:00:00.01 |      11 |       |       |          |
|   3 |    VIEW                      |               |      1 |    107 |  1391 |     4  (25)|      8 |00:00:00.01 |       7 |       |       |          |
|*  6 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |      8 |      1 |       |     0   (0)|      8 |00:00:00.01 |       4 |       |       |          |
|   7 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      8 |      1 |   133 |     0   (0)|      8 |00:00:00.01 |       8 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------





