데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 38816 게시물 읽기

No. 38816

검색 속도 문제 입니다.
작성자	최진규(cjg1012)	작성일	2011-08-24 13:28ⓒ 2011-08-24 13:34ⓜ	조회수	9,458

질문을 하기 위해 테스트 쿼리를 만들려다가 그냥 본쿼리를 보고 문의드리는것이 나을거 같아 본쿼리 올립니다.

SELECT T3.*
FROM (
    SELECT ROWNUM NUM, T2.*
      FROM (
        SELECT T1.*, TO_CHAR(T1.DISTANCE_NUMBER, 'FM999,990.0') || ' km' DISTANCE
          FROM (
            SELECT A.EMAIL, A.NICK_NAME, A.JENDER, A.INTRODUCE,
                   (SELECT FILE_URL_PATH || FILE_NAME_THUMB
                      FROM MT_MEMBER_FILE D
                     WHERE D.MEMBER_EMAIL = A.EMAIL AND D.FILE_INDEX = B.FILE_INDEX) URL,
                   CALC_DISTANCE(A.LATITUDE, A.LONGITUDE, TO_NUMBER('37.627072325'), TO_NUMBER('127.0333922')) DISTANCE_NUMBER --거리구하는 사용자함수사용
              FROM MT_MEMBER A,
                   (SELECT MEMBER_EMAIL, MIN(FILE_INDEX) FILE_INDEX
                      FROM MT_MEMBER_FILE
                     GROUP BY MEMBER_EMAIL) B
             WHERE A.JENDER <> 'F'
               AND A.EMAIL = B.MEMBER_EMAIL(+)
               AND A.BIRTH_DATE <= '20111231'
               AND A.BIRTH_DATE >= '10111231'
               AND A.REPORT_COUNT < 4
               AND A.LATITUDE IS NOT NULL
        ) T1
        WHERE T1.DISTANCE_NUMBER >= 0 AND T1.DISTANCE_NUMBER <= 99999 -- 이 조건이 문제가 되네요...이조건만 빼면 실행시간 반으로 줄어들고요 (1)
        ORDER BY T1.DISTANCE_NUMBER -- 이거까지 빼면 완전빨라요... (2)
    ) T2
   WHERE ROWNUM <=20
) T3 WHERE T3.NUM > 0;

현재 기껏해야 15000ROW 정도의 데이터가 있는데요.. 실행시간이 4초걸려요..(1)을 빼면 2초 (2)까지 빼면 1초 미만인데요...
이런식을 가단 십만이 넘으면 10초이상도 걸릴꺼 같은데요..
보시면 함수를 통하여 산출된 수치를 가지고 조건필터와 정렬을 합니다.
이럴경우 인덱스로 걸수도 없고 뭐 다른 방법 없을까요??? 조언 부탁드립니다.

이 글에 대한 댓글이 총 7건 있습니다.

일단 함수 사용에 대한 부담도 생각해야 합니다만.
우선은 일반적인 페이징 처리시 튜닝포인트를 짚어 드릴께요.
기준이 되는 테이블은 a 이며 그밖의 테이블(b, d)은 부가정보입니다.
필요한 건수는 고작 20건이며 나머지는 버려질 자료입니다.
a, b, d를 모두 조인후 페이지처리하지 마시고
a 만 가지고 20건을 구하고 부가정보는 최종 20건만 가지고 조인하세요.
조인의 일량이 획기적으로 줄어듭니다.

또한 b와 d가 같은 테이블을 두번 읽네요.
한번만 읽고도 가능합니다.
(
SELECT MIN(file_url_path || file_name_thumb)
KEEP(DENSE_RANK FIRST ORDER BY member_email)
FROM mt_member_file d
WHERE d.member_email = a.email
) url

마농(manon94)님이 2011-08-24 14:05에 작성한 댓글입니다.
이 댓글은 2011-08-24 14:08에 마지막으로 수정되었습니다.

function 결과를 sort 사용은 비효율 발생

