RBO vs CBO

[DiggidyMack69]

Hello folks,
I'm really at a loss here and Im hoping you can help.

I several queries that join a number of indexed tables by various id
numbers that are used to pull up only a couple rows (so I think
selectivity is high). The tables are all in the 2000 - 50000 range in
number of rows.

When we run the following ANALYZE statement for each table and run the
qeries through the optimizer we see that we have several FULL scans on
several tables that do have indexes on the fields being queried.
Though we have FULL scans the queries run very fast.

ANALYZE TABLE xxx Estimate statistics sample 20 percent;

On the flip side if we do this oher ANALYZE statement for each table
and run them through the optimizer we see that each table either has
INDEX ROWID or UNIQUE SCAN. This plan which is hitting the indexes
runs much slower than it's counterpart.

ANALYZE TABLE xxx Delete statistics;
Why is the query with the full scans faster? When are FULL scans
usually acceptable? From what I understand about RBO/CBO is that when
the statistics are deleted we are forcing the optimizer to use RBO.
When we create the statistics we are using CBO.

[Hans Forbrich]

DiggidyMack69 wrote:

I'm really at a loss here and Im hoping you can help.

Optimization is extremely version deependant. You need to mention version or
operating system.

Unless you are running Oracle 7.0.36 on Wang OS and are ashamed to admit it
<g>

/Hans

[dharma-]

Tom Kytes Quote
"Full scans are not evil"
http://asktom.oracle.com/pls/ask/f?p...:9422487749968,

HTH
-dharma

Di***********@hotmail.com (DiggidyMack69) wrote in message news:<c8*************************@posting.google.c om>...

Hello folks,
I'm really at a loss here and Im hoping you can help.

I several queries that join a number of indexed tables by various id
numbers that are used to pull up only a couple rows (so I think
selectivity is high). The tables are all in the 2000 - 50000 range in
number of rows.

When we run the following ANALYZE statement for each table and run the
qeries through the optimizer we see that we have several FULL scans on
several tables that do have indexes on the fields being queried.
Though we have FULL scans the queries run very fast.

ANALYZE TABLE xxx Estimate statistics sample 20 percent;

On the flip side if we do this oher ANALYZE statement for each table
and run them through the optimizer we see that each table either has
INDEX ROWID or UNIQUE SCAN. This plan which is hitting the indexes
runs much slower than it's counterpart.

ANALYZE TABLE xxx Delete statistics;
Why is the query with the full scans faster? When are FULL scans
usually acceptable? From what I understand about RBO/CBO is that when
the statistics are deleted we are forcing the optimizer to use RBO.
When we create the statistics we are using CBO.

[Ed prochak]

Di***********@hotmail.com (DiggidyMack69) wrote in message news:<c8*************************@posting.google.c om>...

Hello folks,
I'm really at a loss here and Im hoping you can help.

I several queries that join a number of indexed tables by various id
numbers that are used to pull up only a couple rows (so I think
selectivity is high). The tables are all in the 2000 - 50000 range in
number of rows.

When we run the following ANALYZE statement for each table and run the
qeries through the optimizer we see that we have several FULL scans on
several tables that do have indexes on the fields being queried.
Though we have FULL scans the queries run very fast.

ANALYZE TABLE xxx Estimate statistics sample 20 percent;

On the flip side if we do this oher ANALYZE statement for each table
and run them through the optimizer we see that each table either has
INDEX ROWID or UNIQUE SCAN. This plan which is hitting the indexes
runs much slower than it's counterpart.

ANALYZE TABLE xxx Delete statistics;
Why is the query with the full scans faster? When are FULL scans
usually acceptable? From what I understand about RBO/CBO is that when
the statistics are deleted we are forcing the optimizer to use RBO.
When we create the statistics we are using CBO.

A full scan is faster if the table is relatively small. For an index
scan, ORACLE must make two disc reads: one for the index and the other
for the actual rows. Besides having a small number of rows, I'll bet
the tables have only a few, small columns. IOW, the row itself is
small, meaning more rows per block.

