Explain Plan Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Explain Plan Question

  1. #1
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67
    In my sql below, what determines the order in which tables are processed?

    Is it the order in the FROM clause?

    Or the order of the WHERE clause?

    Thanks,

    P

    SELECT xa.address_1
    ,xa.address_2
    ,xa.address_3
    ,xa.address_4
    ,xa.city
    ,xa.county_code
    ,xa.county_desc
    ,xa.province
    ,xa.state_code
    ,xa.state_desc
    ,xa.zipcode
    ,xa.zip_plus_four
    ,xa.country_code
    ,xa.address_type_code
    ,xa.country_desc
    ,xp.area_code
    ,xp.phone_number
    ,xp.phone_extension
    ,decode(nvl(xp.area_code,-1),xp.area_code,'('||xp.area_code||') ','')||
    decode(nvl(xp.phone_number,-1),xp.phone_number,substr(xp.phone_number,1,3)||'-'||substr(xp.phone_number,4,7),'') phone_f
    ,xf.area_code fax_area
    ,xf.phone_number fax
    ,decode(nvl(xf.area_code,-1),xf.area_code,'('||xf.area_code||') ','')||
    decode(nvl(xf.phone_number,-1),xf.phone_number,substr(xf.phone_number,1,3)||'-'||substr(xf.phone_number,4,7),'') fax_f
    FROM xt_address xa
    ,xt_phone xp
    ,xt_phone xf
    WHERE xa.request_id = p_request_id
    AND xa.source_table_name = 'RISK'
    AND xa.primary_address_b <> 'Y'
    AND xa.effective_to_date = '01-JAN-3000'
    AND xp.request_id(+) = xa.request_id
    AND xp.source_record_fk(+) = xa.source_record_fk
    AND xp.source_table_name(+) = 'RISK'
    AND xp.phone_number_type_code(+) NOT LIKE '%FAX%'
    AND xp.effective_to_date(+) = '01-JAN-3000'
    -- AND xp.primary_number_b(+) = 'Y'
    AND xf.request_id(+) = xa.request_id
    AND xf.source_record_fk(+) = xa.source_record_fk
    AND xf.source_table_name(+) = 'RISK'
    AND xf.phone_number_type_code(+) LIKE '%FAX%'
    AND xf.effective_to_date(+) = '01-JAN-3000'
    AND ROWNUM = 1;

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    if you are running CBO then the optimizer determines the order in which the tables are processed
    I'm stmontgo and I approve of this message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width