Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    12

    Unanswered: query returning wrong results on two databases

    Hi all,
    The following query when run on two different databases returns improper results on one database.

    SELECT count(*) FROM (SELECT * FROM TABLE1 A, TABLE2 B WHERE A.ID = B.PAID (+) AND A.PID = B.PID (+) AND ((A.PID = 147559417235427227 AND A.T <> 'S' AND A.T <> 'L' AND A.T <> 'R') OR (A.P_ID = 147559417235427227 AND A.T = 'S' AND A.SID IN (SELECT ID FROM TABLE2 WHERE NOT T = 'XX')) OR (A.PID IN (SELECT SID FROM TABLE1 WHERE PID = 147559417235427227 AND T = 'Y') AND A.T <> 'R' AND A.T <> 'L' AND A.PID IN (SELECT ID FROM TABLE2 WHERE T = 'EP'))) ORDER BY A.ID ASC, P.ID ASC) WHERE ROWNUM <= 376;


    One one database where it gives correct result (which is 3) the execution plan is:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 COUNT (STOPKEY)
    3 2 VIEW
    4 3 SORT (ORDER BY STOPKEY)
    5 4 FILTER
    6 5 NESTED LOOPS (OUTER)
    7 6 TABLE ACCESS (FULL) OF 'TABLE1'
    8 6 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2'
    9 8 INDEX (RANGE SCAN) OF 'TABLE2_X' (NON-UNIQUE)

    10 5 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2'
    11 10 INDEX (UNIQUE SCAN) OF 'TABLE2_PK' (UNIQUE)
    12 5 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1'
    13 12 INDEX (RANGE SCAN) OF 'TABLE1_KEY' (UNIQUE)

    14 5 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2'
    15 14 INDEX (UNIQUE SCAN) OF 'TABLE2_PK' (UNIQUE)


    On the database where it fails (returning 376. it would have returned more if "ROWNUM <= 376" was not specified) the executionplan is:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=511 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 COUNT (STOPKEY)
    3 2 VIEW (Cost=511 Card=93)
    4 3 SORT (ORDER BY STOPKEY) (Cost=511 Card=93 Bytes=47151)

    5 4 FILTER
    6 5 NESTED LOOPS (OUTER) (Cost=502 Card=93 Bytes=47151)

    7 6 TABLE ACCESS (FULL) OF 'TABLE1' (Cost=223 Card=93 Bytes=11625)

    8 6 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (Cost=3 Card=20341 Bytes=7770262)

    9 8 INDEX (RANGE SCAN) OF 'TABLE2_X' (NON-UNIQUE) (Cost=2 Card=20341)

    10 5 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (Cost=2 Card=1 Bytes=13)

    11 10 INDEX (UNIQUE SCAN) OF 'TABLE2_PK' (UNIQUE) (Cost=1 Card=1)

    12 5 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1' (Cost=2 Card=1 Bytes=15)

    13 12 INDEX (RANGE SCAN) OF 'TABLE1_X' (NON-UNIQUE) (Cost=1 Card=1)

    14 5 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (Cost=2 Card=1 Bytes=13)

    15 14 INDEX (UNIQUE SCAN) OF 'TABLE2_PK' (UNIQUE) (Cost=1 Card=1)


    Both these plans differ in line 13. These are the indexes in question.
    INDEX TABLE1_X ON TABLE1 (PID DESC);
    UNIQUE INDEX TABLE1_KEY ON TABLE1 (PID, DID, AX);

    Both are using the same version of oracle:
    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    PL/SQL Release 8.1.7.0.0 - Production
    CORE 8.1.7.0.0 Production
    TNS for Solaris: Version 8.1.7.0.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production

    Please help me to figure out the problem in the second database. Could this be a data problem?

    Thanks in advance.
    Last edited by vinay100; 05-20-04 at 09:38.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Maybe a dumb question but ... Are you sure the data is equal on both databases; ie, Same number of rows in the tables ????

    Do you have statistics updated on both databases .... are you trying to run parallel querys on 1 database and not the other ... I know with Oracle 8 and Oracle 8i, the optimizer changed to an extent that I have a query running parallel and another that didn't, giving different results...

    Are you parameter files equal (initxxx.ora), or do you have something special turned on in one of the databases ???

  3. #3
    Join Date
    Feb 2004
    Posts
    12
    Data is not same in both databases. I'll be importing the data from the faulty database to the other shortly. I do not have access to the faulty database.

    Regarding parallel queries, no. The queries are fired directly on the sql prompt.

    I do not know if something special is turned on on the faulty database. Can you please tell me the different databse settings that could have affected the result of the above query?

    Please provide me additioinal details about the parameter files? I do not know much about them.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    As far are parallelism ... you setup tables and indexes to have a certain degree of parallelism.... You can check those by:

    select table_name from dba_tables where degree <> 1;
    select index_name from dba_indexes where degree <> 1;

    HTH
    Gregg

Posting Permissions

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