You didn't post any actual timings. are you concerned about time
differences in the range of seconds-to-minutes or
milliseconds-to-seconds? In the latter case, unless you have high
transaction volume, you might not gain much trying to improve this.

HTH,
Ed

[DiggidyMack69]

> Di***********@hotmail.com (DiggidyMack69) wrote in message news:<c8*************************@posting.google.c om>...

Hello folks,
I'm really at a loss here and Im hoping you can help.

I several queries that join a number of indexed tables by various id
numbers that are used to pull up only a couple rows (so I think
selectivity is high). The tables are all in the 2000 - 50000 range in
number of rows.

When we run the following ANALYZE statement for each table and run the
qeries through the optimizer we see that we have several FULL scans on
several tables that do have indexes on the fields being queried.
Though we have FULL scans the queries run very fast.

ANALYZE TABLE xxx Estimate statistics sample 20 percent;

On the flip side if we do this oher ANALYZE statement for each table
and run them through the optimizer we see that each table either has
INDEX ROWID or UNIQUE SCAN. This plan which is hitting the indexes
runs much slower than it's counterpart.

ANALYZE TABLE xxx Delete statistics;
Why is the query with the full scans faster? When are FULL scans
usually acceptable? From what I understand about RBO/CBO is that when
the statistics are deleted we are forcing the optimizer to use RBO.
When we create the statistics we are using CBO.

aj*********@hotmail.com (dharma-) wrote in message >news:<fb**************************@posting.google .com>...
Tom Kytes Quote
"Full scans are not evil"
http://asktom.oracle.com/pls/ask/f?p...:9422487749968,

HTH
-dharma

Thanks for the link and info. After looking that over and re-reading a
section on performance tuning I think I have a good idea what is going
on. I was leaning in that direction but some other folks were telling
me the opposite. Any other links on when and when not to worry about
table scans would be very useful. Thanks....

[Laconic2]

"DiggidyMack69" <Di***********@hotmail.com> wrote:

I several queries that join a number of indexed tables by various id
numbers that are used to pull up only a couple rows (so I think
selectivity is high). The tables are all in the 2000 - 50000 range in
number of rows.

There is a case known as "walking the index". This might be called an
"index scan".
It's often slower than a table scan. Either one is much slower than an
index lookup.
It can come up when a compound index is used, but the prime key of the
compound index is unspecified.

example:

create index MY_INDEX on MY_TABLE (OBJECT_TYPE, OBJECT_ID);

select * from MY_TABLE where OBJECT_ID = 7654321;
MY_INDEX can't be used for a lookup, because the OBJECT_TYPE hasn't been
specified.

if we try

select * from MY_TABLE where OBJECT_ID = 7654321
and OBJECT_TYPE = 1;

The same query runs blazingly fast. Why? Because now the index is being
used for a lookup,
not a scan.

I don't know enough about your case to know if this is relevant. But I hope
it helps.

[Hans Forbrich]

DiggidyMack69 wrote:

I'm really at a loss here and Im hoping you can help.

Optimization is extremely version deependant. You need to mention version or
operating system.

Unless you are running Oracle 7.0.36 on Wang OS and are ashamed to admit it
<g>

/Hans

[Laconic2]

"DiggidyMack69" <Di***********@hotmail.comwrote:

I several queries that join a number of indexed tables by various id
numbers that are used to pull up only a couple rows (so I think
selectivity is high). The tables are all in the 2000 - 50000 range in
number of rows.

There is a case known as "walking the index". This might be called an
"index scan".
It's often slower than a table scan. Either one is much slower than an
index lookup.
It can come up when a compound index is used, but the prime key of the
compound index is unspecified.

example:

create index MY_INDEX on MY_TABLE (OBJECT_TYPE, OBJECT_ID);

select * from MY_TABLE where OBJECT_ID = 7654321;
MY_INDEX can't be used for a lookup, because the OBJECT_TYPE hasn't been
specified.

if we try

select * from MY_TABLE where OBJECT_ID = 7654321
and OBJECT_TYPE = 1;

The same query runs blazingly fast. Why? Because now the index is being
used for a lookup,
not a scan.

I don't know enough about your case to know if this is relevant. But I hope
it helps.