Home » RDBMS Server » Performance Tuning » Improve the performance of an Oracle Query
| Improve the performance of an Oracle Query [message #64668] |
Thu, 18 December 2003 23:51 |
PRABHA
Messages: 4
Registered: December 2003
|
Junior Member |
|
|
Hi ,
Following is the description of the problem .
Can anyone help ?
We have
a table A that stores event_id , id , role , category. The categories are X , Y , Z and the roles under each category is 0 , 1 , 2 , 3 ...
a table B that stores id , type of id (person or company)
a table C that stores id , person_first_name , person_last_name
a table D that stores id , org_name , location
The id can be that of a person or a company. ids of C can reside in D i.e. the company details of the person can also be present.
a table E that has division details . division_id , division_name etc
Each division can have multiple events.
a table F that has event details division_id , event_id , event_name etc
a table G that has division_id , data1 , data2 , data 5
a table H that has division_id , data3
We have three views
CREATE OR REPLACE VIEW V3 ( VW3_EVENT_ID ,
VW3_ID , VW3_TYP, VW3_FRST_NM,
VW3_LST_NM , VW3_ORG_NM ) AS SELECT
A.EVENT_ID ,
B.TYP ,
D.ORG_NME ,
B.ID ,
C.FRST_NME ,
C.LST_NME
FROM
C , A , B , D
WHERE
A.ID = B.ID
AND B.ID = C.ID (+)
AND C.ID = D.ID(+)
AND A.CAT = 'X'
AND A.ROLE = '3'
CREATE OR REPLACE VIEW V1 ( VW1_EVENT_ID ,
VW1_ID , VW1_FRST_NM,
VW1_LST_NM , VW1_ORG ) AS SELECT
A.EVENT_ID ,
B.ID ,
DECODE(UPPER(B.TYP),'PERSON',NVL(C.FRST_NM,''),'') ,
DECODE(UPPER(B.TYP),'PERSON',NVL(C.LST_NM,''),NVL(D.ORG_NM,'')),
D.ORG_NME
FROM
C , A , B , D
WHERE
A.ID = B.ID
AND B.ID = C.ID (+)
AND C.ID = D.ID(+)
AND A.CAT = 'X'
AND A.ROLE = '1'
CREATE OR REPLACE VIEW V0( VW0_EVENT_ID , VW0_ID , VW0_FRST_NM, VW0_LST_NM , VW0_ORG_NM , VW0_ROLE_0_SYS_ID) AS SELECT
A.EVENT_ID ,
B.ID ,
D.ORG_NME ,
A.ROLE_0_SYS_ID ,
DECODE(UPPER(B.TYP),'PERSON',NVL(C.FRST_NM,''),'') ,
DECODE(UPPER(B.TYP),'PERSON',NVL(C.LST_NM,''),NVL(D.ORG_NM,''))
FROM
C , A , B , D
WHERE
A.ID = B.ID
AND B.ID = C.ID (+)
AND C.ID = D.ID(+)
AND A.CAT = 'X'
AND A.ROLE = '0'
The query that I am trying to optimize is given below
SELECT
F.DIVISION_ID DIVISION_ID,
F.DIVISION_NAME ,
E.EVENT_NAME ,
A.EVENT_ID ,
G.DATA1 ,
H.DATA3 ,
(SELECT VW3_TYP FROM V3 WHERE V3.EVENT_ID = E.EVENT_ID ) TYPE ,
(SELECT VW3_ORG_NME FROM V3 WHERE V3.EVENT_ID = E.EVENT_ID ) ORG_NME ,
(SELECT VW3_FRST_NM FROM V3 WHERE V3.EVENT_ID = E.EVENT_ID ) ROLE_3_FRST_NM ,
(SELECT VW3_LST_NM FROM V3 WHERE V3.EVENT_ID = E.EVENT_ID ) ROLE_3_LST_NM ,
(SELECT VW1_FRST_NM FROM V1 WHERE V1.EVENT_ID = E.EVENT_ID ) ROLE_1_FRST_NM ,
(SELECT VW1_LST_NM FROM V1 WHERE V1.EVENT_ID = E.EVENT_ID ) ROLE_1_LST_NM ,
(SELECT VW0_ORG_NM FROM V0 WHERE V0.EVENT_ID = E.EVENT_ID ) ORG_NM ,
(SELECT VW0_FRST_NM FROM V0 WHERE V0.EVENT_ID = E.EVENT_ID ) ROLE_0_FRST_NM ,
(SELECT VW0_LST_NM FROM V0 WHERE V0.EVENT_ID = E.EVENT_ID ) ROLE_0_LST_NM ,
(SELECT VW0_ROLE_0_SYS_ID FROM V0 WHERE V0.EVENT_ID = E.EVENT_ID ) ORG_NM ,
FROM
H,
G,
E,
F,
A ,
V3
WHERE
V3.ROLE_0_LST_NM = <INPUT PARAMETER> AND
V3.EVENT_ID = E.EVENT_ID AND
(F.STATUS_CD IN ('21', '24', '25', '8',
'11111111','55555555', '14562745', '1020546278','9876543210')) AND
(<INPUT PARAMETER> IN (SELECT UPPER(ORG_ID) FROM D WHERE D.ID = A.ID)) AND
A.ROLE IN (0 , 1) AND
A.CAT = 'X' AND
LENGTH(TRIM(A.ROLE_0_SYS_ID)) <> 7 AND
F.DIVISION_ID = E.DIVISION_ID AND
(F.DIVISION_ID = G.DIVISION_ID (+)) AND
(F.DIVISION_ID = H.DIVISION_ID (+)) AND
E.EVENT_ID = A.EVENT_ID AND
(A.EVENT_TYPE IS NULL OR A.EVENT_TYPE <> 'UNUSUAL EVENT')
ORDER BY
UPPER(ROLE_3_LST_NM),
UPPER(ROLE_3_FRST_NM),
DIVISION_ID
The following needs to be taken care of
1. Table Structures cannot be changed.
2. Indexes can be added.
3. New views can be added.
4. The output of the new query should match exactly with the output of the original query taken for optimization.
5. Cannot create any seperate table at design time containing related data from TABLES A , B , C , D , E , F , G , H because these tables can get data from other systems any time during the day.
6. Stored Procedures are not preferred. They expect only this query to be converted to any other form to give the same output.
7. Upper() is used at few places because the data can be in any case.
The existing indexes on the tables are
Table A
- Index 1 - Unique - EVENT_ID , ID , ROLE
- Index 2 - Non-Unique - CAT
- Index 3 - Non-Unique - ROLE_0_SYS_ID , ROLE , EVENT_ID , EVENT_TYPE
- Index 4 - Non-Unique - ROLE
- Index 5 - Non-Unique - EVENT_ID , ROLE_0_SYS_ID
- Index 6 - Non-Unique - ID
- Index 7 - Non-Unique - EVENT_TYPE
Table B
- Index 1 - Unique - EVENT_ID , ID , ROLE
- Index 2 - Non-Unique - ID
- Index 3 - Non-Unique - ROLE
Table C
- Index 1 - Unique - ID
Table D
- Index 1 - Unique - ID
Table E
- Index 1 - Unique - DIVISION_ID
- Index 2 - Non-Unique - STATUS_CD
Table F
- Index 1 - Non-Unique - DIVISION_ID
- Index 2 - Unique - EVENT_ID
Table G
- Index 1 - Non-Unique - DIVISION_ID
- Index 2 - Unique - DIVISION_ID , data5
Table H
- Index 1 - Unique - DIVISION_ID
Regards
Prabha
|
|
|
|
Goto Forum:
Current Time: Sat Apr 20 09:28:15 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
