Home » SQL & PL/SQL » SQL & PL/SQL » Need help in optimizing a query
icon5.gif  Need help in optimizing a query [message #225210] Mon, 19 March 2007 03:00 Go to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Hi, i have attached a view definition and explain plan for the select query of this view. I need to optimize the view query. If I try adding Index hint and force the view to use indexes on the columns in the where clause the cost of the query goes on increasing, not to sure how can i minimize the cost, can any one help me do it?

thanks
Re: Need help in optimizing a query [message #225219 is a reply to message #225210] Mon, 19 March 2007 03:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
My cr4ppy windoze box won't open your attachment I'm afraid.
Please post it as inline text.
Re: Need help in optimizing a query [message #225222 is a reply to message #225219] Mon, 19 March 2007 03:51 Go to previous messageGo to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
CREATE OR REPLACE VIEW V1
AS
SELECT a.id,max(b.phone),maX(c.loc_code)
FROM    person a, PHONE b,JOB_TYPE c,location d
WHERE a.id = b.id(+)
      AND a.id = c.id(+)
      AND c.loc_code = d.loc_code(+)
      AND b.Dir_flag = 'Y'
      AND a.no_flag='N' 	
      GROUP BY a.id


Explian Plan:

Description Object Owner Object Name Cost Cardinality	Bytes
SELECT STATEMENT, GOAL = CHOOSE						                       8285	43726		3716710
 SORT GROUP BY  		8285	43726		3716710
  MERGE JOIN OUTER		1036	444702		37799670
   SORT JOIN			1013	10989		835164
    MERGE JOIN OUTER		871	10989		835164
     MERGE JOIN			654	10989		736263
      SORT JOIN			254	10989		428571
       TABLE ACCESS FULLABCPHONE173	10989		428571
      SORT JOIN			400	43726		1224328
       TABLE ACCESS FULABCPERSON154	43726		1224328
     SORT JOIN			218	43726		393534
      TABLE ACCESS FULLABCJOB_TYPE96	43726		393534
   SORT JOIN			23	4411		39699
    TABLE ACCESS FULLABCLOCATION10	4411		39699

[Added Code Tags]

[Updated on: Mon, 19 March 2007 05:13] by Moderator

Report message to a moderator

Re: Need help in optimizing a query [message #225246 is a reply to message #225222] Mon, 19 March 2007 05:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can get a performance improvement by dropping the LOCATION table out of the view entirely, as you only outer join to it, and never use the information returned.

You can also convert the outer join to PHONE into a standard join, as you are explicitly requiring the value of PHONE.DIR_FLAG to be 'Y', which means that the outer join does nothing (and you don't get the rows from the driving table included either)

1)Is that EXPLAIN plan from a 'SELECT * FROM V1' or from a 'SELECT * FROM v1 WHERE id = :id'
2)How many records do you have in PERSON
3)Is ID a unique column in PERSON
4)What percentage of the PERSON records have the NO_FLAG set to 'Y'
5)Is there an index on PERSON(ID,NO_FLAG)
Re: Need help in optimizing a query [message #225265 is a reply to message #225246] Mon, 19 March 2007 07:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I have re-written your query. Can you tell me are these two queries different. First Query is what you have written and second is what i have written.

SELECT a.id,max(b.phone),maX(c.loc_code)
FROM    person a, PHONE b,JOB_TYPE c,location d
WHERE a.id = b.id(+)
      AND a.id = c.id(+)
      AND c.loc_code = d.loc_code(+)
      AND b.Dir_flag = 'Y'
      AND a.no_flag='N' 	
      GROUP BY a.id


SELECT a.id,max(b.phone),maX(c.loc_code)
FROM    person a, PHONE b,JOB_TYPE c,location d
WHERE a.id = b.id
      AND a.id = c.id
      AND c.loc_code = d.loc_code (+)
      AND b.Dir_flag = 'Y'
      AND a.no_flag='N' 	
      GROUP BY a.id


"a.id = b.id(+) and b.Dir_Flag = 'Y'". These two conditions contradicts each others. Similarly a.id = c.id(+) and c.loc_code = d.loc_code(+) as well. Think about it you will understand what i mean. There is no need for outerjoin except the last one or it is not been implemented correctly.
cheers
Re: Need help in optimizing a query [message #225272 is a reply to message #225265] Mon, 19 March 2007 08:18 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@S.Rajaram - I think you're wrong about removing the outer join from PERSON to JOB_TYPE. The syntax for daisy chaining outer joins looks fine to me:

drop table oj_1;

drop table oj_2;

drop table oj_3;

create table oj_1 (col_a  varchar2(10));

create table oj_2 (col_a varchar2(10), col_b varchar2(10));

create table oj_3 (col_b varchar2(10), col_c varchar2(10));

insert into oj_1 values ('A');
insert into oj_1 values ('B');
insert into oj_1 values ('C');

insert into oj_2 values ('B','X');
insert into oj_2 values ('C','Y');

insert into oj_3 values ('Y','M');

select a.col_a
      ,b.col_a
      ,b.col_b
      ,c.col_b
      ,c.col_c
from   oj_1 a
      ,oj_2 b
      ,oj_3 c
where  a.col_a = b.col_a(+)
and    b.col_b = c.col_b(+)
order by a.col_a;

COL_A      COL_A      COL_B      COL_B      COL_C
---------- ---------- ---------- ---------- ----------
A
B          B          X
C          C          Y          Y          M


I think this is the most you can remove from it:
SELECT a.id,max(b.phone),maX(c.loc_code)
FROM  person a
     ,PHONE b
     ,JOB_TYPE c
WHERE a.id = b.id
AND   a.id = c.id(+)
AND   b.Dir_flag = 'Y'
AND   a.no_flag='N' 	
GROUP BY a.id
Previous Topic: Query regarding Decode() function
Next Topic: Need the solution for the following SQL giving 00918 error(Merged)
Goto Forum:
  


Current Time: Fri Dec 09 19:11:14 CST 2016

Total time taken to generate the page: 0.10445 seconds