| Index on varchar2(2500) [message #123518] |
Mon, 13 June 2005 17:12  |
sandeep55
Messages: 4
Registered: June 2005
|
Junior Member |
|
|
I have a table with over 65 million rows; one of the fields is varchar2(2500) and I need to index it. Are there any performance issues I need to worry about?
Thanks
|
|
|
|
|
|
| Re: Index on varchar2(2500) [message #123549 is a reply to message #123536] |
Mon, 13 June 2005 22:36  |
sandeep55
Messages: 4
Registered: June 2005
|
Junior Member |
|
|
Yes, the table is partitioned. I looked into Oracle text but seems like that is more for full text searching. My field is a concatenation of about 100 smaller text fields and they all need to be indexed together. So, I thought I would create one large varchar2 field and index that.
Does it make sense?
|
|
|
|
| Re: Index on varchar2(2500) [message #123556 is a reply to message #123518] |
Mon, 13 June 2005 23:06 |
DMcG
Messages: 51
Registered: May 2005
Location: Auckland, New Zealand
|
Member |
|
|
So you have a table with over 100 columns in it and you need to be able to search on criteria on any of those columns ?
How distinct are your column values - maybe you can use bitmap indexes - although they're probably going to be huge.
Dougie McGibbon
|
|
|
|
| Re: Index on varchar2(2500) [message #123558 is a reply to message #123556] |
Mon, 13 June 2005 23:11 |
sandeep55
Messages: 4
Registered: June 2005
|
Junior Member |
|
|
the values in these 100 columns can be very distinct. The only way I want to search this table is together on these 100 columns. I will never be searching on any other field. The combination of values in these 100 columns makes a row unique in this table. That is the reason I am going with one concatenated string and putting an index on that - something like field1^field2^field3^....so on.
|
|
|
|
| Re: Index on varchar2(2500) [message #123702 is a reply to message #123518] |
Tue, 14 June 2005 09:08 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Hmm...what kind of updates, deletes, dml happen on this table? Is it basically static with weekly loads or is it constant transactions?
Are the fields searched in one and only one order, or would it be helpful to be able to do multiple combinations of fields? Is it always searching all 100 fields, or if 20 of them match is that ok?
Did you say it takes all 100 fields to be your primary key? Can you describe more about your situation?
|
|
|
|
| Re: Index on varchar2(2500) [message #123707 is a reply to message #123702] |
Tue, 14 June 2005 09:18 |
sandeep55
Messages: 4
Registered: June 2005
|
Junior Member |
|
|
This is an ODS system.
Only inserts will happen on this table, no deletes or updates. Inserts will be nightly when data comes in from the OLTP system. Yes, the fields are searched in one and only one order. It will always search for all 100 fields. Yes, it takes all 100 fields to be the primary key.
|
|
|
|
| Re: Index on varchar2(2500) [message #123711 is a reply to message #123518] |
Tue, 14 June 2005 09:25 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Inserts only, and your 65 million row data is partitioned. What is your partition scheme? Range by month? How big is your "active" partition, the one with the rows being added? How many rows get added each night? How much time do you have in your nightly window to load the data, gather stats, rebuild indexes, etc? Why does it take all 100 columns to identify a row?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
