Indexes

Sorry, but your 'work-around' is nonsensical. I have already specified when an index is and isn't used and I already specified the workarounds

Your query is wrong:

SELECT * FROM table_name WHERE UPPER(name) LIKE '%ABC%'

Will find:

xABCx
xaBcx
xabcx


SELECT * FROM table_name WHERE UPPER(name) LIKE '%ABC%'
AND (name LIKE '%ABC%' OR name LIKE '%abc%' );

Will find:

xABCx
xabcx

Notice that the 2 result sets are different.

Attempting to fix it manually will simply kill you once you get to 4 or 5 characters in length because of the sheer number of permutations.

As I said before:

- Hint the index, it will run faster than the table-scan.
OR
- Create a function-based index
OR
- Use the InterMedia functionality, if it is applicable to your situation.

- Chris

Quote:

---

Originally posted by chrisrlong
- Hint the index, it will run faster than the table-scan.

---

Chris,

I don't belive you could even force the optimizer to use an index if the predicate is something like:

WHERE col LIKE '%x';

End even if you could persuade optimizer to use an index, the only choice it would have would be full index scan + table_access_by_rowid (provided we are talking about b*tree).

So the only option is to create a function index (and make it available, too!) or to use Intermedia.

ya..that was foolish of me:p
But i did soemthing else.....i upadted the entire column with upper case alplabets...
Now i think it should work fine and give proper result set....
doesnt it....
thanx again

Quote:

---

Originally posted by jmodic

Quote:

---

Originally posted by chrisrlong
- Hint the index, it will run faster than the table-scan.

---

Chris,

I don't belive you could even force the optimizer to use an index if the predicate is something like:

WHERE col LIKE '%x';

End even if you could persuade optimizer to use an index, the only choice it would have would be full index scan + table_access_by_rowid (provided we are talking about b*tree).

So the only option is to create a function index (and make it available, too!) or to use Intermedia.

---

Sorry Jurij, but the hint really does works and it is faster :). Further, it uses a range scan on the index, but that's not that important :) It seems a little odd, I know, but its true.

The key is that even a full scan on the index will be faster than a full scan on the table. Then, assuming you're getting a small percentage of the rows, even the ROWID access of the table won't make it slower than the tablescan. Of course, if you are getting more than 5% of the data, as always, the full table scan will be faster. Also, even with a function-based index, you would still have the same problem. Using the function only eliminates the difference in case, it doesn't eliminate the wildcard book-ends. ('%abc%'). Therefore, you still would want to hint the index.

Now, as for HelpMe, changing the data to uppercase was only half your problem, as we kept saying. The optimizer will not automatically pick the index (unless you have OPTIMIZER_INDEX_COST_ADJ = 1 and OPTIMIZER_INDEX_CACHING = 100, or something silly like that ;)). Therefore, you either need to hint the index or use InterMedia. (which is most likely overkill in your situation. We just keep mentioning it because it *is* a viable option). So, again, the best solution is to hint the index. WARNING: As always, when you are hinting a statement, you are taking control away from the optimizer. if you ask for LIKE '%A%', you will most likely be getting most of the table back. In such a case, the table scan would have been faster. Therefore, you need to know whether your users will usually be doing specific or general searches. If they are always general, then don't use the hint.

Make sense?

- Chris

[QUOTE]Originally posted by chrisrlong

Quote:

---

Sorry Jurij, but the hint really does works and it is faster :). Further, it uses a range scan on the index, but that's not that important :) It seems a little odd, I know, but its true.

---

True, Chris, I've checked it and you are right. You can force the optimizer to use the index in this case. But you are only partly correct in " it uses a range scan on the index" and in "but that's not that important :)". Explain plan will trully report "INDEX (RANGE SCAN) OF index_name", but that is only because it never reports a "full index scan" or something simmilary (unles it performs fast full index scan (FFS), which it can not in this situation, even with hint). So INDEX (RANGE SCAN) could mean scaning 1 block or it could mean scaning 1.000.000 blocks. In the case with '%ABC' optimizer has no other alternative but to scan the whole index, starting from root, moving on through branch blocks to the first leaf block, and then sequentialy block by block to the last leaf block.

So this *is* important. At least for two reasons. (And I'm assuming the offended table is not rediculously smal one, that being one of the reasons of the original poster's attempt to use an index.)

Reason 1:
Even if the size of the index in db_blocks is usualy smaler than the size of the table, performing full index scan is generaly *much* slower that performing table full scan. The reason is the fact that index blocks are read one-at-a-time, while database blocks are read in batches of db_file_multiblock_read_count blocks per physical read. Also (although this is not so relevant in multiuser environments) FTS reads are more or less sequential, menaing that in most cases physical read will continue in the location where previous physical read has ended, while in index scan physical reads are very scattered, meaning the disk head has to move much more back and forth.

