How to create index for dynamic search strings

Question

I have a little DB, for academic purpose only, and I have object tables at most. I've created a entity-relationship model (ERM) in Power Designer and the program, by default, creates index for the serial id's for each table.

1. I want to know how do I use a index like that on a query.Say I would want to find a product by its id, but using its index.
2. Is it possible to do select value(s) from supplierf where s.name LIKE '%search%' order by s.name using a index to do a search like that? I know it's possible to create index for the name, but for a search like that I don't know how things work.

Let me say, that I do know that Oracle decides when or if it's worth using index in a query, but I may have to give, at least, a try on using indexs in my BD project

Answer 1

1. By defining a column as PRIMARY KEY (that's what your id column most likely is), Oracle implicitely creates an index for this column. It will most likely decide to use that index when you have a select with WHERE id=123). You can provide a hint in your query to make Oracle use the index (in most cases), but that should not be necessary for you.

2. It is unlikely for Oracle to use an index for LIKE (unless you know that your text starts with the searched string and you can use 'xyz%'). See Tony Andrews' post for more information about when and how to use an index for full table scans.

The article about Oracle LIKE clause searches with text indexes should provide information about a way to handle full text searches.

Answer 2

Regarding your point 1.): I'm not clear what you mean: if you assign indexes sensibly, you can use index hints to force index usage, but it's a far better idea to let the optimzer do it's work first and then, if your index is not being used, analyse why (it could be that index usage under specific circumstances is not the quickest way). For example, if you are combining a search by id with a search using the wildcard match,the optimizer may decide that, if it has to be a complete table scan anyway (because of your '%search%' term) that there is no added benefit using the index on your id column.

Regarding your point 2.): it is (very) unlikely that an index can be used if you are using a wildcard match at the beginning of your search term. For searches like that, take a look at the Oracle fulltext syntax here:

http://www.oracle.com/technology/products/text/index.html