Hello,

Say If I have a table with column a, and column b (say they are
integers).

Sometimes I will do a query with a where clause that just references
column a.

Sometimes I will do a query with a where clause that references both
column a and b.

Is there a need to create an index with a, and an index with a, b? Or
will an index with a,b work for both of the above types of queries?




I know in mysql if I have an index (a, b, c), then it is useful for
queries referencing a or a,b or a, b,c. But, I am not as familiar
with how Oracle indexes work.

Thanks.

"doug" <douglass_davis@earthlink.net> wrote in message
news:a95d43e0-f441-4fc8-8943-75c2e4a90e86@8g2000hse.googlegroups.com...
>
>
> Hello,
>
> Say If I have a table with column a, and column b (say they are
> integers).
>
> Sometimes I will do a query with a where clause that just references
> column a.
>
> Sometimes I will do a query with a where clause that references both
> column a and b.
>
> Is there a need to create an index with a, and an index with a, b? Or
> will an index with a,b work for both of the above types of queries?
>
>
>
>
> I know in mysql if I have an index (a, b, c), then it is useful for
> queries referencing a or a,b or a, b,c. But, I am not as familiar
> with how Oracle indexes work.
>
> Thanks.
It depends on the version; there are some subtle differences. (eg index
skip scan) In Oracle 7 and 8 if you created an index on a,b then a search
on a could use the index and a search on a and b could use the index. A
search only on b could not.
Jim

doug wrote:
>
> Hello,
>
> Say If I have a table with column a, and column b (say they are
> integers).
>
> Sometimes I will do a query with a where clause that just references
> column a.
>
> Sometimes I will do a query with a where clause that references both
> column a and b.
>
> Is there a need to create an index with a, and an index with a, b? Or
> will an index with a,b work for both of the above types of queries?
>
>
>
>
> I know in mysql if I have an index (a, b, c), then it is useful for
> queries referencing a or a,b or a, b,c. But, I am not as familiar
> with how Oracle indexes work.
>
> Thanks.

Assuming a currently supported version of Oracle it works the same
way. It will even in many cases support a query on b and b,c that
does not include a.

But you have, for some reason, not provided version information so
this response is purely generic.

Oracle changes, often dramatically from version to version with
respect to specific functionality. Always, when asking Oracle
questions include full version information ... 3+ decimal places.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Jun 4, 9:01*pm, doug <douglass_da...@earthlink.net> wrote:
> Hello,
>
> Say If I have a table with column a, and column b (say they are
> integers).
>
> Sometimes I will do a query with a where clause that just references
> column a.
>
> Sometimes I will do a query with a where clause that references both
> column a and b.
>
> Is there a need to create an index with a, and an index with a, b? *Or
> will an index with a,b work for both of the above types of queries?
>
> I know in mysql if I have an index (a, b, c), then it is useful for
> queries referencing a or a,b or a, b,c. *But, I am not as familiar
> with how Oracle indexes work.
>
> Thanks.

You should read about indexing in the concepts manual and the
performance tuning manual. You can also search http://tahiti.oracle.com
for the skip scan Jim mentioned, which answers your question.

Once you become comfortable with the basic concepts, you can delve
deeper in many places, especially http://richardfoote.wordpress.com/
(be sure and check out the index internals presentation).

jg
--
@home.com is bogus.
“I just don't understand why kids would do a stupid thing like
that...” http://www.signonsandiego.com/uniont...s_1n5nude.html

"joel garry" <joel-garry@home.com> wrote in message
news:e280efa1-9fb4-482f-957b-a81eba7086de@l28g2000prd.googlegroups.com...
On Jun 4, 9:01 pm, doug <douglass_da...@earthlink.net> wrote:
> Hello,
>
> Say If I have a table with column a, and column b (say they are
> integers).
>
> Sometimes I will do a query with a where clause that just references
> column a.
>
> Sometimes I will do a query with a where clause that references both
> column a and b.
>
> Is there a need to create an index with a, and an index with a, b? Or
> will an index with a,b work for both of the above types of queries?
>
> I know in mysql if I have an index (a, b, c), then it is useful for
> queries referencing a or a,b or a, b,c. But, I am not as familiar
> with how Oracle indexes work.
>
> Thanks.

You should read about indexing in the concepts manual and the
performance tuning manual. You can also search http://tahiti.oracle.com
for the skip scan Jim mentioned, which answers your question.

Once you become comfortable with the basic concepts, you can delve
deeper in many places, especially http://richardfoote.wordpress.com/
(be sure and check out the index internals presentation).

jg
--
@home.com is bogus.
“I just don't understand why kids would do a stupid thing like
that...”
http://www.signonsandiego.com/uniont...s_1n5nude.html

I had forgotten to mention Richard Foote's web site. It is excellent. Lots
of fantastic information there.
Jim