Home » SQL & PL/SQL » SQL & PL/SQL » Improve running time
Improve running time [message #425821] Mon, 12 October 2009 07:02 Go to next message
eyalle
Messages: 5
Registered: October 2009
Junior Member
Hi,
i would like to improve the running time for the following code:

SELECT distinct r.rule_id,
b.be_id,
b.be_name
FROM rules r,
rule_param_be_type pbt,
rule_param_fe_type pft,
be b,
be_types bt,
fe f
WHERE r.RULE_ID = pbt.rule_id(+)
and r.RULE_ID = pft.rule_id(+)
and pbt.be_type_id = b.be_type_id
and pft.fe_type_id = f.fe_type_id
and b.be_type_id = bt.be_type_id
and b.fe_id = f.fe_id
and r.RULE_ID = r.rule_id
and pbt.param_id = 0
and pft.param_id = 0

does anyone has any idea on how to do it so running time will improve ?
thank's
Re: Improve running time [message #425831 is a reply to message #425821] Mon, 12 October 2009 07:29 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Here are some comments:

1) learn how to format your code when you post here. First use a code formatter to format your sql (TOAD, or this are two possibilities). Second use the {..} to wrap CODE tags around your code after you paste it into your post. That way your formatting does not get lost when the page is rendered.

2) Check you outerjoins. Seems to me your are missing some. For your sql, if AND r.rule_id = pft.rule_id (+) finds no row in pft, then what happens at this line AND pft.fe_type_id = f.fe_type_id? As it stands your existing outer joins are useless and you can remove them altogether. Why did you think you needed them anyway?

SELECT DISTINCT r.rule_id, 
                b.be_id, 
                b.be_name 
FROM   rules r, 
       rule_param_be_type pbt, 
       rule_param_fe_type pft, 
       be b, 
       be_types bt, 
       fe f 
WHERE  r.rule_id = pbt.rule_id (+) 
       AND r.rule_id = pft.rule_id (+) 
       AND pbt.be_type_id = b.be_type_id 
       AND pft.fe_type_id = f.fe_type_id 
       AND b.be_type_id = bt.be_type_id 
       AND b.fe_id = f.fe_id 
       AND r.rule_id = r.rule_id 
       AND pbt.param_id = 0 
       AND pft.param_id = 0 

3) I have serious doubts about the validity of your sql. Have you actually checked it at all. Tell me, what does this do?

AND r.rule_id = r.rule_id 

4) Your tuning question is wide open. We don't know much about your data or database so there could be lots of answers. However I might suggest this: you should create indexes to support your joins. When doing this consider adding extra columns to your indexes as referenced by your sql so that you can skip table accesses. I might conside the following indexes:

rules (rule_id)
rule_param_be_type (param_id,rule_id,be_type_id)
rule_param_fe_type (param_id,rule_id,fe_type_id)
be (be_type_id,fe_id,name)
fe (fe_type_id)
bt (be_type_id)

Of particular note is:

be (be_type_id,fe_id,name)

Notice name is on the end of the index. It appears in the select list but not in the where clause. Oracle can skip going to the BE table assuming no other columns are required that are not in used indexes in the query. This avoids extra I/O.

Good luck, Kevin

Re: Improve running time [message #425850 is a reply to message #425831] Mon, 12 October 2009 08:39 Go to previous messageGo to next message
eyalle
Messages: 5
Registered: October 2009
Junior Member
Thank's Kevin
of much help.. Smile
Re: Improve running time [message #425857 is a reply to message #425821] Mon, 12 October 2009 10:33 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
no sweat

Just make sure you RESEARCH these features is you intend to use them. RESEARCH means you go out to the web and do some reading, then you log into a test database and you do some testing, then you formulate a plan and test it out.

Never use a feature unless you understand it and have experimented with it on your own.

Good luck, Kevin
Previous Topic: converting unknown amount of rows to columns
Next Topic: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx...
Goto Forum:
  


Current Time: Sun Sep 25 06:08:06 CDT 2016

Total time taken to generate the page: 0.13269 seconds