> alter table MT_MEMBER add column DISTANCE_NUMBER

: 비정규화로 데이터 입력/수정시 저장하고,

(BIRTH_DATE + DISTANCE_NUMBER + REPORT_COUNT + ..) 구성된 index 생성 후 사용

(function-based index 는 Deterministic value 가 아니므로 불가)

박광일(tohappy)님이 2011-08-24 14:45에 작성한 댓글입니다.

두분모두 조언 감사드립니다.
마농님 덕분에 또 새롭게 하나 배웠네요..^^

박광일님께서 조언해준 되로 했는데요..
더 느려졌어요..ㅜㅜ
DISTANCE_NUMBER 칼럼하나 추가 해서 DEFAULT값으로 넘버형으로 월일 들어가게 한다음
DISTANCE_NUMBER 에 INDEX 생성하고 했는데 1~2초 정도 더 느려졌어요..
물론 위쿼리에서 마농님 조언되로 정리하다음에요...

ORDER BY T1.DISTANCE_NUMBER -> 이놈이 문제입니다.

왜 그럴까요..ㅜㅜ

최진규님이 2011-08-24 16:20에 작성한 댓글입니다.
이 댓글은 2011-08-24 16:41에 마지막으로 수정되었습니다. Edit Ｘ

거리 구하는 공식은 루트( (x-x1)^2 + (y-y1)^2 ) <= 99999
이조건으로 거리를 구한뒤 99999 보다 작은것을 구하는데요.
즉, x1, y1을 기준으로 반지름이 99999 인 원 안의 것을 구하는 것이죠.
원을 감싸는 정사각형을 생각해 보시구요.
이 정사각형을 기준으로 x, y에 조건을 줄 수 있습니다.
물론 실제 구해야 할 원보다 정사각형이 크기 때문에
다시 원 조건으로 두번 조건을 줘야하겠지만
1차적으로 정사각형 조건으로 필터링을 해주면?
그만큼 함수 사용 횟수가 줄겠지요.

WHERE a.jender <> 'F'
AND a.birth_date <= '20111231'
AND a.birth_date >= '10111231'
AND a.report_count < 4
AND a.latitude IS NOT NULL
AND a.latitude BETWEEN '37.627072325' - 99999 AND '37.627072325' + 99999
AND a.longitude BETWEEN '127.0333922' - 99999 AND '127.0333922' + 99999

마농(manon94)님이 2011-08-24 17:26에 작성한 댓글입니다.
이 댓글은 2011-08-24 17:56에 마지막으로 수정되었습니다.

-- DISTANCE 를 사용하는 function 을 다수 조회 시,

데이터 in/mod 시 비정규화 하여 저장하여 사용 한다는 가정하에 테스트한 내용입니다.

- ver. 10.0.2.4

SQL> -- mt_member : 100만건 생성

SQL> create table mt_member nologging as

select level as no,

'memid_'|| level ||'@mailaddr.com' as email,

'nick_' || level as nick_name,

to_char((sysdate-(365*10))+(level/6),'YYYYMMDD') as birth_date,

decode((mod(level,2)), 1, 'M', 'F') as jender,

mod(level,8) as report_count,

37+(level*0.000001) as latitude,

126+(level*0.000001) as longitude,

'INTRODUCE bla bla' as INTRODUCE,

mod(level,100) as DISTANCE

from dual

connect by level <= 1000000;

Statement Processed.

SQL> exec dbms_stats.gather_table_stats('TMALL','mt_member', degree=>'4')

PL/SQL executed.

SQL> create unique index pk_mt_member on mt_member(email) nologging;

Statement Processed.

SQL> create index ix1_mt_member on mt_member(distance, birth_date, jender, report_count) nologging;

Statement Processed.

SQL> select count(*) from mt_member a;

COUNT(*)

-----------

1000000

1 rows selected.

SQL> -- mt_member_file : mt_member 중 75% * 중복 생성

SQL> create table mt_member_file nologging as

select rownum as no,

a.email as member_email,

