/*
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
;
2017년 8월 16일 수요일
갑자기 고민을 해본 어느 SQL
피드 구독하기:
글 (Atom)