SAP DB Interview Questions and Answers
Question - 101 : - Can I Create Several Indexes Simultaneously?
Answer - 101 : -
You can create several indexes simultaneously. However, since only one index build can be carried out in parallel (by several server tasks), we recommend (to speed up the creation of indexes) that you ensure that indexes on large tables are started only if no other CREATE INDEX is active, when you are creating several indexes simultaneously. You can create indexes on small tables, even if a CREATE INDEX statement is already active.
Question - 102 : - Are Locks Set During Create Index?
Answer - 102 : -
Are Locks Set During Create Index?
Yes. Up to and including SAP MaxDB Version 7.6, a lock is set on the relevant tables during the index creation.
As of SAP MaxDB Version 7. 7, the system sets a lock for the entire duration of the index creation only if the following conditions apply:
- if it is a UNIQUE index
- if the transaction that executes the CREATE INDEX statement has already set other locks.
Question - 103 : - How Can I Speed Up The Index Build?
Answer - 103 : -
You can speed up the index build on large tables by ensuring that only one CREATE INDEX statement is active, and therefore several server tasks are carrying out the index build.
The data cache should be configured sufficiently large, so that preferably all data for the index build can be loaded in the cache.
Question - 104 : - Must I Carry Out An Update Statistics For Indexes?
Answer - 104 : -
No. You are not required to explicitly create the statistics for the indexes by carrying out an UPDATE STATISTICS.
Question - 105 : - What Do I Have To Bear In Mind With Regard To Views When Using Hints?
Answer - 105 : -
Hints can be applied to single-table views. However, apart from a few exceptions (such as ORDERED), you cannot apply hints to join views.
Question - 106 : - Why Must Sql Locks Be Set On Database Objects?
Answer - 106 : -
- If several transactions want to access the same objects in parallel, these accesses must be synchronized by the SQL lock management.
- Since the database system allows concurrent transactions for the same database objects, locks are required to isolate individual transactions.
- To lock an object means to lock this object from certain forms of use by other transactions.
Question - 107 : - Which Lock Objects Do Exist In Sap Maxdb?
Answer - 107 : -
Three types of locks exist in SAP MaxDB:
Record locks (ROW):
Individual lines of a table are locked.
Table locks (TAB):
The entire table is locked.
Catalog locks (SYS):
Database catalog entries are locked.
Question - 108 : - Must Locks Be Explicitly Requested Or Is Implicit Locking Possible?
Answer - 108 : -
Locks can be requested implicitly by the database system or explicitly by you (using the relevant SQL statements).
a) Requesting locks implicitly:
All modifying SQL statements (for example, INSERT, UPDATE, DELETE) always request an exclusive lock.
You can select the lock operation mode by specifying an isolation level when you open the database session.
Depending on the specified isolation level, locks are then implicitly requested by the database system when the SQL statements are processed.
b) Requesting locks explicitly:
You can assign locks explicitly to a transaction using the LOCK statement, and you can lock individual table lines by specifying a LOCK option in an SQL statement. This procedure is possible at each isolation level. In addition, you can use the LOCK option to temporarily change the isolation level of an SQL statement.