Secondary Table

[Former Member]

Can you plese tell me What is secondary table? and what is candidate keys?

[RichHeilman]

Not secondary table, secondary index on a table. A secodary index on a table is another access path for the table specified by different fields. What this does is helps with performance when you use these fields in your WHERE clause of your SELECT statements.

Regards,

Rich heilman

[Former Member]

Key is nothing but Primary key (primary index) /secondary key(secondary index) .

primary key in the table remains unique thruout the data base table.

wenevr u check primary key while creating a dbtable , the system internaly will create aprimary index for that primary key. i.e Pkey and Pindex are one and same.

secondary key is craeted to improve teh performance of select statements.

suppose if there is a situation where we r writing select statement in which in where condition we are considering non primary key, then secondarindex(key will be craeted ) to that field in the db table and this will improve performance.

Just go to SE11 and create an index in customer name range (starting with Z or Y).

What do I need to keep in my mind when creating the secondary index?

Just make sure that there are not too many indexes on this table already.

Also, do it at a time when the system load is low because this will slow down the system if you have too many records in the table.

When you activate the index, it will automatically activate the database level index also. But in rare cases you may have to ask the DBAs to do it manually.

Most importantly, if you are creating this index to make one report of yours execute faster, do not do it. It is not worth creating this index for one report.

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb47446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb2d446011d189700000e8322d00/frameset.htm

Thanks

Seshu

[Former Member]

Hi Anil,

The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database. When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated.

Check these links.

http://help.sap.com/saphelp_470/helpdata/en/cf/21ea43446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_470/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_47x200/helpdata/en/cf/21ec84446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_470/helpdata/en/cf/21eb6e446011d189700000e8322d00/frameset.htm

The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

You can create further indexes on a table in the ABAP Dictionary. These are called secondary indexes.

PURPOSE:

This is necessary if the table is frequently accessed in a way that does not take advantage of the sorting of the primary index for the access. Different indexes on the same table are distinguished with a three-place index identifier.

Generally it is used when field which u want is not a primary field .

Advatange:

1. faster access to data when field is not a primary key field.

Disadvatnage:

1. Slower down database table inset, update delete commands.

Regards

Aneesh.

[Former Member]

Maybe you mean a secondary index table - like BSIS. This is a separate table that has some fields from both BSEG and BKPF, but since you can't create a secondary index on BSEG (a cluster table), this table has the general ledger account number as it's key and allows you to get BSEG data quickly.

Rob

[ferry_lianto]

Hi,

The table/view is defined in the Dictionary. A view comprises a primary table and secondary tables which have a foreign key relationship to the primary table.

Secondary tables can be:

- Dependent tables: These are auxiliary primary tables and have the same key as the primary table. All fields can be maintained directly. The secondary tables are linked by a foreign key with dependency factor "ID" and cardinality 1 or C, except for text tables, which must have dependency factor TEXT. The logon language is assigned to the language key when you save.

- Referred tables: The primary table contains fields which belong to the referred secondary table. The secondary table can have other key fields which are not known to the primary table. The secondary table fields cannot be maintained, they can only be displayed. The referred secondary tables are linked by a foreign key with dependency factor OBL or OPT and cardinality CN or N. All other secondary tables which depend on the referring table, are also only referred tables for the primary table.

Regards,

Ferry Lianto

[Former Member]

Hi

The creation of table/view maintenance dialogs is described here. Maintenance dialogs can be created for SAP or customer tables or views.

There are two steps:

Define table/view

if the table or view does not yet exist.

Define maintenance dialog

Define table/view

The table/view is defined in the Dictionary. A view comprises a primary table and secondary tables which have a foreign key relationship to the primary table. Secondary tables can be

Dependent tables: These are auxiliary primary tables and have the same key as the primary table. All fields can be maintained directly. The secondary tables are linked by a foreign key with dependency factor "ID" and cardinality 1 or C, except for text tables, which must have dependency factor TEXT. The logon language is assigned to the language key when you save.

Referred tables: The primary table contains fields which belong to the referred secondary table. The secondary table can have other key fields which are not known to the primary table. The secondary table fields cannot be maintained, they can only be displayed. The referred secondary tables are linked by a foreign key with dependency factor OBL or OPT and cardinality CN or N. All other secondary tables which depend on the referring table, are also only referred tables for the primary table.

You can display the view structure graphically with the View hierarchy struct. function in the Utilities menu.

To create a table/view:

Choose view type Maintenance view.

All primary table key fields must be in the view definition.

The client field must be included for client-dependent tables/views. The field is not displayed in the maintenance dialog.

The language key must not be included for secondary text tables. The logon language is automatically used in maintenance.

The key fields must be defined before the function fields. Function fields and key fields must not be mixed.

Fields of a referred table cannot be maintained and have the maintenance attribute 'R'.

Each field can have a maintenance attribute.

You can specify the maintenance status of the entire table/view with the Maintenance status option. You can:

read only

read, change, delete and create (default setting)

read and change

read and change (time-dependent views)

In time-dependent views, the key comprises a time-independent part and the validity range. Choosing read and change (time-dependent views) restricts maintenance to changing and creating the validity range of existing keys.

Define maintenance dialog

You must specify the following parameters in the maintenance dialog definition:

Function group: the function group in which the tables/view-specific maintenance dialog components are generated. The function group is created if necessary.

Authorization group: the users who are authorized to maintain the table/view contents.

Maintenance type: one or two-step dialog. One-step dialogs comprise only an overview screen containing all fields. In two-step dialogs, only the key and text fields with a length of more than 20 characters are displayed in the overview screen. All fields are offered in the detail screen.

Maintenance screens: the internal number of each maintenance screen. You can get possible values in a search function.

Recording routine: Specify whether and how the table/view contents maintained with the dialog are put in a transport.

Result

The maintenance dialog is generated after all values have been entered. If there are no errors, the dialog can be used immediately to maintain table/view contents.

check this links

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ed2d446011d189700000e8322d00/content.htm

Reward all helpfull answers

Regards

Pavan

[Former Member]

Hi Anil,

Primary key and foriegn key are called candidate keys in sap-abap. in oracle they are call integrity constraints(including unique key, etc.,). You can see normalization in oracle you can completely understand these table and key concepts.

Secondary Table might be the foriegn key referenced table (but actually, there is no name call Secondary table in sap) So, it should be Secondary Index. Primary index will generate the system when you activate your database table for primary key fields(which should not allow the duplicate and null values). Secondary Index, explicitly you have to create for non primary key fields as per client requirement and program performance. Foriegn key table is nothing but dependent table for primarykey table(Child table which refers the parent table like parent->property<-Child)

Reward points help ful.

sekhar