Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Indexes

Re: Indexes

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 11 Jun 1999 11:18:10 GMT
Message-ID: <3761efe7.2613668@newshost.us.oracle.com>


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
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 11 1999 - 06:18:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US