Tuning SQL query with hierarchy result through DB link

5 pts.
Tags:
DBLink
SQL tuning
I have the below SQL query: /********************************************************************************************************/ SELECT tm_super.tm_nbr ,tm_super.tm_fname ,tm_super.tm_lname ,tm_super.super_tm_nbr ,tm_super.super_fname ,tm_super.super_lname ,tm_super.super_job FROM (SELECT papftm.employee_number tm_nbr ,paaftm.supervisor_id tm_super_id ,paaftm.person_id tm_person_id ,papftm.first_name tm_fname ,papftm.last_name tm_lname ,papfsuper.employee_number super_tm_nbr ,papfsuper.first_name super_fname ,papfsuper.last_name super_lname ,UPPER(pjsuper.name) super_job FROM per_all_assignments_f paaftm ,per_all_people_f papftm ,per_all_people_f papfsuper ,per_all_assignments_f paafsuper ,per_jobs_v pjsuper WHERE papftm.person_id = paaftm.person_id AND paaftm.supervisor_id = papfsuper.person_id AND papfsuper.person_id = paafsuper.person_id AND paafsuper.job_id = pjsuper.job_id AND TRUNC(SYSDATE) BETWEEN papftm.effective_start_date AND papftm.effective_end_date AND TRUNC(SYSDATE) BETWEEN paaftm.effective_start_date AND paaftm.effective_end_date AND TRUNC(SYSDATE) BETWEEN papfsuper.effective_start_date AND papfsuper.effective_end_date AND TRUNC(SYSDATE) BETWEEN paafsuper.effective_start_date AND paafsuper.effective_end_date AND TRUNC(SYSDATE) BETWEEN pjsuper.date_from AND NVL(pjsuper.date_to, TO_DATE('31-DEC-4012','DD-MON-YYYY')) ) tm_super START WITH tm_super.tm_nbr = '199756' CONNECT BY NOCYCLE PRIOR tm_super.tm_super_id = tm_super.tm_person_id ; /********************************************************************************************************/ If I run the above SQL in the database in which all the tables I select from reside, the query will return results in 10 seconds. However If I run it in a remote database that has a DB link to link to the database in which all tese tables reside, the query will never return any results. Please note that selecting from each table or just executing the sub-select query is fine through the DB link. The issue only when I added the "Start with...." and "Connect By ....". Please HELP! Alternatively, I can use the below SQL but it takes 40-60 seconds to return the query results. /********************************************************************************************************/ SELECT papftm.employee_number tm_nbr ,papftm.first_name tm_fname ,papftm.last_name tm_lname ,papfsuper.employee_number super_tm_nbr ,papfsuper.first_name super_fname ,papfsuper.last_name super_lname ,UPPER(pjsuper.name) super_job FROM ebs_per_all_assignments_f paaftm ,ebs_per_all_people_f papftm ,ebs_per_all_people_f papfsuper ,ebs_per_all_assignments_f paafsuper ,ebs_per_jobs_v pjsuper WHERE paaftm.person_id = papftm.person_id AND paaftm.supervisor_id = papfsuper.person_id AND papfsuper.person_id = paafsuper.person_id AND paafsuper.job_id = pjsuper.job_id START WITH papftm.employee_number = pc_tm_nbr AND TRUNC(SYSDATE) BETWEEN paaftm.effective_start_date AND paaftm.effective_end_date AND TRUNC(SYSDATE) BETWEEN papftm.effective_start_date AND papftm.effective_end_date AND TRUNC(SYSDATE) BETWEEN papfsuper.effective_start_date AND papfsuper.effective_end_date AND TRUNC(SYSDATE) BETWEEN paafsuper.effective_start_date AND paafsuper.effective_end_date AND TRUNC(SYSDATE) BETWEEN pjsuper.date_from AND NVL(pjsuper.date_to, TO_DATE('31-DEC-4012','DD-MON-YYYY')) CONNECT BY NOCYCLE PRIOR paaftm.supervisor_id = paaftm.person_id AND TRUNC(SYSDATE) BETWEEN paaftm.effective_start_date AND paaftm.effective_end_date AND TRUNC(SYSDATE) BETWEEN papftm.effective_start_date AND papftm.effective_end_date AND TRUNC(SYSDATE) BETWEEN papfsuper.effective_start_date AND papfsuper.effective_end_date AND TRUNC(SYSDATE) BETWEEN paafsuper.effective_start_date AND paafsuper.effective_end_date AND TRUNC(SYSDATE) BETWEEN pjsuper.date_from AND NVL(pjsuper.date_to, TO_DATE('31-DEC-4012','DD-MON-YYYY')) ; /********************************************************************************************************/
ASKED: August 10, 2009  7:22 AM
UPDATED: August 11, 2009  2:29 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Meandyou
    DB link... is the Oracle? When you say it "never returns results" do you mean 1) it returns 0 rows ? 2) it never completes? 3) it disappears into the Twilight Zone?
    5,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following