How to create an index for dynamic search strings

Question

I have a small database, for academic purposes only, and I have no more object tables. I created an entity relationship model (ERM) in Power Designer, and by default the program creates an index for the serial identifier for each table.

• I want to know how to use the index as per request. You want to find a product by its identifier, but using its index.
• Is it possible to do select value(s) from supplierf where s.name LIKE '%search%' order by s.name using an index to search, for example, what? I know that you can create an index for a name, but for a search, it’s kind of like I don't know how things work.

Let me say that I know that Oracle decides when or whether to use an index in a query, but I may have to give at least an attempt to use indexes in my BD project

Answer 1

1. By defining a column as a PRIMARY KEY (which most likely has your identifier column), Oracle implicitly creates an index for that column. Most likely, he will want to use this index if you have a choice with WHERE id=123 ). You can provide hint in your query to force Oracle to use the index (in most cases), but this is not necessary for you.

2. It is unlikely that Oracle will use an index for LIKE (if you do not know that your text starts with the search string and you can use "xyz%"). See Tony Andrews' post for more information on when and how to use the index to fully scan a table.

In the article about searching for sentences, Oracle LIKE with textual indexes should contain information on how to process full-text queries.

Answer 2

Regarding your point 1.): I don’t understand what you mean: if you assign indexes intelligently, you can use index hints to force the use of the index, but it’s a much better idea to let the optimizer do it first, and then if your index is not used, analyze why (perhaps using the index in certain circumstances is not the fastest way). For example, if you combine an identifier search with a search using wildcard matching, the optimizer may decide that if it should be a full table scan in any way (because of your term '%search%' ), there’s no added benefit using the index in the id column.

As for your point 2.): It is very unlikely that an index can be used if you use a wildcard at the beginning of your search query. For such queries, check out the full Oracle syntax here:

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

Answer 3

Is it possible to select a value from the providerf, where s.name LIKE '% search%' order by s.name using the index to do such a search? I know that you can create an index for a name, but for such a search, I do not know how everything works.

Yes, but Oracle may not use a statistics-based index. You can tell Oracle to use the index with a hint, but whether the index really 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); 

Then you choose:

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

There are two obvious ways to respond to this request:

• Full table scan on T
• Checking the full index on T_I, then 1 ROWID search on T for the result found in T_I.

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

Actual cost of requests in terms of reading:

• 5000 views (from T)
• 1000 views (from T_I), and then 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 (when reading) will be:

• 5000 views (from T)
• 1000 views (from T_I), and then 5000 reads of T by ROWID = 6000 reads

Therefore, in this example, Oracle will do a full table scan, although index search will be faster. You might be hinting at an index usage request:

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

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