Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    20

    Question Unanswered: different results of sql query on different servers (with the same data)

    Hi all,

    We noticed different results of sql query on different servers (with the same data). Please help to understand the reason why.

    CREATE TABLE FLATBED.TMP3_FINAN_COST_CENTER
    (
    COMPANY_CODE VARCHAR2(2 BYTE)
    );

    CREATE TABLE FLATBED.TMP3_ORGANIZATIONS
    (
    COMPANY_CODE CHAR(2 BYTE)
    );

    BEGIN
    INSERT INTO flatbed.TMP3_FINAN_COST_CENTER (COMPANY_CODE)
    SELECT '00' AS COMPANY_CODE FROM DUAL;
    COMMIT;
    END;

    BEGIN
    INSERT INTO flatbed.tmp3_ORGANIZATIONS(COMPANY_CODE)
    SELECT 'ZZ' AS COMPANY_CODE FROM DUAL;
    COMMIT;
    END;

    select 1 AS T1
    from flatbed.tmp3_ORGANIZATIONS org1,
    flatbed.TMP3_FINAN_COST_CENTER fin1
    where
    fin1.COMPANY_CODE = '00'
    AND org1.COMPANY_CODE = 'ZZ'
    and
    fin1.COMPANY_CODE =
    CASE
    WHEN (SELECT COUNT(*)
    FROM flatbed.TMP3_FINAN_COST_CENTER this2
    WHERE this2.COMPANY_CODE=org1.COMPANY_CODE)>0
    THEN org1.COMPANY_CODE
    ELSE '00'
    END
    AND ROWNUM = 1

    We tried it on two servers:

    Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE 11.2.0.2.0 Production
    TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production

    result:
    no rows selected.


    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE 11.2.0.4.0 Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    result:
    T1
    ----------
    1
    1 row selected.


    windows:

    Plan
    SELECT STATEMENT ALL_ROWSCost: 7 Bytes: 22 Cardinality: 1
    9 COUNT STOPKEY
    8 HASH JOIN Cost: 7 Bytes: 22 Cardinality: 1
    6 MERGE JOIN CARTESIAN Cost: 4 Bytes: 19 Cardinality: 1
    3 VIEW VIEW SYS.VW_SQ_1 Cost: 2 Bytes: 16 Cardinality: 1
    2 HASH GROUP BY Cost: 2 Bytes: 3 Cardinality: 1
    1 TABLE ACCESS FULL TABLE FLATBED.TMP_FINAN_COST_CENTER Cost: 2 Bytes: 3 Cardinality: 1
    5 BUFFER SORT Cost: 4 Bytes: 3 Cardinality: 1
    4 TABLE ACCESS FULL TABLE FLATBED.TMP_FINAN_COST_CENTER Cost: 2 Bytes: 3 Cardinality: 1
    7 TABLE ACCESS FULL TABLE FLATBED.TMP2_ORGANIZATIONS Cost: 2 Bytes: 6 Cardinality: 2


    linux:

    Plan
    SELECT STATEMENT ALL_ROWSCost: 12 Bytes: 7 Cardinality: 1
    8 COUNT STOPKEY
    7 FILTER
    4 MERGE JOIN CARTESIAN Cost: 6 Bytes: 7 Cardinality: 1
    1 TABLE ACCESS FULL TABLE FLATBED.TMP2_ORGANIZATIONS Cost: 3 Bytes: 4 Cardinality: 1
    3 BUFFER SORT Cost: 3 Bytes: 3 Cardinality: 1
    2 TABLE ACCESS FULL TABLE FLATBED.TMP_FINAN_COST_CENTER Cost: 3 Bytes: 3 Cardinality: 1
    6 SORT AGGREGATE Bytes: 3 Cardinality: 1
    5 TABLE ACCESS FULL TABLE FLATBED.TMP_FINAN_COST_CENTER Cost: 3 Bytes: 3 Cardinality: 1

    Hope this is convenient output (from toad).


    What is the reason of different behavior?

    ( cross-post https://community.oracle.com/message/12984717#12984717 )
    Last edited by marchelloUA; 03-31-15 at 16:38.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The reason is the execution plan is different meaning it is applying the filter clauses in a different order.
    I think there are three reasons for the issue
    1) I suspect the main cause is your sql doesnt make sense, how can you have

    fin1.COMPANY_CODE = '00'

    AND

    fin1.COMPANY_CODE =
    CASE
    WHEN (SELECT COUNT(*)
    FROM flatbed.TMP3_FINAN_COST_CENTER this2
    WHERE this2.COMPANY_CODE=org1.COMPANY_CODE)>0
    THEN org1.COMPANY_CODE
    ELSE '00'
    END

    2) The use of ROWNUM is ambiguous as you dont know when it will be evaluated

    3) Different execution plans can return different results if the result set is dependent on the order criteria are evaluated in.

    Alan

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, would this even be valid? as it should return a NULL, which cannot be compared to >0
    WHEN (SELECT COUNT(*)
    FROM flatbed.TMP3_FINAN_COST_CENTER this2
    WHERE this2.COMPANY_CODE=org1.COMPANY_CODE)>0
    Shouldn't it be:
    Code:
    WHEN (SELECT value(COUNT(*),0)
     FROM flatbed.TMP3_FINAN_COST_CENTER this2
     WHERE this2.COMPANY_CODE=org1.COMPANY_CODE)>0
    Dave

Tags for this Thread

Posting Permissions

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