|
안녕하세요.
PC보안때문인지 표그리기가 안되서 테이블을 쿼리로 드려 죄송하네요.
아래와 같은 테이블이 있고 헤더와 디테일 관계라고 보시면 됩니다. (ex AHD-헤더 , ADT-디테일)
각 테이블별로 약 10만건 정도 데이터가 있습니다.
WHERE 절 조건에 걸려있는 컬럼으로 각 테이블에 PK또는 INDEX 가 ASC로 잡혀있습니다.
각 테이블의 제목으로 검색하기 위해 OR로 걸려있는 상태이고
검색결과를 최근 데이터 순으로 보여주려고
ORDER BY 절을 넣으면 안 넣었을때보다 많이 느려집니다.
실행계획을 보면 옵티마이져가 ALL_ROWS로 잡히고 HASH_JOIN이 설정됩니다.
OUTER JOIN을 쓸수 밖에는 없는 상태라 무지 빨라지지는 않겠지만 속도를 개선하고 싶습니다.
최종에는 페이징 처리도 넣을 예정입니다.
도움 부탁드립니다.
---------------------------------------------------------------------------------------------------------------------------------------
WITH ADT AS
(SELECT 'P01' AS P_NO, '001' AS P_SEQ FROM dual)
SELECT * FROM ADT;
WITH AHD AS
(SELECT 'P01' AS P_NO, 'A제목' AS SUBJECT FROM DUAL)
SELECT * FROM AHD;
WITH BDT AS
(SELECT 'B01' AS B_NO, '001' AS B_SEQ, 'P01' AS P_NO, '001' AS P_SEQ, 'D01' AS D_NO, '001' AS D_SEQ FROM DUAL)
SELECT * FROM BDT;
WITH BHD AS
(SELECT 'B01' AS B_NO, 'B제목' AS SUBJECT FROM DUAL)
SELECT * FROM BHD;
WITH CDT AS
(SELECT 'C01' AS C_NO, '001' AS C_SEQ, 'B01' AS B_NO, '001' AS B_SEQ, 'P01' AS P_NO, '001' AS P_SEQ, 'B01' AS O_B_NO FROM DUAL)
SELECT * FROM CDT;
WITH CHD AS
(SELECT 'C01' AS C_NO, 'B01' AS O_B_NO FROM DUAL)
SELECT * FROM CHD;
WITH CHD2 AS
(SELECT 'CC01' AS CC_NO, 'C01' AS C_NO, 'C제목' AS SUBJECT, 'B01A' AS CB_NO FROM DUAL)
SELECT * FROM CHD2;
WITH DDT AS
(SELECT 'D01' AS D_NO, '001' AS D_SEQ, 'P01' AS P_NO, '001' AS P_SEQ FROM DUAL)
SELECT * FROM DDT;
WITH DHD AS
(SELECT 'D01' AS D_NO, 'D제목' AS SUBJECT FROM DUAL)
SELECT * FROM DHD;
WITH EDT AS
(SELECT 'E01' AS E_NO, '001' AS E_SEQ, 'D01' AS D_NO, '001' AS D_SEQ FROM DUAL)
SELECT * FROM EDT;
WITH EHD AS
(SELECT 'E01' AS E_NO, 'E제목' AS SUBJECT FROM DUAL)
SELECT * FROM EHD;
WITH FDT AS
(SELECT 'F01' AS F_NO, '001' AS F_SEQ, 'E01' AS E_NO, '001' AS E_SEQ, 'D01' AS D_NO, '001' AS D_SEQ FROM DUAL)
SELECT * FROM FDT;
WITH FHD AS
(SELECT 'F01' AS F_NO FROM DUAL)
SELECT * FROM FHD;
------------------------------------------------------------------------------------------------------------------------------
SELECT
*
FROM(
SELECT
AHD.A_NO, ADT.A_SEQ, AHD.SUBJECT AS A_SUBJECT
,BHD.B_NO, BDT.B_SEQ, BHD.SUBJECT AS B_SUBJECT
,CHD2.CC_NO, CHD2.SUBJECT AS C_SUBJECT
,DHD.D_NO, DDT.D_SEQ, DHD.SUBJECT AS D_SUBJECT
,EHD.E_NO, EDT.E_SEQ, EHD.SUBJECT AS E_SUBJECT
,F.F_NO
WHERE AHD.A_NO = ADT.A_NO
AND ADT.A_NO = BDT.A_NO(+)
AND ADT.A_SEQ = BDT.A_SEQ(+)
AND BDT.B_NO = BHD.B_NO(+)
AND BDT.B_NO = CDT.O_B_NO(+)
AND BDT.A_NO = CDT.A_NO(+)
AND BDT.A_SEQ = CDT.A_SEQ(+)
AND CDT.C_NO = CHD.C_NO(+)
AND CDT.O_B_NO = CHD.O_B_NO(+)
AND CHD.O_B_NO = SUBSF(CHD2.CB_NO(+), 0, 3)
AND BDT.D_NO = DDT.D_NO(+)
AND BDT.D_SEQ = DDT.D_SEQ(+)
AND BDT.A_NO = DDT.A_NO(+)
AND BDT.A_SEQ = DDT.A_SEQ(+)
AND DDT.D_NO = DHD.D_NO(+)
AND DDT.D_NO = EDT.D_NO(+)
AND DDT.D_SEQ = EDT.D_SEQ(+)
AND EDT.E_NO = EHD.E_NO(+)
AND EDT.E_NO = FDT.E_NO(+)
AND EDT.E_SEQ = FDT.E_SEQ(+)
AND EDT.D_NO = FDT.D_NO(+)
AND EDT.D_SEQ = FDT.D_SEQ(+)
AND FDT.F_NO = FHD.F_NO(+)
)A
WHERE ( A.A_SUBJECT LIKE '%%' OR A.B_SUBJECT LIKE '%%' OR A.C_SUBJECT LIKE '%%' OR A.D_SUBJECT LIKE '%%' OR A.E_SUBJECT LIKE '%%' )
ORDER BY A.A_NO DESC, A.A_SEQ, A.B_NO DESC, A.B_SEQ, A.CC_NO DESC, A.D_NO DESC, A.D_SEQ, A.E_NO DESC, A.E_SEQ, A.F_NO
|
