WRONG RESULT with range list partitioning
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: WRONG RESULT with range list partitioning

  1. #1
    Join Date
    Mar 2002
    Posts
    534

    Unhappy WRONG RESULT with range list partitioning

    Hi everybody,

    Could some one explain me why these 2 queries return a different result? I'm working on 9iR2 (9.2.0.5) so I would be glade if somebody on 10g could execute the script to check if on 10g the result is also wrong.

    Thanks
    Mike

    PHP Code:
    DROP TABLE test_range_list_part;

    CREATE TABLE test_range_list_part (
       
    part_range_key        NUMBER,
       
    subpart_list_key    number,
       
    value     number)
       
    PARTITION BY RANGE (   part_range_key )
       
    SUBPARTITION BY LIST ( subpart_list_key   )
          
    SUBPARTITION TEMPLATE 
             
    (SUBPARTITION sp1  VALUES 
                
    (1,3),
              
    SUBPARTITION sp2 VALUES 
                
    (2,4),
              
    SUBPARTITION other VALUES (DEFAULT))
          (
    PARTITION p1 VALUES LESS THAN (10),
           
    PARTITION p2 VALUES LESS THAN (20),
           
    PARTITION p3 VALUES LESS THAN (30));
           
    CREATE UNIQUE INDEX pk_test_range_list_part 
    ON test_range_list_part
    (part_range_keysubpart_list_key)
    LOCAL;       
         
    INSERT INTO  test_range_list_part VALUES (1,1,1);

    INSERT INTO  test_range_list_part VALUES (1,2,1);

    INSERT INTO  test_range_list_part VALUES (1,3,1);

    INSERT INTO  test_range_list_part VALUES (1,4,1);

    INSERT INTO  test_range_list_part VALUES (1,11,1);

    INSERT INTO  test_range_list_part VALUES (1,12,1);

    COMMIT
    After this I execute the following 2 queries which should return the same result:

    PHP Code:
     SELECT 
    value
    subpart_list_key,  COUNT(*) 
    FROM test_range_list_part PARTITION (p1
    WHERE 
    (
    subpart_list_key =and value 1)
    OR
    subpart_list_key )
    )
    AND  
    value 1
    GROUP BY value
    subpart_list_key;

    SELECT 
    value
    subpart_list_key,  COUNT(*) 
    FROM test_range_list_part PARTITION (p1
    WHERE 
    (
    subpart_list_key =) -- REMOVED THE AND CONDITION WHICH SHOULD HAVE NO EFFECT ON THE RESULT
    OR
    subpart_list_key )
    )
    AND  
    value 1
    GROUP BY value
    subpart_list_key
    AND I GET !?!

    PHP Code:
     SQLSELECT 
      2  value
    subpart_list_key,  COUNT(*) 
      
    3  FROM test_range_list_part PARTITION (p1
      
    4  WHERE 
      5  
    (
      
    6  (subpart_list_key =and value 1)
      
    7  OR
      
    8  (subpart_list_key )
      
    9  )
     
    10  AND  value 1
     11  GROUP BY value
    subpart_list_key;

         
    VALUE SUBPART_LIST_KEY   COUNT(*)
    ---------- ---------------- ----------
             
    1                2          1


    SQL
    SELECT 
      2  value
    subpart_list_key,  COUNT(*) 
      
    3  FROM test_range_list_part PARTITION (p1
      
    4  WHERE 
      5  
    (
      
    6  (subpart_list_key =)-- REMOVED THE AND CONDITION WHICH SHOULD HAVE NO EFFECT ON THE RESULT 
      7  
    OR
      
    8  (subpart_list_key )
      
    9  )
     
    10  AND  value 1
     11  GROUP BY value
    subpart_list_key;

         
    VALUE SUBPART_LIST_KEY   COUNT(*)
    ---------- ---------------- ----------
             
    1                1          1
             1                2          1 

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    same thing on 10G.. sounds like a bug
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    Hi abhaysk,

    Thanks for your feedback.

    One more question:
    On 10G do you also get, based on the previous script, an ORA 600 when you execute the following script?
    PHP Code:
    SELECT
    part_range_key
    subpart_list_key
    FROM test_range_list_part  PARTITION 
    (p1)
    WHERE
    (subpart_list_key and value )
    or
    (
    subpart_list_key 1
    or
    (
    subpart_list_key 11 and value );
         *
    FEHLER in Zeile 3:
    ORA-00600internal error codearguments: [kkpamFList2], [], [], [], [], [],
    [], [] 
    when I execute it like
    PHP Code:
    SELECT
    part_range_key
    subpart_list_key
    FROM test_range_list_part PARTITION 
    (p1)
    WHERE
    (subpart_list_key 1
    or
    (
    subpart_list_key and value )
    or
    (
    subpart_list_key 11 and value ); 
    I "simply" got a wrong result but no ora 600.

    Nice bug
    Last edited by mike9; 09-23-2004 at 10:55 AM.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    didnt get any internal err.. but got the rite result set..

    though not for the second one.. ( as u said its up with wrong results )

    better raise a tar?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    Hi,

    It seems that we got only the ora 600 on a 9iR2 64bit Oracle DB not on 32bit.

    Did you realy got the correct answer with the second query? Did you get more then 1 row back?

    And yes I opend a TAR (it's a productive system).

    Thanks
    Mike

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by mike9
    Did you realy got the correct answer with the second query? Did you get more then 1 row back?
    Originally posted by abhaysk
    though not for the second one.. ( as u said its up with wrong results )
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I initially thought of using resrved word "VALUE" might be giving problem.

    It seems to be that writing a partition name in the FROM clause gives wrong result set.
    I just commented out partition. It worked correctly.
    Another lesson learned today.

    PHP Code:
    SQLget p1
      1  SELECT
      2  myvalue
    subpart_list_key ,  COUNT(*)
      
    3  FROM test_range_list_part ---PARTITION (p1)
      
    4  WHERE
      5  
    (
      
    6  subpart_list_key =and myvalue 1)
      
    7  OR
      
    8  subpart_list_key )
      
    9  )
     
    10  AND  myvalue 1
     11
    GROUP BY myvaluesubpart_list_key
     12  
    /

       
    MYVALUE SUBPART_LIST_KEY   COUNT(*)
    ---------- ---------------- ----------
             
    1                1          1
             1                2          1

    SQL
    get p2
      1  SELECT
      2  myvalue
    subpart_list_key ,  COUNT(*)
      
    3  FROM test_range_list_part --PARTITION (p1)
      
    4  WHERE
      5  
    (
      
    6  subpart_list_key =)
      
    7  OR
      
    8  subpart_list_key )
      
    9  )
     
    10  AND  myvalue 1
     11
    GROUP BY myvaluesubpart_list_key
     12  
    /

       
    MYVALUE SUBPART_LIST_KEY   COUNT(*)
    ---------- ---------------- ----------
             
    1                1          1
             1                2          1

    SQL
    spool off 
    Tamil

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    Hi Tamil,

    Yes, I also nopticed that one workaround would be not to use the partition name. Another workaround would be to drop the index. Without index, even if it's no using it, Oracle will generate another explain plan and so not make this error.

    Because most of our scripts uses the partiton name to limit the data to the data of the current month it would be a lot of work to use this workaround. Also even then I think that Oracle may, as soon as it's using the same execution plan again, return the wrong result.

    I'm wondernig what kind of solution Oracle Support will recommand.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ========
    I'm wondernig what kind of solution Oracle Support will recommand.
    ========

    Option 1:

    Upgrade to 11G available in 2007.

    Option 2:
    Do not use LIST PARTITION until you reach 2006 b/c oracle needs minimum 3 years to correct it.

    Tamil

  10. #10
    Join Date
    Mar 2002
    Posts
    534
    Another way around would be:

    ALTER SYSTEM/SESSION SET "_no_or_expansion" = TRUE;

    or

    /*+ NO_EXPAND */


    will check tomorow with my DBA if we change the value of this hidden parameter

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