1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by ecivgamer, Mar 31, 2015.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    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 )
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is due to the different releases of Oracle that you're using; 11.2.0.2 on any platform appears to exhibit this behavior while 11.2.0.4, again on any platform, does not.