Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Queries (Oracle 11.2.0.3)
Hierarchical Queries [message #661928] |
Thu, 06 April 2017 04:39 |
pointers
Messages: 451 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 |
|
Barbara Boehmer
Messages: 9097 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
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:47:24 CDT 2024
|