Oracle SQL SELECT subquery Optimization

Question

I need to optimize SELECT query in order to improve the performance. I am using ORACLE 10g. Below is my table:

CREATE TABLE TRNSCTN
(
  TRNSCTN_ID              VARCHAR2(32)   NOT NULL,
  TRNSCTN_DOC         VARCHAR2(60)   NOT NULL,
  TRNSCTN_TYPE            VARCHAR2(60)   NOT NULL,
  STATUS                  NUMBER         NOT NULL,
  TRNSCTN_CREATEDDATE     DATE           NOT NULL,
  TRNSCTN_CREATEDBY       VARCHAR2(60)   NOT NULL,
  TRNSCTN_CHANGEDDATE     DATE           NOT NULL,
  TRNSCTN_CHANGEDBY       VARCHAR2(60)   NOT NULL,
  PARENT_LINK             VARCHAR2(32)   NULL,
  PT_NAME         VARCHAR2(255)  NULL,
  APP_ID          VARCHAR2(255)  NULL,
  DIRECTION               NUMBER         NULL,
  CONSTRAINT PK_TRNSCTN_ID PRIMARY KEY (TRNSCTN_ID)
);

Below are some records:

Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B91','DOC1','TYPE1',5501,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B92','PT_SEMO','APP1',2 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B92','DOC2','TYPE1',5502,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B92','PT_SEMO','APP1',1 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B93','DOC3','TYPE2',5503,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B93','PT_SEMO','APP3',2 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B94','DOC1','TYPE2',5504,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B91','PT_SEMO','APP1',2 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B95','DOC2','TYPE1',5505,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B93','PT_SEMO','APP1',1 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B96','DOC1','TYPE1',5506,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B92','PT_SEMO','APP3',2 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B97','DOC2','TYPE1',5507,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B99','PT_SEMO','APP1',1 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B98','DOC3','TYPE2',5508,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B93','PT_SEMO','APP1',1 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B99','DOC1','TYPE2',5509,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B91','PT_SEMO','APP1',1 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B910','DOC2','TYPE1',5510,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B93','PT_SEMO','APP3',1 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B911','DOC1','TYPE1',5511,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B92','PT_SEMO','APP1',2 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B912','DOC2','TYPE1',5512,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B913','PT_SEMO','APP1',1 );
Insert into TRNSCTN (TRNSCTN_ID,TRNSCTN_DOC,TRNSCTN_TYPE,STATUS,TRNSCTN_CREATEDDATE,TRNSCTN_CREATEDBY,TRNSCTN_CHANGEDDATE,TRNSCTN_CHANGEDBY,PARENT_LINK,PT_NAME,APP_ID,DIRECTION) values ('E840496554B913','DOC3','TYPE2',5513,to_date('01-MAY-13','DD-MON-RR'),'usr1',to_date('01-MAY-13','DD-MON-RR'),'usr1','E840496554B911','PT_SEMO','APP3',2 );

Now, here is my SELECT Query

SELECT TRNSCTN_ID, 
       TRNSCTN_DOC, 
       TRNSCTN_TYPE, 
       STATUS, 
       TRNSCTN_CREATEDDATE, 
       TRNSCTN_CREATEDBY, 
       TRNSCTN_CHANGEDDATE, 
       TRNSCTN_CHANGEDBY, 
       PARENT_LINK, 
       CASE 
          WHEN (SELECT COUNT(*) FROM TRNSCTN sub WHERE TRNSCTN_ID=sub.PARENT_LINK) = 0 THEN 'false' 
          ELSE 'true' 
       END AS CHILDCNT, 
       PT_NAME, 
       APP_ID, 
       DIRECTION 
FROM TRNSCTN

As I am using the subquery to determine if the record has parent link, the query gets too much slower against millions of records.

Sorry that SQLFIDDLE is down so couldn't use it.

I was wondering, if there is another way to rewrite the query to optimize the performance.

Please let me know the optimized SELECT Query.

Thank you.

zibidyum · Accepted Answer · 2013-11-18 15:35:02Z

4

