How to Select Data from Database Table when Searching on Non Key Fields

Sometimes you need to search on data from database table, which are not defined as key fields. In such case, selecting would not be optimal and select may take too much time.

Example

Lets say we want to use following SELECT on non key fields NAME_LAST, NAME_FIRST and NAME_WHOLE:

   SELECT *
       FROM zaa_bd_wpo_vol
       INTO table lt_wpo_vol
       FOR ALL ENTRIES IN lt_name_search
       WHERE name_last lt_name_search-last
         AND name_first  lt_name_search-first )
         OR name_whole   lt_name_search-whole.

Database structure is the following:

Prerequisites

Check if Index is Created

Before you start using such statement, you need to make sure that index is created on searched conditions. Mainly if this operation is performed many times and you are selecting data on huge database table (e.g. table contains few hundreds of thousands entries).

Click on ‘Indexes…’ button in SE11 transaction

When you will find that index like this exists, then you can use the SELECT statement.

If index like this does not exist, either ask your customer to create it, or if you are in charge of this project, just create it.

Check if Index is Used

Sometimes even when index is created, it happens that it is actually not used when program is being processed. This depends on database system.

You can check this in transaction ST05 by clicking on specific statement and hitting EXPLAIN button.

Comments

Add a comment

Your email address will not be published. Required fields are marked *

 

Your personal information will be exclusively used for processing current comment only. Personal data protection policy