Update statistic of a single table
Transaction DB20
What is SAP utilities for executing SQL command?
SAP DBA Cockpit provides a SQL command editor for you to enter a SQL statement/script, parse it, execute it and review the result directly without any ABAP knowledge. You can access SAP DBA Cockpit via SAP transaction code DBACOCKPIT, or SAP transaction ST04, DB02 etc. After you are in SAP DBA cockpit, you can use following path to access SQL command editor functions in the cockpit navigation panel: Performance ->Additional functions -> SQL Command Editor. Double click “SQL Command Editor” to launch the SQL command editor screen
What is LUW or Database LUW or Database Transaction ?
A "LUW" ( logical unit of work ) is the span of time during which any database updates must be performed . Either they are all performed ( committed ) , or they are all thrown away ( rolled back ).
LUW ( or "database LUW" or "database transaction" )
This is the set of updates terminated by a database commit. A LUW lasts, at most, from one screen change to the next ( because the SAP system triggers database commits automatically at every screen change ).
LUWs help to guarantee database integrity. When an LUW has been successfully concluded, the database is once again in a correct state. If, however, an error occurs within an LUW, all database changes made since the beginning of the LUW are canceled and the database is then in the same state as before the LUW started.
An LUW begins
Each time you start a transaction
When the database changes of the previous LUW have been confirmed (database commit) or
when the database changes of the previous LUW have been cancelled (database rollback)
A new internal or external session is started by either a SUBMIT, CALL TRANSACTION, BATCH INPUT MAP, CALL FUNCTION STARTING NEW TASK, CALL FUNCTION IN UPDATE TASK statement. Updates within these procedures start their own LUW.
Using the CALL FUNCTION IN UPDATE TASK implies creating a NEW once.
An LUW ends
- When the database changes have been confirmed (database commit) or
- When the database changes have been canceled (database rollback)
What is SAP LUW or Update Transaction ?
- Update transaction ( or "SAP LUW")
This is a set of updates terminated by an ABAP/4 commit. A SAP LUW may last much longer than a database LUW, since most update processing extends over multiple transaction screens.The programmer terminates an update transaction by issuing a COMMIT WORK statement.
Does the external program run in the same SAP LUW as the caller, or in a separate one?
- Transactions run with a separate SAP LUW
- Reports run with a separate SAP LUW
- Dialog modules run in the same SAP LUW as the caller
Function modules run in the same SAP LUW as the caller
The only exceptions to the above rules are function modules called with IN UPDATE TASK (V2 function only) or IN BACKGROUND TASK (ALE applications). These always run in their own (separate) update transactions.
A Logical Unit of Work (LUW or database transaction) is an inseparable sequence of database operations which must be executed either in its entirety or not at all. For the database system, it thus constitutes a unit.
LUWs help to guarantee database integrity. When an LUW has been successfully concluded, the database is once again in a correct state. If, however, an error occurs within an LUW, all database changes made since the beginning of the LUW are canceled and the database is then in the same state as before the LUW started.
COMMIT WORK.
Executes a database commit and thus closes a logical processing unit or Logical Unit of Work ( LUW ) (see also Transaction processing ). This means that
* all database changes are made irrevocable and cannot be reversed with ROLLBACK WORK and
* all database locks are released.
COMMIT WORK also
* calls the subroutines specified by PERFORM ... ON COMMIT * executes asynchronously any update requests (see CALL FUNCTION ... IN UPDATE TASK ) specified in these subroutines or started just before,
* processes the function modules specified in CALL FUNCTION ... IN BACKGROUND TASK ,
* cancels all existing locks (see SAP locking concept ) if no update requests exist,
* closes all open database cursors (see OPEN CURSOR ) and
* resets the time slice counter to 0.
COMMIT WORK belongs to the Open SQL command set.
Return code value
The SY-SUBRC is set to 0.
ROLLBACK WORK
Closes a logical processing unit by reversing all database changes made since the last COMMIT .
You use this statement if you cannot be certain that all the database changes have been executed correctly.
The update routines are not performed.
ROLLBACK WORK belongs to the Open SQL command set.
Note
If the ROLLBACK statement occurs within a SELECT loop, the processing cannot continue because the database cursor is invalid. After the ROLLBACK statement, you should therefore ensure that all SELECT processing has been explicitly terminated.
//----------------------
Database Access using Advanced Business Application Programming (ABAP) by Arindam Ghosh
Introduction
Accessing the Database in the R/3 System is performed through the interface provided by the SAP System, which in the SAP System is referred to as the Database Interface. A user can access the database from his/her program through Open SQL and Native SQL depending upon the circumstances.
About SQL
The Structured Query Language (SQL) is a largely standardized language, which is used for accessing relational databases. It can be divided as follows:
Data Manipulation Language (DML)
These statements are for reading and changing data in database tables.
Data Definition Language (DDL)
These statements are for creating and administering database tables.
Data Control Language (DCL)
These statements are used for authorization and consistency checks.
Here, each database has a programming interface. This programming interface allows the user to access the database tables by using SQL statements. But, these SQL statements in the programming interfaces are not fully standardized. So, you must refer to the documentation of that system for a list of the SQL statements available and also their correct syntax in order to access a specific database system.
Database Interface
Each work process on an application server must have a database interface if you want to make the R/3 system independent of the database system, and also to use it correctly despite the differences in the SQL syntax between various databases. By means of this interface only, the R/3 system can communicate with the database. All of the database requests from the R/3 system are converted into the correct Standard SQL statements for the database system by the database interface. In order to perform this function, it has to use a database-specific component, which shields the differences between database systems from the rest of the database interface. You have to choose the appropriate layer when installing the R/3 system. A user can access a database from a program through Open SQL and Native SQL.
Open SQL
Open SQL are statements that make up a subset of Standard SQL which is fully integrated in ABAP. Open SQL consists of Data Manipulation Language (DML) which is a part of Standard SQL.
One of the ways to access the database from a program is Open SQL. These Open SQL statements are nothing but a subset of Standard SQL, which is fully integrated in ABAP. Irrespective of which database system the R/3 installation is using, they allow you to access data. When I said that, Open SQL consists of the Data Manipulation Language (DML). I meant that it allows you to read (i.e. to SELECT) and change (i.e. to INSERT, UPDATE, DELETE) data.
Moreover, Open SQL also goes beyond Standard SQL. This is to provide statements that can simplify or speed up database access in conjunction with other ABAP constructions. Apart from that, it also gives you the freedom to buffer certain tables on the application server, thereby enabling you to save excessive database access. In this case, the database interface is responsible for comparing the buffer with the database. As far as buffer storage is concerned, they may be stored in two parts: the working memory of the current work process, and the shared memory for all work processes on an application server. The data in the various buffers is synchronized at set intervals by buffer management where an R/3 system is distributed across more than one application server. It should be noted that data in the buffer is not always up to date when you are buffering the database. That is why you should only use the buffer for data which does not change often. You can specify whether a table can be buffered in its definition in the ABAP Dictionary.
Open SQL consists of a set of ABAP statements. These statements perform operations on the central database in the R/3 system. The results of the operations and any error messages which come out of it are independent of the current database system. Thus, uniform syntax and semantics for all of the database systems supported by SAP is provided by Open SQL. Regardless of the current database system, the ABAP programs, which use Open SQL statements only, will work in any R/3 system. Moreover, Open SQL statements work only with database tables that have been created in the ABAP Dictionary.
You have the freedom to combine columns belonging to different database tables to a database view (or view for short) in the ABAP Dictionary. Views are also handled in exactly the same way as database tables in Open SQL statements.
Some Open SQL keywords are as follows:
SELECT - It reads data from database tables.
INSERT - It adds rows to database tables.
UPDATE - It changes the contents of rows of database tables.
MODIFY - It inserts rows into database tables or changes the content of existing rows.
DELETE - It deletes rows from database tables.
OPEN CURSOR, FETCH, CLOSE CURSOR - It reads rows of database tables using the cursor.
Return Codes
The following two system fields are filled with return codes by all Open SQL statements:
SY-SUBRC: The system field SY -SUBRC contains the value 0 after every Open SQL statement if the operation was successful. When a value is other than 0, then it is unsuccessful.
SY-DBCNT: The system field SY-DBCNT contains the number of database lines processed after an open SQL statement.
Native SQL
The other possible way to access the database from a program is Native SQL. It is only loosely integrated into ABAP. It allows access to all of the functions contained in the programming interface of the respective database system. Native SQL statements are not checked and converted as compared to Open SQL statements. Unlike Open SQL, these are sent directly to the database system. The function of the database-dependent layer remains minimal when you use Native SQL. The Programs which use Native SQL are written specifically for a database system. You should avoid using Native SQL wherever possible when writing R/3 applications. However, you can use it in some parts of the R/3 Basis System, for instance, for creating or changing table definitions in the ABAP Dictionary.
Regardless of the database platform that your R/3 system is using, Open SQL allows you to access database tables, which are declared in the ABAP Dictionary. Native SQL allows you to use database specific SQL statements in an ABAP program. This means that you can use database tables that are not administered by the ABAP Dictionary. Aside from that, you can also integrate data that is not part of the R/3 system.
As a rule, an ABAP program that contains database-specific SQL statements will not run under different database systems. You have to use Open SQL statements only, if your program is used on more than one database platform.
You must proceed with the EXEC SQL statement, and follow the ENDEXEC statement to use a: Native SQL statement. For example
There is no period after Native SQL statements. Also, using quotation marks (") or an asterisk (*) at the beginning of a native SQL statement's line does not introduce a comment as it would in normal ABAP syntax. You need to know if the table and field names are case-sensitive in your chosen database.
The data is transported between the database table and the ABAP program using host variables in Native SQL statements. These are preceded in a Native SQL statement by a colon ( and are declared in the ABAP program. The elementary structures can be used as host variables. The structures of an INTO clause are treated exceptionally, as though all of their fields are listed individually. If the selection in a Native SQL SELECT statement is a table, then you can pass it to ABAP line by line using the PERFORMING addition. For each line read, the program calls a subroutine <form>. Further, you can process the data within the subroutine.
After the ENDEXEC statement, SY-DBCNT contains the number of lines processed as it does in Open SQL. In almost all cases, SY-SUBRC contains the value a after the ENDEXEC statement. Cursor operations form an exception: after FETCH, SY-SUBRC is 4 if no more records could be read. This is also applied when you read a result set using EXEC SQL PERFORMING.
Native SQL Scope
Native SQL is very important as it allows you to execute nearly all available statements through the SQL programming interface (usually known as SQL Call Interface or similar) for directly executing SQL program code (using EXEC IMMEDIATE or a similar command). The statements that are not supported are listed in the following section:
· Native SQL and the Database Interface,
· Native SQL and Transactions
· Native SQL and the Database Interface
Native SQL statements bypass the R/3 database interface. With the database buffer on the application server, there is no table logging, and no synchronization. Therefore, you should use Open SQL to change database tables declare in the ABAP dictionary wherever possible. Since the columns contain extra database specific length information for the column tables declared in the ABAP dictionary, containing long columns with the type LCHAR or LRAW should only be addressed using Open SQL. Native SQL may not produce the correct result, as it does not take this information into account. Native SQL does not support automatic client handling. Instead, you must treat a client field like any other field
Native SQL and Transactions
One should not use any transaction control statement such as COMMIT, ROLLBACK WORK, or any statements that set transaction parameters using Native SQL to ensure that transaction in the R/3 System are consistent.
ABAP Dictionary
The ABAP Dictionary is nothing but a part of the ABAP Workbench. It allows you to create and administer database tables. There are no statements from the DDL part of Standard SQL in Open SQL. It should be noted that normal application programs should not create or change their own database tables.
To create and change database tables, the ABAP Dictionary has to use the DDL part of Open SQL. Besides this, it also administers the ABAP Dictionary in the database. In addition, the ABAP Dictionary contains meta-descriptions of all database tables in the R/3 system. Here, only database tables appears in the Dictionary, which you have created using the ABAP Dictionary. Open SQL statements can only access tables, which exists in the ABAP Dictionary.
Authorization and Consistency Checks
With regard to authorization and consistency checks, the DCL part of Standard SQL is not used in R/3 programs. Whereas, the work processes which are within the R/3 system are logged into the database system as users with full rights. By using the R/3 authorization concept, the authorizations of programs or users to read or change database tables is administered within the R/3 system. In addition, transactions must equally ensure their own data consistency using the R/3 locking concept.
The R/3 lock concept allows you to monitor your system with regards to lock logics. The R/3 lock concept works closely together with the R/3 updates.
As an example, say that a travel agent wants to book a flight for a customer who wants to fly to a particular city with a certain airline on a certain day. If there are still available seats on the flight, then the booking will be possible, otherwise it will lead to overbooking. Hence, the database entry corresponding to the flight must be locked against access from other transactions to avoid the possibility of overbooking. This is because two agents might both be doing this same thing at the same time, and we need to make sure that we don't overbook.
Lock Mechanisms
When the database system receives change statements (INSERT, UPDATE, MODIFY, DELETE) from a program, it automatically sets database locks. Database locks are locks on the database entries affected by statements to prevent problems. Since the lock mechanism uses a, lock flag in the entry, you can only set a lock for an existing database entry. After each database commit, these flags are automatically deleted. This means that database locks can never be set for longer than a single database LUW, a single dialog step in an R/3 application program.
Therefore, physical locks in the database system are insufficient for the requirements of an R/3 transaction. Locks in the R/3 system must remain set for the duration of a whole SAP LUW, that is, over several dialog steps. They must also be capable of being handled by different work processes and application servers. As a result, each lock must apply on all servers in that R/3 system.
Last edited by admin on Sun Aug 15, 2021 12:10 pm; edited 3 times in total
Age: 160 Joined: 04 Oct 2007 Posts: 1218 Location: Санкт-Петербург
Posted: Fri May 08, 2009 12:40 pm Post subject:
Почему может не использоваться индекс
An index will not be used if the following operators are present on the indexed fields in your WHERE clause:
'NOT' or 'NE' or '<>' or NOT IN
If fields in the WHERE clause are specified with operators NOT or <>, these WHERE conditions cannot be used for a search over a database index. You should therefore formulate SQL statements positively wherever possible.
If a positive formulation cannot be used, for example because the IN list would be too long, you should still specify the WHERE condition with NOT, in order to reduce the amount of data to be transferred. An index search will not be performed, but the amount of data returned will be smaller.
An index supports data searches in the database. All SAP tables have a primary index, which consists of the key fields that the user defines when creating a custom table. For SELECTs in which the primary index cannot be used in the WHERE clause, or when SELECTs are not properly qualified, the data base searches the entire table (performs a full table scan).
Indexes should, generally, only be created with less than five fields.
The most unique / selective fields should come first in the index definition, unless you can match the generic keys of the primary index for the table (e.g.-MANDT, BUKRS).
In general, if a condition includes OR, the optimizer stops processing (and invokes a full table scan) as soon as the first OR is encountered. The possible exception is an OR that proposes a separate and unique condition for evaluation.
Example
ZTABLE is defined with a secondary index:
Field name Type Length
FIELDC CHAR 3
FIELDF CHAR 2
Code:
SELECT * FROM ZTABLE
WHERE FIELDC = 'ABC'
AND (FIELDF = '12' OR '13').
=> Will execute, but will not use the index as expected.
Code:
SELECT * FROM ZTABLE
WHERE (FIELDC = 'ABC' AND FIELDF = '12')
OR (FIELDC = 'ABC' AND FIELDF = '13)
=> Will execute using the index as expected.
IN clauses are often interpreted as OR conditions and may lead to the same problems.
OR
Most optimizers have problems with OR, NEQ and LIKE conditions.
A field in an index is only valid for use if all fields that precede it in the index definition are fully qualified.
An index can only support search criteria which describe the search value positively, such as EQ or LIKE. The response time of conditions including NEQ is not improved by an index.
Example
ZTABLE is defined with a secondary index:
Field name Type Length
FIELDA CHAR 3
FIELDB CHAR 3
FIELDC CHAR 2FIELDD CHAR 4
Code:
SELECT * FROM ZTABLE
WHERE FIELDA = 'ABC'
AND FIELDB = 'XYZ'
AND FIELDC = '12'.
=> Will work fine.
Code:
SELECT * FROM ZTABLE
WHERE FIELDA = 'ABC'
AND FIELDB = 'XYZ'
AND FIELDD = 'DEFG'.
=> Will not use the index as expected and will probably invoke a full table scan based on the primary index.
LIKE ‘%pattern’
A field specified with LIKE can narrow the selection of database indexes and be used for a search over a database index only if it does not begin with the wildcard character ‘_’ or ‘%’. In ABAP, the wildcard ‘+’ is used for any character, and ‘*’ is used for any character string. For database accesses, however, the characters ‘_’ and ‘%’ are used.
IS (NOT) NULL
The value NULL is not stored in the index structure of some database systems. The consequence of this is that the index is not used for that field.
Potential problem with <, >, BETWEEN
On ORACLE databases, a WHERE condition with BETWEEN is evaluated together with the costs for reading 5 % of all index blocks and table blocks, regardless of the size of the BETWEEN interval. If the BETWEEN interval is small enough, you can replace SELECT field list WHERE field BETWEEN value1 AND value5 by SELECT field list WHERE field IN (value1, value2, value3, value4, value5) or by SELECT field list WHERE field IN ranges table.
On ORACLE databases, a WHERE condition with LIKE, > or < field =" ‘999’.">
WHERE conditions with critical operators that are linked to the WHERE clause with OR can be particularly problematic. Since BETWEEN is calculated as costing 5% and LIKE is calculated as costing 10%, the cost-based database optimizer opts for a full table scan after a certain number of corresponding WHERE conditions, instead of using an index.
Using an index consisting of several fields
Even if an index consists of several fields, you can still use it when only a few of the fields actually appear in the WHERE clause. The sequence in which the fields are specified in the index is important. You can only use a field in the index if all of the preceding fields in the index definition are included in the WHERE condition.
Specify field values without gaps. In other words, if a table has field1, field2, and field3 as the index, your where clause should not have only field1 and field3. Leaving out field2 causes the leading index for the only the first field to be used. If the where clause contained field1 and field2 but not field3, the index would include field1 and field2.
Example: A database table dbtable1 has an index on field1, field2, field3.
Good
Code:
SELECT fieldn INTO v_fieldn
FROM dbtable1
WHERE field1 = value1
AND field2 = value2
AND field3 = value3
The database can use index on field1, field2 and field3.
Bad
Code:
SELECT fieldn INTO v_fieldn
FROM dbtable1
WHERE field1 = value1
AND field3 = value3
The database can only use index on field1.
Good
You can use an index efficiently by using the below examples.
Example 1: Provide bound values to complete the index.
Code:
SELECT WERKS MATNR J_3ASTAT AUFNR
INTO TABLE T_BDBFROM J_3ABDBS
WHERE WERKS IN O_WERKS
AND MATNR IN O_MATNR
AND J_3ASTAT = C_FIXED "C_FIXED = F
AND AUFNR IN O_AUFNR.
Example 2: Use subquery on a check table to complete the index
Code:
SELECT BUKRS BELNR GJAHR BLART
INTO TABLE T_BKPF
FROM BKPF
WHERE BUKRS IN ( SELECT BUKRS FROM T001 )
AND BELNR IN O_BELNR
AND GJAHR = P_GJAHR.
Index statistics
To ensure that the cost-based database optimizer functions properly, the table and index statistics must be updated regularly. The database optimizer uses these statistics as a basis for selecting the access strategy used to execute a particular SQL statement.
The database administrator must update the table and index statistics regularly. To find out when the last update was performed, from the SAP standard menu choose Tools >> CCMS >> DB administration >> DBA Planning Calendar, or use Transaction DB13. You must ensure that the last update was not performed more than a week ago, and that it was performed successfully (refer to the log).
For making changes to index design, we recommend that roles and responsibilities are distributed as follows. The ABAP programmer designs the database indexes to support SQL statements. In a production system or test system, only the database administrator should be responsible for creating database indexes. The database administrator must verify whether the programmer's index design is correct.
When database indexes are created, the table and index statistics must be updated for the relevant tables. If the index design is correct, you can transport the database index to the production system at a time of low system activity, and then update the table and index statistics in the production system.
Using the addition CLIENT SPECIFIED incorrectly can lead to ineffective use of a database index. If you use CLIENT SPECIFIED, specify a WHERE condition for MANDT.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
and are declared in the ABAP program. The elementary structures can be used as host variables. The structures of an INTO clause are treated exceptionally, as though all of their fields are listed individually. If the selection in a Native SQL SELECT statement is a table, then you can pass it to ABAP line by line using the PERFORMING addition. For each line read, the program calls a subroutine <form>. Further, you can process the data within the subroutine.
