Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Indexes
A copy of this was sent to "Terry Sullivan" <terry_sullivan_at_sgsgroup.com>
(if that email address didn't require changing)
On Fri, 11 Jun 1999 09:57:24 +0100, you wrote:
>Hi there,
>
>I'm new to Oracle. If I have an index which uses multiple columns and the
>WHERE clause of my SELECT statement uses a column which is in the index but
>NOT the first column in the index - will Oracle use this index to find the
>data?
>
no, it cannot (well it could but it would be painfully inefficient). say you have an index on the columns(a,b). In the index, in order we have:
a,b
---
1,1 1,2 2,1 2,2 3,1 3,2 .... (etc)
and you have "select * from T where b = 2"
using the index we would have to scan the entire structure (b=2 can be anywhere) row by row and then do a table access by rowid in T. So, we would do a scattered block read on the index and a scattered block read on the table. Compare that to just full scanning T...
>Thanks in advance.
>
>Terry Sullivan
>
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
![]() |
![]() |