Re: Partition Indexes

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Tue, 10 Sep 2002 11:09:42 +0200
Message-ID: <alkctm$g88$1@ctb-nnrp2.saix.net>

Think I have traced the problem. Though I'm not sure why it happens.

---

SQL1:
select count(*) from partitioned_table where col1='foo'

Result:
Fast Full Index Scan on the local parititioned indexes for col1 using partition iteration. On our system, that results in 3,023,064 blocks being read (according to TKPROF).

Elapsed time: 414 seconds

---

SQL2:
select sum(x) from
(
select count(*) X from partitioned_table partition(p1) where col1='foo' union all
select count(*) from partitioned_table partition(p2) where col1='foo' etc...
)

Result:
Index Range Scan per local paritioned index per partition. On our system that is 487 blocks being (again according to TKPROF).

Elapsed time : 0.42 seconds

---

Question:
Is this an Oracle 8.1.7 bug*? It it a known bug? Any suggestions how to work around this problem (it severely degrades the performance of certain queries)

Comments, suggestions and German beer appreciated.

--
Billy
* HP-UX 11 running 32bit 8.1.7

Received on Tue Sep 10 2002 - 04:09:42 CDT