Index | performance
ORA FAQ Performance Tuning Series--
The Oracle (tm) Users ' co-operative FAQ
Why would a reverse index to useful when the leading column of the "index is" generated from a sequence?
When the first column of the index is generated by the sequence, what is the use of a reverse index?
--------------------------------------------------------------------------------
Author ' s Name:norman Dunbar
Author ' s email:oracle@bountifulsolutions.co.uk
Date written:18/04/2004
Oracle version (s): 9.2.0.3
When your store data in a 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, and not necessarily in the order to put them there.
When you store data in an indexed table, some columns of data are copied to the index of the rowid of the data rows in the table. The data in the table is stored "randomly", or at least not in the order in which you put them.
The index entries, on the other hand, must being stored in order, otherwise the usability of the "index is removed." If you are could walk through the entries in a index, you would to that they to are in order, usually ascending, but since 8i, Descending also works.
Index entries should be stored sequentially, otherwise they are not useful. If you drill down into an indexed item, you'll see that they are ordered, usually ascending, but starting from 8i, you can also be descending.
Entries are stored in order of their internal representation, don't necessarily the same as what you SELECT the columns from a table.
Items are stored in the order of internal representations and do not need to be consistent with what you see on the screen when you select columns from a table.
If the indexed column (s) contain character data (CHAR, NCHAR, VARCHAR2 or NVARCHR2) then the data would appear on screen ex Actly as it does in the index. For example, if the column contains ' Oracle ' The index entry'll also be ' Oracle '.
If the indexed column contains character data (char, NCHAR, VARCHAR2, or NVARCHR2), the display of the data on the screen will be consistent with the index. For example, if the column contains "Oracle", then the index entry is also "Oracle."
We can use the ' DUMP command to show us ' internal representation of any data type. This command takes four parameters. The ' The ' is the ' data ' for you ' wish to dump, the second are the base you wish to dump it in. The default is ten which means that DUMP would display the characters in decimal, or base 10. The other allowed values are 8 (octal), (hexadecimal) or (Characters).
We can use the dump instruction to display an internal representation of any data type. This command has four parameters, the first is to dump the data, the second is to dump the cardinality, the default is 10, that is, dump will display decimal characters. The other optional parameters are 8 (octal), 16 (hexadecimal), 17 (character).
The third parameter is the "start position in" and "wish to dump from and" final parameter is the amount of data You are wish to dump. All but the parameter have sensible defaults.
The third parameter is the starting position of the dump in the data, and the last parameter is the amount of data to dump. All but the first argument has a reasonable default value.
Using DUMP, we can individual character codes for our ' ORACLE ' data:
With dump, we can see the individual character codes for ORACLE data:
We can prove that's correct by converting back from decimal character codes to actual characters:
We can prove that the decimal character code can be correctly converted back to the true name character:
Numeric columns are very much different. The internal format of a number is different from then which appears on screens after a SELECT because the internal format is converted to ASCII format so, it can be displayed. We can following, in the character format:
The number columns are different. The internal format of the number is different from the display on the screen after the select, and the internal format is converted to ASCII format for normal display. We can look at the following, first, the character format:
sql> Select ' 1234 ' as "1234",
2 dump (' 1234 ', 17)
3 from dual;
Then in internal format:
Then the 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 the columns in both examples look identical, but it is only because Sqlplus has converted the internal format of T He number 1,234 into the character format, so this, the display device (the monitor screen) are able to show it. Binary characters have a nasty tendency to disrupt character devices like computer when monitors in text mode.
The first column of the two example looks the same, but only because sqlplus converts the internal format of the number 1,234 to character format, showing the device (the monitor screen) to display it. Binary characters are always annoying to disrupt character devices like computer monitors running in text mode.
Take a look at the second column in the above examples and notice the difference. In the ' the ' example we ' individual characters ' 1 ', ' 2 ', ' 3 ' and ' 4 ' while the second example-shows only three byte s in the internal format of the number 1,234. Lets change the DUMP calls slightly, and does the whole lot in one command:
Look at the second column of the example above and notice the difference. In the first example we see one character, ' 1 ', ' 2 ', ' 3 ' and ' 4 ', while the second example shows only 1,234 bytes in the internal format of the number three. Let's change the dump call a little bit and complete the comparison with a single instruction:
sql> Select ' 1234 ' as "1234",
2 dump (' 1234 ', 10),
3 1234 as "a number",
4 Dump (1234, 10)
5 from dual;
1234 dump (' 1234 ', ten) 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 are actual character codes used internally. Once again columns 2 and 4 differ. Column 4 is showing three bytes and three bytes are the internal binary of the number 1,234.
This time, we see the real character code inside. Columns 2 and 4 are still different. Column 4 shows three bytes, which are the internal binary representations of 1,234.
It is this binary representation the ' is ' used in ' Index entry when ' A number column is indexed.
When a numeric column is indexed, it is the binary representation used in the index entry.
Take a few minutes and experiment with dumping a few the other numbers-stick to integers for now as those are what Generate.
Take a few minutes to experiment with dump some other numbers--now focusing on integers--exactly 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 have a entry is ascending in a similar manner to the ' a ' C Olumn. Here are:
If we look at column B in the table, we can see that each entry is incremented and is similar to column A. Here are the first 20 lines:
sql> Col b format A20 wrap
Sql> Select A,b from Test where a < 21;
The entries are very similar and all have the same leading byte.
The entries are very similar and have a leading byte.
How sequences affect indexes.
As mentioned above, index entries have to is stored in order, however, the table data need not is. If your indexed column is fed by a sequence, the data would be similar to the rows shown above.
How the sequence affects the index
As mentioned earlier, index entries must be stored sequentially, but the data in the table is not required. If your index column comes from a sequence, the data will resemble the 20 rows shown above.
Similar entries'll group together in the index, so the index blocks would split as necessary and new entries to All hitting the same blocks until it too fills up and splits.
Similar items will be in the same group in the index, so the index block will be split on demand, and the new item will be filled in the same block until the block is full and split.
If you are have one person running the application, this isn ' t too much of a problem. If the application is multi-user then it means that every user'll tend to write into the same index blocks and buffer bus Y waits would be the outcome as transactions ' queue ' to the hottest index block around.
This is not a problem if a single user executes the application. If the application is multiuser, it means that each user will write to the same index block. Transaction "Queued" to write data to the most "hot" index block will cause buffering busy waiting.
Back in We small test, if you select more data from the test table, you'll find this in the 1 million rows, there are O Nly 4 different values for the leading byte on the internal numeric format and even worse, most of the entries X have the same leading byte value:
Back to our little experiment, if you select more data from the test table, you will find that in 1 million rows, the leading (/starting) byte of the internal data format has only 4 different values, or worse, that most items in the index have the same leading (/starting) byte:
Sql> Select substr (b,1,3), COUNT (*)
2 from Test
3 GROUP by substr (b,1,3);
I cheated and discovered that there is a comma in position 4 of every row in the table that's how I knew to use a three C Haracter length in my SUBSTR.
I got a coincidence and found that the 4th digit of each row in the table is a comma, which is why I know that a three-bit character length is used in substr.
What The above shows is-is-in-a-1 million sequential entries, the vast-majority have the same leading byte and So'll all is trying to get into the same blocks in the index.
As shown above, in the index of a 1 million sequence item, most have an identical leading (/starting) byte, so that all (operations) 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 ' was 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 Lea F blocks and the problem of a single hot blocks is removed because the index entries are stored in reverse order.
How to reverse index to solve this problem
A reverse key index stores the byte of the indexed column in reverse order, so the data "ORACLE" is saved as "Elcaro" in the index. Use the reverse index to allocate the sequence value to a wider leaf block on the column filled by the sequence, and the problem of a separate "hot" block does not exist because the index entry is stored in reverse 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)
All other numbers between 6 and inclusive, have 10,101 entries each.
96 10101
97 10101
98 10101
99 10101
100 10101
Selected rows.
This time, our 1 million row index entry has it's leading byte value spread across (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.
This time, our 1 million-line index will spread the leading (/start) byte to 99 (if 0 has 100) different values, not just 4. Further, in fact, the reverse byte is also fairly randomly dispersed over each of the different values.
As more entries are added to the index, blocks'll be split to accomodate the new entries in their proper. As the data is arriving almost ' randomly ' by means of the reversing of the actual data bytes for the index, the index Itse LF would be extended to accomodate these new values. However, rather than always being stored in the same single ' hot ' index block, new entries would be spread across a number of existing blocks (assuming the index has been around for a while) thus reducing. Course, block splits'll still occur on this blocks as new values fill up the existing blocks but it ' s happening all O Ver the index not just in one place.
As more items are added to the index, the blocks are split to accommodate the new item in the appropriate place. Because the data is almost "random" from the data byte reverse processing in the index, the index itself expands to accommodate the new value. However, unlike the one that is always stored in the same "hot" block, the new project is allocated to several existing blocks (assuming the index has been in operation for some time). Of course, block splits still occur when new values fill these blocks, but this will occur in each part of the index, not in one place.
This is the reason why reversing the index as its leading column are fed by a sequence reduces buffer contention, removes The hot blocks problem and by doing so, reduces the potential for buffer busy waits on a multi-user system.
This is why the current lead (/start) column, when the sequence is generated, can reduce cache contention, solve hot block problems, and reduce cache busy waiting opportunities in a multiuser system.
Drawbacks to Reverse Key Indexes
Of course, there are drawbacks as. By setting up a reverse key index for you are increasing the clustering factor of the index. The clustering factor (from user_indexes) are used by the Optimiser (CBO) to determine how best to access data in a INDEX RANGE SCAN. If The clustering factor is roughly equal to BLOCKS minus the free_blocks from User_tables then the chances are that a range s Can'll read one index block, and locate all (or nearly all) of the data rows in needs into one or more adjacent blocks in The table.
The insufficiency point of the reverse key index
Of course, there are also deficiencies. Sets a reverse index that adds the index of the cluster factor (clustering Factor). The optimizer (CBO) uses the cluster factor (from user_indexes) to determine how best to access data in a single index RANGE scan. If the cluster factor is nearly similar to the Blocks-free_blocks (from the User_tables), an interval scan may read an index block and then locate all (or nearly all) data rows in one or more adjacent blocks as needed.
On the other hand, if the clustering factor are close to num_rows in User_tables then the chances are of that entries Ed together in one index blocks are likely to is scattered throughout a wide range of table blocks-so the index range SCA N May is chosen as a good method of access.
On the other hand, if the cluster factor is close to the num_rows in the user_tables, the items stored in one index block are likely to be scattered across a large number of blocks-so an index interval scan may not be a good way to access it.
Obviously the above applies to a analysed table and index.
This is obviously applied to a parsed table and index.
In a quick test on a table with 100,000 rows loaded using a sequence, a normal index is used for most queries returning u p to rows, as is the reverse key index, however, when the number of rows went up to 1,000,000 the Reverse key index WA s never used and a full table scan is used every time.
Do a quick test on a table loaded with a sequence of 100,000 rows, using normal and reverse-indexed queries and returning 30 rows of data, and when the number of rows grows to 1,000,000, the reverse index is no longer used, and the full table scan is used each time.
Further reading:
Oracle Reference Manual for your version of Oracle.
Read further:
Your Oracle version of the Oracle reference manual
--------------------------------------------------------------------------------
The translator only retains the copyright in translation from http://www.jlcomp.demon.co.uk/faq/autotrace_pruning.html
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
