I work primarily in SQL Server and have run across a situatiion in
Oracle that's troubling me....

I have a query that contains a where clause with 2 restrictions. Both
these fields are in a composite index of 3 fields. If I add the third
field to the restriction the optimizer uses the index, however if i
keep it with just the 2 fields it does a table scan and takes forever
unless I put in an index hint to use the composite index. Is this
right?..is this a dba issue?

On Jan 15, 2:49*pm, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
It could be a problem with how up-to-date the statistics are for the
table and its indexes. Or it could be something else.

Very brief, random thoughts worded very loosely: Oracle determines the
access path, whether to use a specific index or not, based on cost
calculations. Cost calculations are the optimizer's best guess as to
the amount of time a particular access path will require - the lowest
cost access path wins. Creating an index on three columns, when only
two are need by the majority of SQL statements potentially (and
unnecessarily) increases the clustering factor calculation for the
index, which describes to Oracle how ordered the rows in the table are
in comparision to the index. The clustering factor is used in cost
calculations. When all three columns in the index are referenced, the
index access path appears to Oracle to be more selective, meaning that
it should return fewer rows than it would if only two columns of the
index were specified.

If the table and index statistics are not up to date, Oracle may
believe, for instance, that there are only 100 rows in the table, and
that the selectivity is indicating that 1% of the rows will be
retrieved. If Oracle believes that the average row length is 80
bytes, a full table scan may only require Oracle to read one 8KB
block, while an index lookup may require 3 or 4 8KB block reads per
row to be retrieved. In such a case, Oracle would determine that a
full table scan is less expensive than an index based access path.
What if this table actually contains 10,000,000 rows, rather than the
100 rows indicated by the statistics? Oracle calculates the cost of
the access paths too low, and may pick an inefficient/wrong access
path.

Oracle initialization parameters can also control the cost of an index
based access path as compared to a full table scan access path. Some
of these initialization parameters change from one Oracle version to
the next, so the exact Oracle version may be very important. If you
really want to find the answer, pick up a copy of "Cost-Based Oracle
Fundamentals" and start digging through a 10053 trace file.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 15, 2:49*pm, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
It could be a problem with how up-to-date the statistics are for the
table and its indexes. Or it could be something else.

Very brief, random thoughts worded very loosely: Oracle determines the
access path, whether to use a specific index or not, based on cost
calculations. Cost calculations are the optimizer's best guess as to
the amount of time a particular access path will require - the lowest
cost access path wins. Creating an index on three columns, when only
two are need by the majority of SQL statements potentially (and
unnecessarily) increases the clustering factor calculation for the
index, which describes to Oracle how ordered the rows in the table are
in comparision to the index. The clustering factor is used in cost
calculations. When all three columns in the index are referenced, the
index access path appears to Oracle to be more selective, meaning that
it should return fewer rows than it would if only two columns of the
index were specified.

If the table and index statistics are not up to date, Oracle may
believe, for instance, that there are only 100 rows in the table, and
that the selectivity is indicating that 1% of the rows will be
retrieved. If Oracle believes that the average row length is 80
bytes, a full table scan may only require Oracle to read one 8KB
block, while an index lookup may require 3 or 4 8KB block reads per
row to be retrieved. In such a case, Oracle would determine that a
full table scan is less expensive than an index based access path.
What if this table actually contains 10,000,000 rows, rather than the
100 rows indicated by the statistics? Oracle calculates the cost of
the access paths too low, and may pick an inefficient/wrong access
path.

Oracle initialization parameters can also control the cost of an index
based access path as compared to a full table scan access path. Some
of these initialization parameters change from one Oracle version to
the next, so the exact Oracle version may be very important. If you
really want to find the answer, pick up a copy of "Cost-Based Oracle
Fundamentals" and start digging through a 10053 trace file.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 15, 2:49*pm, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
It could be a problem with how up-to-date the statistics are for the
table and its indexes. Or it could be something else.

Very brief, random thoughts worded very loosely: Oracle determines the
access path, whether to use a specific index or not, based on cost
calculations. Cost calculations are the optimizer's best guess as to
the amount of time a particular access path will require - the lowest
cost access path wins. Creating an index on three columns, when only
two are need by the majority of SQL statements potentially (and
unnecessarily) increases the clustering factor calculation for the
index, which describes to Oracle how ordered the rows in the table are
in comparision to the index. The clustering factor is used in cost
calculations. When all three columns in the index are referenced, the
index access path appears to Oracle to be more selective, meaning that
it should return fewer rows than it would if only two columns of the
index were specified.

