SECONDARY INDEX

[Former Member]

HOW MANY SECONDARY INDEX WE CAN CREATE IN DATA DICTIONARY?

WHAT IS THE PURPOSE OF SECONADRY INDEX.?

KINDLY HELP ME

[BGarcia]

Hello Saritha,

Don't know the limit but you should read carefully this:

http://help.sap.com/saphelp_sem40bw/helpdata/EN/cf/21eb20446011d189700000e8322d00/frameset.htm

Especially the topic (bottom link): 'What to Keep in Mind when Creating Secondary Indexes'

Hope that this helps you.

Regards,

Bruno

[Former Member]

What is pooled table ?

what is the procedure to create pool table, with example?

What is cluster table,with example ?

what is the procedure to create cluster table,with example?

[Former Member]

hi saritha,

pool table are those which stores internal control information along with the data of several database tables,

i.e., screen sequences,

program parameters,

temporary data,

continuous texts such as documentation.

please dont confuse about transparent tables because they are meant for comercial relevance.

so u need some field names for a pool table to store control information along with the main comercial data.

Therefore field for pool table TABLENAME -

VARKEY- to store information of all key fields of pooled tables as string

DATALN-length of string in VARDATA

VARDATA-Contains the entries from all data fields of the pooled table record as a string, max. length n depends on the database system used.

[Former Member]

creation of pool table:

In the initial screen of the ABAP Dictionary, choose Utilities ® Further Dictionary Objects.

A dialog box appears.

Select the object type Table pool/cluster and enter the object name. Choose .

A dialog box appears in which you must specify if it is a table pool or a table cluster.

Select the required object type and choose .

The maintenance screen for table pools/clusters appears.

The necessary entries will have been made automatically for the fields for table pools since a table pool has a fixed structure. You should not change these standard settings if you can avoid it.

The structure of a table cluster is also mostly fixed. Certain fields are therefore proposed when the table cluster is created. You can adjust this proposal to your requirements, for example by inserting further key fields. However, make sure you conform to the structure necessary for a table cluster.

Enter an explanatory text in the field Short text.

If necessary, select the activation type of the table pool/cluster with Utilities ® Activation type.

Create documentation about the table pool/cluster with Goto ® Documentation.

This documentation should describe what the table pool/cluster is used for. The documentation is also output when the table pool/cluster is printed.

Go to the maintenance screen for the technical settings by choosing Goto ® Technical settings.

In contrast to the table maintenance screen, you can only define the size category here. All other attributes of the technical settings are preset.

Activate the table pool/cluster with .

[Former Member]

Tables assigned to a table pool or table cluster are referred to as pooled tables.

[Former Member]

Hi Saritha:

A secondary index on a transparant table is SAP's name for what everyone else in the database world just calls an index. I'll give a quick rundown of primary keys first, and then go to indexes:

When you create a database table you should always define a "Primary Key". The primary key of a table is the set of columns (it may be just one column) that uniquely identify a row in the table. Example. Let's say you have a table called EMPLOYEE with columns (EMPLOYEE_ID, LAST_NAME, FIRST_NAME, DEPARTMENT, CUBE_NUMBER, PHONE_NUMBER).

The primary key for the table would be just the single column EMPLOYEE_ID. That means that if you know the value of an Employee_ID you can use that value to grab exactly one row from the Employee table. If people access the Employee table using EMPLOYEE_ID, the retreival will be very fast because the database engine just looks in the index for the ID, and from the index entry is told exactly where to go in the table to get the whole row.

But most peole don't know everyone else's employee_id. If the table just has the primary key, and someone says "show me all the employees with last name "Pyla", the database will look at every row in the Employee table to bring the correct rows. Looking at every row takes a lot of time and is called a table scan. It is best to avoid table scans if possible.

If you put an index on the LAST_NAME column, then the query SELECT * FROM EMPLOYEE WHERE LAST_NAME = 'Pyla' (probably) won't result in a full table scan. Obviously, the index on LAST_NAME would have to be non unique, because there could be more than one employee with the same name. The database will look in the index and see that there are a total of (let's say) three rows with last name 'Pyla'. The database will go straight to those three rows, and retrieve them very quickly.

In the previous paragraph I put the word "probably" in parenthesis when talking about avoiding a full scan. That's because sometimes the engine will still do a full scan, even though the column is indexed. If the index indicates that a significant pecentage of rows will satisfy the WHERE clause, the optimizer may decide it is faster to scan the whole table anyway. Example: Say the EMPLOYEE table contained a STATE column. If your company only has offices in three states, and the employees are pretty evenly distributed, then the index will report that about a third of the rows are in each state. If the engine has to bounce all over the table checking every third row, it is faster to do a sequential scan.

So indexes don't always solve data access problems. There is also the issue that indexes take up disk (but that isn't too big of a deal usually). A bigger issues is that every index on a table does slow down INSERT operations (usually marginally, but it can matter), and each index is another db object to manage.

Indexes can make a huge positive difference if they are applied judiciously, but don't use them unless they are needed.

Thank You,

Larry Smith

[Former Member]

Purpose of secondary Index?

To support a where clause on non primary key fields we will create secondary index.

[Former Member]

HI,

1. There is no maximum limitation to the no of secondary indexes.

2, These secondary indexes are useful when you want to obtain the data from Db tables without using primary key fields. I t will sppeed up the process of obtaining.

reward if helpful

rgds

Umakanth

[Former Member]

Hi Saritha,

You can create upto 15 secondary indexes only.more than 15 you are not able to create.to speed up the select query we will create secondary index.by default all primary indexes acts as primary index.we can create upto 16 primary keys only.so we can create only 16 primary indexes (in the sense primary keys).

please reward if helpful.