Parallel Query on a Partitioned IOT Issue 2005-09-09 - By Shivanischal A
Hi All,
A parallel query on a Range Partitioned IOT is exiting with the below
error:
ORA-12801 (See ORA-12801.ora-code.com): error signaled in parallel query server P024
ORA-00904 (See ORA-00904.ora-code.com): "A1"."rowid": invalid identifier
The oracle version is 9.2.0.5 on AIX 5.2
The query is like this:
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
WITH
usage AS
(
SELECT id1, SUM(v1) AS tv1
FROM range_partitioned_iot
WHERE date_field BETWEEN :StartDate AND :EndDate
GROUP BY id1
)
SELECT
rpi.id1
rpi.id2
rpi.id1_type
rpi.id2_type
(rpi.v1/u.tv1) * 100
rpi.v2
rpi.v3
ROUND(rpi.v1/60, 2)
FROM
usage u,
range_partitioned_iot rpi
WHERE
rpi.flag= 1 AND
rpi.id1 = u.id1
ORDER BY
rpi.id1
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
Please note that the query has pseudo column and table names, but the
query is real. range_partitioned_iot is the paritioned IOT. All columns
except v1, v2, v3 are part of the primary key. The table was created
with the PARALLEL option. I noticed PQ errors only on partitioned IOTs.
I noticed PQ errors on the partitioned IOTs only when the query includes
the WITH clause.
I came up 3 (unacceptable) ways to solve the problem:
1. alter table range_partitioned_iot NOPARALLEL ;
2. replace the WITH with SQL Analytic functions (I have more complex
queries where I use up to 4 WITH clauses, so SQL Analytics cannot help
me and I categorized this method as unacceptable)
3. Recreate all my partitioned IOTs as Heap tables. Something I would do
the last thing in the world.
Facing client pressure, I chose the first as an interim measure. Gurus,
please advise whats wrong here. I have been to asktom, metalink and
found nothing that was of use. Maybe I was not looking properly....
Thanks for your time,
Shiva
--
http://www.freelists.org/webpage/oracle-l
|
|
