|
1. 버전 : 9.2.0.8
--------------------------------------------------------------------------------------------
2 SQL 문
SELECT A.NO,
A.PIECE_ID,
REPLACE ( A.PIECE_NM, ’"’, ’’ ) PIECE_NM,
A.ALBUM_ID,
REPLACE ( D.ALBUM_NM, ’"’, ’’ ) ALBUM_NM,
A.LYRIC_ID,
A.ARTIST_ID,
B.ARTIST_NM,
D.ALBUM_SECT,
’Y’ SERVICE_SECT,
E.CNT
FROM (
SELECT *
FROM (
SELECT ROWNUM NO, A2.*
FROM (
SELECT A.PIECE_ID, A.PIECE_NM, A.ALBUM_ID, A.LYRIC_ID, A.ARTIST_ID, A.REG_DATETIME
FROM DS_PIECE_COPY_INFO B,
DS_CONTENTS_GENRE F,
DS_PIECE A
WHERE B.PIECE_ID=F.CON_ID AND
A.PIECE_ID=B.PIECE_ID AND
F.GENRE_ID = ’110’ AND
B.copy_yn=’Y’
GROUP BY A.PIECE_ID, A.PIECE_NM, A.ALBUM_ID, A.LYRIC_ID, A.ARTIST_ID, A.REG_DATETIME
ORDER BY A.REG_DATETIME DESC) A2
) A1
WHERE NO BETWEEN 1 AND 20
) A,
DS_ARTIST B,
IM_DRM C,
DS_ALBUM D,
(
SELECT COUNT(DISTINCT(B.PIECE_ID)) CNT
FROM DS_PIECE_COPY_INFO B,
DS_CONTENTS_GENRE F
WHERE B.PIECE_ID=F.CON_ID AND
F.GENRE_ID = ’110’ and
B.copy_yn=’Y’ ) E
WHERE A.ALBUM_ID = D.ALBUM_ID
AND A.ARTIST_ID = B.ARTIST_ID
AND A.PIECE_ID= C.CON_ID
AND C.DRM_TYPE=’streaming’
ORDER BY NO
-------------------------------------------------------------------
3번 SET AUTOTRACE TRACEONLY EXPLAIN 후 결과
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 VIEW
7 6 COUNT
8 7 VIEW
9 8 SORT (GROUP BY)
10 9 NESTED LOOPS
11 10 NESTED LOOPS
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'DS_CONTENTS_GENRE'
13 12 INDEX (RANGE SCAN) OF 'DS_CONTENTS_GENRE_X1' (NON-UNIQUE)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'DS_PIECE'
15 14 INDEX (UNIQUE SCAN) OF 'MS_PIECE_NEW_P' (UNIQUE)
16 10 TABLE ACCESS (BY INDEX ROWID) OF 'DS_PIECE_COPY_INFO'
17 16 INDEX (UNIQUE SCAN) OF 'DS_PIECE_COPY_INFO_NEW_P' (UNIQUE)
18 5 TABLE ACCESS (BY INDEX ROWID) OF 'DS_ALBUM'
19 18 INDEX (UNIQUE SCAN) OF 'MS_ALBUM_NEW_P' (UNIQUE)
20 4 TABLE ACCESS (BY INDEX ROWID) OF 'DS_ARTIST'
21 20 INDEX (UNIQUE SCAN) OF 'MS_ARTIST_NEW_P' (UNIQUE)
22 3 TABLE ACCESS (BY INDEX ROWID) OF 'IM_DRM'
23 22 INDEX (RANGE SCAN) OF 'IM_DRM_X1' (NON-UNIQUE)
24 2 VIEW
25 24 SORT (AGGREGATE)
26 25 VIEW
27 26 SORT (GROUP BY)
28 27 NESTED LOOPS
29 28 NESTED LOOPS
30 29 TABLE ACCESS (BY INDEX ROWID) OF 'DS_CONTENTS_GENRE'
31 30 INDEX (RANGE SCAN) OF 'DS_CONTENTS_GENRE_X1' (NON-UNIQUE)
32 29 INDEX (UNIQUE SCAN) OF 'MS_PIECE_NEW_P' (UNIQUE)
33 28 TABLE ACCESS (BY INDEX ROWID) OF 'DS_PIECE_COPY_INFO'
34 33 INDEX (UNIQUE SCAN) OF 'DS_PIECE_COPY_INFO_NEW_P' (UNIQUE)
-----------------------------------------------------------------------------------
4. SQL 설명...
F.GENRE_ID = ’110’ AND
B.copy_yn=’Y’ 조건으로
PHP에서 페이징을 할려고 합니다. 근데 몇건 안되는 20건 만 뽑아 낼려고 이렇게 힘들게
할 필요가 있나 싶어서 올립니다.. 전 최선을 다해서 그나마 튜닝을 해서 0.15가 나오는뎅..ㅠㅠ
단축이 필요 합뉘당.. 커억... 아 그리고 마지막 CNT 는 위 조건에 만족하는 합계가 필요 해서
그냥 넣어 본거구요..-_-; 어떤 특단의 조치가 있을까욤?... 고작 20건 때문에 위에 6000건이 되는걸
다 뽑아서 꼭 BETWEEN 으로 해결을 해야 할까용?.. 분석 쩜 부탁드립니다.. 쩝...
---------------------------추 가 내 용 ----------------------
E의 조건도
F.GENRE_ID = ’110’ AND
B.copy_yn=’Y’ 같은 테이블에서 총 갯수를 구하는 겁니다. PHP 전체 행을 구할려구요
|
