Re: The LIKE Clause and the optimizer!

George wrote in message <81iajg$27n$1_at_perki.connect.com.au>...
>I have a query:
> SELECT /* + INDEX(invoices inv_num) */
> FROM invoices
> where inv_num LIKE '%xxxx'
>
>inv_num is of VARCHAR2(10) type and has an index on it.
>
>I am aware that Oracle will choose to do a FULL TABLE SCAN because
>the character string contains a '%' as the first letter, however I find it
>hard to believe that
>Oracle would rather use a full table scan rather than use the index in this
>case.

Because the first letter is unknown, Oracle needs to read every single index anyway. A full index scan. Then it needs to access the table and pick up up those rows with matching index entries. 50,000 rows you mentioned. I assume you're index is a unique one. This make the index size also 50,000.

If Oracle scans the index, it means 50,000 i/o calls. Let's assume that 10% of the data matches the criteria. This means that there are 5,000 matching rows. Oracle now needs to perform another 5,000 i/o's to read the table data. Number of i/o calls = 55,000.

Same query. Oracle does a full table scans and ignores the index. Number of i/o calls = 50,000.

Which one is the fastest? Second option of course. What about the size of the i/o call I hear you ask? That plays a very minor role.

Let's for argument sake take the example of an i/o call returning 10 bytes vs an i/o call returning 2KB. The number of bytes to read plays a insignificant role at these sizes. Why? Because of the internal hardware block i/o sizes. A physical i/o may for example always read 5KB (or whatever block sizes are used) into the internal hardware cache of that disk. So whether the i/o was to read 10 bytes only instead of 2KB does not matter.

Now add to this the size and thru put of the SCSI channel, the operating system disk buffers, operating system i/o block size and so on. The physical row size plays less and less of a role. What does play an important role is the -number- of i/o calls. Each i/o call has to be serviced by the operating system and if not cached, also serviced by the hardware. Lots of fingers in the pie for every single call made. Which why there is inherent latency when doing large amount of i/o calls. (and which is why the only solution to speed a large number of i/o calls up is to do it in parallel)

When it comes down to it that size does matter (e.g. storing geosat images as BLOBs in a database, ala Microsoft's Terra Server), this issue is addressed at hardware level, and not at database level. You simply need to increase the size of the pipe between the disks and operating system. Once again even in this situation of giant size rows, number of i/o calls plays a far more critical role than the size of each i/o call. Which is why Microsoft really failed to prove SQL-Server 7's capability of handling a terabyte size database. Size is not important. Number of rows are.

Hopes this answers your question.

regards,
Billy Received on Thu Nov 25 1999 - 05:16:35 CST