OraFAQ Forum: Performance Tuning » Index on varchar2(2500)

Home » RDBMS Server » Performance Tuning » Index on varchar2(2500)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Index on varchar2(2500) [message #123536 is a reply to message #123518]

Mon, 13 June 2005 21:25

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.

Report message to a moderator

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?

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

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?

Report message to a moderator

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.

Report message to a moderator

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?

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 19:39:28 CDT 2025