Home » RDBMS Server » Performance Tuning » Index on varchar2(2500)
Index on varchar2(2500) [message #123518] Mon, 13 June 2005 17:12 Go to next message
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 #123536 is a reply to message #123518] Mon, 13 June 2005 21:25 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You might want to look into Oracle Text.

I'll assume your table is partitioned.
Re: Index on varchar2(2500) [message #123549 is a reply to message #123536] Mon, 13 June 2005 22:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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?


Previous Topic: RBO is there in oracle 10g?
Next Topic: SQL QUERY NOT USING BITMAP INDEX
Goto Forum:
  


Current Time: Sun Sep 20 15:29:16 CDT 2020