Oracle Partition Tables

Hello we are running OneWorld on oracle 8.1.6.3.
We are thinking about partitioning the F0911 table.
Has anyone ever partitioned this or anyother OneWorld table in oracle 8i?
If so what table(s) and index(es)?
What type of partition did you use? Hash? Range?
What version of Oracle?
What were the reuslts?
Do you recommend this?

HI,

Did you ever manage to do this. We are just considering the same and I would be grateful for any pointers.

Thanx

Maria

Maria
We have not done this yet.
It is still being considered.
The problem we are having with the idea is what
filed(s) to partition on. We have so many different
queries constantly going against this table it imakes
it hard to decide what is the best choice of partions.

Let's keep in touch about this issue.

Tom Goehring


--- MariaG <[email protected]>
wrote:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OW&Number=54674
pointers.ThanxMaria


__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

Tom, Maria,
I talked to a DBA at a big client (huge F0911) not too long ago. From
what I understood he had partinioned F0911 by date in such a way that one
month's data would go to one tablespace and nexts month's data to the
other, alternating back and forth. The setup was to optimize I/O and the
results were quite positive.
(I am not really a DBA and I hope I didn't write any nonsense).

Thanks, Gerd

We are currently testing partitioning on the F0911 on the GL Date. We have just started testing so I don't really know anything else about it. I will let you know though when I find out more. Thanks!

Since the primary index does not have GLDate as the first parameter.
Partitioning on date does not make sense unless you recreate all indices
with GLDate being the first parameter which could be done but in that case
date queries would only improve. I'll give you an example and may be that
would make things clear. Let say you partition on date. You want to query
on doc type. Pretty simple query. It has to go through all the partition
to grab all the data which would kill your performance. You have 12
partition for a year but on the doc type query you have to go through 12
partitions. So you would win if all your queries were based on date and not
on doc type and other columns which unfortunately is not the case. I can
guarantee that any performance that someone gained from partitioning is not
because of partitioning is probably because of something else or they gained
performance which is probably based on date. If you are having issues with
amount of records in any oneworld table you need to improve performance from
the OneWorld side. You have to write better OneWorld apps that are
performance tuned. Out of the box JDE apps are not truely performance
tuned. You can only improve database to an extent because oracle already
has pretty fast data rate. You can enter millions records in an hour into
an oracle database which is pretty fast and truly you can write an OneWorld
app that does 500000 inserts per hour but you have to know what to do and
what not to do in OneWorld.
Thanks

Good point Gerd. I would add that before the partionitioning into 2 different tablespaces..make sure that datafiles corresponding to these tablespaces resides into 2 different hard drives( I/O peformance) Otherwise there will be hardly any I/O performance gain.

Re: RE: Oracle Partition Tables

So then, it would seem that if you partitioned by GL Date you could potentially see some performance benefits in GL Date related queries, but you would suffer performance hits when queries based on other data were run such as doc type.

Is that the general way partitioning affects performance? Does anyone else here have input on this matter. Much appreciated.