Re: Please help designing partitioning free project

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_at_gmail.com> To: oracle-l_at_freelists.org 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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 29 2006 - 08:19:21 CST