0

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed for posting purposes only (this means they are strange and have no meaning). Both queries return the exact same results. However, the Query Version 2 performs and average of 50 seconds slower than Query Version 1.

Question
If the only thing that changed between the two queries is the syntax and syntax should have no effect on query speed, why is the second query consistently slower?

Query Version 1

SELECT 
    ar.bom_type,
    details.time_from_unix,
    details.location,
    i.prod_item_partnr,
    i.random_column,
    i.name,
    details.identity,
    details.saving,
    details.some_string,
    quantity_table.serial,
    quantity_table.variable_argument4,
    quantity_table.variable_argument1,
    quantity_table.variable_argument3,
    SUM(quantity_table.qty) * - 1 AS change_directive,
    details.catalogued,
    COUNT(*) AS cnt,
    details.log_id,
    i.volume,
    i.random_item,
    t.random_spec,
    i.random_id,
    NVL(linkdata.holding_nr, waiting_list.holding_Nr) AS holding_nr,
    details.variable_argument1 AS detail_specification
FROM 
    product_table i,
    spec_table t,
    comment_table ar,
    identity_table u,
    (
        SELECT 
            l.location,
            l.variable_argument4,
            l.event_type,
            l.variable_argument1,
            DECODE(plant_code, RANDOM_CODE, DECODE(header_info.date_code, 'MR_T', 'MR_R', ''), '') || l.variable_argument3 AS variable_argument3,
            l.serial,
            DECODE(l.event_type, 9001, l.variable_argument1, - 1 * l.variable_argument1) AS qty
        FROM 
            logging_table l,
            header_list header_info
        WHERE 
            l.variable_argument4 IS NOT NULL
            AND l.time_from_unix >= starting_date
            AND l.time_from_unix < ending_date
            AND l.serial = COALESCE(item_filter, l.serial)
            AND l.catalogued = COALESCE(
                DECODE(l.event_type, 9003, saved_status, LOCATION_DETAIL, saved_status, LAST_LOCATION, saved_status),
                l.catalogued
            )
            AND UPPER(l.identity) = UPPER(COALESCE(random_filter_filter, l.identity))
            AND l.event_type IN (
                9001,
                9002
            )
            AND header_info.location = l.location
    ) quantity_table,
    (
        SELECT
            waiting_for_tonight.holding_nr,
            waiting_for_tonight.id_number,
            brand.brand_cd
        FROM
            vr_holding hldh,
            vr_waiting_for_tonighttl waiting_for_tonight,
            vr_brand brand
        WHERE 
            hldh.holding_sts = HOLD_STATUS_OPEN
            AND waiting_for_tonight.holding_nr = hldh.holding_nr
            AND brand.brand_id = waiting_for_tonight.brand_id
    ) waiting_list,
    (
        SELECT DISTINCT
            holding_nr,
            b.information_style
        FROM 
            variable_table b
        WHERE 
            b.unix_timestamp >= starting_date - 15
            AND b.unix_timestamp < ending_date + 15
            AND b.information_style IS NOT NULL
    ) linkdata,
    (
        SELECT DISTINCT
            l.log_id,
            l.variable_argument4,
            l.event_type,
            l.variable_argument1,
            l.variable_argument3,
            l.serial,
            TRUNC(l.time_from_unix) AS time_from_unix,
            l.location,
            l.identity,
            l.catalogued,
            processing_status.saving,
            COALESCE(TRIM(processing_status.some_string), l.variable_argument3) AS some_string
        FROM
            logging_table l
        INNER JOIN comment_table ar ON (
            ar.adjust_reason_code = l.variable_argument1
        )
        LEFT OUTER JOIN (
                SELECT 
                    b.bom_type AS saving,
                    b.serial,
                    b.some_string,
                    b.information_style
                FROM 
                    variable_table b
                WHERE 
                    b.unix_timestamp >= starting_date - 15
                    AND b.unix_timestamp < ending_date + 15
                    AND b.information_style IS NOT NULL
                    AND b.bom_type <> BOM_TYPE_HOLD
        ) processing_status ON (
            l.serial = processing_status.serial
            AND l.variable_argument4 = processing_status.information_style
        )
        WHERE
            l.time_from_unix >= starting_date
            AND l.time_from_unix < ending_date
            AND l.serial = COALESCE(item_filter, l.serial)
            AND l.catalogued = COALESCE(
                DECODE(l.event_type, 9003, saved_status, LOCATION_DETAIL, saved_status, LAST_LOCATION, saved_status),
                l.catalogued
            )
            AND UPPER(l.identity) = UPPER(COALESCE(random_filter_filter, l.identity))
            AND l.event_type = 9003
            AND ar.adjust_reason_code NOT IN ('YES')
            AND (
                l.variable_argument1 <> 'NO'
                AND l.variable_argument2 = 'MAYBE'
            )
    ) details
WHERE 
    quantity_table.variable_argument4 = details.variable_argument4
    AND linkdata.information_style(+) = quantity_table.variable_argument4
    AND ar.adjust_reason_code = details.variable_argument1
    AND i.serial = quantity_table.serial
    AND waiting_list.id_number(+) = quantity_table.location
    AND waiting_list.brand_cd(+) = quantity_table.variable_argument3
    AND t.specification = i.specification
    AND UPPER(details.identity) = UPPER(u.USER_SNAME)
    AND u.person = COALESCE(person_filter, u.person)
    AND quantity_table.variable_argument4 IN (
        SELECT DISTINCT 
            l.variable_argument4
        FROM 
            logging_table l
        WHERE 
            l.variable_argument4 IS NOT NULL
            AND l.time_from_unix >= starting_date
            AND l.time_from_unix < ending_date
            AND l.serial = COALESCE(item_filter, l.serial)
            AND l.catalogued = COALESCE(
                DECODE(l.event_type, 9003, saved_status, LOCATION_DETAIL, saved_status, LAST_LOCATION, saved_status),
                l.catalogued
            )
            AND UPPER(l.identity) = UPPER(COALESCE(random_filter_filter, l.identity))
            AND l.event_type = '9002'
    )

Query Version 2

SELECT 
    ar.bom_type,
    details.time_from_unix,
    details.location,
    i.prod_item_partnr,
    i.random_column,
    i.name,
    details.identity,
    details.saving,
    details.some_string,
    quantity_table.serial,
    quantity_table.variable_argument4,
    quantity_table.variable_argument1,
    quantity_table.variable_argument3,
    SUM(quantity_table.qty) * - 1 AS change_directive,
    details.catalogued,
    COUNT(*) AS cnt,
    details.log_id,
    i.volume,
    i.random_item,
    t.random_spec,
    i.random_id,
    NVL(linkdata.holding_nr, waiting_list.holding_Nr) AS holding_nr,
    details.variable_argument1 AS detail_specification
