I'm trying to speed up my query which selects only 30 records out of
3million records.
All my conditions are indexed but when I explains it, it says TABLE ACCESS
FULL.
I use "between" and, it seemingly, when I reduce range of "between" then it
does INDEX RANGE SCAN.

Is there anyway I can force to do INDEX RANGE SCAN?
Thanks.
-Jay

Jay wrote:

optimizer_index_caching and optimizer_index_cost_adj set to?
Have you analyzed the relevant tables/indexes?

select /*+ index(table_name index_name */ rest of your query ...

domenic.

"Jay" <no (AT) spam (DOT) com> wrote

1- Use an index hint. Build an index on the columns in youtr WHERE
clause and add a hint to the query.

2 - Set optimizer_index_cost_adj=10

3 - Add a rule hint. Rule will always use an index.

"Jay" <no (AT) spam (DOT) com> wrote

The view that a Full Table Scan (FTS) is a bad thing.. is nothing but
an OWT (old wifes' tale).

If the cost of using indexes is bigger than using a FTS, then Oracle
will use a FTS.

Exactly!!! And there you have stated WHY there is a difference in
execution plan. Pity that you did not spend some time thinking this
through as you would have arrived at the answer.

Simple scenario. I have a 3GB index. I do a select with a BETWEEN
clause.

Case 1. The value range specified in the between clause covers 2GB of
data in that index (e.g. WHERE surname BETWEEN 'A' AND 'RZ')

Case 2. The value range specified covers 10MB of index data (e.g.
WHERE surname BETWEEN 'SMITH' AND 'SMYTHE')


Now look at this from the work that the database needs to do.

In case 1, the db looks at this as says "hmm.. you are covering 80% of
all index values. That means that 80% or even more of the data in the
table will satisfy the criteria."

Thus the database, being the clever bugger it is (unlike what many
seems to think), decides NOT to use the index.

80% of the data in the table will match the criteria. So instead of
reading 80% of the index followed by reading 80% of the table, it
rather reads 100% of the table and not use the index at all. (80%
being an example - the actual percentage value is indexes & tables
specific)

Q. what is the most expensive operation in a database?
A. I/O

Case 2. The exact opposite of case 1. Thus the index IS used.


You have proved two things.
a) Oracle CBO works as it should (and better than you thought)
b) You could spend some more time with the Oracle manuals :-)


IMO never ever enforce an index range scan (yes it is possible using
hints). Index range scans can be a lot more painful than a FTS. When
dealing with unknown index ranges, let the database (i.e. CBO)
decides.

--
Billy

"Billy Verreynne" <vslabs (AT) onwe (DOT) co.za> wrote



Like he said: he's doing case 2 (30 rows out of 3 million)
and the optimizer is NOT picking the index...
In the chapter about indexes in Expert oneonone, TK goes
at length under which conditions using an index is or is
not better than doing a FTS. With examples. Worth a look.

Maybe not in his case?

TK's book for one helps in these situations.


His words:

In anyone's book that is not an unknown index range...
I agree though: WHEN dealing with *unknown* ranges, then
it's better to let the CBO do its work. If it tends to err
on the side of pessimism, it's always possible to
nudge it in the right direction with the "optimizer_*"
stuff. That is of course the general case.

OT: waddyareckon: Boks, AllBlacks or the Poms?

--
Cheers
Nuno Souto
wizofoz2k (AT) yahoo (DOT) com.au.nospam

Can you post the query and the explain? I can think of a number of reasons
why your index might not be being used, but you don't give sufficient
information as to why.



--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"Jay" <no (AT) spam (DOT) com> wrote

"Noons" <wizofoz2k (AT) yahoo (DOT) com.au.nospam> wrote

Yeah, I did see that "30 rows being returned/selected", but I was
thinking group by was neglected to be mentioned.

Well either that, or the CBO is seriously screwy when deciding FTS to
do only 30 rows. The latter I will only believe when hard evidence is
given. :-)

Yep. Total coincidence. The Book is right here next to the keyboard.
:-)

Just a pity that he did not add to the myths that FTSs are not evil.
I've seen a lot of frustration and tuning by people who sees a FTS in
an execution plan and immediately go "holy crap!" and then start
swinging away at the problem with index hints. Instead the warning
bells should go as a FTS implies that you want to hit big volumes with
your SQL.

Agree. That is IF the statement on 30 rows being selected ALSO implies
that these are also *only* 30 rows (of the millions) being processed.
And in the absense of more detail, I cannot see that to be the case
when the CBO decides to do a FTS.

Tough one. Of course I'm hoping for the Boks. The Poms are damn good,
but the longer you're on the top, the more difficult it becomes to
stay there. I do not think that they can keep up that level of play
consistently for such a long time. They have everything to lose and
little to gain. Not forgetting the Kiwis either. They have a good
team. And then there's the mightly AllBlacks... So the heart says
Bokke but the brain says mebbe the AllBlacks.

Besides, us southern hemisphere okes have to stand together. :-)

--
Billy

"Billy Verreynne" <vslabs (AT) onwe (DOT) co.za> wrote

One of the scenarios I suspect is that the data is seriously skewed and
there are no histogram stats. (Or alternatively bind variables are being
used in a skewed case). 30 Rows out of 3m have a status of 'UNKNOWN' but as
the table only has 3 distinct values for status 'PROCESSED','UNPROCESSED'
and 'UNKNOWN' the optimizer thinks that it will pick a 3rd of the table with
the condition AND STATUS='UNKNOWN'.

<snip on topic stuff>

yeah that's what I saw in SA all the SA guys I met standing shoulder to
shoulder with the Aussies FWIW I see England winning the competition in a
tough fight with NZ in the final. And then collapsing for the next 10 years.

And another thing I fly 6000 miles to SA and there's no bloody southern
cross or any constellation at all to see in the night sky. Talk about a let
down. sheesh.


--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************