SAP DB Interview Questions and Answers
Question - 71 : - What Is The Technical Structure Of Indexes?
Answer - 71 : -
Indexes, like tables, are implemented as B* trees in SAP MaxDB. These consist of a root node that forms a tree with the subsequent nodes.
The complete index key and the references to the table data are in the lowest level of the index tree, otherwise known as the leaf level. The database system does not use physical items to identify these references, but instead these are saved in the primary key of the data records. The data record is identified by the primary key (the physical item of this data record is determined via the converter).
Since access to the data does not follow the sequence Primary key -> Data, but rather Data -> Primary key, it is also known as an inversion.
The idea behind this is that the relational table design provides for the fact that all data is dependent on a unique primary key.
While the access Primary key -> Data always collects one or no rows, the access Data -> Primary key collects no, one or multiple rows.
Question - 72 : - Is The Primary Key For Sap Maxdb Also Stored In A Separate Index-b* Tree?
Answer - 72 : -
Each database table has a primary key (primary index). The primary key is either defined by the user or generated by the system. A user-defined primary key can consist of one or more columns. The primary key must have a unique value for each row of the table.
The primary key is implemented directly on the data tree, which means there is no separate primary key tree. There is no ROWID or anything similar. The unique identification of a record is the primary key (for multiple keys, this is the combination of fields that are defined as the primary key).
Question - 73 : - How Is Data Accessed Using An Index?
Answer - 73 : -
If the SQL optimizer evaluates access using the index as the best solution, the primary keys of the table that match the index key fields used are determined in the table tree.
The corresponding rows are read from the table using this list of primary keys.
Question - 74 : - What Do I Need To Consider When I Create New Indexes?
Answer - 74 : -
Indexes are additional data structures that must be maintained with each change to the table data. Therefore, the effort involved in a data change (INSERT, UPDATE, DELETE) in the database increases with the number of indexes in a table. You should therefore ensure that the indexes you create in the customer namespace are actually used by the application also.
Question - 75 : - What Search Strategies Are There With Indexes?
Answer - 75 : -
You will find a description of the strategies used by the SQL optimizer under 'Strategy' in the glossary of the SAP MaxDB documentation.
Question - 76 : - Can Indexes Fragment In Sap Maxdb?
Answer - 76 : -
No. SAP MaxDB does not have an index fragmentation problem like that of Oracle (Note 771929). Here, indexes are kept optimal permanently, and storage space is immediately allocated to the freespace again.
Question - 77 : - Can I Check Individual Indexes For Consistency?
Answer - 77 : -
As of Version 7. 8, individual indexes can be checked for consistency. For more information about the statement CHECK INDEX.
Question - 78 : - Can An Index Become Larger Than The Table To Which It Belongs?
Answer - 78 : -
Yes. This is possible, for example, if the index is created on a little used field (that is, a field filled with too little data), but the primary key requires too much memory.
The key information is stored in a reduced form at index level in the B* tree of the table, while the secondary index must store each complete primary key.
Question - 79 : - Can I Create An Index On A View?
Answer - 79 : -
No. View tables are views on tables. The tables that are involved in view tables are called base tables.
These views on base tables are implemented as SELECT statements on the base tables. Technically, views tables can be compared to saved SELECT statements.
Therefore, no indexes can be created on view tables, but they can be created on the base tables of view tables.
Question - 80 : - What Happens If A Unique Index Is Set To Bad?
Answer - 80 : -
If a UNIQUE index is set to BAD, the corresponding table is set to READ ONLY. This lock is necessary since the UNIQUE option of the index can no longer be ensured for each additional write operation that concerns this index in the table. At the latest when the database is started the next time, UNIQUE indexes that are set to BAD are automatically recreated.
As of Version 7. 8, the DBM command auto_recreate_bad_index is available. You can use this command to activate the automatic recreation of indexes (including UNIQUE indexes).