Optimizing Sql - unable to use index

[Haider Kazmi]

I need help trying to optimize a SQL query. I am using Oracle 8i.

I have a table with about 1.2 million records, lets call it T1. I am
doing a join from another table, lets say T2 which has a restriction
on it. T2's id is a foreign key on T1.

T1 also has a index on
T2id2T1id T1 (T1.t2Id, T1.id)

What happens is if I retrive just the T1.id from the query, it uses
the T2id2T1id index for a reverse walk. However as soon as I retrieve
some other column from T1, say T1.some_col, oracle decides to do a
full table scan of T1.

Even if I force an optimizer hint /*+ index (t1 t2id2t1id) */, it does
a full scan of this index.

Any clues to why oracle decides to do a full table scan on a
1.2million record table???

[Jim Kennedy]

"Haider Kazmi" <hk****@despammed.com> wrote in message
news:f1**************************@posting.google.c om...

I need help trying to optimize a SQL query. I am using Oracle 8i.

I have a table with about 1.2 million records, lets call it T1. I am
doing a join from another table, lets say T2 which has a restriction
on it. T2's id is a foreign key on T1.

T1 also has a index on
T2id2T1id T1 (T1.t2Id, T1.id)

What happens is if I retrive just the T1.id from the query, it uses
the T2id2T1id index for a reverse walk. However as soon as I retrieve
some other column from T1, say T1.some_col, oracle decides to do a
full table scan of T1.

Even if I force an optimizer hint /*+ index (t1 t2id2t1id) */, it does
a full scan of this index.

Any clues to why oracle decides to do a full table scan on a
1.2million record table???

Are the datatypes of the joined fields the same? If not that might be the
problem. Also is the table and indexes analyzed? Are you retrieving a
small percentage of the rows or something larger like 10% or more? (that can
cause a full table scan because it might be faster.)

What is the explain plan? What is the tkprof output?
Jim

[Michael Draves]

hk****@despammed.com (Haider Kazmi) wrote in message news:<f1**************************@posting.google. com>...

I need help trying to optimize a SQL query. I am using Oracle 8i.

I have a table with about 1.2 million records, lets call it T1. I am
doing a join from another table, lets say T2 which has a restriction
on it. T2's id is a foreign key on T1.

T1 also has a index on
T2id2T1id T1 (T1.t2Id, T1.id)

What happens is if I retrive just the T1.id from the query, it uses
the T2id2T1id index for a reverse walk. However as soon as I retrieve
some other column from T1, say T1.some_col, oracle decides to do a
full table scan of T1.

Even if I force an optimizer hint /*+ index (t1 t2id2t1id) */, it does
a full scan of this index.

Any clues to why oracle decides to do a full table scan on a
1.2million record table???
Take a look at this article on setting the init.ora parameters
optimizer_index_cost_adj and optimizer_index_caching at
http://www.dbazine.com/jlewis12.shtml .

I ran the following, written based on my understanding of the above
article, through out a day without any values overriding the defaults
to get a guess for some 'good' initials settings. After bouncing the
instance users did see a significant improvement in search time
because indexes were used more frequently. When I ran the same
scripts after setting them the first time and trying the new values
the response was actually worse.

set linesize 50;
select to_char(sysdate,'YYYY-MM-DD-HH24:MI:SS') from dual;
select 'optimizer_index_cost_adj='||to_char(round(100*(ma x(seq)/max(scat))))
as "jlewis init.ora settings"
from (
select average_wait as "SEQ",0 as "SCAT" from v$system_event
where event = 'db file sequential read'
union all
select 0,average_wait from v$system_event
where event = 'db file scattered read'
)
union all
select 'optimizer_index_caching='||to_char(round(avg(cach e_hit_ratio)))
from (
select (1-(phy.value/(cur.value+con.value)))*100 cache_hit_ratio
from v$sysstat cur
,v$sysstat con
,v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads'
and (1-(phy.value/(cur.value+con.value)))*100 > 0
union all
select (1-((phy.value-dir.value)/(cur.value+con.value)))*100
cache_hit_ratio
from v$sysstat cur
,v$sysstat con
,v$sysstat phy
,v$sysstat dir
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and dir.name = 'physical reads direct'
and phy.name = 'physical reads'
and (1-((phy.value-dir.value)/(cur.value+con.value)))*100 > 0
union all
select (1-(physical_reads/
(decode(db_block_gets+consistent_gets,0,.000000000 1,db_block_gets+consistent_gets))))*100
cache_hit_ratio
from v$buffer_pool_statistics
where
(1-(physical_reads/
(decode(db_block_gets+consistent_gets,0,.000000000 1,db_block_gets+consistent_gets))))*100 0

);

[Misho]

Try something simple first.
If T1 and T2 have primary keys defined, and T2 has a FK to the PK of
T1, try DROPPING the combined index(es) first. Let the DB use PK's
only.
However, if you are trying to select other fields than the ones
indexed, it is normal for the DB to do a full table scan. Try a
COUNT(*) of the query. Does it run faster?
Post a script for the tables, PK's and FK's, and the query. It may
give us some more clues.

Cheers.