If the table and index statistics are not up to date, Oracle may
believe, for instance, that there are only 100 rows in the table, and
that the selectivity is indicating that 1% of the rows will be
retrieved. If Oracle believes that the average row length is 80
bytes, a full table scan may only require Oracle to read one 8KB
block, while an index lookup may require 3 or 4 8KB block reads per
row to be retrieved. In such a case, Oracle would determine that a
full table scan is less expensive than an index based access path.
What if this table actually contains 10,000,000 rows, rather than the
100 rows indicated by the statistics? Oracle calculates the cost of
the access paths too low, and may pick an inefficient/wrong access
path.

Oracle initialization parameters can also control the cost of an index
based access path as compared to a full table scan access path. Some
of these initialization parameters change from one Oracle version to
the next, so the exact Oracle version may be very important. If you
really want to find the answer, pick up a copy of "Cost-Based Oracle
Fundamentals" and start digging through a 10053 trace file.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 15, 2:49*pm, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
It could be a problem with how up-to-date the statistics are for the
table and its indexes. Or it could be something else.

Very brief, random thoughts worded very loosely: Oracle determines the
access path, whether to use a specific index or not, based on cost
calculations. Cost calculations are the optimizer's best guess as to
the amount of time a particular access path will require - the lowest
cost access path wins. Creating an index on three columns, when only
two are need by the majority of SQL statements potentially (and
unnecessarily) increases the clustering factor calculation for the
index, which describes to Oracle how ordered the rows in the table are
in comparision to the index. The clustering factor is used in cost
calculations. When all three columns in the index are referenced, the
index access path appears to Oracle to be more selective, meaning that
it should return fewer rows than it would if only two columns of the
index were specified.

If the table and index statistics are not up to date, Oracle may
believe, for instance, that there are only 100 rows in the table, and
that the selectivity is indicating that 1% of the rows will be
retrieved. If Oracle believes that the average row length is 80
bytes, a full table scan may only require Oracle to read one 8KB
block, while an index lookup may require 3 or 4 8KB block reads per
row to be retrieved. In such a case, Oracle would determine that a
full table scan is less expensive than an index based access path.
What if this table actually contains 10,000,000 rows, rather than the
100 rows indicated by the statistics? Oracle calculates the cost of
the access paths too low, and may pick an inefficient/wrong access
path.

Oracle initialization parameters can also control the cost of an index
based access path as compared to a full table scan access path. Some
of these initialization parameters change from one Oracle version to
the next, so the exact Oracle version may be very important. If you
really want to find the answer, pick up a copy of "Cost-Based Oracle
Fundamentals" and start digging through a 10053 trace file.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 15, 11:49*am, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
The optimizer only can work with the information it is given. It may
or may not be a DBA issue, that depends on the division of
responsibilities at your site. I would expect the maintenance of
indices to be under the DBA purview.

What you as a developer need to do is become familiar with the tools
available to you. You _must_ become adept with reading and using
explain plans. You should also become familiar with tracing for the
more in-depth problems. You need to be able to work with your DBA to
resolve problems.

You should look at the doc set, especially the concepts manual and the
performance guide, all the basics are there. Tuning is a big subject,
the best book on the optimizer is by Jonathan Lewis. Other books you
will find useful are by Tom Kyte and Cary Milsap. They all show how
to determine what is going on, rather than guess or give overly-broad
rules of thumb. There have been many expositions here and elsewhere
on the web on how to figure this stuff out, but it really helps to
have a foundation in the basics first, then work examples.

In general it is better to tell the optimizer enough for it to figure
the correct course than to use hints. To get past this problem
quickly you might want to look into plan stability (which uses hints,
of course). The optimizer uses costing to figure out which way to
access data, with just the two fields it figures it is cheaper to do
the table scan.

If you set autotrace and post the plan here some might help you. It's
best if you post a complete test case, including code and data.

Always be sure and post the exact version and exact hardware/OS
version you are using. It can make a difference. I'm assuming a lot,
you could be on Oracle7 with the rule based optimizer...

jg
--
@home.com is bogus. "Flying the airplane is more important than
radioing your plight to a person on the ground who is incapable of
understanding or doing anything about it."

On Jan 15, 11:49*am, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
The optimizer only can work with the information it is given. It may
or may not be a DBA issue, that depends on the division of
responsibilities at your site. I would expect the maintenance of
indices to be under the DBA purview.

What you as a developer need to do is become familiar with the tools
available to you. You _must_ become adept with reading and using
explain plans. You should also become familiar with tracing for the
more in-depth problems. You need to be able to work with your DBA to
resolve problems.