FROM 
    (
        SELECT DISTINCT
            l.log_id,
            l.variable_argument4,
            l.event_type,
            l.variable_argument1,
            l.variable_argument3,
            l.serial,
            TRUNC(l.time_from_unix) AS time_from_unix,
            l.location,
            l.identity,
            l.catalogued,
            processing_status.saving,
            COALESCE(TRIM(processing_status.some_string), l.variable_argument3) AS some_string
        FROM
            logging_table l
        INNER JOIN comment_table ar ON (
            ar.adjust_reason_code = l.variable_argument1
        )
        LEFT OUTER JOIN (
                SELECT 
                    b.bom_type AS saving,
                    b.serial,
                    b.some_string,
                    b.information_style
                FROM 
                    variable_table b
                WHERE 
                    b.unix_timestamp >= starting_date - 15
                    AND b.unix_timestamp < ending_date + 15
                    AND b.information_style IS NOT NULL
                    AND b.bom_type <> BOM_TYPE_HOLD
        ) processing_status ON (
            l.serial = processing_status.serial
            AND l.variable_argument4 = processing_status.information_style
        )
        WHERE
            l.time_from_unix >= starting_date
            AND l.time_from_unix < ending_date
            AND l.serial = COALESCE(item_filter, l.serial)
            AND l.catalogued = COALESCE(
                DECODE(l.event_type, 9003, saved_status, LOCATION_DETAIL, saved_status, LAST_LOCATION, saved_status),
                l.catalogued
            )
            AND UPPER(l.identity) = UPPER(COALESCE(random_filter_filter, l.identity))
            AND l.event_type = 9003
            AND ar.adjust_reason_code NOT IN ('YES')
            AND (
                l.variable_argument1 <> 'NO'
                AND l.variable_argument2 = 'MAYBE'
            )
    ) details
    INNER JOIN (
        SELECT 
            l.location,
            l.variable_argument4,
            l.event_type,
            l.variable_argument1,
            DECODE(plant_code, RANDOM_CODE, DECODE(header_info.date_code, 'MR_T', 'MR_R', ''), '') || l.variable_argument3 AS variable_argument3,
            l.serial,
            DECODE(l.event_type, 9001, l.variable_argument1, - 1 * l.variable_argument1) AS qty
        FROM 
            logging_table l,
            header_list header_info
        WHERE 
            l.variable_argument4 IS NOT NULL
            AND l.time_from_unix >= starting_date
            AND l.time_from_unix < ending_date
            AND l.serial = COALESCE(item_filter, l.serial)
            AND l.catalogued = COALESCE(
                DECODE(l.event_type, 9003, saved_status, LOCATION_DETAIL, saved_status, LAST_LOCATION, saved_status),
                l.catalogued
            )
            AND UPPER(l.identity) = UPPER(COALESCE(random_filter_filter, l.identity))
            AND l.event_type IN (
                9001,
                9002
            )
            AND header_info.location = l.location
    ) quantity_table ON (
        quantity_table.variable_argument4 = details.variable_argument4
    )
    LEFT OUTER JOIN (
        SELECT
            waiting_for_tonight.holding_nr,
            waiting_for_tonight.id_number,
            brand.brand_cd
        FROM
            vr_holding hldh,
            vr_waiting_for_tonighttl waiting_for_tonight,
            vr_brand brand
        WHERE 
            hldh.holding_sts = HOLD_STATUS_OPEN
            AND waiting_for_tonight.holding_nr = hldh.holding_nr
            AND brand.brand_id = waiting_for_tonight.brand_id
    ) waiting_list ON (
        waiting_list.id_number = quantity_table.location
        AND waiting_list.brand_cd = quantity_table.variable_argument3
    )
    LEFT OUTER JOIN (
        SELECT DISTINCT
            holding_nr,
            b.information_style
        FROM 
            variable_table b
        WHERE 
            b.unix_timestamp >= starting_date - 15
            AND b.unix_timestamp < ending_date + 15
            AND b.information_style IS NOT NULL
    ) linkdata ON (
        linkdata.information_style = quantity_table.variable_argument4
    )
    INNER JOIN (
        comment_table
    ) ar ON (
        ar.adjust_reason_code = details.variable_argument1
    )
    INNER JOIN (
        product_table 
    ) i ON (
        i.serial = quantity_table.serial    
    )
    INNER JOIN (
        spec_table 
    ) t ON (
        t.specification = i.specification
    )
    INNER JOIN (
        identity_table
    ) u ON (
        UPPER(details.identity) = UPPER(u.USER_SNAME)
    )
WHERE 
    u.person = COALESCE(person_filter, u.person)
    AND quantity_table.variable_argument4 IN (
        SELECT DISTINCT
            l.variable_argument4
        FROM 
            logging_table l
        WHERE 
            l.variable_argument4 IS NOT NULL
            AND l.time_from_unix >= starting_date
            AND l.time_from_unix < ending_date
            AND l.serial = COALESCE(item_filter, l.serial)
            AND l.catalogued = COALESCE(
                DECODE(l.event_type, 9003, saved_status, LOCATION_DETAIL, saved_status, LAST_LOCATION, saved_status),
                l.catalogued
            )
            AND UPPER(l.identity) = UPPER(COALESCE(random_filter_filter, l.identity))
            AND l.event_type = '9002'
    )
  • Please post the execution plans for both versions. 9i? Really? Why not upgrade to a supported version? 9i was known to have several bugs related to the newly introduced JOIN operator. – a_horse_with_no_name Sep 5 '14 at 17:17
  • You have aggregate functions (count,sum) besides column names in your "select" list but no "group by" clause. This query will not work. – miracle173 Sep 5 '14 at 17:18
  • @a_horse_with_no_name I'm changing all the syntax because we have upgraded to 10g(...). I will get the execution plans shortly. – Dodzi Dzakuma Sep 5 '14 at 17:21
  • Do you see the slow execution on 9i or 10g? Why are upgrading from one unsupported version to another unsupported version? – a_horse_with_no_name Sep 5 '14 at 17:22
  • @a_horse_with_no_name The slow execution is being seen on 10g. As for the decisions in regards to Oracle version, those are out of my hands. I am only allowed to comment and give suggestions, but I can't make any of the decisions. Working with what I have and that's the best that can be done. – Dodzi Dzakuma Sep 5 '14 at 17:25
0

some speculations:

It is not really true that the only difference is the oracle style outer join and ansi join. Maybe the queries are theoretically equivalent. But there are at 9 different tables in the where clause and the order of the table differs. 9 different tables means 9!=362880 different possibilities for the sequence of tables. Oracle optimizer checks only a small number of combinations.

Maybe you can try out if different orders of tables using the same join type gives different plans.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.