ORA FAQ Performance Adjustment Series - THE Oracle (TM) Users' Co-Operative FAQWHY WOULD A Reverse Index Be Useful WHENED From A Sequence? When the index is generated by the sequence, an inverse index is generated What is the use? -------------------------------------------------- ------------------------------
Author's name: Norman Dunbar Author's Email: Oracle@bountifulsolutions.co.uk Date Written: 18/04 / 2004Oracle Version (s): 9.2.0.3 ------------------- -------------------------------------------------- -----------
When you store data in an indexed table, certain columns of data are copied into the index alongside the rowid of the data row in the table. The data in the table is stored 'randomly', or at least, not necessarily in the order you Put the There. When you store data in an index table, some columns of data are copied into the index. The data in the table is "randomly" stored, or at least do not need to be placed in their order.
The index entries, on the other hand, must be stored in order, otherwise the usability of the index is removed. If you could walk through the entries in an index, you would see that they are in order, usually ascending, but since 8i The index item should be stored in order, otherwise it is unused. If you go deep into an index, you will see that they are orderly, usually ascertailed, but starting from 8i, you can also descend.
Entries Are Stored in Order of Their Internal Representation, Not Necessarily The Same As What Column From A Table. The item is stored in the order of internal representation, does not need to be columns from the SELECT you from the table. Assemble it on the screen.
If the indexed column (s) contain character data (CHAR, NCHAR, VARCHAR2 or NVARCHR2) then the data will appear on screen exactly as it does in the index. For example, if the column contains 'ORACLE' the index entry will also be 'Oracle'. If the index column contains character data (CHAR, NCHAR, VARCHAR2, or NVARCHR2), the data on the screen will be consistent in the index. For example, if the columns contain "Oracle", the index is also "oracle". We can use the DUMP command to show us the internal representation of any data type. This command takes four parameters. The first is the data you wish to dump, the second is the base you wish to dump it in. The default is 10 which Means That Dump Will Display The Characters in DISPLAY The Characters In Decimal, or Base 10. The Other Allowed Values Are 8 (Octal). We can use DUMP instructions to display internal representations of any data type. This command has four parameters, the first is the data to be DUMP, the second is the base to DUMP, the default is 10, that is, DUMP will display the decimal character. Other optional parameters are 8 (octal), 16 (sixteen), 17 (characters).
THIRD Parameter Is The Start Position In The Data You wish to dump from and the final parameter is The Amount of Data You wish to dump. All but the first parameter Have Sensible Defaults. The third parameter is the start position to be DUMP The last parameter is the amount of data to be DUMP. In addition to the first parameters, there are reasonable default values.
Using dump, we can see the individual character code: Using DUMP, we can see the "Oracle" data of each character code:
SQL> SELECT DUMP ('Oracle', 10) from Dual;
DUMP ('Oracle', 10) ------------------------------ TYP = 96 LEN = 6: 79, 82, 65, 67, 76, 69
We can prot this is correct by Converting Back from Decimal Character Codes To Actual Characters: We can prove that you can convert a decimal character code back to true characters correctly:
SQL> SELECT CHR (79), CHR (82), CHR (65), CHR (67), CHR (76), CHR (69) from DUAL
C C C C C C C- - - - - -O R A c L Ewe Could Have Used Base 17 To Do The Same Thing: We can use 17 as the base to achieve the same thing:
SQL> SELECT DUMP ('Oracle', 17) from DUAL;
DUMP ('Oracle', 17) ------------------------ TYP = 96 LEN = 6: O, R, A, C, L, E
Numeric columns are very much different. The internal format of a number is different from that which appears on screen after a SELECT because the internal format is converted to ASCII format so that it can be displayed. We can see this in the following, first in Character Format: The numeric column is different. The number of internal formats is different from SELECT on the screen, and the internal format is converted to the ASCII format to be displayed normally. We can see the following, first is the character format:
SQL> SELECT '1234' AS "1234", 2 DUMP ('1234', 17) 3 from DUAL
1234 Dump ('1234', 17) ---- --------------------- 1234 TYP = 96 LEN = 4: 1, 2, 3, 4
THENIN INTERNAL FORMAT: Then internal format:
SQL> SELECT 1234 AS "a Number", 2 Dump (1234, 17) 3 from DUAL;
A Number Dump (1234, 17) ---------- -------------------- 1234 TYP = 2 LEN = 3: C2, ^ M , #
The first columns in both examples look identical, but this is only because SQLPlus has converted the internal format of the number 1,234 into the character format so that the display device (the monitor screen) is able to show it. Binary characters have a nasty tendency To Disrupt Character Devices Like Computer Monitors When Running in text mode. The first column of the two appears, but this is just because SQLPLUS converts the internal format of 1,234 to the character format, thereby displaying a device (display screen) can display it. . Binary characters always hate a character device such as a computer display that is running on text mode.
Take a look at the second column in the above examples and notice the difference. In the first example we see the individual characters '1', '2', '3' and '4' while the second example shows only three bytes in the INTERNAL FORMAT of the Number 1,234. Lets change the dump calls slightly, and do the whole lot in one command: Take a look at the second column of the above example and pay attention to different points. In the first example, we see a character '1', '2', '3' and '4', while the second example only shows three bytes in the number of 1, 234 internal formats. Let us change the call of DUMP, complete all comparison with a directive: SQL> SELECT '1234' AS "1234", 2 Dump ('1234', 10), 3 1234 AS "a Number", 4 DUMP (1234, 10) 5 from Dual;
1234 Dump ('1234', 10) a Number Dump (1234, 10) ---- ------------------------- ---- ------ ---------------------- 1234 TYP = 96 LEN = 4: 49, 50, 51, 52 1234 TYP = 2 LEN = 3 : 194, 13, 35
This time, we see the actual character codes used internally. Once again columns 2 and 4 differ. Column 4 is showing three bytes and these three bytes are the internal binary representation of the number 1,234. This time, we see the inside of a real character code. Columns 2 and 4 are still different. Column 4 shows three bytes, which is the internal binary representation of 1, 234.
IT IS this binary representation That Used in the index entry when a number column is indexed. When the numeric column establishes an index, it is this binary representation for an index item.
Take a Few Minutes and Experiment with Dumping a Few Other NumBers - Stick to Integers For Now AS Those Are What Sequences Generate. Take some other numbers in Dump - now pay attention to integers, is the type of sequence generation.
SQL> CREATE TABLE TEST (A Number);
Table created.
SQL> Begin 2 for x in 1 .. 1E6 3 Loop 4 Insert Into Test Values (x, Substr (Dump (x, 10), 14)); 5 end loop; 6 end; 7 /
PL / SQL Procedure SuccessFully Completed.
IF WE HAVE A LOOK AT The 'B' Column of The Table, We Can See That Each Entry Is Ascending In A Similar Manner To The 'A' Column. Here Are The First 20 Rows: If we look at the list of the table, You can see that each entry is incremented, and is similar to the A column. Here is the top 20 lines: SQL> Col B Format A20 Wrapsql> SELECT A, B from Test Where A <21;
A b ---------- ---------- 1 193, 2 2 193, 3 3 193, 4 193, 5 5 193, 6 6 193, 7 7 193, 8 8 193, 9 9 193, 10 10 193, 11 11 193, 12 12 193, 13 13 193, 14 ,twenty one
The Entries Are Very Similar And All Have The Same Leading Byte. Every item is very similar and has a leader byte.
How sequences affect indexes.As mentioned above, index entries have to be stored in order, however, the table data need not be. If your indexed column is fed by a sequence, the data will be similar to the 20 rows shown above. Sequence How to influence indexes As mentioned earlier, the index item must be stored in order, but the data in the table is not required. If your index column comes from a sequence, the data will be similar to the 20 lines shown above.
Similar entries will group together in the index, so the index blocks will split as necessary and new entries will end up all hitting the same block until it too fills up and splits. Similar items will index the same group, so the index blocks Separation on demand, and new items will be filled in the same block until the block is full and split.
If you have one person running the application, this is not too much of a problem. If the application is multi-user then it means that every user will tend to write into the same index block and buffer busy waits will be the outcome as This is not a problem if the user performs an application if the user performs an app. If the application is multi-user, it means that each user will write the same index block. The transaction "Queu" to write data to the most "hot" index block will cause a buffer waiting.
Back in our small test, if you select more data from the test table, you will find that in the 1 million rows, there are only 4 different values for the leading byte on the internal numeric format and even worse, most of the entries in THE INDEX HAVE The Same Leading Byte Value: Go back to our small experiment, if you choose more data from the test form, you will find that in one million lines, the leadfire (/ start) byte of the internal data format is only 4 Different values, even worse, most items in the index have the same leader (/ start) byte: SQL> Select Substr (B, 1, 3), count (*) 2 from test 3 Group by Substr B, 1, 3);
Sub count (*) ------------ 193 99194 9900195 990000196 1
I cheated and discovered what there is a comma in Position 4 of every in the table.'S how is it I knew to use a three character length in my substr. I have discovered that the 4th place in the table is a comma. This is why I know that I use a three-character length in SubStr.
What the above shows is that in an index of 1 million sequential entries, the vast majority have the same leading byte and so will all be trying to get into the same block in the index. The above is illustrated in a sequence of one million In the index of the item, most of them have the same leader (/ start) byte, so that all (operation) will use the same block in the index.
How reverse indexes cure the problem.A reverse key index stores the bytes of the indexed column (s) in reverse order, so the data 'ORACLE' is actually stored in the index as 'ELCARO'. Using a reverse index on a column fed by a sequence spreads the location of sequential numbers across a wider range of leaf blocks and the problem of a single hot block is removed because the index entries are stored in reverse order. how reverse index solve this problem a reverse key index in reverse order to store the index The byte of the column, so the data "Oracle" is saved as "Elcaro" in the index. The sequence value is distributed to a wider leaf on the column of the sequence filled, and the problem of a separate "hot" block is not present due to the index item storage as an inverse order.
SQL> ALTER TABLE TEST ADD (C Varchar2 (30));
Table altered.
SQL> Update Test Set C = SUBSTR (Dump (Reverse (A), 10), 14); 1000000 rows updated.
SQL> SELECT SUBSTR (C, 1, INSTR (C, ',') - 1), count (*) 2 from test 3 Group by Substr (C, 1, INSTR (C, ',') - 1) 4 Order By to_Number (Substr (C, 1, INSTR (C, ',') - 1))
Sub count (*) ------------ 2 101023 101014 101015 10101
All Other NumBers Between 6 and 95 Inclusive, Have 10,101 Entries Each.
96 1010197 1010198 1010199 10101100 10101
99 rows selected.
This time, our 1 million row index entry has it's leading byte value spread across 99 (100 if you include a value for zero) different values, rather than just 4. In addition, the actual reversed bytes are fairly randomly scattered across each of the DiffERENT VALUES TOO. This time, our one million rows of indexes are allocated to 99 (if 0 is included in 0), not only 4. Further, in fact, the inverse byte is still relatively random dispersion on each of the different values.
As more entries are added to the index, blocks will be split to accomodate the new entries in their proper location. As the data is arriving almost 'randomly' by means of the reversing of the actual data bytes for the index, the index itself will be extended to accomodate these new values. However, rather than always being stored in the same single 'hot' index block, new entries will be spread across a number of existing blocks (assuming the index has been around for a while) thus reducing contention ., Block splits ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, Suitable location accommodates new projects. Due to the data byte in the index, the data is almost "random", and the index itself expands to accommodate the new value. However, different from the same "hot" block is always stored, the new item will be distributed to multiple existing blocks (assuming index has been working for a while). Of course, block segmentation will occur when the new value fills these blocks, but this will occur in each part of the index, not one place. This is the reason why reversing the index when its leading column is fed by a sequence reduces buffer contention, removes the hot block problem and by doing so, reduces the potential for buffer busy waits on a multi-user system. This is the reason why Guide (/ Start) Columns can reduce the cache dominance, solve heat block issues, and do this can reduce the opportunity to wait in a multi-user system in a multi-user system.
Drawbacks to Reverse Key Indexes Of course, there are drawbacks as well. By setting up a reverse key index you are increasing the clustering factor of the index. The clustering factor (from USER_INDEXES) is used by the optimiser (CBO) to determine how best to access data in an iNDEX RANGE SCAN. If the clustering factor is roughly equal to BLOCKS minus FREE_BLOCKS from USER_TABLES then the chances are that a range scan will read one index block, and locate all (or nearly all) of the data rows in needs In One or More Adjacent Blocks in The Table. The deficiencies of the reverse order is of course, and there is also a shortage. Set an inverse index to increase the CLUSTERING factor of the index. Optimizer (CBO) uses cluster factors (from user_indexes) to determine how best to access data in a primary Index Range Scan. If cluster factors are almost similar to blocks - free_blocks, a range scan will be able to read an index block, and then locate all (or close to all) data lines in one or more proximity blocks as needed. On the other hand, if the clustering factor is close to NUM_ROWS in USER_TABLES then the chances are that the entries stored together in one index block are likely to be scattered throughout a wide range of table blocks - so the index range scan may not be chosen AS A Good Method of Access. On the other hand, if cluster factor approaches Num_Rows in user_tables, a proximity item in a cable block is likely to disperse into a lot of table blocks - so the index interval scan may not be an access. A good way.
Obviously the Above Applies to an analysis. It is clear that it is applied to an analysis table and index.
-------------------------------------------------- ------------------------------
In a quick test on a table with 100,000 rows loaded using a sequence, a normal index was used for most queries returning up to 30 rows, as was the reverse key index, however, when the number of rows went up to 1,000,000 the reverse key Index Was NEVER Used and A Full Table Scan Was Used Every Time. Make a quick test in a table containing 100,000 rows in a single sequence, with a normal index and an inverse index query and return 30 line data, when the number of lines Growing to 1,000,000, will no longer use an inverse index to use a full mete scan. Further Reading: Oracle Reference Manual for Your Version of Oracle. Read: Your Oracle Version Oracle Reference Manual
-------------------------------------------------- ----------------------------- This article is translated from http://www.jlcomp.deMon.co.uk/faq/autotrace_pruning .html translator only retains the translation copyright