| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: The LIKE Clause and the optimizer!
Billy Verreynne wrote:
>
> 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
8.0.4+ has index fast full scan which does allow the "multiblock read" concept on an index - but typically the optimizer does not seem to choose it even with the init.ora parameter enabled. --
"Some days you're the pigeon, and some days you're the statue." Received on Thu Nov 25 1999 - 03:46:24 CST
 |
 |