index usage on dynamic search criteria

0 pts.
Tags:
Oracle
Hi, I have a very dynamic searching requirement. We can searh any columns of the tables in the database, using any criteria columns of tables in the database. Right now, we are facing some performance problems as the data grows in a rate of 2,000,000 records every three months. We plan to build the indexes on it to solve the performance problem. As the system is very dynamic, we plan to build the full index on the table that is being search. I would like to ask if statement has multiple criteria and there is an index on each of them, will the sql statement run even faster? If only one index will be used, which index will it choose? Also, will that be a big performance problem on index rebuild on top of these amount of data? Does anyone has any idea how long it will take? If you think indexes is not a good solution. Is there any more suggestions? Thanks... Cassidy 6o6
ASKED: January 12, 2006  8:00 PM
UPDATED: January 13, 2006  8:29 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

When a query is issued, one of the steps in the processing is called the Optimizer. Its job is to decide how to use the indeces present. If you search on a single criterion, then that one index will be used in a direct lookup. If you search on multiple criteria, and each has an index, then the optimizer will decide how to go about it. Usually, it will work through index hashing, and do a good job. So, from the searching end, the answer is that you can never have too many indeces. However, from the record insertion end, each added index costs you added time in doing the insertion. If there are far more insertions than searches, then don’t index. Otherwise, index the heck out of it. Note that some indeces are faster than others. If the data can be encoded numerically, then that index will be faster. A good rule of thumb is that the “cost” of an index (in a search) is roughly equivalent to the number of bytes in the data item. For DB2, here’s the list:

INTEGER: 4
SMALLINT: 2
CHAR(n): n
VARCHAR(n): 2n (note the exception to the timing rule)
VARGRAPHIC(n): 3n (note the exception to the timing rule)
NUMERIC(n,m): n
DECIMAL(n,m): n/2, rounded up

Hashed indeces are faster when there is a large data item, but <10,000 distinct data values. UNIQUE indeces are faster than UNIQUE WHEN NOT NULL indeces, and UNIQUE WHEN NOT NULL indeces are faster than non-unique indeces for searching, but using the UNIQUE keyword costs time in insert.

If you’re using Oracle, the bad news is that the optimizer isn’t as good, but the good news is that you can drop hints. There’s a whole manual section on writing effective hints in Oracle.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (http://www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Setirobert
    Hi, First up which RDBMS are you using ? what version? Indexing all columns is usually a bad idea as it will slow down inserts,updates,deletes though it will speed up selects, so it depends a lot on how the table is used Is this table in a datawarehouse or OLTP environment? indexing scheme will be completely different in those environment. How about getting some stats on which criterions are the most used, and index those. Why not always force one criterion like a date range to limit rows? I've faced a similar situation in the past and that's what we've done, usually people had an idea of the timeframe they were looking for combined with a free search and a resource consumption limit, that way even if somebody tried a query that would take too long to run during working hours it just would not run and they had to wait for off hours to run it. to your second question multiple indexes can be used but there are a lot of factors which will make the optimizer choose one or more indexes . >> If only one index will be used, which index will it choose? simple answer, the most discriminating one. >>Also, will that be a big performance problem on index rebuild on top of these amount of data? Does anyone has any idea how long it will take? that depends on RDBMS,version, platform, server processing power,disks speed cheers
    30 pointsBadges:
    report
  • Cassidy6o6
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/index-usage-on-dynamic-search-criteria/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following