t.id as file_index,

'/img/url/' ||t.id as FILE_URL_PATH ,

'file_'||t.id ||'.jpg' as FILE_NAME_THUMB

from mt_member a, copy_t t

where mod(a.no,4) <> 0

and t.id <=2;

Statement Processed.

SQL> exec dbms_stats.gather_table_stats('TMALL','MT_MEMBER_FILE', degree=>'4')

PL/SQL executed.

SQL> create unique index pk_MT_MEMBER_FILE on MT_MEMBER_FILE(no) nologging;

Statement Processed.

SQL> create unique index ix1_MT_MEMBER_FILE on MT_MEMBER_FILE(MEMBER_EMAIL, file_index) nologging;

Statement Processed.

SQL> select count(*) from MT_MEMBER_FILE a;

COUNT(*)

-----------

1500000

1 rows selected.

MT_MEMBER

---------

Rows=1,000,000 Blocks=13,455

Empty Blocks=0 Avg Space=0

Chain Count=0 Avg Row Length=90

Avg Space Freelist Blocks=0 Freelist Blocks=0

Sample Size=1,000,000 Last Analyze=2011/08/25

Partitioned=NO

Column Name Nullable Column Type Distinct Buckets

-------------------------------- -------- ------------- ---------- ----------

NO NUMBER 999,039 1

EMAIL VARCHAR2(59) 992,719 1

NICK_NAME VARCHAR2(45) 999,039 1

BIRTH_DATE VARCHAR2(8) 166,668 254

JENDER VARCHAR2(1) 2 2

REPORT_COUNT NUMBER 8 8

LATITUDE NUMBER 999,039 1

LONGITUDE NUMBER 999,039 1

INTRODUCE CHAR(17) 1 1

DISTANCE NUMBER 101 1

INDEX

-----------------------------------------------------------------------------

IX1_MT_MEMBER : DISTANCE + BIRTH_DATE + JENDER + REPORT_COUNT

Type=NORMAL, Uniq=NO, Distinct=1,000,000, Rows=1,000,000, Last Analyze=2011/08/25

PK_MT_MEMBER : EMAIL

Type=NORMAL, Uniq=YES, Distinct=1,032,165, Rows=1,032,165, Last Analyze=2011/08/25

MT_MEMBER_FILE

--------------

Rows=1,500,000 Blocks=12,916

Empty Blocks=0 Avg Space=0

Chain Count=0 Avg Row Length=56

Avg Space Freelist Blocks=0 Freelist Blocks=0

Sample Size=1,500,000 Last Analyze=2011/08/25

Partitioned=NO

Column Name Nullable Column Type Distinct Buckets

-------------------------------- -------- ------------- ---------- ----------

NO NUMBER 1,499,395 1

MEMBER_EMAIL VARCHAR2(59) 750,000 1

FILE_INDEX NOT NULL NUMBER(2) 2 1

FILE_URL_PATH VARCHAR2(49) 2 1

FILE_NAME_THUMB VARCHAR2(49) 2 1

INDEX

-----------------------------------------------------------------------------

IX1_MT_MEMBER_FILE : MEMBER_EMAIL + FILE_INDEX

Type=NORMAL, Uniq=YES, Distinct=1,500,000, Rows=1,500,000, Last Analyze=2011/08/25

PK_MT_MEMBER_FILE : NO

Type=NORMAL, Uniq=YES, Distinct=1,500,000, Rows=1,500,000, Last Analyze=2011/08/25

SQL> select /*+ gather_plan_statistics */

mt.*,

(select /*+ index_asc(a ix1_mt_member_file)*/ -- min(file_idx) 에 해당하는 index 사용으로 scalar처리 가능

min(file_url_path || file_name_thumb) as url

from mt_member_file ia

where ia.member_email = mt.email

) as file_index