And of course, once the maching key is found in the index, it still has to get this row from the table by rowid, which can result in another physical read (or at least logical one, if that block is allready in the cache).

Reason 2:
Full table scan will only use a small portion of your buffer cache to read all of the table blocks by reusing previously read blocks , while index scan will read each and every block into a new buffer, meaning that it could eventually flush from the cache other blocks that are used very frequently. This of course could result in decreased performance of other queries.

Quote:

---

Also, even with a function-based index, you would still have the same problem. Using the function only eliminates the difference in case, it doesn't eliminate the wildcard book-ends. ('%abc%').

---

Yes, that one was silly of me. Function based index can not help in the situation with '%abc%' ;)

First, let me say that debating SQL with you is always a pleasure, Mr. Modic.

Now, into the fray... ;)

Actually, my comment on the range scan ("...but that's not important") was a poor attempt at being cute. Obviously, the whole index would have to be scanned because of the leading wildcard, as you said.

Now, as for the arguments against the index usage being optimal:

- Index reads are done a block at a time
- They are scattered reads
- They use more cache

I agree with all these points. I must confess that I hadn't worked the problem the whole way through, but all of your points seem valid enough.

I would add, however, that I am not terribly concerned about the entire index being loaded into the cache. Assuming that this isn't a once-a-week type of query, having this index in the cache is as valid as having any other index there, all things being equal. As long as I'm not replacing and index block with a data block, I'm generally happy. Of course, this can be fine-tuned with the 3-section cache.

Anyway...

- I have seen this technique used in the past.
- I have used this technique in the past
- I recently saw this tip in Guy Harrison's book as well

So I decided to run another test, to make sure that it was still faster.

1: select * from indvl
where last_nm like '%AAM%'

2: select /*+ INDEX(indvl, xie1indvl) */ * from indvl
where last_nm like '%AAM%'

There are 25 Million record in indvl. 94 of them satisfy this query.

1:
Logical Reads: 35914
Physical Reads: 35782
Time: ~52 Secs

2:
Logical Reads: 5634
Physical Reads: 5577
Time: ~20 Secs

So it appears that the hint did indeed speed up this particular query. Now, one can assume that certain variables could change the results:

- If the index is anywhere near the size of the table itself
- If the search criteria is too broad - definitely if it will return more than 5% of the data.
- If db_file_multiblock_read_count is set very high (32,64)
- If the physical layout of the table/index is bad (all on same disk)

If any of the above conditions are true, I would wager that the simple tablescan will be faster.

Anyway, I gotta run. Let me know if I missed anything (which I'm sure you will :D).

- Chris

Quote:

---

Originally posted by chrisrlong
First, let me say that debating SQL with you is always a pleasure, Mr. Modic.

---

The general public enjoys reading it as well!

Quote:

---

Originally posted by chrisrlong
First, let me say that debating SQL with you is always a pleasure, Mr. Modic.

---

Likewise!

All your arguments are totaly valid. Maybe I wasn't clear in my previous post: I'm not saying that this technique of forcing index scan is something that should be avoided at all costs! Like anything else, it has its pros and cons, what is important is that we must be aware of all the goodies and pitfalls it might bring. And, BTW, I knew all the time that you are aware of all of them ;).

But still, something bothers me regarding your mesurments:

Quote:

---

So I decided to run another test, to make sure that it was still faster.

1: select * from indvl
where last_nm like '%AAM%'

2: select /*+ INDEX(indvl, xie1indvl) */ * from indvl
where last_nm like '%AAM%'

There are 25 Million record in indvl. 94 of them satisfy this query.

1:
Logical Reads: 35914
Physical Reads: 35782
Time: ~52 Secs

2:
Logical Reads: 5634
Physical Reads: 5577
Time: ~20 Secs

---

Putting asside that this index is (at least IMHO) somewhat non-typically small compared to the table size, I'm under the impresion that the time difference between the two queries is too high. If I assume your DB_FILE_MULTIBLOCK_READ_COUNT is set to 8, it means that in your first query the actual number of disk reads is approx 35782/8 ~ 4500, which is even less than in your second query, yet it took 2.5 times longer to finish! As it is almost 100% sure that the disk readsare the bottleneck in both queries, I could only guess what could be the reason for such a large time differencet:

- your DB_FILE_MULTIBLOCK_READ_COUNT is actually set lower than 8?
- your table extents are very small and not sized optimaly for the DB_FILE_MULTIBLOCK_READ_COUNT which could lead to additional disk reads (I know, this could have influence only in very extreme cases and I'm sure your storage parameters are not set so to cause such extremes :))?
- your table is located in much slower or much more utilized disk than your index?

Or is there some other reason that I can't see?