Yudi Ifan
Reputation: 3
Optimize Oracle Query - for View
I have single question, to optimize a View Query using Oracle. Is there anyone able to help me out.
I'm about to select circuitname and oldname (the old name of circuitname) from only one table. But each circuitname, possibly has more than 1 sub name (oldname, sub-oldname, etc), and i want to join all of them with this View Query. This query is good, but when I select 1 by 1 circuitname, oldname, sub-oldname, etc until 20 circuitname, it takes too much time. I want to optimize the query, to perform less time, before the app timeout. Here's the query
CREATE OR REPLACE FORCE EDITIONABLE VIEW "IMS"."VW_SI_CASCADE" ("R1", "L1", "L2", "L3", "L4", "L5", "L6", "L7", "L8", "L9", "L10", "L11", "L12", "L13", "L14", "L15", "L16", "L17", "L18", "L19", "L20", "L21", "M1", "M2", "M3", "M4", "M5", "M6", "M7", "M8", "M9", "M10", "M11", "M12", "M13", "M14", "M15", "M16", "M17", "M18", "M19", "M20", "M21") AS
SELECT
ROWNUM AS R1,
V1.OLDNAME AS L1,
V1.CIRCUITNAME AS L2,
V2.CIRCUITNAME AS L3,
V3.CIRCUITNAME AS L4,
V4.CIRCUITNAME AS L5,
V5.CIRCUITNAME AS L6,
V6.CIRCUITNAME AS L7,
V7.CIRCUITNAME AS L8,
V8.CIRCUITNAME AS L9,
V9.CIRCUITNAME AS L10,
V10.CIRCUITNAME AS L11,
V11.CIRCUITNAME AS L12,
V12.CIRCUITNAME AS L13,
V13.CIRCUITNAME AS L14,
V14.CIRCUITNAME AS L15,
V15.CIRCUITNAME AS L16,
V16.CIRCUITNAME AS L17,
V17.CIRCUITNAME AS L18,
V18.CIRCUITNAME AS L19,
V19.CIRCUITNAME AS L20,
V20.CIRCUITNAME AS L21,
V1.OLDCIRCUITID AS M1,
V1.CIRCUITID AS M2,
V2.CIRCUITID AS M3,
V3.CIRCUITID AS M4,
V4.CIRCUITID AS M5,
V5.CIRCUITID AS M6,
V6.CIRCUITID AS M7,
V7.CIRCUITID AS M8,
V8.CIRCUITID AS M9,
V9.CIRCUITID AS M10,
V10.CIRCUITID AS M11,
V11.CIRCUITID AS M12,
V12.CIRCUITID AS M13,
V13.CIRCUITID AS M14,
V14.CIRCUITID AS M15,
V15.CIRCUITID AS M16,
V16.CIRCUITID AS M17,
V17.CIRCUITID AS M18,
V18.CIRCUITID AS M19,
V19.CIRCUITID AS M20,
V20.CIRCUITID AS M21
FROM VW_SI_OLDNAME V1
LEFT JOIN VW_SI_OLDNAME V2 ON V2.OLDNAME = V1.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V3 ON V3.OLDNAME = V2.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V4 ON V4.OLDNAME = V3.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V5 ON V5.OLDNAME = V4.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V6 ON V6.OLDNAME = V5.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V7 ON V7.OLDNAME = V6.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V8 ON V8.OLDNAME = V7.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V9 ON V9.OLDNAME = V8.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V10 ON V10.OLDNAME = V9.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V11 ON V11.OLDNAME = V10.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V12 ON V12.OLDNAME = V11.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V13 ON V13.OLDNAME = V12.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V14 ON V14.OLDNAME = V13.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V15 ON V15.OLDNAME = V14.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V16 ON V16.OLDNAME = V15.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V17 ON V17.OLDNAME = V16.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V18 ON V18.OLDNAME = V17.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V19 ON V19.OLDNAME = V18.CIRCUITNAME
LEFT JOIN VW_SI_OLDNAME V20 ON V20.OLDNAME = V19.CIRCUITNAME;
Pardon if I have any mistakes, and kindly need your help guys, my fellow heroes!
Upvotes: 0
Views: 83
Answers (2)
Sayan Malakshinov
Reputation: 8655
For example, your VW_SI_OLDNAME contains a data like this:
with VW_SI_OLDNAME as (
select
'name_'||to_char(level-1,'fm0000') OLDNAME
,'name_'||to_char(level ,'fm0000') CIRCUITNAME
,level-1 OLDCIRCUITID
,level CIRCUITID
from dual
connect by level<=100
)
select *
from VW_SI_OLDNAME;
Example:
OLDNAME CIRCUITNAM OLDCIRCUITID CIRCUITID
---------- ---------- ------------ ----------
name_0000 name_0001 0 1
name_0001 name_0002 1 2
name_0002 name_0003 2 3
name_0003 name_0004 3 4
name_0004 name_0005 4 5
...
name_0028 name_0029 28 29
name_0029 name_0030 29 30
name_0030 name_0031 30 31
...
name_0097 name_0098 97 98
name_0098 name_0099 98 99
name_0099 name_0100 99 100
In this case, you can easily create your view using MATCH_RECOGNIZE that will scan your VW_SI_OLDNAME just once:
VW_SI_CASCADE as (
select *
from VW_SI_OLDNAME
match_recognize(
MEASURES
classifier() event
,match_number() match
,prev(CIRCUITID,1) CIRCUITID_1
,prev(CIRCUITID,2) CIRCUITID_2
,prev(CIRCUITID,3) CIRCUITID_3
,prev(CIRCUITID,4) CIRCUITID_4
,prev(CIRCUITID,5) CIRCUITID_5
,prev(CIRCUITID,6) CIRCUITID_6
,prev(CIRCUITID,7) CIRCUITID_7
,prev(CIRCUITID,8) CIRCUITID_8
,prev(CIRCUITNAME,1) CIRCUITNAME_1
,prev(CIRCUITNAME,2) CIRCUITNAME_2
,prev(CIRCUITNAME,3) CIRCUITNAME_3
,prev(CIRCUITNAME,4) CIRCUITNAME_4
,prev(CIRCUITNAME,5) CIRCUITNAME_5
,prev(CIRCUITNAME,6) CIRCUITNAME_6
,prev(CIRCUITNAME,7) CIRCUITNAME_7
,prev(CIRCUITNAME,8) CIRCUITNAME_8
ALL ROWS PER MATCH
PATTERN (p)
DEFINE
p AS OLDNAME = prev(CIRCUITNAME)
)
)
I've created only 1-8 just to make this example shorter.
Full example with test data:
with
VW_SI_OLDNAME as (
select
'name_'||to_char(level-1,'fm0000') OLDNAME
,'name_'||to_char(level ,'fm0000') CIRCUITNAME
,level-1 OLDCIRCUITID
,level CIRCUITID
from dual
connect by level<=100
)
,VW_SI_CASCADE as (
select *
from VW_SI_OLDNAME
match_recognize(
MEASURES
classifier() event
,match_number() match
,prev(CIRCUITID,1) CIRCUITID_1
,prev(CIRCUITID,2) CIRCUITID_2
,prev(CIRCUITID,3) CIRCUITID_3
,prev(CIRCUITID,4) CIRCUITID_4
,prev(CIRCUITID,5) CIRCUITID_5
,prev(CIRCUITID,6) CIRCUITID_6
,prev(CIRCUITID,7) CIRCUITID_7
,prev(CIRCUITID,8) CIRCUITID_8
,prev(CIRCUITNAME,1) CIRCUITNAME_1
,prev(CIRCUITNAME,2) CIRCUITNAME_2
,prev(CIRCUITNAME,3) CIRCUITNAME_3
,prev(CIRCUITNAME,4) CIRCUITNAME_4
,prev(CIRCUITNAME,5) CIRCUITNAME_5
,prev(CIRCUITNAME,6) CIRCUITNAME_6
,prev(CIRCUITNAME,7) CIRCUITNAME_7
,prev(CIRCUITNAME,8) CIRCUITNAME_8
ALL ROWS PER MATCH
PATTERN (p)
DEFINE
p AS OLDNAME = prev(CIRCUITNAME)
)
)
select *
from VW_SI_CASCADE;
Results:
EVENT MATCH CIRCUITID_1 CIRCUITID_2 CIRCUITID_3 CIRCUITID_4 CIRCUITID_5 CIRCUITID_6 CIRCUITID_7 CIRCUITID_8 CIRCUITNAM CIRCUITNAM CIRCUITNAM CIRCUITNAM CIRCUITNAM CIRCUITNAM CIRCUITNAM CIRCUITNAM OLDNAME CIRCUITNAM OLDCIRCUITID CIRCUITID
----- ------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ----------
P 1 1 name_0001 name_0001 name_0002 1 2
P 2 2 1 name_0002 name_0001 name_0002 name_0003 2 3
P 3 3 2 1 name_0003 name_0002 name_0001 name_0003 name_0004 3 4
P 4 4 3 2 1 name_0004 name_0003 name_0002 name_0001 name_0004 name_0005 4 5
P 5 5 4 3 2 1 name_0005 name_0004 name_0003 name_0002 name_0001 name_0005 name_0006 5 6
P 6 6 5 4 3 2 1 name_0006 name_0005 name_0004 name_0003 name_0002 name_0001 name_0006 name_0007 6 7
P 7 7 6 5 4 3 2 1 name_0007 name_0006 name_0005 name_0004 name_0003 name_0002 name_0001 name_0007 name_0008 7 8
P 8 8 7 6 5 4 3 2 1 name_0008 name_0007 name_0006 name_0005 name_0004 name_0003 name_0002 name_0001 name_0008 name_0009 8 9
P 9 9 8 7 6 5 4 3 2 name_0009 name_0008 name_0007 name_0006 name_0005 name_0004 name_0003 name_0002 name_0009 name_0010 9 10
P 10 10 9 8 7 6 5 4 3 name_0010 name_0009 name_0008 name_0007 name_0006 name_0005 name_0004 name_0003 name_0010 name_0011 10 11
P 11 11 10 9 8 7 6 5 4 name_0011 name_0010 name_0009 name_0008 name_0007 name_0006 name_0005 name_0004 name_0011 name_0012 11 12
P 12 12 11 10 9 8 7 6 5 name_0012 name_0011 name_0010 name_0009 name_0008 name_0007 name_0006 name_0005 name_0012 name_0013 12 13
P 13 13 12 11 10 9 8 7 6 name_0013 name_0012 name_0011 name_0010 name_0009 name_0008 name_0007 name_0006 name_0013 name_0014 13 14
P 14 14 13 12 11 10 9 8 7 name_0014 name_0013 name_0012 name_0011 name_0010 name_0009 name_0008 name_0007 name_0014 name_0015 14 15
P 15 15 14 13 12 11 10 9 8 name_0015 name_0014 name_0013 name_0012 name_0011 name_0010 name_0009 name_0008 name_0015 name_0016 15 16
P 16 16 15 14 13 12 11 10 9 name_0016 name_0015 name_0014 name_0013 name_0012 name_0011 name_0010 name_0009 name_0016 name_0017 16 17
P 17 17 16 15 14 13 12 11 10 name_0017 name_0016 name_0015 name_0014 name_0013 name_0012 name_0011 name_0010 name_0017 name_0018 17 18
P 18 18 17 16 15 14 13 12 11 name_0018 name_0017 name_0016 name_0015 name_0014 name_0013 name_0012 name_0011 name_0018 name_0019 18 19
P 19 19 18 17 16 15 14 13 12 name_0019 name_0018 name_0017 name_0016 name_0015 name_0014 name_0013 name_0012 name_0019 name_0020 19 20
P 20 20 19 18 17 16 15 14 13 name_0020 name_0019 name_0018 name_0017 name_0016 name_0015 name_0014 name_0013 name_0020 name_0021 20 21
P 21 21 20 19 18 17 16 15 14 name_0021 name_0020 name_0019 name_0018 name_0017 name_0016 name_0015 name_0014 name_0021 name_0022 21 22
P 22 22 21 20 19 18 17 16 15 name_0022 name_0021 name_0020 name_0019 name_0018 name_0017 name_0016 name_0015 name_0022 name_0023 22 23
P 23 23 22 21 20 19 18 17 16 name_0023 name_0022 name_0021 name_0020 name_0019 name_0018 name_0017 name_0016 name_0023 name_0024 23 24
P 24 24 23 22 21 20 19 18 17 name_0024 name_0023 name_0022 name_0021 name_0020 name_0019 name_0018 name_0017 name_0024 name_0025 24 25
P 25 25 24 23 22 21 20 19 18 name_0025 name_0024 name_0023 name_0022 name_0021 name_0020 name_0019 name_0018 name_0025 name_0026 25 26
P 26 26 25 24 23 22 21 20 19 name_0026 name_0025 name_0024 name_0023 name_0022 name_0021 name_0020 name_0019 name_0026 name_0027 26 27
P 27 27 26 25 24 23 22 21 20 name_0027 name_0026 name_0025 name_0024 name_0023 name_0022 name_0021 name_0020 name_0027 name_0028 27 28
P 28 28 27 26 25 24 23 22 21 name_0028 name_0027 name_0026 name_0025 name_0024 name_0023 name_0022 name_0021 name_0028 name_0029 28 29
P 29 29 28 27 26 25 24 23 22 name_0029 name_0028 name_0027 name_0026 name_0025 name_0024 name_0023 name_0022 name_0029 name_0030 29 30
P 30 30 29 28 27 26 25 24 23 name_0030 name_0029 name_0028 name_0027 name_0026 name_0025 name_0024 name_0023 name_0030 name_0031 30 31
P 31 31 30 29 28 27 26 25 24 name_0031 name_0030 name_0029 name_0028 name_0027 name_0026 name_0025 name_0024 name_0031 name_0032 31 32
P 32 32 31 30 29 28 27 26 25 name_0032 name_0031 name_0030 name_0029 name_0028 name_0027 name_0026 name_0025 name_0032 name_0033 32 33
P 33 33 32 31 30 29 28 27 26 name_0033 name_0032 name_0031 name_0030 name_0029 name_0028 name_0027 name_0026 name_0033 name_0034 33 34
P 34 34 33 32 31 30 29 28 27 name_0034 name_0033 name_0032 name_0031 name_0030 name_0029 name_0028 name_0027 name_0034 name_0035 34 35
P 35 35 34 33 32 31 30 29 28 name_0035 name_0034 name_0033 name_0032 name_0031 name_0030 name_0029 name_0028 name_0035 name_0036 35 36
P 36 36 35 34 33 32 31 30 29 name_0036 name_0035 name_0034 name_0033 name_0032 name_0031 name_0030 name_0029 name_0036 name_0037 36 37
P 37 37 36 35 34 33 32 31 30 name_0037 name_0036 name_0035 name_0034 name_0033 name_0032 name_0031 name_0030 name_0037 name_0038 37 38
P 38 38 37 36 35 34 33 32 31 name_0038 name_0037 name_0036 name_0035 name_0034 name_0033 name_0032 name_0031 name_0038 name_0039 38 39
P 39 39 38 37 36 35 34 33 32 name_0039 name_0038 name_0037 name_0036 name_0035 name_0034 name_0033 name_0032 name_0039 name_0040 39 40
P 40 40 39 38 37 36 35 34 33 name_0040 name_0039 name_0038 name_0037 name_0036 name_0035 name_0034 name_0033 name_0040 name_0041 40 41
P 41 41 40 39 38 37 36 35 34 name_0041 name_0040 name_0039 name_0038 name_0037 name_0036 name_0035 name_0034 name_0041 name_0042 41 42
P 42 42 41 40 39 38 37 36 35 name_0042 name_0041 name_0040 name_0039 name_0038 name_0037 name_0036 name_0035 name_0042 name_0043 42 43
P 43 43 42 41 40 39 38 37 36 name_0043 name_0042 name_0041 name_0040 name_0039 name_0038 name_0037 name_0036 name_0043 name_0044 43 44
P 44 44 43 42 41 40 39 38 37 name_0044 name_0043 name_0042 name_0041 name_0040 name_0039 name_0038 name_0037 name_0044 name_0045 44 45
P 45 45 44 43 42 41 40 39 38 name_0045 name_0044 name_0043 name_0042 name_0041 name_0040 name_0039 name_0038 name_0045 name_0046 45 46
P 46 46 45 44 43 42 41 40 39 name_0046 name_0045 name_0044 name_0043 name_0042 name_0041 name_0040 name_0039 name_0046 name_0047 46 47
P 47 47 46 45 44 43 42 41 40 name_0047 name_0046 name_0045 name_0044 name_0043 name_0042 name_0041 name_0040 name_0047 name_0048 47 48
P 48 48 47 46 45 44 43 42 41 name_0048 name_0047 name_0046 name_0045 name_0044 name_0043 name_0042 name_0041 name_0048 name_0049 48 49
P 49 49 48 47 46 45 44 43 42 name_0049 name_0048 name_0047 name_0046 name_0045 name_0044 name_0043 name_0042 name_0049 name_0050 49 50
P 50 50 49 48 47 46 45 44 43 name_0050 name_0049 name_0048 name_0047 name_0046 name_0045 name_0044 name_0043 name_0050 name_0051 50 51
P 51 51 50 49 48 47 46 45 44 name_0051 name_0050 name_0049 name_0048 name_0047 name_0046 name_0045 name_0044 name_0051 name_0052 51 52
P 52 52 51 50 49 48 47 46 45 name_0052 name_0051 name_0050 name_0049 name_0048 name_0047 name_0046 name_0045 name_0052 name_0053 52 53
P 53 53 52 51 50 49 48 47 46 name_0053 name_0052 name_0051 name_0050 name_0049 name_0048 name_0047 name_0046 name_0053 name_0054 53 54
P 54 54 53 52 51 50 49 48 47 name_0054 name_0053 name_0052 name_0051 name_0050 name_0049 name_0048 name_0047 name_0054 name_0055 54 55
P 55 55 54 53 52 51 50 49 48 name_0055 name_0054 name_0053 name_0052 name_0051 name_0050 name_0049 name_0048 name_0055 name_0056 55 56
P 56 56 55 54 53 52 51 50 49 name_0056 name_0055 name_0054 name_0053 name_0052 name_0051 name_0050 name_0049 name_0056 name_0057 56 57
P 57 57 56 55 54 53 52 51 50 name_0057 name_0056 name_0055 name_0054 name_0053 name_0052 name_0051 name_0050 name_0057 name_0058 57 58
P 58 58 57 56 55 54 53 52 51 name_0058 name_0057 name_0056 name_0055 name_0054 name_0053 name_0052 name_0051 name_0058 name_0059 58 59
P 59 59 58 57 56 55 54 53 52 name_0059 name_0058 name_0057 name_0056 name_0055 name_0054 name_0053 name_0052 name_0059 name_0060 59 60
P 60 60 59 58 57 56 55 54 53 name_0060 name_0059 name_0058 name_0057 name_0056 name_0055 name_0054 name_0053 name_0060 name_0061 60 61
P 61 61 60 59 58 57 56 55 54 name_0061 name_0060 name_0059 name_0058 name_0057 name_0056 name_0055 name_0054 name_0061 name_0062 61 62
P 62 62 61 60 59 58 57 56 55 name_0062 name_0061 name_0060 name_0059 name_0058 name_0057 name_0056 name_0055 name_0062 name_0063 62 63
P 63 63 62 61 60 59 58 57 56 name_0063 name_0062 name_0061 name_0060 name_0059 name_0058 name_0057 name_0056 name_0063 name_0064 63 64
P 64 64 63 62 61 60 59 58 57 name_0064 name_0063 name_0062 name_0061 name_0060 name_0059 name_0058 name_0057 name_0064 name_0065 64 65
P 65 65 64 63 62 61 60 59 58 name_0065 name_0064 name_0063 name_0062 name_0061 name_0060 name_0059 name_0058 name_0065 name_0066 65 66
P 66 66 65 64 63 62 61 60 59 name_0066 name_0065 name_0064 name_0063 name_0062 name_0061 name_0060 name_0059 name_0066 name_0067 66 67
P 67 67 66 65 64 63 62 61 60 name_0067 name_0066 name_0065 name_0064 name_0063 name_0062 name_0061 name_0060 name_0067 name_0068 67 68
P 68 68 67 66 65 64 63 62 61 name_0068 name_0067 name_0066 name_0065 name_0064 name_0063 name_0062 name_0061 name_0068 name_0069 68 69
P 69 69 68 67 66 65 64 63 62 name_0069 name_0068 name_0067 name_0066 name_0065 name_0064 name_0063 name_0062 name_0069 name_0070 69 70
P 70 70 69 68 67 66 65 64 63 name_0070 name_0069 name_0068 name_0067 name_0066 name_0065 name_0064 name_0063 name_0070 name_0071 70 71
P 71 71 70 69 68 67 66 65 64 name_0071 name_0070 name_0069 name_0068 name_0067 name_0066 name_0065 name_0064 name_0071 name_0072 71 72
P 72 72 71 70 69 68 67 66 65 name_0072 name_0071 name_0070 name_0069 name_0068 name_0067 name_0066 name_0065 name_0072 name_0073 72 73
P 73 73 72 71 70 69 68 67 66 name_0073 name_0072 name_0071 name_0070 name_0069 name_0068 name_0067 name_0066 name_0073 name_0074 73 74
P 74 74 73 72 71 70 69 68 67 name_0074 name_0073 name_0072 name_0071 name_0070 name_0069 name_0068 name_0067 name_0074 name_0075 74 75
P 75 75 74 73 72 71 70 69 68 name_0075 name_0074 name_0073 name_0072 name_0071 name_0070 name_0069 name_0068 name_0075 name_0076 75 76
P 76 76 75 74 73 72 71 70 69 name_0076 name_0075 name_0074 name_0073 name_0072 name_0071 name_0070 name_0069 name_0076 name_0077 76 77
P 77 77 76 75 74 73 72 71 70 name_0077 name_0076 name_0075 name_0074 name_0073 name_0072 name_0071 name_0070 name_0077 name_0078 77 78
P 78 78 77 76 75 74 73 72 71 name_0078 name_0077 name_0076 name_0075 name_0074 name_0073 name_0072 name_0071 name_0078 name_0079 78 79
P 79 79 78 77 76 75 74 73 72 name_0079 name_0078 name_0077 name_0076 name_0075 name_0074 name_0073 name_0072 name_0079 name_0080 79 80
P 80 80 79 78 77 76 75 74 73 name_0080 name_0079 name_0078 name_0077 name_0076 name_0075 name_0074 name_0073 name_0080 name_0081 80 81
P 81 81 80 79 78 77 76 75 74 name_0081 name_0080 name_0079 name_0078 name_0077 name_0076 name_0075 name_0074 name_0081 name_0082 81 82
P 82 82 81 80 79 78 77 76 75 name_0082 name_0081 name_0080 name_0079 name_0078 name_0077 name_0076 name_0075 name_0082 name_0083 82 83
P 83 83 82 81 80 79 78 77 76 name_0083 name_0082 name_0081 name_0080 name_0079 name_0078 name_0077 name_0076 name_0083 name_0084 83 84
P 84 84 83 82 81 80 79 78 77 name_0084 name_0083 name_0082 name_0081 name_0080 name_0079 name_0078 name_0077 name_0084 name_0085 84 85
P 85 85 84 83 82 81 80 79 78 name_0085 name_0084 name_0083 name_0082 name_0081 name_0080 name_0079 name_0078 name_0085 name_0086 85 86
P 86 86 85 84 83 82 81 80 79 name_0086 name_0085 name_0084 name_0083 name_0082 name_0081 name_0080 name_0079 name_0086 name_0087 86 87
P 87 87 86 85 84 83 82 81 80 name_0087 name_0086 name_0085 name_0084 name_0083 name_0082 name_0081 name_0080 name_0087 name_0088 87 88
P 88 88 87 86 85 84 83 82 81 name_0088 name_0087 name_0086 name_0085 name_0084 name_0083 name_0082 name_0081 name_0088 name_0089 88 89
P 89 89 88 87 86 85 84 83 82 name_0089 name_0088 name_0087 name_0086 name_0085 name_0084 name_0083 name_0082 name_0089 name_0090 89 90
P 90 90 89 88 87 86 85 84 83 name_0090 name_0089 name_0088 name_0087 name_0086 name_0085 name_0084 name_0083 name_0090 name_0091 90 91
P 91 91 90 89 88 87 86 85 84 name_0091 name_0090 name_0089 name_0088 name_0087 name_0086 name_0085 name_0084 name_0091 name_0092 91 92
P 92 92 91 90 89 88 87 86 85 name_0092 name_0091 name_0090 name_0089 name_0088 name_0087 name_0086 name_0085 name_0092 name_0093 92 93
P 93 93 92 91 90 89 88 87 86 name_0093 name_0092 name_0091 name_0090 name_0089 name_0088 name_0087 name_0086 name_0093 name_0094 93 94
P 94 94 93 92 91 90 89 88 87 name_0094 name_0093 name_0092 name_0091 name_0090 name_0089 name_0088 name_0087 name_0094 name_0095 94 95
P 95 95 94 93 92 91 90 89 88 name_0095 name_0094 name_0093 name_0092 name_0091 name_0090 name_0089 name_0088 name_0095 name_0096 95 96
P 96 96 95 94 93 92 91 90 89 name_0096 name_0095 name_0094 name_0093 name_0092 name_0091 name_0090 name_0089 name_0096 name_0097 96 97
P 97 97 96 95 94 93 92 91 90 name_0097 name_0096 name_0095 name_0094 name_0093 name_0092 name_0091 name_0090 name_0097 name_0098 97 98
P 98 98 97 96 95 94 93 92 91 name_0098 name_0097 name_0096 name_0095 name_0094 name_0093 name_0092 name_0091 name_0098 name_0099 98 99
P 99 99 98 97 96 95 94 93 92 name_0099 name_0098 name_0097 name_0096 name_0095 name_0094 name_0093 name_0092 name_0099 name_0100 99 100
So you can take this example and modify it for your needs: add required number of previous data(9-20), change column names, remove unneeded columns.
Upvotes: 0
APC
Reputation: 146239
You're querying the view twenty times. That's going to be slow. You could try using subquery factoring, so you query the view once and re-use the result set multiple times.
CREATE OR REPLACE FORCE EDITIONABLE VIEW "IMS"."VW_SI_CASCADE" ("R1", "L1", "L2", "L3", "L4", "L5", "L6", "L7", "L8", "L9", "L10", "L11", "L12", "L13", "L14", "L15", "L16", "L17", "L18", "L19", "L20", "L21", "M1", "M2", "M3", "M4", "M5", "M6", "M7", "M8", "M9", "M10", "M11", "M12", "M13", "M14", "M15", "M16", "M17", "M18", "M19", "M20", "M21") AS
with cte as (
select * from VW_SI_OLDNAME
)
SELECT
ROWNUM AS R1,
V1.OLDNAME AS L1,
V1.CIRCUITNAME AS L2,
V2.CIRCUITNAME AS L3,
V3.CIRCUITNAME AS L4,
V4.CIRCUITNAME AS L5,
V5.CIRCUITNAME AS L6,
V6.CIRCUITNAME AS L7,
V7.CIRCUITNAME AS L8,
V8.CIRCUITNAME AS L9,
V9.CIRCUITNAME AS L10,
V10.CIRCUITNAME AS L11,
V11.CIRCUITNAME AS L12,
V12.CIRCUITNAME AS L13,
V13.CIRCUITNAME AS L14,
V14.CIRCUITNAME AS L15,
V15.CIRCUITNAME AS L16,
V16.CIRCUITNAME AS L17,
V17.CIRCUITNAME AS L18,
V18.CIRCUITNAME AS L19,
V19.CIRCUITNAME AS L20,
V20.CIRCUITNAME AS L21,
V1.OLDCIRCUITID AS M1,
V1.CIRCUITID AS M2,
V2.CIRCUITID AS M3,
V3.CIRCUITID AS M4,
V4.CIRCUITID AS M5,
V5.CIRCUITID AS M6,
V6.CIRCUITID AS M7,
V7.CIRCUITID AS M8,
V8.CIRCUITID AS M9,
V9.CIRCUITID AS M10,
V10.CIRCUITID AS M11,
V11.CIRCUITID AS M12,
V12.CIRCUITID AS M13,
V13.CIRCUITID AS M14,
V14.CIRCUITID AS M15,
V15.CIRCUITID AS M16,
V16.CIRCUITID AS M17,
V17.CIRCUITID AS M18,
V18.CIRCUITID AS M19,
V19.CIRCUITID AS M20,
V20.CIRCUITID AS M21
FROM cte V1
LEFT JOIN cte V2 ON V2.OLDNAME = V1.CIRCUITNAME
LEFT JOIN cte V3 ON V3.OLDNAME = V2.CIRCUITNAME
LEFT JOIN cte V4 ON V4.OLDNAME = V3.CIRCUITNAME
LEFT JOIN cte V5 ON V5.OLDNAME = V4.CIRCUITNAME
LEFT JOIN cte V6 ON V6.OLDNAME = V5.CIRCUITNAME
LEFT JOIN cte V7 ON V7.OLDNAME = V6.CIRCUITNAME
LEFT JOIN cte V8 ON V8.OLDNAME = V7.CIRCUITNAME
LEFT JOIN cte V9 ON V9.OLDNAME = V8.CIRCUITNAME
LEFT JOIN cte V10 ON V10.OLDNAME = V9.CIRCUITNAME
LEFT JOIN cte V11 ON V11.OLDNAME = V10.CIRCUITNAME
LEFT JOIN cte V12 ON V12.OLDNAME = V11.CIRCUITNAME
LEFT JOIN cte V13 ON V13.OLDNAME = V12.CIRCUITNAME
LEFT JOIN cte V14 ON V14.OLDNAME = V13.CIRCUITNAME
LEFT JOIN cte V15 ON V15.OLDNAME = V14.CIRCUITNAME
LEFT JOIN cte V16 ON V16.OLDNAME = V15.CIRCUITNAME
LEFT JOIN cte V17 ON V17.OLDNAME = V16.CIRCUITNAME
LEFT JOIN cte V18 ON V18.OLDNAME = V17.CIRCUITNAME
LEFT JOIN cte V19 ON V19.OLDNAME = V18.CIRCUITNAME
LEFT JOIN cte V20 ON V20.OLDNAME = V19.CIRCUITNAME;
Beyond that you may need to consider the internal logic of the view. There may be a better way to re-construct the chain of names. You also might be exposed to cycles in the re-naming.
Upvotes: 1
Related Questions
- Optimizing SQL Query for a View in Oracle
- Oracle slow join with view
- how can i optimize view oracle sql
- Optimise Oracle Join query (multiple joins on the same table)
- How to improve query performance in Oracle
- how to Optimize query inside view
- Optimize Oracle query, multiple joins table
- Optimized query in join
- sql query optimization (oracle)
- Query Optimization in oracle