갑자기 고민을 해본 어느 SQL

/*
  Test DB : Oracle Enterprise 12.2.0.1 Single 

*/

-- 01. test table 생성

CREATE TABLE "SCOTT"."MYTEST"
(
  "ID"      VARCHAR2(50)  NOT NULL ENABLE,
  "UPDATED" DATE          NOT NULL ENABLE,
  "MYNAME"  VARCHAR2(100) NOT NULL ENABLE,
  PRIMARY KEY ("ID", "UPDATED")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
    STATISTICS
    TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING
TABLESPACE "USERS"

-- 02. 통계정보 설정
BEGIN
  dbms_stats.set_column_stats ('SCOTT', 'MYTEST', 'ID', distcnt => 8 );
  dbms_stats.set_column_stats ('SCOTT', 'MYTEST','UPDATED', distcnt => 300000);
  dbms_stats.set_table_stats ('SCOTT', 'MYTEST',    NUMROWS => 2400000);
  dbms_stats.set_index_stats ('SCOTT', 'SYS_C0011249', NUMROWS => 2400000);
END;

-- 03. 원본 쿼리

SELECT *
FROM mytest T1
WHERE T1.updated = (SELECT max(updated)
                    FROM mytest
                    WHERE id = T1.id);

Plan hash value: 4215771275
 
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     8 |   992 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     8 |   992 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |              |     8 |   992 |     2   (0)| 00:00:01 |
|   3 |    VIEW                      | VW_SQ_1      |     8 |   288 |     1   (0)| 00:00:01 |
|   4 |     HASH GROUP BY            |              |     8 |   288 |     1   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN         | SYS_C0011249 |  2400K|    82M|     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0011249 |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| MYTEST       |     1 |    88 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("ITEM_1"="T1"."ID" AND "T1"."UPDATED"="MAX(UPDATED)")



-- 04. 쿼리 변형

select *
  from mytest aa,
    (select /*+ index_ffs(bb) */
            id, max(updated) as updated
      from mytest bb
      group by ID
      ) cc
where aa.id = cc.id
and aa.UPDATED = cc.updated
;

Plan hash value: 198263429
 
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     8 |   992 |    69  (96)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     8 |   992 |    69  (96)| 00:00:01 |
|   2 |   NESTED LOOPS               |              |     8 |   992 |    69  (96)| 00:00:01 |
|   3 |    VIEW                      |              |     8 |   288 |    68  (98)| 00:00:01 |
|   4 |     HASH GROUP BY            |              |     8 |   288 |    68  (98)| 00:00:01 |
|   5 |      INDEX FAST FULL SCAN    | SYS_C0011249 |  2400K|    82M|     2   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0011249 |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| MYTEST       |     1 |    88 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("AA"."ID"="CC"."ID" AND "AA"."UPDATED"="CC"."UPDATED")

select
      *
  from mytest aa,
    (select /*+ parallel_index(bb, 2) index_ffs(bb) */
            id, max(updated) as updated
      from mytest bb
      group by ID
      ) cc
where aa.id = cc.id
and aa.UPDATED = cc.updated
;

Plan hash value: 1395081935
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     8 |   992 |    38  (93)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10001     |     8 |   992 |    38  (93)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                |              |     8 |   992 |    38  (93)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     NESTED LOOPS               |              |     8 |   992 |    38  (93)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      VIEW                      |              |     8 |   288 |    73  (98)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       HASH GROUP BY            |              |     8 |   288 |    73  (98)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE              |              |     8 |   288 |    73  (98)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |         PX SEND HASH           | :TQ10000     |     8 |   288 |    73  (98)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   9 |          HASH GROUP BY         |              |     8 |   288 |    73  (98)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |           PX BLOCK ITERATOR    |              |  2400K|    82M|     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  11 |            INDEX FAST FULL SCAN| SYS_C0011249 |  2400K|    82M|     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 12 |      INDEX UNIQUE SCAN         | SYS_C0011249 |     1 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  13 |     TABLE ACCESS BY INDEX ROWID| MYTEST       |     1 |    88 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  12 - access("AA"."ID"="CC"."ID" AND "AA"."UPDATED"="CC"."UPDATED")

--
-- Table 에 MYNAME 칼럼이 없다면 
--

SELECT /*+ index_ffs(T1, SYS_C0011249) */
      *
FROM mytest T1
WHERE T1.UPDATED =  (
                     select
                         UPDATED
                     from
                       (SELECT
                          /*+ index_desc(mytest, SYS_C0011249) */
                            updated
                        FROM mytest
                        WHERE id = T1.id
                        ORDER BY updated DESC
                       )
                    where rownum <= 1
                   );


Plan hash value: 2051182498
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     1 |    36 |     3   (0)| 00:00:01 |
|*  1 |  FILTER                        |              |       |       |            |          |
|   2 |   INDEX FAST FULL SCAN         | SYS_C0011249 |     1 |    36 |     2   (0)| 00:00:01 |
|*  3 |   COUNT STOPKEY                |              |       |       |            |          |
|   4 |    VIEW                        |              |     1 |     9 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN DESCENDING| SYS_C0011249 |     1 |    36 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T1"."UPDATED"= (SELECT "UPDATED" FROM  (SELECT /*+ INDEX_DESC ("MYTEST" 
              "SYS_C0011249") */ "UPDATED" "UPDATED" FROM "MYTEST" "MYTEST" WHERE "ID"=:B1 ORDER BY 
              "UPDATED" DESC) "from$_subquery$_002" WHERE ROWNUM<=1))
   3 - filter(ROWNUM<=1)
   5 - access("ID"=:B1)


select t1.*
FROM
  (
    SELECT
      aa.id,
      aa.updated
    FROM
      (
        SELECT
          t1.id,
          t1.updated,
          rank() over (partition BY id ORDER BY updated DESC) AS rnum
        FROM mytest t1
      ) aa
    WHERE rnum <= 1
  ) bb, mytest t1
where t1.id = bb.id
and t1.updated = bb.updated
;