You can use left-join as i wrote below and you can consider giving parallel hint (e.g /*+ parallel */ )

    SELECT sub.TRNSCTN_ID,
    sub.TRNSCTN_DOC,
    sub.TRNSCTN_TYPE,
    sub.STATUS,
    sub.TRNSCTN_CREATEDDATE,
    sub.TRNSCTN_CREATEDBY,
    sub.TRNSCTN_CHANGEDDATE,
    sub.TRNSCTN_CHANGEDBY,
    sub.PARENT_LINK,
    DECODE(prnt.trnsctn_id, null, 'false', 'true') AS CHILDCNT,
    sub.PT_NAME,
    sub.APP_ID,
    sub.DIRECTION
    from TRNSCTN sub
    left join TRNSCTN prnt
    on sub.parent_link = prnt.trnsctn_id;

answered Nov 18, 2013 at 15:35

zibidyum

1741 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2013-11-19 01:58:08Z

1

SELECT TRNSCTN_ID, 
       TRNSCTN_DOC, 
       TRNSCTN_TYPE, 
       STATUS, 
       TRNSCTN_CREATEDDATE, 
       TRNSCTN_CREATEDBY, 
       TRNSCTN_CHANGEDDATE, 
       TRNSCTN_CHANGEDBY, 
       PARENT_LINK, 
       CASE 
          WHEN NOT EXISTS (SELECT 1
                           FROM   TRNSCTN sub
                           WHERE  t.TRNSCTN_ID=sub.PARENT_LINK)
          THEN 'false' 
          ELSE 'true' 
       END AS CHILDCNT, 
       PT_NAME, 
       APP_ID, 
       DIRECTION 
FROM TRNSCTN t;

SQLFIDDLE

edited Nov 19, 2013 at 1:58

answered Nov 19, 2013 at 1:50

MT0

173k12 gold badges70 silver badges136 bronze badges

Comments

Stephen · Accepted Answer · 2013-11-18 15:35:14Z

0

Perhaps you could use a self join rather than a sub-query

i.e. puesdo-code:

SELECT <the_columns> FROM Transactions maintrans
LEFT OUTER JOIN (SELECT DISTINCT ParentID FROM Transactions) subtrans
ON maintrans.TranscationID = subtrans.ParentID

Now, where subtrans.ParentID is NULL, would equate to a transaction without a parent

answered Nov 18, 2013 at 15:35

Stephen

1131 silver badge10 bronze badges

Comments

usergray · Accepted Answer · 2013-11-19 05:18:22Z

SELECT 
  TRNSCTN_ID,
  TRNSCTN_DOC,
  TRNSCTN_TYPE,
  STATUS,
  TRNSCTN_CREATEDDATE,
  TRNSCTN_CREATEDBY,
  TRNSCTN_CHANGEDDATE,
  TRNSCTN_CHANGEDBY,
  PARENT_LINK,
  CHILDCNT,
  PT_NAME,
  APP_ID,
  DIRECTION
FROM
  (SELECT /*+use_hash(TRNSCTN, TRNSCTN1) */
    TRNSCTN.TRNSCTN_ID,
    TRNSCTN.TRNSCTN_DOC,
    TRNSCTN.TRNSCTN_TYPE,
    TRNSCTN.STATUS,
    TRNSCTN.TRNSCTN_CREATEDDATE,
    TRNSCTN.TRNSCTN_CREATEDBY,
    TRNSCTN.TRNSCTN_CHANGEDDATE,
    TRNSCTN.TRNSCTN_CHANGEDBY,
    TRNSCTN.PARENT_LINK,
    DECODE(COUNT(TRNSCTN1.TRNSCTN_ID) over (partition BY TRNSCTN.PARENT_LINK), 0, 'false', 'true') CHILDCNT,
    TRNSCTN.PT_NAME,
    TRNSCTN.APP_ID,
    TRNSCTN.DIRECTION,
    rank() over (partition BY TRNSCTN.TRNSCTN_ID order by TRNSCTN1.TRNSCTN_ID) r
  FROM
    TRNSCTN TRNSCTN,
    TRNSCTN TRNSCTN1
  WHERE
    TRNSCTN.PARENT_LINK=TRNSCTN1.TRNSCTN_ID(+)
  )
WHERE
  r=1;

You can insert parallel hint if it nessesary

Collectives™ on Stack Overflow

Oracle SQL SELECT subquery Optimization

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related