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.

Assuming you have 9i or above the above is not an explain plan. Well at least not one you should be using.

Instead of whatever ancient script you are running ... run this:

SELECT * FROM TABLE(dbms_xplan.display);

Obviously Oracle has decided the cost of using your indexes is higher than not using them. In addition to running proper EXPLAIN PLANS post the DDL for the indexes and verify the validity of statistics gathered using DBMS_STATS.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org