Replicating List Partitioning on Oracle 8i

[clarcombe]

Hello

We have a large table which needs to be partitioned on a 3 digit product code.

However, from the research I have been doing I understand that 8i cannot do this and my only options are range and hash partitioning.

We are trying to ensure that we have equal size partitions for these product codes bearing in mind that some product codes produce huge volumes of data and others very minimal so we are looking at something like

partition 1 (most frequently used) 324, 307
partition 2 308,315,386
partition 3 316,399,374,392

etc

Am I going to be able to do this at all ?

Many Thanks

[hrishy]

Hi
It would be helpful if you can post tabel structures and some relevant info.to eanble us to provide valuable inputs

regards
Hrishy

[mike9]

what do you want to improve by list partitioning?
what kind of application is it (oltp/dwh)?
how big is your table?
how do you acces to the data in this table?
do you know that 8i is not supported any more?
why do you want to have "equal size partitions"?

[clarcombe]

There are 3 tables I want to partition currently contain the following
ASSIN_VERS 147 million rows
ASSIN_CONTR 269 million rows
ASSIN_EVT_ACTUARIAL 326 million rows

Each table structure has about fields of which the field I want to partition on is

EVA_CD_PROD_CIAL VARCHAR2(5),

Currently this field is not indexed.

what do you want to improve by list partitioning?
We are currently using Datastage to perform Bulk loads on these tables and we are trying to improve access times to the tables and reduce contention and increase parallel access as many applications access these tables.

what kind of application is it (oltp/dwh)?
Dwh

how big is your table?
see sizes above.
Current Tablespace definition for ASSIN_VERS
TABLESPACE DWH00DATA1024M
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1024M
NEXT 1024M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;

how do you access the data in this table?
The data is created by Datastage and loaded using shell scripts calling bulk loads.
The data is read by downstream applications, Business objects and other Datastage jobs

do you know that 8i is not supported any more?
It is not that 8i is not supported just that list partitioning doesnt exist in 8i. It was added in 9i

why do you want to have "equal size partitions"?
It is not so much equal partitions but we have several product lines that can be stored in 1 partition and some contracts that take up a whole partition.

[mike9]

The easiest way to do that would be to create a range partitioned table with one range partition for each key.

The best solution would be to migrate to 10gr2 because it is a supported version and because, compared to 8i, it contains a lot of new features which are really useful for a DWH.

[clarcombe]

The easiest way to do that would be to create a range partitioned table with one range partition for each key.

There are almost 200 product codes. I assume have to add a new partition every time we added a product code ?

How would this look syntactically. Can you give me an example ?

The best solution would be to migrate to 10gr2 because it is a supported version and because, compared to 8i, it contains a lot of new features which are really useful for a DWH.

Unfortunately migrating to future version of Oracle is not an option at this point. They have a huge Oracle installation and the regression testing would take years. I believe they have uncovered some big issues as well.

[mike9]

There are almost 200 product codes. I assume have to add a new partition every time we added a product code ?

Not if it is ok for you to have more than one product per partition and if there is a MAXVALUE partition.

Unfortunately migrating to future version of Oracle is not an option at this point. They have a huge Oracle installation and the regression testing would take years. I believe they have uncovered some big issues as well.

Related to the migration to a new Oracle version I would keep in minde that:
- 8i is not supported anymore. This means that if you discover a new bug or got a problem you may not always get support from Oracle
- As longer (more versions in between) you wait for the migration as complexer will get the regression testing.
- Nice and usefull new features are not available (e.g. improved query rewrite, impoved MV refresh, table compression, new partitioning, improved partition pruning, new PGA management, new groupping algorithm...)

[marist89]

Assuming you have valid reasons (which I'm not convinced of), I would create a lookup table that contained numeric codes that grouped the product codes together. I would then take this field and partition by it. For example:

PRODUCT_CODE_LOOKUP
-------------------------
ProductCd   PartKeyID
324               99
307               99
308               199
315               199
386               199
316               299
399               299
374               299
392               299

I would then place this field in your main tables and use it as your partitioning key:

CREATE TABLE myTable (
   partkey,
   productcd,
   blah,
   bla)
partition by range (
   values less than 100...
   values less than 200...
)

Then in the Transformation phase, you would join to pickup the partkey.

[clarcombe]

I did think of adding a partitioning key to the tables but that was rejected.

Assuming you have valid reasons (which I'm not convinced of), Do have doubts about the partitioning or the way of partitioning (in equal groups) ?

I am trying to reduce the table size and optimise the access of those parts of the table that are frequently read and written to. The product code is the only way it can be split. Is this not the way to go ?

[tamilselvan]

Have you thought about HASH PARTITIONING?

Tamil