You should look at the doc set, especially the concepts manual and the
performance guide, all the basics are there. Tuning is a big subject,
the best book on the optimizer is by Jonathan Lewis. Other books you
will find useful are by Tom Kyte and Cary Milsap. They all show how
to determine what is going on, rather than guess or give overly-broad
rules of thumb. There have been many expositions here and elsewhere
on the web on how to figure this stuff out, but it really helps to
have a foundation in the basics first, then work examples.

In general it is better to tell the optimizer enough for it to figure
the correct course than to use hints. To get past this problem
quickly you might want to look into plan stability (which uses hints,
of course). The optimizer uses costing to figure out which way to
access data, with just the two fields it figures it is cheaper to do
the table scan.

If you set autotrace and post the plan here some might help you. It's
best if you post a complete test case, including code and data.

Always be sure and post the exact version and exact hardware/OS
version you are using. It can make a difference. I'm assuming a lot,
you could be on Oracle7 with the rule based optimizer...

jg
--
@home.com is bogus. "Flying the airplane is more important than
radioing your plight to a person on the ground who is incapable of
understanding or doing anything about it."

On Jan 15, 11:49*am, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
The optimizer only can work with the information it is given. It may
or may not be a DBA issue, that depends on the division of
responsibilities at your site. I would expect the maintenance of
indices to be under the DBA purview.

What you as a developer need to do is become familiar with the tools
available to you. You _must_ become adept with reading and using
explain plans. You should also become familiar with tracing for the
more in-depth problems. You need to be able to work with your DBA to
resolve problems.

You should look at the doc set, especially the concepts manual and the
performance guide, all the basics are there. Tuning is a big subject,
the best book on the optimizer is by Jonathan Lewis. Other books you
will find useful are by Tom Kyte and Cary Milsap. They all show how
to determine what is going on, rather than guess or give overly-broad
rules of thumb. There have been many expositions here and elsewhere
on the web on how to figure this stuff out, but it really helps to
have a foundation in the basics first, then work examples.

In general it is better to tell the optimizer enough for it to figure
the correct course than to use hints. To get past this problem
quickly you might want to look into plan stability (which uses hints,
of course). The optimizer uses costing to figure out which way to
access data, with just the two fields it figures it is cheaper to do
the table scan.

If you set autotrace and post the plan here some might help you. It's
best if you post a complete test case, including code and data.

Always be sure and post the exact version and exact hardware/OS
version you are using. It can make a difference. I'm assuming a lot,
you could be on Oracle7 with the rule based optimizer...

jg
--
@home.com is bogus. "Flying the airplane is more important than
radioing your plight to a person on the ground who is incapable of
understanding or doing anything about it."

On Jan 15, 11:49*am, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
The optimizer only can work with the information it is given. It may
or may not be a DBA issue, that depends on the division of
responsibilities at your site. I would expect the maintenance of
indices to be under the DBA purview.

What you as a developer need to do is become familiar with the tools
available to you. You _must_ become adept with reading and using
explain plans. You should also become familiar with tracing for the
more in-depth problems. You need to be able to work with your DBA to
resolve problems.

You should look at the doc set, especially the concepts manual and the
performance guide, all the basics are there. Tuning is a big subject,
the best book on the optimizer is by Jonathan Lewis. Other books you
will find useful are by Tom Kyte and Cary Milsap. They all show how
to determine what is going on, rather than guess or give overly-broad
rules of thumb. There have been many expositions here and elsewhere
on the web on how to figure this stuff out, but it really helps to
have a foundation in the basics first, then work examples.

In general it is better to tell the optimizer enough for it to figure
the correct course than to use hints. To get past this problem
quickly you might want to look into plan stability (which uses hints,
of course). The optimizer uses costing to figure out which way to
access data, with just the two fields it figures it is cheaper to do
the table scan.

If you set autotrace and post the plan here some might help you. It's
best if you post a complete test case, including code and data.

Always be sure and post the exact version and exact hardware/OS
version you are using. It can make a difference. I'm assuming a lot,
you could be on Oracle7 with the rule based optimizer...

jg
--
@home.com is bogus. "Flying the airplane is more important than
radioing your plight to a person on the ground who is incapable of
understanding or doing anything about it."

On Jan 15, 2:49 pm, Jimbo <jamesfer... (AT) hotmail (DOT) com> wrote:
If the 2 restrictions are against the 2nd and 3rd columns in the
index, the only option the optimizer has (in using this index) is to
perform a index skip scan. If you ensure the leading (1st) column in
the index is specified as one of the restrictions, then the index can
be used.

Dave