Re: 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" <spendius_at_muchomail.com> wrote in message 
news:1167563904.416245.84940_at_v33g2000cwv.googlegroups.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

> ***************************************

>