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

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

Answer 2

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 3

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.

Yes, but Oracle may choose not to use the index based on statistics. You can tell Oracle to use the index via a hint, but whether the index actually helps will depend on your data. Suppose you have this table and index:

create table t (id integer primary key, text varchar2(50), other_cols...);
create index t_i on t (text);

You then do this select:

select * from t where text like '%something%';

There are two obvious ways this query can be answered:

1. Full table scan on T
2. Full index scan on T_I, then 1 ROWID lookup of T per result found in T_I.

Suppose T has 100,000 rows, and only 5 of them match your search criteria. Suppose also that table T occupies 5000 blocks, and the index T_I occupies 1000 (i.e. only 20% of the size of T).

The actual cost of the queries in terms of reads is then:

1. 5000 reads (of T)
2. 1000 reads (of T_I) followed by 5 reads of T by ROWID = 1005 reads

Clearly in this case the index is better. However, Oracle tends to assume that the LIKE query will return 5% of the rows (i.e. 5000 rows), so its estimated costs (in reads) will be:

1. 5000 reads (of T)
2. 1000 reads (of T_I) followed by 5000 reads of T by ROWID = 6000 reads

Hence in this example, Oracle will go for the full table scan although the index search would be quicker. You could hint the query to use the index:

select /*+ index(t t_i) */ from t where text like '%something%';

However, note that this is only better if you are sure the query will return less than 5% of the rows most of the time.