| Compressing indexes that have a timestamp in first column. [message #181818] |
Tue, 11 July 2006 13:55  |
cajohn
Messages: 10
Registered: July 2006
|
Junior Member |
|
|
I am looking for some clarification here.
I have been told two different answers and I am not sure which one to believe. Is their a benefit to compressing indexes for performance gains that start with a timestamp as the first column? I have been told that index compression only works on the first column and since timestamp is my first column that it would not do much compression so it would not change the performance. The other person said it would help performance even with very little compression gain. Who is right and why?
Thanks.
[Updated on: Tue, 11 July 2006 14:21] Report message to a moderator |
|
|
|
| Re: Compressing indexes that have a timestamp in first column. [message #181846 is a reply to message #181818] |
Tue, 11 July 2006 22:06  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Compression works on a leading subset of columns in the index, you choose how many. You only get a benefit if there are many rows with the same leading subset. If (say) five rows have the same leading subset, then oracle stores that subset once, and then stores the values of the remaining columns separately for each row.
The benefit comes by reducing the average width of an index entry and packing more of them into a block. When you perform an index range scan, full index scan, or fast full scan, you read fewer blocks. Less IO means better performance.
Unique scans and "short" range scans (where the whole range of rows fits in a block anyway) will have no performance gain. These types of scans read 1 block whether or not the index is compressed. Depending on your block size and column values, a block may contain from 1 to perhaps 100's of rows.
If you have dozens or hundreds of rows with EXACTLY the same timestamp (down to the second for DATE or fraction of second for TIMESTAMP data type), then compression MAY help if you regularly perform the types of index scans mentioned above.
Ross Leishman
|
|
|
|
| Re: Compressing indexes that have a timestamp in first column. [message #182175 is a reply to message #181846] |
Thu, 13 July 2006 07:32 |
cajohn
Messages: 10
Registered: July 2006
|
Junior Member |
|
|
|
Thanks for the information. Your explanation was 100X's better then the way the past two people explained to me. This database receives feeds from different security devices and some have fractions of a second while others do not. Since most of the queries use fast or full index scan I will start utilizing compression on the indexes.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
