hash table

[Former Member]

what is hash table and how it wrks?

wht is secondary index?

[h_senden2]

use the search functionality of this forum, and you will find a lot of info on this subject.

Hans

[former_member194613]

a hashed table is a special internal table type, where the table key is processed by the hashed function to find directly the wanted line.

=> very fast, independent of table size, but works only for full table key not for ranges

read ABAP help, and wikipedia for hashed functions

Secondary Index:

On database, primary key consists of table key fields, if accesses must be fast which use other fields than the table keys then other indexes must be defined.

read ABAP help, and wikipedia for SQL

Siegfried

[Former Member]

hi bhabhani,

Hashed table is useful when your have to work with very big internal table and to read it with

"READ TABLE WITH KEY ..."

The time access is constant !

Hashed Table:

"Defines the table as one that is managed with an internal hash procedure. You can imagine a hashed table as a set, whose elements you can address using their unique key. Unlike standard and sorted tables, you cannot access hash tables using an index. All entries in the table must have a unique key.

Access time using the key is constant, regardless of the number of table entries.

You can only access a hashed table using the generic key operations or other generic operations (SORT, LOOP, and so on). Explicit or implicit index operations (such as LOOP ... FROM to INSERT itab within a LOOP) are not allowed."

reward if helpful..

[Former Member]

Hi,

A hased table is the kind of an internal table which is closest to a database table.

The fundamental difference is that the entries in a hashed table cannot be accessed through an index.

For example, the following statement is not applicable to a hashed table -

READ TABLE ITAB INDEX <n>.

If you notice, even in case of a database table, let us say, SPFLI, we cannot say, "get me the 10th record of the table SPFLI". The entries can only be retrieved by specifying a "key". So you would have something like -

READ TABLE ITAB WITH KEY....

This is similar to a SELECT statement that we use for a database table. We specify the key fields in the WHERE condition.

Obviously, a hashed table has to have a KEY, and cannot have duplicate entries for the key fields. Which is again just like in case of a database table.

And finally, even though it is only used rarely, in some cases using a hashed table can improve the performance significantly - the reason being that the time required to fetch any record from a hashed internal table is constant.

SECONDARY INDEX.

They are generally used for faster access.

EG.

In a table there are 10 fields.

1,2 are primary fields (primary index)

2. But the table is queried many times

on field number 6 (eg).

So we can create a NEW Index

(Secondary index)

only on that 6th field.

3. Due to this,

the sql will become faster

because NOW

the database will search on the

basis of secnodary index (made on 6th field)

regards,

Omkar.

[Former Member]

hiiii Omkaram Yanamala

i gt ur response its beautifully described .

still can u clarify me

how we can create a secondary index ?

[Former Member]

Hash Table each entry has a unique key, which has a constant response time for each key access, regardless of the number of table entries. This is very different then a Standard, or Sorted, Table which uses a standard index.

itab type hashed table of <> initial size 10 with header line .

[Former Member]

Hi,

<b>Hashed table</b>

Hashed tables have no linear index. You can only access a hashed table using its key. The response time is independent of the number of table entries, and is constant, since the system access the table entries using a hash algorithm. The key of a hashed table must be unique. When you define the table, you must specify the key as UNIQUE.

This is the most appropriate type for any table where the main operation is key access. You cannot access a hashed table using its index. The response time for key access remains constant, regardless of the number of table entries. Like database tables, hashed tables always have a unique key. Hashed tables are useful if you want to construct and use an internal table which resembles a database table or for processing large amounts of data.

syntax:

HASHED TABLE

For creating hashed tables.

Ex.

TYPES VECTOR TYPE HASHED TABLE OF I WITH UNIQUE KEY TABLE

LINE.

TYPES: BEGIN OF LINE,

COLUMN1 TYPE I,

COLUMN2 TYPE I,

COLUMN3 TYPE I,

END OF LINE.

TYPES ITAB TYPE SORTED TABLE OF LINE WITH UNIQUE KEY COLUMN1.

TYPES: BEGIN OF DEEPLINE,

FIELD TYPE C,

TABLE1 TYPE VECTOR,

TABLE2 TYPE ITAB,

END OF DEEPLINE.

TYPES DEEPTABLE TYPE STANDARD TABLE OF DEEPLINE

WITH DEFAULT KEY.

The program defines a table type VECTOR with type hashed table, the elementary

line type I and a unique key of the entire table line. The second table type is the

same as in the previous example. The structure DEEPLINE contains the internal

table as a component. The table type DEEPTABLE has the line type DEEPLINE.

Therefore, the elements of this internal table are themselves internal tables. The key is the default key - in this case the column FIELD. The key is non-unique, since the table is a standard table.

<b>Secondary Index</b>

The primary index is always created automatically in the R/3 System. It consists of the primary key fields of the database table. This means that for each combination of fields in the index, there is a maximum of one line in the table. This kind of index is also known as UNIQUE.

If you cannot use the primary index to determine the result set because, for example, none of the primary index fields occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

You specify the fields of secondary indexes using the ABAP Dictionary. You can also determine whether the index is unique or not. However, you should not create secondary indexes to cover all possible combinations of fields.

Only create one if you select data by fields that are not contained in another index, and the performance is very poor. Furthermore, you should only create secondary indexes for database tables from which you mainly read, since indexes have to be updated each time the database table is changed. As a rule, secondary indexes should not contain more than four fields, and you should not have more than five indexes for a single database table.

If a table has more than five indexes, you run the risk of the optimizer choosing the wrong one for a particular operation. For this reason, you should avoid indexes with overlapping contents.

Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that column’s selectivity. Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to at most five percent of the table entries. If this is not the case, it is not worth creating the index. You should

also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table.

If all of the columns in the SELECT clause are contained in the index, the system does not have to search the actual table data after reading from the index. If you have a SELECT clause with very few columns, you can improve performance dramatically by including these columns in a secondary index.

Regards,

Bhaskar

[Former Member]

Hi,

HASHED table

Defines the table as one that is managed with an internal hash procedure

You can only access a hashed table using the generic key operations or other generic operations ( SORT, LOOP, and so on). Explicit or implicit index operations (such as LOOP ... FROM oe INSERT itab within a LOOP) are not allowed.

When possible, in the WHERE clause the fields of the INDEX are in the specified order and linked by the logical AND with comparisons for equality.

A secondary index has been considered if:

1. Non-key fields or fields for which index support does not exist are repeatedly used to make selections.

2. Only a small part of a large table is selected (<5%).

3. The WHERE condition of the SELECT is simple.

4. Fields that make up the index significantly reduce the selection set of the records by matching the unique qualifiers in the WHERE clause.

Note: prior to creating secondary indexes, consultation with the database administrator is required.

Hope it was useful.

Thanks,

Sandeep.