Re: A hint on indexes to avoid table access full

alex.malgaroli_at_gmail.com wrote:
> Hi all.
> I excuse myself for this topic that has probably been answered many
> times, but I have a complex query and I can't figure how to make it
> without getting a TABLE ACCESS FULL.
>
> I have 4 tables (say A, B, C, D), each table has an ID field
> (number(10)) that is primary key.
> Table C has a field OWNER that has its index.
> Table C has two foreign keys towards table B and D.
> Table D has a foreign key towards table A.
>
> Cardinality of tables:
> C ~ 280.000 rows
> B ~ 80.000 rows
> A, B ~ 20.000 rows
>
> Query is like this (prepared statement query with a ? placeholder for
> the parameter):
>
> select a.id as USERID, b.name as NAME, c.*
> from a, b, c, d
> where c.OWNER = ?
> and d.id_D = a.id
> and c.ID_D = d.id
> and c.ID_B = b.id
>
> SQLPLUS Execution plan via "set autotrace on" says:
>
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=801
> Bytes=46458)
> 1 0 NESTED LOOPS (Cost=30 Card=801 Bytes=46458)
> 2 1 HASH JOIN (Cost=30 Card=801 Bytes=43254)
> 3 2 HASH JOIN (Cost=12 Card=801 Bytes=36846)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'C' (Cost=2 Card=801
> Bytes=23229)
> 5 4 INDEX (RANGE SCAN) OF 'IDX_ICMP_OWNER' (NON-UNIQUE)
> (Cost=1 Card=321)
> 6 3 TABLE ACCESS (FULL) OF 'B' (Cost=8 Card=8061
> Bytes=137037)
> 7 2 TABLE ACCESS (FULL) OF 'D' (Cost=16 Card=16993
> Bytes=135944)
> 8 1 INDEX (UNIQUE SCAN) OF 'SYS_C003016' (UNIQUE)
>
> but
>
> select * from B where id = 2
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=51)
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TABINSTRUMENTS' (Cost=2
> Card=1 Bytes=51)
> 2 1 INDEX (UNIQUE SCAN) OF 'SYS_C003049' (UNIQUE) (Cost=1
> Card=8061)
>
> select * from D where id = 4
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=100)
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TABPORTFOLIOS' (Cost=2
> Card=1 Bytes=100)
> 2 1 INDEX (UNIQUE SCAN) OF 'SYS_C003027_1' (UNIQUE) (Cost=1
> Card=16993)
>
>
> I'd like to know if there is a way to avoid the table access full for
> those two tables.
>
> Thanks in Advance.

Frankly, this results in the following questions

- Does your Oracle have a version?
- Are the statistics current and did you use dbms_stats to get them?
- Did you set the opt% parameters correctly, or gather_system_stats?
- Did you run the statement with event 10053 enabled?
- Did you try index hints?
- Do you believe in the myth full table scans are evil?

-- 
Sybrand Bakker
Senior Oracle DBA