What index access path does INDEX_SS yield ?

I can't reproduce the case of
index_ss(alias index_name)
becoming a full tablescan.

However, thinking further about this piece of trace file,
it looks to me as if Oracle hasn't see your hint at all.
This suggests that there is a syntax error somewhere
in your hint.

Can you show us:
The COMPLETE set of hints you have included
and
An example of the SQL statement with the
output from dbms_xplan - including the
filter_predicates section.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Spendius" wrote in message
news:1167563904.416245.84940-at-v33g2000cwv.googlegro ups.com...
> Forgot to add the following lines from a 10053 trace:
> ***************************************
> SINGLE TABLE ACCESS PATH
> Column (#2): ID(NUMBER)
> AvgLen: 5.00 NDV: 228728 Nulls: 0 Density: 4.3720e-06 Min: -1 Max:
> 5766027
> Table: TABLE_1 Alias: TF
> Card: Original: 12956500 Rounded: 57 Computed: 56.65 Non
> Adjusted: 56.65
> Access Path: TableScan
> Cost: 29085.35 Resp: 29085.35 Degree: 0
> Cost_io: 28450.00 Cost_cpu: 5144333483
> Resp_io: 28450.00 Resp_cpu: 5144333483
> kkofmx: index filter:"TF"."ID"=5485186
> AND "TF"."OTHE_ID"="TT"."OTHE_ID"
> AND "OT"."DSCR"="TT"."DSCR"
> Access Path: index (skip-scan)
> SS sel: 4.3720e-06 ANDV (#skips): 4378945
> SS io: 4378945.00 vs. table scan io: 28450.00
> Skip Scan rejected
> Access Path: index (FullScan)
> Index: TABLE_1_PK
> resc_io: 48164.00 resc_cpu: 3060392722
> ix_sel: 1 ix_sel_with_filters: 4.3720e-06
> Cost: 48621.98 Resp: 48621.98 Degree: 1
> Best:: AccessPath: TableScan
> Cost: 29085.35 Degree: 1 Resp: 29085.35 Card: 56.65 Bytes:
> 0
> ***************************************
>

> However, thinking further about this piece of trace file,
> it looks to me as if Oracle hasn't see your hint at all.
> This suggests that there is a syntax error somewhere
> in your hint.
I don't think so as the optimizer reacts to my hint, it
reacts the wrong way but does react anyway: when
I remove the hint I get an FTS on TABLE_1.

> However, thinking further about this piece of trace file,
> it looks to me as if Oracle hasn't see your hint at all.
> This suggests that there is a syntax error somewhere
> in your hint.
I don't think so as the optimizer reacts to my hint, it
reacts the wrong way but does react anyway: when
I remove the hint I get an FTS on TABLE_1.

"Spendius" wrote in message
news:1167908823.841971.158290-at-51g2000cwl.googlegro ups.com...
>> However, thinking further about this piece of trace file,
>> it looks to me as if Oracle hasn't see your hint at all.
>> This suggests that there is a syntax error somewhere
>> in your hint.
> I don't think so as the optimizer reacts to my hint, it
> reacts the wrong way but does react anyway: when
> I remove the hint I get an FTS on TABLE_1.
>


True,

I was fooled by the presence of the calculation of
the full tablescan - which doesn't appear in any of
my attempts to reproduce the problem when I use
the hint.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

"Spendius" wrote in message
news:1167908823.841971.158290-at-51g2000cwl.googlegro ups.com...
>> However, thinking further about this piece of trace file,
>> it looks to me as if Oracle hasn't see your hint at all.
>> This suggests that there is a syntax error somewhere
>> in your hint.
> I don't think so as the optimizer reacts to my hint, it
> reacts the wrong way but does react anyway: when
> I remove the hint I get an FTS on TABLE_1.
>


True,

I was fooled by the presence of the calculation of
the full tablescan - which doesn't appear in any of
my attempts to reproduce the problem when I use
the hint.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

"Jonathan Lewis" wrote in message
news:b96dnZlGYbfNdAHYnZ2dnUVZ8turnZ2d-at-bt.com...
>
> "Spendius" wrote in message
> news:1167908823.841971.158290-at-51g2000cwl.googlegro ups.com...
>>> However, thinking further about this piece of trace file,
>>> it looks to me as if Oracle hasn't see your hint at all.
>>> This suggests that there is a syntax error somewhere
>>> in your hint.
>> I don't think so as the optimizer reacts to my hint, it
>> reacts the wrong way but does react anyway: when
>> I remove the hint I get an FTS on TABLE_1.
>>
>
>
> True,
>
> I was fooled by the presence of the calculation of
> the full tablescan - which doesn't appear in any of
> my attempts to reproduce the problem when I use
> the hint.
>

Unless, of course, a syntax error is causing
some of your hints to become "invisible", so that
the net effect of the "visible" hints forces
the optimizer into an execution path that
has to do the full tablescan

Do you have any hints AFTER the index_ss() ?

What's the exact list of hints as it appears in
your query ?

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

"Jonathan Lewis" wrote in message
news:b96dnZlGYbfNdAHYnZ2dnUVZ8turnZ2d-at-bt.com...
>
> "Spendius" wrote in message
> news:1167908823.841971.158290-at-51g2000cwl.googlegro ups.com...
>>> However, thinking further about this piece of trace file,
>>> it looks to me as if Oracle hasn't see your hint at all.
>>> This suggests that there is a syntax error somewhere
>>> in your hint.
>> I don't think so as the optimizer reacts to my hint, it
>> reacts the wrong way but does react anyway: when
>> I remove the hint I get an FTS on TABLE_1.
>>
>
>
> True,
>
> I was fooled by the presence of the calculation of
> the full tablescan - which doesn't appear in any of
> my attempts to reproduce the problem when I use
> the hint.
>

Unless, of course, a syntax error is causing
some of your hints to become "invisible", so that
the net effect of the "visible" hints forces
the optimizer into an execution path that
has to do the full tablescan

Do you have any hints AFTER the index_ss() ?

What's the exact list of hints as it appears in
your query ?

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

"Jonathan Lewis" wrote in message
news:ReGdnda80ec0SAHYnZ2dnUVZ8turnZ2d-at-bt.com...
>
> I can't reproduce the case of
> index_ss(alias index_name)
> becoming a full tablescan.
>
>

My earlier comment should have been

I can't reproduce the case of
index_ss(alias index_name)
becoming a __index full scan__.


rather than the
becoming a full tablescan.
that I typed.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

"Jonathan Lewis" wrote in message
news:ReGdnda80ec0SAHYnZ2dnUVZ8turnZ2d-at-bt.com...
>
> I can't reproduce the case of
> index_ss(alias index_name)
> becoming a full tablescan.
>
>

My earlier comment should have been

I can't reproduce the case of
index_ss(alias index_name)
becoming a __index full scan__.


rather than the
becoming a full tablescan.
that I typed.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Jonathan Lewis wrote:
> Sybrand,
>
> It has also been pointed out to you several times that
> Oracle "hints" are not just hints in the English-language
> sense of the word. In the absence of bugs, and provided
> they are syntactically correct and legal, they must be
> obeyed.

Bugs. That kind of negates this whole paragraph, doesn't it?
We can't explicate all of the internal processes that oracle
goes through to determine a path, only marvel at what a trace
tells us. So even though the mere fact that it is binary code
makes it deterministic, the fact that we can't know all of the code
adds an element of chaos. Which drives "hints" to the English
meaning of the word, since we can only use heuristics to figure
it out. And of course, you, Jonathan, are the best at that! Kinda
makes me wonder what I'm missing if we disagree here.

As many simple demonstrations have shown, chaos doesn't
require many variables.
http://www.physics.lsa.umich.edu/demolab/demo.asp?id=60

>
> The last time you made your erroneous claim, I asked you
> for an example that demonstrated your point - I am still
> waiting for an answer.
>

I believe this thread answers that, and the answer to Spendius'
original question is "yes, changing the relative volumes of data
can affect the path chosen even with a hint."

Somehow I wonder if we are stumbling around a strangeness
with global indices here. Something like "expect sparseness
from the point of view of a field within a global index so increase
the cost of a skip scan."

jg
--
@home.com is bogus.
"The Saints Are Coming... and their quarterback used to play for us." -
San Diego DJ introducing a U2 song.