topic Re: database table creation in Application Development and Automation Discussions

database table creation

Former Member — Fri, 12 Feb 2010 00:11:33 GMT

Hi friends,

need some help on this.

I have to store data in a data base table. basically the table will have millions of records.

I have a program which updates/creates/deletes the data in this heavy table in background job.

But strong point i want to mention here is its very very huge table ( like 900,000 is minimum records and it can increase 4 to 5 times of 900,000).

I even have a FM which reads the data from this table which some conditions .

so what is the best way in this scenario? what kind of table shall i create ? transparant? pooled/cluster? secondary indexes? etc...

what is the best approach so that i may not come accross performance issues..

thanks in advance

Re: database table creation

sivakrishna_boddapati — Fri, 12 Feb 2010 07:18:44 GMT

Hi,

we can create transparent table size up to 120 000 000 to some 50 billions so that, your required records are only 900000 records ,

but performance of retrieving data is depends up on making of index that is secondary index . so create a transparent table with size category 9.

cluster tables :

>maintains prinary key reationship.

>these are less no of cluster table which had large no of records

pooled tables :

> doesn't maintain any primary key relationship

>these are more no of pooled table which had less no of records

we cant access these tables from outside sap.

Edited by: sivakrishna boddapati on Feb 12, 2010 1:11 PM

Re: database table creation

Former Member — Fri, 12 Feb 2010 10:42:45 GMT

Hi,

There are two ways you can create table which suits your requirement.

1.You can analysze the data and can splits the tables into 2 or more (whic is called as Normalization) and then maintain foreign key relation ships between them.

This solves your problem w.r.t. Data volume.Hence then data stored in the database drastically decreases.

2.You can maontain a single table but choose the Size category to the maximun which suits to ur requiremnet.

Hope this helps you.

Regards,

Rama.P

Re: database table creation

Former Member — Tue, 16 Feb 2010 22:23:58 GMT

So are you saying no reason to create a pooled/cluster table?

if i want to make secondary indexes, do i need to make it to each field in that table? sorry, i m new to ABAP so...

if i want to go with pooled/cluster tables, is it possible to update/modify/delete/insert records the same way we do to transperant tables? like INSERT,MODIFY,UPDATE,DELETE statementss???

how to create and see the data in pooled/table? can u explain me this scenario aswell>

Thanks,

Re: database table creation

Former Member — Wed, 17 Feb 2010 08:40:33 GMT

Create a transparent table with dataclass APPL1 and size category 6 and create an secondary index to the table according to your select statment where condition,which retrieves data in your program which is updating or deleting data from this table.

Thank you

srinivas

Re: database table creation

Former Member — Thu, 18 Feb 2010 06:56:17 GMT

Hi,

You can do everymodifications, Insertions, Deletions and updates same as in transparent tables in pooled\clusterd tables also.

Regards,

pavan.

Re: database table creation

Former Member — Fri, 19 Feb 2010 00:27:24 GMT

which will give the good performance, cluster table or tarnsparent table with secondary index. ?

Also when i create a secondary index, i can give more than one field of the table to that index? how does this work?

when i say i have 5 fields in table with 3 key fields. Now i created 1 secondary index with all those 3 key fields, how does the select query works?

what is the use of secondary indexes? do i need to select all the fields for secondary index, which are used in my wehre condition of select?

can u pls give me some explaination on this secondary indexes?

thanks and appreciate your answers

Re: database table creation

vinod_vemuru2 — Fri, 19 Feb 2010 06:19:47 GMT

Hi,

I think cluster and pooled tables won't support the secondary INDEXes. You can create secondary index only for transparent table. So go ahead with transparent table and create the secondary index based on the fields that is mostly used in the where clause of the selects in your FM.

Too many secondary indexes is also not suggested and it has serious performance implications when you update the table with data because when ever a record is created changed, pointers of secondary indexes also updated.(index works on pointers concept of C language where we directly have the address id of a record in the database)

Thanks,

Vinod.

Re: database table creation

Former Member — Fri, 19 Feb 2010 11:39:16 GMT

Hi Mr Singh,

When it comes to DB tables, whenever you want to optimise database access it is highly recommended that you use transparent tables, preferably with secondary indexes.

Indexes work like this:

Whenever data is stored in the DB, a default index is created for all the entries. This is done based on the primary key.

However there may be instances where you execute specific queries on the DB using a specific set of fields, which are not the key fields.

In this case, you create a secondary index for the DB using the above-mentioned fields.

This way, every time the DB is accessed through SELECT statements using these (and only these) fields, data will be retrieved much much faster.

Please note that indexes are physically maintained by the system and not calculated at runtime. Hence, if your table gets updated frequently, the system must not only update the table entries, it must also update the secondary index. This is why you must be extra cautious and create secondary indexes prudently.

If you do not want a secondary index, you can completely skip this. This is especially true if most of your database queries involve your primary keys.

Check this link for understanding more on this topic:

Hope this helps! Please revert if you need anything else.

Cheers,

Shailesh.

Always provide feedback for helpful answers

Re: database table creation

Former Member — Mon, 22 Feb 2010 22:11:42 GMT

I am having issues in creating cluster table.

I created a table cluster It came up by default with fields: VARKEY ( key field ), PAGENO( Key field) , TIMESTMP, PAGELG, VARDATA. ( I donno what and why these fields are used ).. can someboday tell me??? what these fileds are for?

Acitvate this table cluster.

Created a transperant table and created fields FIELD1( KEY FIELD), FIELD2(KEY FIELD), FIELD3( KEY FIELD) AND FIELD4( NON KEY FIELD).

NOW utilites->table category, i selected table cluster and in delivery and maintaince entered the table cluster which i created before.

Now when i check it , this is throwing error " Generic key field is missing from logical cluster VARKEY "

what does this mean?

I am confused why those defaults fields populated in table cluster?

can someone explain me? i even saw BSEG, and there are some custom fields are also entered in the table cluster ( RFBLG ).. BUKRS

BELNR

GJAHR.

what is the use of giving those BUKRS

BELNR

GJAHR fields in table cluster ?

I think something related to the size is also been seen, like the size is exceeded when i tried to enter some more fields FIELD1,FIELD2, FIELD3 in table cluster? what is this mean??

replies will be appreciated.

thnks,

Re: database table creation

Former Member — Tue, 23 Feb 2010 00:08:09 GMT

Friends,

Somehow I am able to activate table cluster and cluster table.

I have given FIELD1 and FIELD2 in table cluster( not sure what difference it makes if i give , and what is the use of giving field names here). same names which i used in cluster table.

I am not understanding what is the use of giving cluster table fieldnames in table cluster??????

ALso, I am able to use select, insert,delete statments on this cluster table. but when i am trying to use modify table using wa tranporting FIELD1, nothing is getting updated.

I am trying to modify a value of a filed in the table( this is not happening).

Is this is the way we use to retrienve . modify.insert delete data from cluster tables? or any other way?

can u provide inputs

thanks