Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Queries (Oracle 11.2.0.3)
Hierarchical Queries [message #661928] Thu, 06 April 2017 04:39 Go to next message
pointers
Messages: 439
Registered: May 2008
Senior Member
Hi,

I have a table -

CREATE TABLE SCOTT.T_MSA_HUB
(
  KEY_VAL  NUMBER,
  HUBID    NUMBER,
  MSAID    NUMBER
);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (1, 10, 100);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (2, 11, 101);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (3, 12, 102);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (4, 13, 104);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (5, 10, 105);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (6, 15, 100);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (7, 15, 106);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (8, 12, 101);

INSERT INTO SCOTT.T_MSA_HUB (key_val, hubid, msaid)
     VALUES (9, 13, 101);

COMMIT;


I would like to get all the matching records for a given HUBID, Also records whose MSAID is matching to the records found based on the
HUBID.
OR
get all the matching records for a given MSAID, Also records whose HUBID is matching to the records found based on the
MSAID.

We have the following code which is performing poorly.

For e.g. Following is the code for HUBID=12 and MSAID=100

SELECT *
  FROM t_msa_hub
 WHERE (msaid IN (SELECT msaid
                    FROM t_msa_hub
                   WHERE    hubid = 12)
     OR hubid IN (SELECT hubid
                    FROM t_msa_hub
                   WHERE msaid = 100))
                   order by 1;

KEY_VAL HUBID   MSAID
1	10	100
2	11	101
3	12	102
5	10	105
6	15	100
7	15	106
8	12	101
9	13	101


We re-wrote it to imporve performace as below.

SELECT i.key_val, i.hubid, i.msaid
    FROM t_msa_hub i, t_msa_hub o
   WHERE    (o.msaid = 100 AND i.hubid = o.hubid)
         OR (o.hubid = 12 AND i.msaid = o.msaid)
ORDER BY 1;

Still, we are accessing the T_MSA_HUB table twice, I thought of writing it using HIERARCHICAL Queries.

SELECT DISTINCT a.* 
 FROM t_msa_hub a
START WITH (msaid=100 or hubid=12)
CONNECT BY  NOCYCLE (PRIOR hubid=hubid AND PRIOR key_val <> key_val
                  OR PRIOR msaid=msaid AND PRIOR key_val<>key_val)

It gives me an extra row -

KEY_VAL HUBID MSAID
1 10 100
2 11 101
3 12 102
4 13 104
5 10 105
6 15 100
7 15 106
8 12 101
9 13 101

It works fine if I write two seperate quries and UNION ALL
SELECT DISTINCT a.*
      FROM t_msa_hub a
START WITH (msaid = 100)
CONNECT BY NOCYCLE PRIOR hubid = hubid AND PRIOR key_val <> key_val
--
UNION ALL
--
    SELECT DISTINCT a.*
      FROM t_msa_hub a
START WITH (hubid = 12)
CONNECT BY NOCYCLE PRIOR msaid = msaid AND PRIOR key_val <> key_val
ORDER BY 1;

KEY_VAL HUBID   MSAID
1	10	100
2	11	101
3	12	102
5	10	105
6	15	100
7	15	106
8	12	101
9	13	101

1. Could you please help me how to write compound HIERARCHICAL Queries i.e. two or more START WITH
and TWO or MORE PRIOR operators.

2. Also, Could you please show me other ways of doing it.

Thank you in advance.

Regards,
Pointers
Re: Hierarchical Queries [message #661961 is a reply to message #661928] Fri, 07 April 2017 18:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8805
Registered: November 2002
Location: California, USA
Senior Member
pointers wrote on Thu, 06 April 2017 02:39

...
1. Could you please help me how to write compound HIERARCHICAL Queries i.e. two or more START WITH
and TWO or MORE PRIOR operators.
...
This is just to answer your question, not necessarily a more efficient method.

SCOTT@orcl_12.1.0.2.0> SELECT  *
  2  FROM    t_msa_hub
  3  START   WITH hubid = 12 OR msaid = 100
  4  CONNECT BY NOCYCLE (PRIOR hubid = 12 AND PRIOR msaid = msaid)
  5  		     OR (PRIOR msaid = 100 AND PRIOR hubid = hubid)
  6  ORDER   BY 1
  7  /

   KEY_VAL      HUBID      MSAID
---------- ---------- ----------
         1         10        100
         2         11        101
         3         12        102
         5         10        105
         6         15        100
         7         15        106
         8         12        101
         9         13        101

8 rows selected.

You can significantly limit the rows accessed by adding level<=2 and you can eliminate the need for nocycle by providing further limitations, as shown below.

SCOTT@orcl_12.1.0.2.0> SELECT  *
  2  FROM    t_msa_hub a
  3  START   WITH hubid = 12 OR msaid = 100
  4  CONNECT BY (PRIOR hubid = 12 AND PRIOR msaid = msaid
  5  		 AND hubid != 12 AND PRIOR key_val != key_val
  6  		 AND LEVEL <= 2)
  7  	     OR (PRIOR msaid = 100 AND PRIOR hubid = hubid
  8  		 AND msaid != 100 AND PRIOR key_val != key_val
  9  		 AND LEVEL <= 2)
 10  ORDER   BY 1
 11  /

   KEY_VAL      HUBID      MSAID
---------- ---------- ----------
         1         10        100
         2         11        101
         3         12        102
         5         10        105
         6         15        100
         7         15        106
         8         12        101
         9         13        101

8 rows selected.

However, your current query might be more efficient, especially if you have an index on (hubid, msaid).


[Updated on: Fri, 07 April 2017 20:29]

Report message to a moderator

Re: Hierarchical Queries [message #661965 is a reply to message #661961] Sat, 08 April 2017 07:42 Go to previous message
pointers
Messages: 439
Registered: May 2008
Senior Member
Thank you Barbara for your answer.

Regards,
Pointers
Previous Topic: UNION and dependent LOV
Next Topic: testing rollback
Goto Forum:
  


Current Time: Sun Feb 25 19:57:47 CST 2018

Total time taken to generate the page: 0.02569 seconds