Home » RDBMS Server » Performance Tuning » Performance tuning of query with self join
Performance tuning of query with self join [message #501776] Thu, 31 March 2011 07:08 Go to next message
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 #501780 is a reply to message #501776] Thu, 31 March 2011 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable!

Regards
Michel
Re: Performance tuning of query with self join [message #501781 is a reply to message #501780] Thu, 31 March 2011 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Mon, 21 February 2011 12:24
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Re: Performance tuning of query with self join [message #501782 is a reply to message #501776] Thu, 31 March 2011 07:49 Go to previous messageGo to next message
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))
Re: Performance tuning of query with self join [message #501785 is a reply to message #501782] Thu, 31 March 2011 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still unreadable!
I will suggest as soon as I'll be able to read it and to get all required information.

Regards
Michel
Re: Performance tuning of query with self join [message #501786 is a reply to message #501785] Thu, 31 March 2011 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 23039
Registered: January 2009
Senior Member
With Oracle characters between single quote marks are STRINGS!
'This is a string, 02/01/2010 00:00, not a date'
When a DATE datatype is desired, then use TO_DATE() function.
Re: Performance tuning of query with self join [message #501871 is a reply to message #501782] Fri, 01 April 2011 02:48 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
In addition,
Make sure statistics are gathered.You did not mention your database version.
EXISTS (SELECT * from rptkys pwmethkeys 
WHERE pwmethkeys.runid = 'R0276518' 
AND pwmethkeys.tablename = 'PWMETH'
AND pwmethkeys.tableky = testreq.prea_pwmethkey)

Use EXISTS (SELECT 1 from rptkys.. instead


But before that you should follow the below link as suggested to get help.
http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888

Regards,
Ved

[Updated on: Fri, 01 April 2011 02:56]

Report message to a moderator

Previous Topic: split a large table to small pieces?
Next Topic: using between slows query
Goto Forum:
  


Current Time: Mon Nov 24 14:38:03 CST 2014

Total time taken to generate the page: 0.08992 seconds