Thiết kế website giá rẻ

Question

I am running into performance issues using CONNECT BY NOCYCLE PRIOR in my query.

What I am trying to do is given a CUST_NUM, identify all CUST_ID’s associated with it and all CUST_NUM for those, and so on…

Below is the query I am running but having performance issues with it on a large database with 2M+ rows.

Any help is much appreciated and if you could explain what am I doing wrong

SELECT DISTINCT CUST_NUM, CUST_ID, TIER, STARTDATE, ENDDATE
FROM TABLE1
CONNECT BY NOCYCLE PRIOR UPPER(CUST_NUM)=UPPER(CUST_NUM)
  OR PRIOR UPPER(CUST_ID)=UPPER(CUST_ID)
START WITH UPPER(CUST_NUM) = UPPER('2987530')
ORDER BY CUST_NUM, STARTDATE;

**Below is the test data:

CUST# CUST_ID TIER STARTDATE ENDDATE
2819579 P000000000001924474 TIER_A 2015-09-24 00:00:00 2018-06-20 00:00:00
2819579 P000000000001924474 TIER_B 2018-06-20 00:00:00 2019-01-29 00:00:00
2819579 P000000000001924474 TIER_C 2019-01-29 00:00:00 2021-04-13 11:15:00
2819579 P000000000001763124 TIER_C 2022-03-17 11:00:00 2022-09-14 00:00:00
2987530 P000000000002057322 TIER_A 2016-05-14 00:00:00 2016-07-15 00:00:00
2987530 P000000000001634343 TIER_A 2017-03-24 00:00:00 2018-06-20 00:00:00
2987530 P000000000001634343 TIER_B 2018-06-20 00:00:00 2019-02-14 00:00:00
2987530 P000000000001634343 TIER_C 2019-02-14 00:00:00 2021-06-16 00:00:00
2987530 P000000000001711763 TIER_C 2022-02-07 12:45:00 2030-12-31 19:00:00
2987541 P000000000001968900 TIER_A 2016-04-30 00:00:00 2018-06-20 00:00:00
2987541 P000000000001968900 TIER_B 2018-06-20 00:00:00 2019-02-04 00:00:00
2987541 P000000000001968900 TIER_C 2019-02-04 00:00:00 2024-01-31 09:45:00
2987508 P000000000001705747 TIER_A 2016-04-16 00:00:00 2018-06-20 00:00:00
2987508 P000000000001705747 TIER_B 2018-06-20 00:00:00 2019-02-04 00:00:00
2987508 P000000000001705747 TIER_C 2019-02-04 00:00:00 2022-01-26 10:00:00
2987572 P000000000002023008 TIER_A 2016-10-06 00:00:00 2018-06-20 00:00:00
2987572 P000000000002023008 TIER_B 2018-06-20 00:00:00 2019-02-25 00:00:00
2987572 P000000000002023008 TIER_C 2019-02-25 00:00:00 2019-10-18 00:00:00
2987572 P000000000001832530 TIER_C 2021-01-13 09:30:00 2030-12-31 19:00:00
2987581 P000000000001866100 TIER_A 2016-12-13 00:00:00 2018-06-20 00:00:00
2987581 P000000000001866100 TIER_B 2018-06-20 00:00:00 2019-02-16 00:00:00
2987581 P000000000001866100 TIER_C 2019-02-16 00:00:00 2023-12-13 10:45:00
2987595 P000000000002023001 TIER_A 2016-08-27 00:00:00 2018-06-20 00:00:00
2987595 P000000000002023001 TIER_B 2018-06-20 00:00:00 2019-01-31 00:00:00
2987595 P000000000002023001 TIER_C 2019-01-31 00:00:00 2023-11-20 11:45:00
2987595 P000000000001968900 TIER_C 2024-01-31 09:45:00 2030-12-31 19:00:00
2987637 P000000000001745445 TIER_A 2017-04-17 00:00:00 2018-06-20 00:00:00
2987637 P000000000001745445 TIER_B 2018-06-20 00:00:00 2019-02-23 00:00:00
2987637 P000000000001745445 TIER_C 2019-02-23 00:00:00 2023-11-01 13:00:00
2987637 P000000000002023001 TIER_C 2023-11-20 11:45:00 2030-12-31 19:00:00
2880699 P000000000002057322 TIER_A 2016-07-15 00:00:00 2018-06-20 00:00:00
2880699 P000000000002057322 TIER_B 2018-06-20 00:00:00 2019-02-11 00:00:00
2880699 P000000000002057322 TIER_C 2019-02-11 00:00:00 2021-02-05 11:00:00
2880699 P000000000001696121 TIER_C 2021-04-02 11:45:00 2030-12-31 19:00:00
3535914 P000000000001711763 TIER_A 2016-09-17 00:00:00 2018-06-20 00:00:00
3535914 P000000000001711763 TIER_B 2018-06-20 00:00:00 2019-02-23 00:00:00
3535914 P000000000001711763 TIER_C 2019-02-23 00:00:00 2022-02-07 12:45:00
3535914 P000000000001743922 TIER_C 2022-10-26 09:45:00 2030-12-31 19:00:00
3535924 P000000000001743922 TIER_A 2016-11-19 00:00:00 2018-06-20 00:00:00
3535924 P000000000001743922 TIER_B 2018-06-20 00:00:00 2019-01-30 00:00:00
3535924 P000000000001743922 TIER_C 2019-01-30 00:00:00 2020-08-17 14:00:00
3535924 P000000000001705747 TIER_C 2022-01-26 10:00:00 2030-12-31 19:00:00
3535890 P000000000002035081 TIER_A 2016-11-19 00:00:00 2018-06-20 00:00:00
3535890 P000000000002035081 TIER_B 2018-06-20 00:00:00 2019-02-19 00:00:00
3535890 P000000000002035081 TIER_C 2019-02-19 00:00:00 2022-05-20 13:30:00
3535890 P000000000001725058 TIER_C 2023-11-02 14:30:00 2030-12-31 19:00:00
3136624 P000000000001832530 TIER_A 2016-02-13 00:00:00 2018-06-20 00:00:00
3136624 P000000000001832530 TIER_B 2018-06-20 00:00:00 2019-02-14 00:00:00
3136624 P000000000001832530 TIER_C 2019-02-14 00:00:00 2021-01-13 09:30:00
3136624 P000000000001743922 TIER_C 2021-10-05 00:00:00 2022-10-26 09:45:00
3136624 P000000000004969578 TIER_C 2023-11-02 10:15:00 2030-12-31 19:00:00
2987781 P000000000001763124 TIER_A 2016-10-29 00:00:00 2018-06-20 00:00:00
2987781 P000000000001763124 TIER_B 2018-06-20 00:00:00 2019-02-17 00:00:00
2987781 P000000000001763124 TIER_C 2019-02-17 00:00:00 2022-03-07 11:15:00
2987781 P000000000001722432 TIER_C 2022-05-04 14:15:00 2030-12-31 19:00:00
3754236 P000000000004969578 TIER_A 2017-05-15 00:00:00 2018-06-20 00:00:00
3754236 P000000000004969578 TIER_B 2018-06-20 00:00:00 2019-02-13 00:00:00
3754236 P000000000004969578 TIER_C 2019-02-13 00:00:00 2023-11-02 10:15:00
3754269 P000000000001722432 TIER_A 2017-09-16 00:00:00 2018-06-20 00:00:00
3754269 P000000000001722432 TIER_B 2018-06-20 00:00:00 2019-02-11 00:00:00
3754269 P000000000001722432 TIER_C 2019-02-11 00:00:00 2022-04-04 13:45:00
3754269 P000000000002035081 TIER_C 2022-05-20 13:30:00 2030-12-31 19:00:00
3011204 P000000000001696121 TIER_A 2016-02-02 00:00:00 2018-06-20 00:00:00
3011204 P000000000001696121 TIER_B 2018-06-20 00:00:00 2019-01-30 00:00:00
3011204 P000000000001696121 TIER_C 2019-01-30 00:00:00 2021-04-02 11:45:00
3011204 P000000000001763124 TIER_C 2022-03-07 11:15:00 2022-03-17 11:00:00
3011204 P000000000001866100 TIER_C 2023-12-13 10:45:00 2030-12-31 19:00:00
3910132 P000000000001656589 TIER_A 2018-03-19 13:15:00 2018-06-20 00:00:00
3910132 P000000000001656589 TIER_B 2018-06-20 00:00:00 2019-02-16 00:00:00
3910132 P000000000001656589 TIER_C 2019-02-16 00:00:00 2030-12-31 19:00:00
3910271 P000000000002057322 TIER_C 2021-02-05 11:00:00 2022-07-06 00:00:00
3940456 P000000000001924474 TIER_C 2021-04-13 11:15:00 2030-12-31 19:00:00
3816264 P000000000001743922 TIER_C 2020-08-17 14:00:00 2021-10-05 00:00:00
3820058 P000000000001656589 TIER_A 2017-02-17 00:00:00 2018-03-19 13:15:00
3820058 P000000000001722432 TIER_C 2022-04-04 13:45:00 2022-05-04 14:15:00
3820058 P000000000001745445 TIER_C 2023-11-01 13:00:00 2030-12-31 19:00:00
1111111 P000000000001111111 TIER_A 2017-02-17 00:00:00 2018-03-19 13:15:00
1111111 P000000000001111111 TIER_C 2022-04-04 13:45:00 2022-05-04 14:15:00

SELECT DISTINCT CUST_NUM, CUST_ID, TIER, STARTDATE, ENDDATE
FROM TABLE1
CONNECT BY NOCYCLE PRIOR UPPER(CUST_NUM)=UPPER(CUST_NUM)
  OR PRIOR UPPER(CUST_ID)=UPPER(CUST_ID)
START WITH UPPER(CUST_NUM) = UPPER('2987530')
ORDER BY CUST_NUM, STARTDATE;

Thiết kế website giá rẻ

Danh mục

Oracle – Performance issues using CONNECT BY NOCYCLE PRIOR