Parallel Query on a Partitioned IOT Issue

From: Shivanischal A <shivan_at_subexsystems.com>
Date: Sat, 10 Sep 2005 16:42:12 +0530
Message-Id: <1126350732.14510.280.camel@linux1.subex>

Hi All,

A parallel query on a Range Partitioned IOT is exiting with the below error:

ORA-12801: error signaled in parallel query server P024 ORA-00904: "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

Received on Sat Sep 10 2005 - 06:20:56 CDT