| Column width convergation with Index performance [message #477424] |
Thu, 30 September 2010 10:09  |
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
|
Junior Member |
|
|
Hi experts,
Does anyone had calculated how the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For egz.: i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
Thanks for replies.
|
|
|
|
|
|
|
|
|
|
| Re: Column width convergation with Index performance [message #477440 is a reply to message #477424] |
Thu, 30 September 2010 11:54  |
sunroser
Messages: 16
Registered: September 2010
|
Junior Member |
|
|
|
Hi, that depends on what kind of performance you want, for example, if you want performance on read, then you should use "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED', and build bitmap index on it, if you want write it faster (quick transaction responding time), then you might not want the index on the column...
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Column width convergation with Index performance [message #477455 is a reply to message #477453] |
Thu, 30 September 2010 14:21 |
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
|
Junior Member |
|
|
>Hi try to focus on my point: what kind of performance we want to measure
I need both inserts and selects. The purpose for my question is in what direction i should go. I can't make tetscases for tenths of different scenarios(as BlackSwan suggested). First i have to be at least theoretically sure
i am going write dirrection.
I want to build IOT from HEAP table on existing application,
so converting column job to using lookup table, will result plenty of modifications, i have to be sure i am picking the best scenario.
[Updated on: Thu, 30 September 2010 14:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Column width convergation with Index performance [message #477470 is a reply to message #477466] |
Thu, 30 September 2010 18:01 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you are talking about WRITE speed, an insert will cost exactly ONE block write for the table, and typically ONE block write for each index (although, on average, it will be more like 1.01 - 1.05 blocks per index because of block splitting). That applies for VARCHAR2 and NUMBER equally, because Oracle does not write volumes smaller than one block.
If you are talking about READ speed, it will cost you exactly ONE block read for the table, and 2-4 for the index - depending on its b-tree depth. It is entirely possible for your VARCHAR2 example to make the b-tree deeper, requiring an extra block read for the VARCHAR2 example. But because the maximum size of b-tree indexes increases polynomially with each increment in depth, this difference is highly improbable in practice.
Yes, there would be a measurable difference in the size of the index. This would probably affect the performance if index rebuilds.
The fact is, if you can think of ANY other reason to choose one or the other, then that factor will almost certainly carry more weight than read and write performance.
Ross Leishman
|
|
|
|
| Re: Column width convergation with Index performance [message #477571 is a reply to message #477455] |
Fri, 01 October 2010 15:15 |
sunroser
Messages: 16
Registered: September 2010
|
Junior Member |
|
|
sky_lt wrote on Thu, 30 September 2010 15:21>Hi try to focus on my point: what kind of performance we want to measure
I need both inserts and selects. The purpose for my question is in what direction i should go. I can't make tetscases for tenths of different scenarios(as BlackSwan suggested). First i have to be at least theoretically sure
i am going write dirrection.
I want to build IOT from HEAP table on existing application,
so converting column job to using lookup table, will result plenty of modifications, i have to be sure i am picking the best scenario.
Hi Sky_it, now new point: unlikely you gonna pick the lookup solution which brings too much modification. If that is the case, the answer is clear: use the column with the characters. If couldn't absolutely say: best for read might not be best for writing, but the idea of how to design is different when you are trying to get the best performance.
So please answer this question first:
if there is ever a business performance impact: e.c. select is too slow / submit is too slow. Which one is going to win the resource?
|
|
|
|
| Re: Column width convergation with Index performance [message #477574 is a reply to message #477453] |
Fri, 01 October 2010 15:56 |
sunroser
Messages: 16
Registered: September 2010
|
Junior Member |
|
|
sky_lt wrote on Thu, 30 September 2010 15:14sunroser,
>and build bitmap index on it,
bitmap indexes require mapping table on IOT. besides i have OLTP enviroment, correct if i am wrong, but bitmaps require addition locking and that is kind of problem for OLTP case.
Right, that is exactly what I want to stress on.
If you want the best on reading, and the column's distinct value is fixed and limited (in your case), than bitmap can give the "incredible" speed, but the column with bitmap gets updated slow. If you ever decide to use this solution and like to improve the writing speed on bitmap column, then you:
1. make this column updated in night batch.
2. drop the bitmap index before the update
3. update
4. build up the bitmap index
However, if the app is updating this column online frequently, then this is not what you want.
|
|
|
|
| Re: Column width convergation with Index performance [message #477575 is a reply to message #477453] |
Fri, 01 October 2010 16:04 |
sunroser
Messages: 16
Registered: September 2010
|
Junior Member |
|
|
sky_lt wrote on Thu, 30 September 2010 15:14sunroser,
>and build bitmap index on it,
bitmap indexes require mapping table on IOT. besides i have OLTP enviroment, correct if i am wrong, but bitmaps require addition locking and that is kind of problem for OLTP case.
Hi, Sky-it, bitmap is one type of index, it doesn't require any extra mapping table. When you select on it, no additional lock required. It is kind of problem for OLTP if you are updating this bitmap-indexed column frequently.
Refer to my another post for solution.
|
|
|
|
| Re: Column width convergation with Index performance [message #477582 is a reply to message #477575] |
Fri, 01 October 2010 17:49 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
sunroser wrote on Sat, 02 October 2010 07:04Hi, Sky-it, bitmap is one type of index, it doesn't require any extra mapping table. When you select on it, no additional lock required. It is kind of problem for OLTP if you are updating this bitmap-indexed column frequently.
Refer to my another post for solution.
I would qualify that: don't use bitmap indexes whenever there is a chance of concurrent DML: that's inserts, updates or deletes. So by that rule, if your OLTP system has 2 users who modify this table, don't use a bitmap index.
The reason - which has been danced around above - is because bitmap indexes effectively lock at the block level, not the row level. If two sessions each insert new rows when the table is otherwise "full" (ie. no free space except at the end), then one session will almost certainly block the other one. If those sessions are permitted to make further changes to the table before committing, then you are exposed to a deadlock situation.
So - technically, if every session that modifies the table commits after every row, you will avoid deadlocks, but still be exposed to blocking.
Ross Leishman
|
|
|
|
| Re: Column width convergation with Index performance [message #477667 is a reply to message #477575] |
Sun, 03 October 2010 05:06 |
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
|
Junior Member |
|
|
sunroser wrote on Sat, 02 October 2010 00:04sky_lt wrote on Thu, 30 September 2010 15:14sunroser,
>and build bitmap index on it,
bitmap indexes require mapping table on IOT. besides i have OLTP enviroment, correct if i am wrong, but bitmaps require addition locking and that is kind of problem for OLTP case.
Hi, Sky-it, bitmap is one type of index, it doesn't require any extra mapping table. When you select on it, no additional lock required. It is kind of problem for OLTP if you are updating this bitmap-indexed column frequently.
Refer to my another post for solution.
Using Bitmap indexes on INDEX-ORGANIZED table require additional mapping heap table. So that is performance overhead.
THANKS EVERYONE FOR ANSWERS!!!
Now i am confident - in my case to use varchar2 columns, as oracle will read/write 1 block for every write and read operation.
I will have the size issue here as Mr. rleishman has noted, but it is not an problem for me.
[Updated on: Sun, 03 October 2010 05:12] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
