{"id":7641,"date":"2025-08-24T16:58:54","date_gmt":"2025-08-24T11:28:54","guid":{"rendered":"https:\/\/www.sapewmhelp.com\/?question=types-of-database-indexes-in-sap"},"modified":"2025-08-24T16:58:54","modified_gmt":"2025-08-24T11:28:54","slug":"types-of-database-indexes-in-sap","status":"publish","type":"question","link":"https:\/\/www.sapewmhelp.com\/?question=types-of-database-indexes-in-sap","title":{"rendered":"Types of Database Indexes in SAP"},"content":{"rendered":"<h5>1. <strong>Primary Index<\/strong><\/h5>\n<ul>\n<li>\n<p>Created <strong>automatically<\/strong> when you create a table in SE11.<\/p>\n<\/li>\n<li>\n<p>Based on the <strong>primary key fields<\/strong> of the table.<\/p>\n<\/li>\n<li>\n<p>Always <strong>unique<\/strong>.<\/p>\n<\/li>\n<li>\n<p>Cannot be deleted.<\/p>\n<\/li>\n<li>\n<p>Used whenever you do a <code>SELECT ... WHERE<\/code> on the primary key.<\/p>\n<\/li>\n<\/ul>\n<p>\u00a0Example:<br \/>For table <strong>MARA<\/strong>, the <strong>primary index<\/strong> is on <code>MATNR<\/code> (Material Number).<\/p>\n<\/p>\n<p>SELECT * FROM mara WHERE matnr = &#8216;MAT001&#8217;.<\/p>\n<\/p>\n<h5>2. <strong>Secondary Index<\/strong><\/h5>\n<ul>\n<li>\n<p>Created <strong>manually<\/strong> in SE11 (or SE14) for performance optimization.<\/p>\n<\/li>\n<li>\n<p>Can be <strong>unique<\/strong> or <strong>non-unique<\/strong>.<\/p>\n<\/li>\n<li>\n<p>Useful when you frequently query on <strong>non-primary key fields<\/strong>.<\/p>\n<\/li>\n<li>\n<p>Stored in the database dictionary as <code>Z<\/code> indexes (<code>MARA~Z01<\/code>, etc.).<\/p>\n<\/li>\n<li>\n<p>Too many indexes = bad performance on <code>INSERT\/UPDATE\/DELETE<\/code> (because index maintenance consumes resources).<\/p>\n<\/li>\n<\/ul>\n<p>Example:<br \/>If you often query MARA by <code>MTART<\/code> and <code>MBRSH<\/code>, you might define a <strong>secondary index<\/strong> on those fields.<\/p>\n<div class=\"contain-inline-size rounded-2xl relative bg-token-sidebar-surface-primary\">\n<div class=\"sticky top-9\">\n<div class=\"absolute end-0 bottom-0 flex h-9 items-center pe-2\">\n<div class=\"bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs\"><\/div>\n<\/div>\n<\/div>\n<div class=\"overflow-y-auto p-4\">SELECT * FROM mara WHERE mtart = &#8216;FERT&#8217; AND mbrsh = &#8216;M&#8217;.<\/div>\n<\/div>\n<div><\/div>\n<div>\n<h5>3. <strong>Unique Index<\/strong><\/h5>\n<ul>\n<li>\n<p>Ensures that <strong>no duplicate values<\/strong> exist for the indexed fields.<\/p>\n<\/li>\n<li>\n<p>Primary indexes are always <strong>unique<\/strong>, but you can also define unique secondary indexes.<\/p>\n<\/li>\n<\/ul>\n<p>Example:<br \/>On <code>MARC<\/code>, you might create a <strong>unique secondary index<\/strong> on <code>(MATNR, WERKS)<\/code> to ensure material\u2013plant combination is unique.<\/p>\n<\/div>\n<h5>4. <strong>Non-Unique Index<\/strong><\/h5>\n<ul>\n<li>\n<p>Allows <strong>duplicate values<\/strong> for the indexed fields.<\/p>\n<\/li>\n<li>\n<p>Used mainly for improving performance, not data consistency.<\/p>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3 data-start=\"1842\" data-end=\"1889\">5. <strong>Bitmap Index (special, DB-dependent)<\/strong><\/h3>\n<ul>\n<li>\n<p>In <strong>SAP on Oracle<\/strong>, bitmap indexes may be created automatically for fields with <strong>low cardinality<\/strong> (few distinct values, e.g., &#8220;Gender&#8221;).<\/p>\n<\/li>\n<li>\n<p>Rarely created manually in ABAP Dictionary, but worth knowing.<\/p>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Summary table:<\/p>\n<table>\n<thead>\n<tr>\n<th>Index Type<\/th>\n<th>Created By<\/th>\n<th>Based On<\/th>\n<th>Uniqueness<\/th>\n<th>Example<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><strong>Primary<\/strong><\/td>\n<td>Auto<\/td>\n<td>Primary Key<\/td>\n<td>Unique<\/td>\n<td><code>MARA~MATNR<\/code><\/td>\n<\/tr>\n<tr>\n<td><strong>Secondary<\/strong><\/td>\n<td>Manual<\/td>\n<td>Any fields<\/td>\n<td>Unique \/ Non-Unique<\/td>\n<td><code>MARA~Z01<\/code> on MTART+MBRSH<\/td>\n<\/tr>\n<tr>\n<td><strong>Unique<\/strong><\/td>\n<td>Manual<\/td>\n<td>Any fields<\/td>\n<td>Unique only<\/td>\n<td><code>MARC~MATNR+WERKS<\/code><\/td>\n<\/tr>\n<tr>\n<td><strong>Non-Unique<\/strong><\/td>\n<td>Manual<\/td>\n<td>Any fields<\/td>\n<td>Duplicates allowed<\/td>\n<td>Index on <code>MTART<\/code><\/td>\n<\/tr>\n<tr>\n<td><strong>Bitmap<\/strong><\/td>\n<td>DB-specific<\/td>\n<td>Low-cardinality fields<\/td>\n<td>Depends<\/td>\n<td>Oracle-specific<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","template":"","question-category":[158],"question_tags":[178,374,240,375,177],"class_list":["post-7641","question","type-question","status-publish","hentry","question-category-abap","question_tags-abap","question_tags-database-index","question_tags-ddic","question_tags-index","question_tags-sap"],"_links":{"self":[{"href":"https:\/\/www.sapewmhelp.com\/index.php?rest_route=\/wp\/v2\/question\/7641","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sapewmhelp.com\/index.php?rest_route=\/wp\/v2\/question"}],"about":[{"href":"https:\/\/www.sapewmhelp.com\/index.php?rest_route=\/wp\/v2\/types\/question"}],"author":[{"embeddable":true,"href":"https:\/\/www.sapewmhelp.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sapewmhelp.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7641"}],"wp:attachment":[{"href":"https:\/\/www.sapewmhelp.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7641"}],"wp:term":[{"taxonomy":"question-category","embeddable":true,"href":"https:\/\/www.sapewmhelp.com\/index.php?rest_route=%2Fwp%2Fv2%2Fquestion-category&post=7641"},{"taxonomy":"question_tags","embeddable":true,"href":"https:\/\/www.sapewmhelp.com\/index.php?rest_route=%2Fwp%2Fv2%2Fquestion_tags&post=7641"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}