| Performance tuning of query with self join [message #501776] |
Thu, 31 March 2011 07:08  |
 |
preethavinay
Messages: 5
Registered: February 2011
|
Junior Member |
|
|
Please suggest ways to improve the performance of the following query:
The self join may be causing problem when run relatively large db.
INSERT INTO rptkys (rptkyskey,runid,tablename,tableky)
(SELECT s_catalog4.nextval, 'R0276518', 'TESTREQ',testreq.testreqkey
FROM testreq,entorder,patient
WHERE (testreq.established
BETWEEN '01/01/2010 00:00'
AND '02/01/2010 00:00' )
AND testreq.requesttype = 'C'
AND entorder.entorderkey = testreq.entorderkey
AND entorder.patientkey = patient.patientkey
AND not testreq.status = 'X'
AND EXISTS (SELECT * from rptkys pssitekeys
WHERE pssitekeys.runid = 'R0276518'
AND pssitekeys.tablename = 'PS_SITE'
AND pssitekeys.tableky = testreq.prea_sitekey)
AND EXISTS (SELECT * from rptkys pwmethkeys
WHERE pwmethkeys.runid = 'R0276518'
AND pwmethkeys.tablename = 'PWMETH'
AND pwmethkeys.tableky = testreq.prea_pwmethkey)
AND 1 = pkg_rptpatsec.ps_granted('R0276518' , 'MGR', NULL,NULL,patient.patseckey)
AND 1 = pkg_rptpatsec.ps_granted('R0276518' , 'MGR', entorder.sfkey,entorder.ptkey)
AND 1 = pkg_rptpatsec.ps_granted('R0276518',testreq.testkey,testreq.issecure))
Query Plan
--------------------------------------------------------------------------------
1.0 INSERT STATEMENT SUPDBA Cost =
2.1 SEQUENCE S_CATALOG4 SEQUENCE
3.1 FILTER
4.1 NESTED LOOPS
5.1 NESTED LOOPS
6.1 TABLE ACCESS BY INDEX ROWID TESTREQ TABLE
7.1 INDEX RANGE SCAN IX_TESTREQ_ESTABLISHED INDEX
6.2 TABLE ACCESS BY INDEX ROWID ENTORDER TABLE
7.1 INDEX UNIQUE SCAN PK_ENTORDER INDEX (UNIQUE)
5.2 TABLE ACCESS BY INDEX ROWID PATIENT TABLE
6.1 INDEX UNIQUE SCAN PK_PATIENT INDEX (UNIQUE)
4.2 AND-EQUAL
5.1 INDEX RANGE SCAN IX_RPTKYS_RUNID INDEX
5.2 INDEX RANGE SCAN IX_RPTKYS_TABLENAME INDEX
5.3 INDEX RANGE SCAN IX_RPTKYS_TABLEKY INDEX
4.3 AND-EQUAL
5.1 INDEX RANGE SCAN IX_RPTKYS_RUNID INDEX
5.2 INDEX RANGE SCAN IX_RPTKYS_TABLENAME INDEX
5.3 INDEX RANGE SCAN IX_RPTKYS_TABLEKY INDEX
|
|
|
|
|
|
|
|
| Re: Performance tuning of query with self join [message #501782 is a reply to message #501776] |
Thu, 31 March 2011 07:49  |
|
preethavinay
Messages: 5
Registered: February 2011
|
Junior Member |
|
|
Please suggest ways to improve the performance of following query:INSERT INTO rptkys (rptkyskey,runid,tablename,tableky)
(SELECT s_catalog4.nextval, 'R0276518', 'TESTREQ',testreq.testreqkey
FROM testreq,entorder,patient
WHERE (testreq.established
BETWEEN '01/01/2010 00:00'AND '02/01/2010 00:00' )
AND testreq.requesttype = 'C' AND entorder.entorderkey = testreq.entorderkey AND entorder.patientkey = patient.patientkey
AND not testreq.status = 'X' AND EXISTS (SELECT * from rptkys pssitekeys WHERE pssitekeys.runid = 'R0276518'
AND pssitekeys.tablename = 'PS_SITE' AND pssitekeys.tableky = testreq.prea_sitekey) AND EXISTS (SELECT * from rptkys pwmethkeys WHERE pwmethkeys.runid = 'R0276518' AND pwmethkeys.tablename = 'PWMETH' AND pwmethkeys.tableky = testreq.prea_pwmethkey) AND 1 = pkg_rptpatsec.ps_granted('R0276518' , 'MGR', NULL,NULL,patient.patseckey)
AND 1 = pkg_rptpatsec.ps_granted('R0276518' , 'MGR', entorder.sfkey,entorder.ptkey)
AND 1 = pkg_rptpatsec.ps_granted('R0276518',testreq.testkey,testreq.issecure))
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
