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_key, subpart_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 =2 and value = 1)
OR
( subpart_list_key = 1 )
)
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 =2 ) -- REMOVED THE AND CONDITION WHICH SHOULD HAVE NO EFFECT ON THE RESULT
OR
( subpart_list_key = 1 )
)
AND value = 1
GROUP BY value, subpart_list_key;
AND I GET !?!
PHP Code:
SQL> SELECT
2 value, subpart_list_key, COUNT(*)
3 FROM test_range_list_part PARTITION (p1)
4 WHERE
5 (
6 (subpart_list_key =2 and value = 1)
7 OR
8 (subpart_list_key = 1 )
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 =2 )-- REMOVED THE AND CONDITION WHICH SHOULD HAVE NO EFFECT ON THE RESULT
7 OR
8 (subpart_list_key = 1 )
9 )
10 AND value = 1
11 GROUP BY value, subpart_list_key;
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 = 2 and value = 1 )
or
(subpart_list_key = 1)
or
(subpart_list_key = 11 and value = 1 );
*
FEHLER in Zeile 3:
ORA-00600: internal error code, arguments: [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 = 2 and value = 1 )
or
(subpart_list_key = 11 and value = 1 );
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:
SQL> get p1
1 SELECT
2 myvalue, subpart_list_key , COUNT(*)
3 FROM test_range_list_part ---PARTITION (p1)
4 WHERE
5 (
6 ( subpart_list_key =2 and myvalue = 1)
7 OR
8 ( subpart_list_key = 1 )
9 )
10 AND myvalue = 1
11* GROUP BY myvalue, subpart_list_key
12 /
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.
WRONG RESULT with range list partitioning
Reply With Quote
Bookmarks