How could optimise union SQL?

[zealot_w]

The only differece between two sub-SQL is the year range. I wanna know how I can combine it or any other ways to optimize this SQL. I apprecate any help.. if somebody know
Select
NVL(SUM(-1), -1) as Flag1,
NVL( SUM( DECODE( SIGN(SUBSTR(eo.dVcScheduledStartTOD, 9, 6) - '075959') * SIGN(SUBSTR(eo.dVcScheduledStartTOD, 9, 6) - '090000'), -1, DECODE(eo.dNumInOutPatientFlg,1, 1))), 0) as eIN1,
NVL( SUM( DECODE( SIGN(SUBSTR(eo.dVcScheduledStartTOD, 9, 6) - '075959') * SIGN(SUBSTR(eo.dVcScheduledStartTOD, 9, 6) - '090000'), -1, DECODE(eo.dNumInOutPatientFlg,0, 1))), 0) as eOUT1,
NVL(SUM(DECODE(eo.dNumInOutPatientFlg, 1, 1)), 0) as eIN12,
NVL(SUM(DECODE(eo.dNumInOutPatientFlg, 0, 1)), 0) as eOUT12,
NVL(COUNT(*), 0) as eTotal12
From
ExamOrderData eo,
Exam1stGroupData e1,
Exam2ndGroupData e2,
Exam3rdGroupData e3
Where
eo.aNumDataID = e1.xNumExamOrderID
and e1.aNumDataID = e2.xNumExam1stGroupDataID
and e2.aNumDataID = e3.xNumExam2ndGroupDataID
and (eo.dNumInOutPatientFlg = 1 or eo.dNumInOutPatientFlg = 0)
and e1.dVcExam1stGroupCode in ('10000000','20000000','30000000')
and e2.dVcExam2ndGroupCode in ('11000000',' .....................)
and e3.dVcExam3rdGroupCode in ('11010000','11020000' ...........)
and (eo.dVcScheduledStartTOD > '20040400000000' and eo.dVcScheduledStartTOD < '20050400000000')

UNION

Select
NVL(SUM(1), 1) as Flag2,
NVL( SUM( DECODE( SIGN(SUBSTR(eo.dVcScheduledStartTOD, 9, 6) - '075959') * SIGN(SUBSTR(eo.dVcScheduledStartTOD, 9, 6) - '090000'), -1, DECODE(eo.dNumInOutPatientFlg,1, 1))), 0) as eIN1,
NVL( SUM( DECODE( SIGN(SUBSTR(eo.dVcScheduledStartTOD, 9, 6) - '075959') * SIGN(SUBSTR(eo.dVcScheduledStartTOD, 9, 6) - '090000'), -1, DECODE(eo.dNumInOutPatientFlg,0, 1))), 0) as eOUT1,
NVL(SUM(DECODE(eo.dNumInOutPatientFlg, 1, 1)), 0) as eIN12,
NVL(SUM(DECODE(eo.dNumInOutPatientFlg, 0, 1)), 0) as eOUT12,
NVL(COUNT(*), 0) as eTotal12
From
ExamOrderData eo,
Exam1stGroupData e1,
Exam2ndGroupData e2,
Exam3rdGroupData e3
Where
eo.aNumDataID = e1.xNumExamOrderID
and e1.aNumDataID = e2.xNumExam1stGroupDataID
and e2.aNumDataID = e3.xNumExam2ndGroupDataID
and (eo.dNumInOutPatientFlg = 1 or eo.dNumInOutPatientFlg = 0)
and e1.dVcExam1stGroupCode in ('10000000','20000000','30000000')
and e2.dVcExam2ndGroupCode in ('11000000',' .....................)
and e3.dVcExam3rdGroupCode in ('11010000','11020000' ...........)
and (eo.dVcScheduledStartTOD > '20030400000000' and eo.dVcScheduledStartTOD < '20040400000000')

[skhanal]

Can you use merge the date range to one range?, that is

eo.dVcScheduledStartTOD > '20040400000000' and eo.dVcScheduledStartTOD < '20050400000000'

eo.dVcScheduledStartTOD > '20030400000000' and eo.dVcScheduledStartTOD < '20040400000000'

to

eo.dVcScheduledStartTOD > '20030400000000' and eo.dVcScheduledStartTOD < '20050400000000'


If not one thing you can do is UNION ALL rather than UNION, if you don't have duplicate rows (or if you don't care about duplicate rows).

[zealot_w]

Thank you.
This SQL is used in a Japanese web application, and Japanese year is range from April to April of next year. So I
wanna to calculate two year's info separately.
What's more, it takes more time, if I use UNION ALL instead of UNION. There are 130000 data in Database, and excution time will more than 14 seconds. And it will lead to time out in Poolman.

[skhanal]

What version of Oracle are you using?. Are you using rule based optimizer?

Do you have indexes defined on the join columns?

[zealot_w]

We use Oracle 8.1.7, and I think there have defined Index in tabls.
Following is this SQL's execution, and I just understand a few part of it. The only result I could tell is that my SQL is slow.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 UNION-ALL
3 2 SORT (AGGREGATE)
4 3 CONCATENATION
5 4 NESTED LOOPS
6 5 NESTED LOOPS
7 6 NESTED LOOPS
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'EXAMORDERD
ATA'

9 8 INDEX (RANGE SCAN) OF 'IDX_EXAMORDERINOUT'
(NON-UNIQUE)

10 7 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM1STGRO
UPDATA'

11 10 INDEX (RANGE SCAN) OF 'IDX_EXAM1STD2EXAMOR
DERD' (NON-UNIQUE)

12 6 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM2NDGROUP
DATA'

13 12 INDEX (RANGE SCAN) OF 'IDX_EXAM2NDD2EXAM1STD
' (NON-UNIQUE)

14 5 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM3RDGROUPDA
TA'

15 14 INDEX (RANGE SCAN) OF 'IDX_EXAM3RDD2EXAM2NDD'
(NON-UNIQUE)

16 4 NESTED LOOPS
17 16 NESTED LOOPS
18 17 NESTED LOOPS
19 18 TABLE ACCESS (BY INDEX ROWID) OF 'EXAMORDERD
ATA'

20 19 INDEX (RANGE SCAN) OF 'IDX_EXAMORDERINOUT'
(NON-UNIQUE)

21 18 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM1STGRO
UPDATA'

22 21 INDEX (RANGE SCAN) OF 'IDX_EXAM1STD2EXAMOR
DERD' (NON-UNIQUE)

23 17 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM2NDGROUP
DATA'

24 23 INDEX (RANGE SCAN) OF 'IDX_EXAM2NDD2EXAM1STD
' (NON-UNIQUE)

25 16 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM3RDGROUPDA
TA'

26 25 INDEX (RANGE SCAN) OF 'IDX_EXAM3RDD2EXAM2NDD'
(NON-UNIQUE)

27 2 SORT (AGGREGATE)
28 27 CONCATENATION
29 28 NESTED LOOPS
30 29 NESTED LOOPS
31 30 NESTED LOOPS
32 31 TABLE ACCESS (BY INDEX ROWID) OF 'EXAMORDERD
ATA'

33 32 INDEX (RANGE SCAN) OF 'IDX_EXAMORDERINOUT'
(NON-UNIQUE)

34 31 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM1STGRO
UPDATA'

35 34 INDEX (RANGE SCAN) OF 'IDX_EXAM1STD2EXAMOR
DERD' (NON-UNIQUE)

36 30 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM2NDGROUP
DATA'

37 36 INDEX (RANGE SCAN) OF 'IDX_EXAM2NDD2EXAM1STD
' (NON-UNIQUE)

38 29 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM3RDGROUPDA
TA'

39 38 INDEX (RANGE SCAN) OF 'IDX_EXAM3RDD2EXAM2NDD'
(NON-UNIQUE)

40 28 NESTED LOOPS
41 40 NESTED LOOPS
42 41 NESTED LOOPS
43 42 TABLE ACCESS (BY INDEX ROWID) OF 'EXAMORDERD
ATA'

44 43 INDEX (RANGE SCAN) OF 'IDX_EXAMORDERINOUT'
(NON-UNIQUE)

45 42 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM1STGRO
UPDATA'

46 45 INDEX (RANGE SCAN) OF 'IDX_EXAM1STD2EXAMOR
DERD' (NON-UNIQUE)

47 41 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM2NDGROUP
DATA'

48 47 INDEX (RANGE SCAN) OF 'IDX_EXAM2NDD2EXAM1STD
' (NON-UNIQUE)

49 40 TABLE ACCESS (BY INDEX ROWID) OF 'EXAM3RDGROUPDA
TA'

50 49 INDEX (RANGE SCAN) OF 'IDX_EXAM3RDD2EXAM2NDD'
(NON-UNIQUE)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1511618 consistent gets
1302 physical reads
0 redo size
2038 bytes sent via SQL*Net to client
9648 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed

[skhanal]

Execute each SELECT separately and find which one is the performance hog and concentrate on that.