from (select /*+ index_desc(a ix1_mt_member)*/

a.email,

a.distance,

a.nick_name,

a.jender,

a.introduce

from mt_member a

where a.jender = 'M' -- 부정형 > 긍정형

and a.birth_date <= '20101231'

and a.birth_date >= '2010101'

and a.report_count < 4

and a.latitude > 0 -- latitude 가 null 이라면 distance는 0 따라서 불필요 조건

and a.distance >= 0

and a.distance <= 99999

and rownum <= 20 -- index 사용으로 부분범위 처리가능

) mt

;

---------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

| 1 | SORT AGGREGATE | | 20 | 1 | 20 |00:00:00.01 | 83 |

| 2 | TABLE ACCESS BY INDEX ROWID | MT_MEMBER_FILE | 20 | 2 | 40 |00:00:00.01 | 83 |

|* 3 | INDEX RANGE SCAN | IX1_MT_MEMBER_FILE | 20 | 2 | 40 |00:00:00.01 | 43 |

| 4 | VIEW | | 1 | 1 | 20 |00:00:00.01 | 1154 |

|* 5 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 1154 |

|* 6 | TABLE ACCESS BY INDEX ROWID | MT_MEMBER | 1 | 1 | 20 |00:00:00.01 | 1154 |

|* 7 | INDEX RANGE SCAN DESCENDING| IX1_MT_MEMBER | 1 | 11 | 20 |00:00:00.01 | 1134 |

---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("IA"."MEMBER_EMAIL"=:B1)

5 - filter(ROWNUM<=20)

6 - filter("A"."LATITUDE">0)

7 - access("A"."DISTANCE"<=99999 AND "A"."BIRTH_DATE"<='20101231' AND "A"."JENDER"='M' AND

"A"."REPORT_COUNT"<4 AND "A"."DISTANCE">=0 AND "A"."BIRTH_DATE">='2010101')

filter(("A"."BIRTH_DATE"<='20101231' AND "A"."JENDER"='M' AND "A"."REPORT_COUNT"<4 AND

"A"."BIRTH_DATE">='2010101' AND "A"."DISTANCE"<=99999 AND "A"."DISTANCE">=0))

36 rows selected.

>> mt_member은 최소 row 처리 로 부분 범위 사용 가능

- sort 에 따른 overhead 없음

- birth_date range 가 넓을 수록 성능 영향 큼

- mt_member_file 는 select 절에서 1회 join 처리, index 사용으로 aggregate 불필요.

- 1237 Block Read

박광일(tohappy)님이 2011-08-25 11:06에 작성한 댓글입니다.

혹시 Distance_number의 값이 Null값이 존재하는 것은 아닌가요 ?

Null값 처리를 하면 빨라질 것 같은데....

simon님이 2011-08-25 11:30에 작성한 댓글입니다. Edit Ｘ

구면 혹은 타원체의 표면에서의 거리공식은 평면 거리공식과 다릅니다. 예를 들면, 두 자오선사이의 거리는, 위도가 커질수록 작아지므로, 경도와 위도를 사용하여 직접 평면 좌표계의 거리공식으로 거리를 계산할 수 없읍니다. 따라서, 각 지점에 대한 위도와 경도를, (일부 지역에 대해 적합한) 평면 좌표계로 바꾸어, 그 데이터를 테이블에 먼저 저장해야 합니다. 그 다음에 마농님이 제안한 필터링방법을 사용할수 있읍니다.

hopper(bunny)님이 2011-08-31 13:22에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
38822	쿼리 좀 부탁 드립니다. [2]	초보	2011-08-25	3834
38820	안뇽하세요 질문이 있습니다. (재귀 프로시져) [2]	윤명철	2011-08-25	5286
38819	자동백업스크립트입니다.	오라클초보임	2011-08-25	5025
38816	검색 속도 문제 입니다. [7]	최진규	2011-08-24	9458
38815	Script 관련 질문입니다. [3]	나그네	2011-08-24	5564
38814	MERGE INTO DBLINK 사용~ [2]	주하파파	2011-08-23	7149
38813	TIMESTAMP 필드 검색 속도 향상 [2]	레이첼	2011-08-23	6995