OraFAQ Forum: SQL & PL/SQL » query fine tune

Home » SQL & PL/SQL » SQL & PL/SQL » query fine tune (Oracle Database 10g Release 10.2.0.1.0)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

query fine tune [message #574138]

Mon, 07 January 2013 06:43

Shaam
Messages: 6
Registered: January 2013
Location: chennai

Junior Member

I have used regexp_substr,connect by prior and XMLAGG function to achive the following result.
But it takes 5 second to display the result.Is there any other option to get the result...

Data in Table:
-------------
Field1 Field2 Field3 Parent
4003 30850 p1 N/A
4004 30850 p2 N/A
4005 30850 p3 p1,p2
4006 30850 p4 p3
4007 30850 p5 p4
4008 30850 p6 p4
4009 30850 p7 p5,p6

o/p should come like this :

Field3 Field2 Field1 Parent
p1 30850 4003 N/A
p2 30850 4004 N/A
p3 30850 4005 p1,p2
p4 30850 4006 p3
p6 30850 4008 p4

Input will be field2 :30850 and Field1:4008

For this i have iterated the previous parent for Field3.I could not use connect by prior because parent have comma(,) seperated value. First i have used regexp_substr to separate the comma values then i have used connect by prior menthod to iterate the parent.Next i used XMLAGG function to show the parent as comma seperated value.

Report message to a moderator

Re: query fine tune [message #574143 is a reply to message #574138]

Mon, 07 January 2013 07:07

Littlefoot
Messages: 16960
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It is difficult to debug code you can't see. How is anyone supposed to fix your query if you don't post it? Besides, it appears that you didn't pay enough attention to the guide; please, have a look.

Report message to a moderator

Re: query fine tune [message #574145 is a reply to message #574143]

Mon, 07 January 2013 07:13

Shaam
Messages: 6
Registered: January 2013
Location: chennai

Junior Member

Hi,

I have used the following query.

SELECT STG_NAME,
PROD_UID AS clonedFrom,
PROD_STG_MAP_ID AS prodStageMapId,
RTRIM( XMLAGG( XMLELEMENT (C, PARENT_PROCESS_STAGE
|| ',')
ORDER BY PARENT_PROCESS_STAGE ).EXTRACT ('//text()'), ',') AS PARENT_PROCESS_STAGE,
IS_LAST_STAGE AS isLastStage
FROM
(SELECT STG_NAME,
PROD_UID,
PROD_STG_MAP_ID,
PARENT_PROCESS_STAGE,
IS_LAST_STAGE
FROM
( SELECT DISTINCT a.STG_NAME,
TO_CHAR(a.PARENT_PROCESS_STAGE) PARENT_PROCESS_STAGE ,
a.PROD_UID,
a.PROD_STG_MAP_ID,
a.is_last_stage
FROM
( SELECT DISTINCT STG_NAME,
regexp_substr(parent_process_stage,'[^,]+', 1, level) parent_process_stage ,
PROD_UID,
PROD_STG_MAP_ID,
IS_LAST_STAGE
FROM prod_stg_map
WHERE PROD_UID =30850
CONNECT BY regexp_substr(parent_process_stage, '[^,]+', 1, level) IS NOT NULL
) A
WHERE a.PROD_UID =30850
START WITH a.stg_name=
(SELECT stg_name
FROM prod_stg_map psm,
Prod_Master Pm
WHERE psm.PROD_STG_MAP_ID = 4008
AND pm.prod_uid = psm.prod_uid
AND pm.prod_uid = 30850
)
CONNECT BY a.stg_name = PRIOR a.PARENT_PROCESS_STAGE
ORDER BY a.PROD_UID
) parent
)
GROUP BY STG_NAME,
PROD_UID,
PROD_STG_MAP_ID,
IS_LAST_STAGE
ORDER BY 3;

Report message to a moderator

Re: query fine tune [message #574147 is a reply to message #574145]

Mon, 07 January 2013 07:18

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As it seems you do not understand what Littlefoot told you, I will specify:

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
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" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Report message to a moderator

Re: query fine tune [message #574215 is a reply to message #574147]

Tue, 08 January 2013 01:25

Shaam
Messages: 6
Registered: January 2013
Location: chennai

Junior Member

Quote:

I have created the following tables.

CREATE TABLE  EX_PRD_MASTER 
  (
     PRD_UID   NUMBER(10,0),
     PRD_ID    NUMBER(10,0),
     PRD_NAME  VARCHAR2(20 CHAR),
    CONSTRAINT  PRD_MASTER_PK  PRIMARY KEY ( PRD_UID ) 
  )   ;

CREATE TABLE  EX_PRD_CHILD 
(
	PRD_STG_ID    NUMBER(10,0),
	PRD_UID       NUMBER(10,0),
	STG_NAME      VARCHAR2(25 BYTE),
	PARENT_STAGE  VARCHAR2(500 BYTE),
	CONSTRAINT  PRD_STG_FK1  FOREIGN KEY ( PRD_UID ) REFERENCES  EX_PRD_MASTER  ( PRD_UID ) ON
	DELETE CASCADE ENABLE
)   ;	
 
 Insert Into Ex_Prd_Master (Prd_Uid,Prd_Id,Prd_Name)Values(301,1,'product1' );
 insert into EX_PRD_MASTER (PRD_UID,PRD_ID,PRD_NAME)values(302,2,'product2' );
 
 Insert Into EX_PRD_CHILD (PRD_STG_ID,PRD_UID,STG_NAME,PARENT_STAGE)Values(4001,301,'p1','N/A');
 Insert Into EX_PRD_CHILD (PRD_STG_ID,PRD_UID,STG_NAME,PARENT_STAGE)Values(4002,301,'p2','N/A');
 Insert Into Ex_Prd_Child (Prd_Stg_Id,Prd_Uid,Stg_Name,Parent_Stage)Values(4003,301,'p3','p1,p2');
 Insert Into Ex_Prd_Child (Prd_Stg_Id,Prd_Uid,Stg_Name,Parent_Stage)Values(4004,301,'p4','p3');
 Insert Into EX_PRD_CHILD (PRD_STG_ID,PRD_UID,STG_NAME,PARENT_STAGE)Values(4005,301,'p5','p4');
 Insert Into Ex_Prd_Child (Prd_Stg_Id,Prd_Uid,Stg_Name,Parent_Stage)Values(4006,301,'p6','p4');
 Insert Into EX_PRD_CHILD (PRD_STG_ID,PRD_UID,STG_NAME,PARENT_STAGE)Values(4007,301,'p7','p5,p6');
 
 
SQL> select * from EX_PRD_CHILD;

PRD_STG_ID    PRD_UID STG_NAME                  PARENT_STAGE
---------- ---------- ------------------------- -----------------------
      4001        301 p1                        N/A
      4002        301 p2                        N/A
      4003        301 p3                        p1,p2
      4004        301 p4                        p3
      4005        301 p5                        p4
      4006        301 p6                        p4
      4007        301 p7                        p5,p6

Quote:

Root node is p1 and p2.
for the STG_NAME p3 ---> p1 and p2 is the parent
for the STG_NAME p4 ---> p3 is the parent
for the STG_NAME p5 ---> p4 is the parent
for the STG_NAME p6 ---> p4 is the parent
for the STG_NAME p7 ---> p5 and p6 is the parent

i have to find the previous parents of p6 in the above hierarchy .

I can't use conect by prior directly because the PARENT_STAGE have comma seperated values.
I have used the following query to get all the previous PARENT_STAGE of a particular STG_NAME in a PRD_UID by using
Regexp_substr,XMLAGG,CONNECT BY PRIOR to iterate the previous parents in the above hierarchy.The performance of this query is very slow.
It's taking nearly 7 seconds to display the result in front end application.Is there any other way to increase the performance?

SQL> SELECT STG_NAME,
  2    PRD_UID        ,
  3    PRD_STG_ID  ,
  4    RTRIM( XMLAGG( XMLELEMENT (C, PARENT_STAGE || ',')
  5    ORDER BY PARENT_STAGE ).EXTRACT ('//text()'), ',') AS PARENT_STAGE 
  6  FROM
  7      (SELECT STG_NAME,
  8     PRD_UID,
  9     PRD_STG_ID,
 10     PARENT_STAGE 
 11      FROM
 12         ( SELECT DISTINCT a.STG_NAME,
 13               TO_CHAR(a.PARENT_STAGE) PARENT_STAGE ,
 14               a. PRD_UID,
 15               a.PRD_STG_ID 
 16     FROM
 17            (  SELECT DISTINCT STG_NAME,
 18                  regexp_substr(PARENT_STAGE,'[^,]+', 1, level) PARENT_STAGE ,
 19                   PRD_UID,
 20                  PRD_STG_ID 
 21       FROM EX_PRD_CHILD
 22       WHERE  PRD_UID =301
 23       CONNECT BY regexp_substr(PARENT_STAGE, '[^,]+', 1, level) IS NOT NULL
 24            ) A
 25         WHERE a. PRD_UID       =301
 26         START WITH a.stg_name=
 27                ( SELECT  stg_name
 28           FROM    EX_PRD_CHILD psm, Ex_Prd_Master Pm
 29           WHERE  psm.PRD_STG_ID = 4006
 30           AND  pm. PRD_UID    = psm. PRD_UID
 31           AND  pm. PRD_UID    = 301
 32                )
 33          CONNECT BY a.stg_name = PRIOR a.PARENT_STAGE
 34     ORDER BY a. PRD_UID
 35         ) parent
 36    )
 37  GROUP BY STG_NAME,
 38    PRD_UID,
 39    PRD_STG_ID 
 40  ORDER BY 3;

SQL> /

STG_NAME                     PRD_UID PRD_STG_ID PARENT_STAGE
------------------------- ---------- ---------- --------------
p1                               301       4001 N/A
p2                               301       4002 N/A
p3                               301       4003 p1,p2
p4                               301       4004 p3
p6                               301       4006 p4

Report message to a moderator

Re: query fine tune [message #574246 is a reply to message #574215]

Tue, 08 January 2013 03:54

Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select *
  2  from  EX_PRD_CHILD
  3  connect by instr(','||prior parent_stage||',', ','||stg_name||',') != 0
  4  start with stg_name = 'p6'
  5  order by 1
  6  /
PRD_STG_ID    PRD_UID STG_NAME                  PARENT_STAGE
---------- ---------- ------------------------- ------------
      4001        301 p1                        N/A
      4002        301 p2                        N/A
      4003        301 p3                        p1,p2
      4004        301 p4                        p3
      4006        301 p6                        p4

Regards
Michel

[Edit: add ORDER BY]

[Updated on: Tue, 08 January 2013 03:56]

Report message to a moderator