Let’s get started with secondary Index in Traditional SAP Database and SAP HANA Database. We as a whole know File in a data set table assists with getting the chosen column quicker as a list is an arranged duplicate of the chose data set table fields. In pragmatic situations and genuine undertakings, frequently we don’t have the essential keys in our choices boundaries and consequently, the entire table must be checked without any the essential list. In such cases, we are enticed to make Optional Record in exemplary non-HANA SAP Data set tables. Secondary Index in Traditional SAP Database and SAP HANA Database.
By and large, making Auxiliary Record makes information recovery quicker. Be that as it may, we need to pay something for this Optional Record. What is the cost of having Auxiliary List? Secondary Index in Traditional SAP Database and SAP HANA Database.
Reply:
1) Each time a passage is saved in the data set table, there is an extra above of refreshing the Auxiliary Files. Each extra record dials back the addition of columns in the table.
2) On the off chance that we have an excessive number of Auxiliary Lists for the information base table, odds are there that the capacity memory consumed for these files is nearly essentially as immense as need might have arisen for the entire data set table itself.
3) Premise Group needs to invest a lot of energy routinely to redesign the records that get divided over the long haul.
4) An excessive number of files can likewise cause the information base framework enhancer to choose some unacceptable record. To forestall this, the files in a table should share as couple of fields as could be expected.
What number of Optional Lists would it be a good idea for us to have in an exemplary data set table?
Reply: SAP suggests something like five auxiliary files.
When would it be a good idea for us to make Optional File?
Reply:
1) The fields in an Optional List ought to be fields through which we frequently select. The field or fields of an optional list ought to be particular to the point that each file section compares to exceptionally least percent of the complete passages in the table. SAP suggests Optional List ought to hold a limit of 5% of the all out number of table passages.
2) Auxiliary files are to be made exclusively for information base tables where the read gets to are additional time-basic than the compose gets to since each made list must be kept up with for compose gets to.
3) The fields that are probably going to be questioned with the = administrator ought to be toward the start of the list.
WHERE Proviso for Auxiliary Record:
1) ‘=’ (Equivalent to Administrator), IN conditions ‘AND’ joins are productively upheld by Auxiliary File, i.e they love Positive administrators. Auxiliary Record additionally works for LIKE proviso on the off chance that you can’t give EQ, IN statement.
IN implies various EQ for a section. Consequently, IN is viable in WHERE Condition.
2) Negative circumstances like <>, NE, NOT ought to be stayed away from. If potential, we ought to modify the WHERE provision to make it Sure. In the event that this is preposterous by any means then we ought to in any case determine the circumstances in the WHERE condition and not overlook them totally. This is the main manner by which the necessary information records will be chosen. Any other way, you would peruse pointless records that you would then need to eliminate in the ABAP program, which would go against the exhibition rule.
3) In the event that you don’t determine all fields in the list, ensure that you encase the underlying part of the file in the WHERE condition. In any case, the utilization of a record is beyond the realm of possibilities as a rule.
The enhancer for the most part stops on the off chance that the determination condition contains an ‘OR’. As such, it doesn’t assess the fields checked by ‘OR’ while choosing and applying the record. An exemption for this is ‘OR’ connections remaining all alone. Thusly, conditions containing an OR join for one of the filed fields ought to be reformulated if vital.
The optimizer stops working when it encounters OR in the following SELECT statement.
Not Recommended
SELECT * FROM spfli WHERE carrid = 'LH' AND ( CITYFROM = 'FRANKFURT' OR cityfrom = 'NEW YORK' ).
When replaced by the equivalent statement (below), the entire condition can be optimized with respect to the existing indexes.
Better use of ‘OR’ clause.
SELECT * FROM spfli WHERE ( carrid = 'LH' AND cityfrom = 'FRANKFURT' ) OR ( carrid = 'LH' AND cityfrom = 'NEW YORK' ).
What is the SAP Table which stores the file names and data for any data set table?
Reply: DD12L – R/3 S_SECINDEX: auxiliary lists, header
Above we discovered that SAP suggests greatest 5 Optional Files be made. Actually, what number of Optional Files could we at any point make in conventional (non-HANA) SAP data set?
There are many responses in famous SAP Discussion. Some say 9 others say 16 and some say limitless.
Reply: I checked our SAP framework and found that we have Auxiliary Lists to the count of 25. Check, even the MARA table has 16 records.
Some place in the Discussion, that’s what I read, SAP tosses a message when we attempt to make in excess of 16 Optional Files. Check we have 16 files in the framework.
I’m attempting to make the seventeenth Optional File and framework doesn’t caution me or toss any message.
Appears as though we can make limitless Auxiliary Files. SAP suggests least records however doesn’t stop you on the off chance that you don’t comply with SAP. That is the magnificence of SAP.
What is the greatest number of fields that can be there in an Optional File?
Reply: In one famous SAP blog, a creator has referenced that he could utilize greatest 16 fields to make the file.
I attempted to repeat the hard stop where SAP prevents you from utilizing in excess of 16 fields. Check the beneath picture, I had the option to add in excess of 25 fields in a List and SAP didn’t cry.
Additionally, check the length of these fields are in excess of 255 characters despite everything SAP didn’t stop. Seems to be, actually Auxiliary Record can have limitless fields. Could any master at any point affirm this?
Note: With respect to Drain Help Report, a record ought to just comprise of a couple of fields and, generally speaking, something like four fields.
There are such countless contemplations to be considered while making and utilizing Auxiliary Lists. Isn’t it minimal befuddling as well? Uplifting news. With the HANA advancement, we may in all likelihood never need Auxiliary Records in HANA Data set.
By and large, SAP HANA doesn’t need optional records for good inquiry execution. HANA data set store information in segment store naturally. Thus, every segment is a list in itself. SAP suggests utilizing Line store just in remarkable cases.
To diminish primary memory utilization, and to further develop embed execution all current non-special optional data set files on columnar tables are taken out during movement or don’t get made during establishment. Special files stay as they address an imperative on the table.
This is substantial for all AS ABAP frameworks from SAP NetWeaver 7.4 onwards!
This is our customary framework for all the above screen captures.
How about we rehash the inquiry once again. How might HANA Information base bear to dispose of Optional Lists?
Reply: HANA framework can examine the data set table at easing up speed as it is not quite the same as old style information bases. HANA data set tables are Segment based rather than Line situated in conventional data sets. This implies we don’t require files any longer. As currently referenced above, HANA data set section fields act as though we have physically made a list on each field of the table yet with practically no expense/cost/disadvantages of optional records as referenced above for conventional information base.
Eliminating files mean we really want less data set space, for example indeed, even the 5% memory which auxiliary lists would have consumed is saved. It resembles the file pages at the rear of the actual books are taken out and the general page quantities of a book are decreased. :). Furthermore, you have the super ability to peruse 1,000,000 words each second and track down any word in a negligible part of the second in the entire book. . So you don’t have to allude to the lists at the rear of the book to find the page number where that word is utilized in the book.
Does it mean, we can’t make Records in HANA Data set?
Reply: Indeed, we can make Files in HANA Data set. HANA permits the Make Record order.
SAP master John Appleby says: Never make File in HANA. At the point when we make a table in HANA, it is, truth be told, making a bunch of arranged, compacted and connected lists. Therefore, optional records never further develop execution.
He says: “There is one situation when an optional file can further develop execution: when you have an inquiry which chooses a tiny measure of information from an extremely enormous table (or gathering of joined tables). In this example, making an optional file on the entirety of your sort sections can permit HANA to find the information quicker. Yet, this is what is going on – the straightforward exhortation is, never make files”.
As such, in specific dark situations where we find a presentation issue, an optional list can help. This is just in OLTP situations, where you have complex joins and just return a little subset of information from the table. The vast majority of situations won’t ever profit from a record. They occupy room and will slow embed activities, so ought to be stayed away from in HANA Data set.
Since we discovered that we can in fact make Files in HANA. What are the kinds of Lists in HANA?
Reply: There are two kinds of Files in HANA.
1) Inverted Index and
2) Composite Index.
Inverted Index: Rearranged records allude to just a single section. Here, the list information is put away in inside memory structures that have a place with the separate segment.
Composite Index: Composite files allude to more than one segment. In the first place, the items these segments are assembled in an inward section, and a rearranged file is then made for this inside section.
We will examine insights regarding Rearranged/Composite Files in a different post.
To get such helpful and reasonable articles directly to your inbox, kindly Buy in. We regard your security and view safeguarding it in a serious way.
Much thanks for your time!!
YOU MAY LIKE THIS
ABAP Test Cockpit(ATC) – Introduction and Steps
Create and Consume Business Add-in(BAdI) in ABAP
How to Convert JSON Data Structure to ABAP Structure without ABAP Code or SE11?
WhatsApp us