oracle indexes

#1

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.

#2

"doug" <douglass_davis (AT) earthlink (DOT) net> wrote

Quote:

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

#3

"doug" <douglass_davis (AT) earthlink (DOT) net> wrote

Quote:

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

#4

"doug" <douglass_davis (AT) earthlink (DOT) net> wrote

Quote:

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

#5

"doug" <douglass_davis (AT) earthlink (DOT) net> wrote

Quote:

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

#6

doug wrote:

Quote:

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

#7

doug wrote:

Quote:

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

#8

doug wrote:

Quote:

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

#9

doug wrote:

Quote:

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

#10

On Jun 4, 9:01*pm, doug <douglass_da... (AT) earthlink (DOT) net> wrote:

Quote:

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