We are analysing the use of Oracle8 partitioning,
we plan to experiment it on finance table TFGLD106.......(more than 5Gbytes on our system).The difficulties is to find the right axes for partition.
Can anybody help ?

Well, I have no practical experience with partitioning myself (yet) but I think the main question here would be to determine the type of partitioning you need:
range partitioning
hash partioning
composite key partitioning
Do you want to partition because of performance issues, maintenance issues, etc?

The range - or - partition key based form would be the simplest but for that your data needs to be distributed evenly according the partition key value intervals that you choose.

e.g. for tfgld106 the Transaction Type (o$typ) would be an obvious choice to select as partition key I guess. But are your transactions evenly distributed across the transactions types?
1 select distinct(t$otyp) "Trans. Type", count(t$otyp) "# of Records"
2 from baan.ttfgld999255
3* group by t$otyp
SQL> /
Trans. Type # of Records
--------------- ------------
AA 3241
001 37076
002 5710
004 42258
005 51614
016 3102
017 5708
018 17114
031 306
032 2774
037 7346
040 5540
ACP 7293
ADT 6
ALC 506
APR 11131
AUP 84
AUR 78
CZZ 502
CVP 92
CVR 67
DZZ 24665
JMR 14
JNL 827
JNM 1657
LAP 28
MSC 2504
MSI 696
OPB 402
PAP 7196
PCR 590
PUR 7073
SCO 343
SCR 2430
SLS 42934
SZZ 2341
VZZ 10
XXX 2

38 rows selected.

Above results would not point to an even distribution per transaction type. Options here I think would be to base your partition key on groups of transaction types, to use a different partition key or to use hash/composite key partioning.

Thanks for your quick answer.
The goal of partitioning for us is to improve performance (and optionnaly to help for maintenance).
The biggest performance problems we have are on sessions tfgld3206m000 (table tfgld106) and tfgld4201m000 (table tfgld410).
I don't think partitioning tfgld106 on Transaction Type (o$typ) would help the concerned session (and it's not an even distribution). As we are still using Oracle 8.0.5, the only type of partitioning available is range partitioning. So I thought about partitioning tfgld106 on vyer and vprd (year and period). What do you think about it?
I have no idea of how Baan access these tables for those sessions and it's the key of the problem (and of the partitioning). If you know that, I would be glad to share knowledge.
Thanks.

I don't have much knowledge in the finance area so I can't really say how these sessions work. You are right that is the key to the problem. Assuming you would take t$vyer and t$vprd as elements of your partition key then I have the following concern:

how much of the processing that occurs in these sessions is happening in the same Tax Period and Year?

If this is the case then the (t$vyer, t$vprd) would be a bad partition key as I/O would still be concentrated on a small group of partitions?

I would like to hear the opinion of a finance expert on this.

Hi,

I'm not an expert in Finance.
I would like to know if your largest finance tables is in the same tablespace as all your other baan tables. I believe if you place these large tables in it's own tablespace without using oracle partitioning might give you a significant increase in performance. It allows you to monitor the growth of this tablespace and take necessary actions when due.

To patvdv
I don't share your thoughts. For me, partitioning tfgld106 by Tax period and Year would help if the session work essentially on same periods and then on few partitions. I think it can improve performance as all others partitions would be eliminated by optimizer and also because there's no index on these columns.
Anyway, I will experiment on next weeks and actually, my point of view is only based on Oracle documentation ("Partition elimination can provide remarkable performance improvements, by using range and composite partitioning in combination with predicates in WHERE clauses. This key optimization intelligently removes from consideration whole subsets of partitions that need not be examined by queries or DML operations." )
To zaidlaz
the largest table is TFGLD106 and it's alone in its tablespace. All others tables are in several tablespace (one per company). I don't know if we can do better and tha's why we want to use partitioning.
Thanks to both of you.

Let us know the outcome of your experiments!

VERSION(S):
Oracle database

SITUATION IDENTIFIED:
Reconfiguring tables, using session ttaad4225m000 or bdbpre6.x and bdbpost6.x, a table drop can take three hours and more for a single table of a size of about 200 MB.

SITUATION DESCRIPTION:
The maximum number of extents for tables or indexes exceeds 20. It is recommended by ORACLE to have a maximum of 20 extents per index or table. Exceeding this number a considerable performance impact can be observed as a consequence. You find a script under point SOLUTION DESCRIPTION how to determine the number of extents for your tables and indexes. With the information from this script you can modify your ora_storage file as needed.

1. Recalculate your new next extents: take actual size multiplied by the actual number of extents
2. Increase the extent size according to the size of the specific table.

EXAMPLE:
Baan query shows # of extents = 112: BAANDB TTFGLD410100 Extents: 112
Current size of extent is: 64 KB

Calculation: 112 * 64 KB = 7186 KB

Old ora_storage:
*:*:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANDAT INITRANS 3 storage(initial 64K Next 64K)
*:*:I:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANIDX INITRANS 3 storage(initial 64K Next 64K)


Changes for new ora_storage (place the change before the * lines or they are not read):

tfgld410:100:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANDAT INITRANS 3 storage(initial 7186K Next 10M maxextents unlimited)

*:*:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANDAT INITRANS 3 storage(initial 64K Next 64K)
*:*:I:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANIDX INITRANS 3 storage(initial 64K Next 64K)


Be aware that the change in the ora_storage only takes effect when a table is newly created. In that moment the ora_storage is read. Nevertheless, it might be useful for your system to change your ora_storage only for the Reconfigure tables and have another ora_storage for the productive environment.

N.B.:
The examples and indicated numbers are only meant for demonstration purpose. As all systems behave differently the actual values for your respective system(s) need to be verified on that respective system.

SOLUTION DESCRIPTION:

Create the following SQL statement in Oracle:
SQL>
select owner, segment_name, segment_type,
count (extent_id), sum (blocks)
from dba_extents
where owner not in ('SYS, 'SYSTEM')
group by owner, segment_name, segment_type
having count (extent_id) > 20 ;

The extent issue is not a problem anymore in Oracle8

Oracle Database Clustering will help to resolve the performance related issues. Basically u can have different database clusters running for tfgld106 and tfgld410. The various clustering algorithms like nearest neighbour clustering, hierarchial clustering will help to increase performance rapidly.

I have given a lot of thought to partitioning the tfgld106 table too and feel that, for us, the partition would be on FPRD (Financial Period) and FYER (Financial Year). It tends to be a consistant number of records each period. I have moved the table into its own tablespace because of its size. I'm looking forward to trying it to see how it affects performance against this table, especially for queries run against it. Right now we have 44 periods loaded in it which adds up to 17 Gig worth of data not to mention the additional space that the indexes take up on top of that.

I partitioned tfgld106 and tfgld410 on FPRD and FYER (financial period and year). Results are quite poor : in some case, performance is better (up to 10% faster) but it also can be slower (up to 20%) !
Then I decided to partition the indexes of tfgld106 and results were much more interesting : all sessions were faster (from 5% to 100%) !!!
The problem is that I had to add the partitioning columns (FPTRD and FYER) at the end of each index (because for a unique index, the partitioning key must be a subset of the index key).
My problem now is that Baan administrators say that indexes cannot be changed in Baan.
Is it true ? What sort of risks we run if we change indexes ?

I was reading this older line of questinion on Oracle Partitioning. What was your final outcome from partitioning oracle tables? How did you address the partition with the multiple indexes defined in Baan in relation to your partitions? This part is a little 'fuzzy' for me. I'm experimenting with creating partitions on some of our larger tables.

Please advise.