Re: Please some opinion in the free partitioning for Oracle standard Edition project

  • From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
  • To: juancarlosreyesp@xxxxxxxxx
  • Date: 14 Sep 2006 10:55:53 -0700
  • Date: Thu, 14 Sep 2006 11:55:05 -0600

This is simlar to the Partition View functionality that used to be
(maybe still is) available in Oracle, and which has some advantages over
the Partitioning Option

http://oraclesponge.wordpress.com/2005/08/20/partition-not-quite-pruning/
http://oraclesponge.wordpress.com/2005/08/21/more-on-partition-not-quite-pruning/
http://oraclesponge.wordpress.com/2005/08/22/execution-plans-for-partition-not-quite-pruning/

A lot of the features you are including may be redundant in the light of
the optimizer's ability to use partition views, so you might like to
look at that as well. for example you ought to be able to delete
efficiently from your global view as the optimizer can takeinto account
constraints on the underlying table in determining from which table the
rows need to be deleted.

Juan Carlos Reyes Pacheco wrote:
Hi, if you have some feedback please this is the proyect basically, I
hope be in this year
check it first please.

http://www.oracleboliviaspecialists.com/partition_scheme.bmp
<http://www.oracleboliviaspecialists.com/partition_scheme.bmp>

For every thing there is a function in a master package for every
partitioned table, for example there is a function to create addition
partitions

1. you have the table partitioned in several tables, every table is a
partition, every one has the indexes it needs
2. the information about range of every partition is store in the
partitions information table, the table name, and the range, and other
info,
   there is a header table for partitioned tables is not in the scheme,
sorry.
3. You can create global indexes, this are iot tables storing primary
key, the table partition where its stored and the rowid in that table
4. how you query?
   a. you have a view, union all of all tables, this give a transparent
full access
   b. you have a function table using the globalindextable
  c. you have a function table which queries only the tables partitiosn
you need, the primary key are parameter in the function table
5. To insert,update you use a temporary table, a funcion move data from
the temporary table to the proper partition, based on the partitions
information table
6. To delete there is function too, using the partitions table information

The main benefits are the fact
1.  to create historic data, you can create a new partition and move it
to a read only tablespace
2. you can improve the access in historic tables having a lot of
historic rows which are rarely accessed

This doesn't too simple, but the performance improvement in big tables
deserves the effort.
I either see really as a complex proyect  to program, is true there will
be lots of details, specially to add list partitioning, etc.
If you have some idea about improving this free project partitioning
concept, you are welcome.

:)

--
----------------------------------------
http://www.oracleboliviaspecialists.com/
<http://www.oracleboliviaspecialists.com/>

Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i

10 years of experience from Oracle 7 to Oracle10g and developer 6i


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


Other related posts: