Re: Please help designing partitioning free project

  • From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 29 Mar 2006 06:19:21 -0800 (PST)

Have a look through someof theearly documentation on "Partition Views", the 
precursor to what we know as partitioning. It's pretty viable, and in some 
cases performance is better than on regular partitions because the optimizer 
can generate a different plan for accessing each of the underlying tables 
("partitions") and considers "partition-level" statistics for queries that 
access multiple "partitions".

http://oraclesponge.blogspot.com/2005/08/partition-not-quite-pruning.html
http://oraclesponge.blogspot.com/2005/08/more-on-partition-not-quite-pruning.html


----- Original Message ----
From: Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, March 29, 2006 6:28:02 AM
Subject: Please help designing partitioning free project

Hi, I had decided to start a free project to desging partitioning,
this is always free not open source.

I think the concept is too simple
And I askyou please to pointme some mistake or improvement in the design

The idea is to create a partitioning package, for a simple good
reason, you don't
have this feature on standard edition, and hoenstly to setup a simple
partitioning,
is more simple that it seems.

Obviously this is not the same as using partitioning from Oracle

This are only the first two ideas I had now.

1)
The first design idea is the following, this is not for a high load tables

1. You have a temporary table where you insert the data.
2. You have a table that structures the data by ranges, this is table
name, lower range, upper range.

The idea is
1. you insret to a temporary table
2. you call a functoin that moves IN ONE STATEMENT, the data from
temporary tables, to the partitioned tables
3. you have a function table, to get data when you query, the function
table helps you to query only the tables in the range of you query

For global indexes, you can create a table storing tablename, value
(or hashvalue), which are indexed.

2)
The second idea could be to create a view, with an instead trigger,
with a similar functionality
I was thinking about the idea this view could call a function table,
setting the values for the query in package variables.

Problems:
-The insertion is done in two steps, I'm going to check how works on
10g partitioned views, and if using istead of trigger I could avoid
this additional step.

-The way to query is different, you don't simple set a where, you have
to set it specifically

Any comment? :)
Thank you
--
//www.freelists.org/webpage/oracle-l






Other related posts: