Will Compressing My Indexes IMPROVE PERFORMANCE? Contracting index increases performance?
Author's name: Jonathan Lewis
Author's email: jonathan@jlcomp.demon.co.uk Date Written: 26th Feb 2003
Oracle Version (S): 8.1 - 9.2
Compressed Indexes Have Been Around for a couple yerrs now - But Will Compressing Your Indexes Automatical IMPROVE Performance? Compressed index has been existed for several years - then compressed indexes automatically improve performance?
. Oracle introduced a compression option for indexes in Oracle 8.1 You can create an index as compressed, or rebuild it to compress it (although there are some restrictions about online rebuilds, rebuilds of partitioned indexes etc.) Typical syntax might be: Oracle in Oracle The compression characteristics of the index are introduced in 8.1. You can create a compressed index, or compress an index when reconstructing (although there are some restrictions on online reconstruction, rebuild partition indexes). The standard syntax is as follows:
Create INDEX T1_CI_1 ON T1 (COL1, COL2, COL3, COL4) COMPRESS 2;
Alter Index T1_CI_1 Rebuild Compress 2;
The benefits of compression come from the fact that a properly compressed index uses a smaller number of leaf blocks - which tends to mean that less I / O is involved when the index is used, there is a reduced amount of buffer cache flushing, and the optimizer is likely to calculate a lower cost for using that index for range scans. (There is a tiny chance that the number of branch blocks, and the index height might be reduced, too, but that is a little unlikely). the benefits of compression From an appropriate compressed index uses fewer leafs - such that less I / O, the buffer Cache cleaning is reduced when used to index, and the optimizer can lower the calculation of the INDEX RANGE SCAN. (Even the chance branch block and the index height will also be reduced, but this is unlikely).
But compressing indexes, especially compressing the wrong number of columns, can have negative impact on your performance. If you compress more columns than you should, the 'compressed' index may be larger than the uncompressed index. Use the validate option on the index, . and check view index_stats to find out the optimum compression count How did I know that I should compress just the first two columns of the t1_ci_1 index (Apart from knowing the data, that is):? but the compression index, especially the compressed Lie When incorrect, performance will have a negative impact. If too much column is compressed, the "compressed" index may be greater than an uncompressed index. Use the Validate option to the index, then check the view index_stats to find the best compression. How do I know that the first two columns need to compress index T1_CI_1? (You don't need to know data): Validate Index T1_CI_1;
SELECT OPT_CMPT_COUNT, OPT_CMPR_PCTSAVE from Index_Stats;
OPT_CMPT_COUNT OPT_CMPR_PCTSAVE ------------------------------ 2 50
Unfortunately these TWO Column Don't Exist in 8.1, Only In Version 9 (Possibly Only 9.2). Fortunately Steve Adams Has A Script on His Website to Recommend A Compression Level (See www.ixora.com.au) Unfortunately It does not exist in 8.1, which is only 9 (may be only 9.2). Fortunately, Steve Adams have a script on his site to recommend compression (refer to www.ixora.com.au)
Even if you get the 'right' number of columns compressed, there is a price to pay: The main penalties are: (a) reads and mods of a compressed index cost more CPU than they would (typically) for an equivalent uncompressed index ( b) execution paths change - and you may not have predicted the changes, and some nominally cheaper paths may actually be slower for example: Oracle may choose an index fast full scan instead of an index range scan because the compressed index is now much smaller. , and your setting for parameter db_file_multiblock_read_count is large;. or Oracle may choose to use an index and do a nested loop because the index is now 30% smaller, where previously it was doing a table scan and hash join even if you get compression column "Correct" number, there is also a price: the main performance loss is: (a) read, change a compressed index consume more CPUs than a equivalent uncompressed index; (b) execute the path change - and you may I don't realize this change, some seemingly slow paths may be slow. For example: Since the compressed index is now smaller, the parameter db_file_multiblock_read_count is also large, then Oracle may select an Index Fast Full Scan instead of index ran scan; or due to the index minus 30%, Oracle chooses to use an index and Nested loop. Previously it scans and hash Join. So - Don't Go and Compress All The Indexes in Your Schema. So - Do not compress all indexes.
Think Carefully About Which Indexes Could Give You Significant Gains, And WHETHER You CAN AFFORD Some CPU Loss To Reduce Buffer THRashing and I / O. Thinking that the index will increase your greater performance, can you withstand some CPU losses to decrease? Buffer cleaning and I / O.
Remember too, if the way you use an index is such that the column order does not matter, then perhaps you could rearrange the column order to maximise the compression. The most critical point, perhaps, is that you should avoid moving a column that Is Typically Used With a Range Scan Towards The Front of Themb.t also remembers that if you don't have a set of order when you use an index, then you can reach the order of the column to increase the compression ratio. The most critical point maybe you should avoid a column that is generally used to Range Scan to the index. -------------------------------------------------- ----------------------------- This article is translated from http://www.jlcomp.demon.co.uk/faq/ Only reserved the translation copyright
