Partition Views Again

From: Roberto Bruno <rbruno_at_centroin.com.br>
Date: 1997/05/31
Message-ID: <3390A751.93F82535@centroin.com.br>#1/1

--------------7F034237DCF33E98B6E3C406
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I still have the partition view problem described in my mail of 18/5 which was:

I'm having a problem using partition views in Oracle 7.3.2 under a AlphaServer 4100. What I have are some huge tables on a Data Warehouse that
I want to partition, but still presenting to users and applications as a

single logical table.

According to Oracle documentation, that is accomplished through creating a
check constraint on each partition to tell the RDBMS what is the partitioning rule. After that, an index should be created on the column or
columns referenced on the check constraint (for each table). Then, the view
should be created as a series of select * ... union all.

Well, I did all that and got no partition access. The optimizer tried to

make full scans in all tables involved, even though it was perfectly defined which ones could or could not have rows satisfying the query. I made several testes, with 2 to 5 different partitions and different hints,
and nothing seemed to work. Does anyone have any idea on how to make this
work out ?

If you can, please send ma an answer with a copy to Roberto_Bruno_at_south-america.notes.pw.notes.com (yes, with the capital letters and all - that's a Lotus Notes address).

I have the partition_views_enabled = true in init.ora and am using cost-based optimisation with statistics for all partitions. It seems that my constraints are too complex for Oracle to recognize them as partitions. Is there a limit to what kind of constraint I can put, or a known bug with partition views under version 7.3.2 ? I really would get a much-needed performance boost with this feature, since it would avoid scanning a multi-million record table.

--------------7F034237DCF33E98B6E3C406
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML><BODY>

I still have the partition view problem described in my mail of 18/5 which was:
 
 I'm having a problem using partition views in Oracle
7.3.2 under a
 AlphaServer 4100. What I have are some huge tables
on a Data Warehouse that
 I want to partition, but still presenting to users
and applications as a
 single logical table.
 
 According to Oracle documentation, that is accomplished
through creating a
 check constraint on each partition to tell the RDBMS
what is the
 partitioning rule. After that, an index should be
created on the column or
 columns referenced on the check constraint (for each
table). Then, the view
 should be created as a series of select * ... union

all.</FONT>

 
 Well, I did all that and got no partition access.
The optimizer tried to
 make full scans in all tables involved, even though
it was perfectly
 defined which ones could or could not have rows satisfying
the query. I
 made several testes, with 2 to 5 different partitions
and different hints,
 and nothing seemed to work. Does anyone have any
idea on how to make this
 work out ?
 
 If you can, please send ma an answer with a copy
to
 Roberto_Bruno_at_south-america.notes.pw.notes.com
 (yes, with the capital letters and all - that's a
Lotus Notes address).
 
 I have the partition_views_enabled = true in init.ora
and am using
 cost-based optimisation with statistics for all partitions.
It seems that my constraints are too complex for Oracle to recognize them as partitions. Is there a limit to what kind of constraint I can put, or a known bug with partition views under version 7.3.2 ? I really would get a much-needed performance boost with this feature, since it would avoid scanning a multi-million record table.

</BODY>
</HTML>

--------------7F034237DCF33E98B6E3C406-- Received on Sat May 31 1997 - 00:00:00 CDT