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
Previous Topic: Index scans
Next Topic: explain plan
Goto Forum:
  


Current Time: Thu Sep 24 16:51:22 CDT 2020