A hint on indexes to avoid table access full

From: <alex.malgaroli_at_gmail.com>
Date: 22 Aug 2006 07:52:10 -0700
Message-ID: <1156258330.500768.121530@h48g2000cwc.googlegroups.com>

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. Received on Tue Aug 22 2006 - 09:52:10 CDT