How to use index

[Ken]

Dear all,

I have a beginner of oracle and recently I have a prob with using
indexes. I have a table with 5 columns, with no primary key set in the
table. An index has created for column 1. I have a sql which retrieve
rows based on the indexed column:

select *
from my_table
where
column_1 in ('A','B','C')

bcoz I select columns which are not indexed, the index created for
column 1 cannot used either. Pls suggest a way so that I can retrieve
all columns, at the same time db will make use of my index in column
1.

Many many thanks if anyone can kindly help me.

[mcstock]

> bcoz I select columns which are not indexed, the index created for

column 1 cannot used either not true. the columns in the select list do not affect which indexes the
optimizer uses

your IN clause is the equivalent of 3 equality predicates with OR -- the
optimizer may very well not use the index if it appears that it would be
faster to do a full table scan

do you have statistics on the table?

how many rows are in the table? the 'rule of thumb' is that oracle will use
an index if it determines that about 2% of the rows will be returned

--mcs

"Ken" <wi***********@hotmail.com> wrote in message
news:63**************************@posting.google.c om... Dear all,

I have a beginner of oracle and recently I have a prob with using
indexes. I have a table with 5 columns, with no primary key set in the
table. An index has created for column 1. I have a sql which retrieve
rows based on the indexed column:

select *
from my_table
where
column_1 in ('A','B','C')

bcoz I select columns which are not indexed, the index created for
column 1 cannot used either. Pls suggest a way so that I can retrieve
all columns, at the same time db will make use of my index in column
1.

Many many thanks if anyone can kindly help me.

[Ken]

Actually the whole sql is a join with 2 tables:

select
a.INDEXED_COLUMN1,
b.INDEXED_COLUMN2,
b.COLUMN2
b.COLUMN3
b.COLUMN4

from
tableA a, tableB b

where
a.INDEXED_COLUMN1 IN ('A','B','C','D') and
a.INDEXED_COLUMN2=b.INDEXED_COLUMN1 and
b.INDEXED_COLUMN2 > SYSDATE

TableA has a composite index for column2 and column1
(a.INDEXED_COLUMN2, a.INDEXED_COLUMN1). TableB has a composite index
for column1 and column2 (b.INDEXED_COLUMN1, b.INDEXED_COLUMN2)

The optimizer does not use the index in tableB when the 2 tables
joined together. Instead, it does a full scan. The trace is listed
below:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=4 Bytes=124)
1 0 HASH JOIN (Cost=5 Card=4 Bytes=124)
2 1 INDEX (FAST FULL SCAN) OF 'IX_TABLE_A_COL2_COL1' (NON
-UNIQUE) (Cost=1 Card=4 Bytes=24)

3 1 TABLE ACCESS (FULL) OF 'TABLE_B' (Cost=3 Card=16 Bytes=4
00)

Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
111 consistent gets
0 physical reads
0 redo size
854 bytes sent via SQL*Net to client
1039 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
4 rows processed

Thanks

[Ed prochak]

wi***********@hotmail.com (Ken) wrote in message news:<63**************************@posting.google. com>...

Actually the whole sql is a join with 2 tables:

select
a.INDEXED_COLUMN1,
b.INDEXED_COLUMN2,
b.COLUMN2
b.COLUMN3
b.COLUMN4

from
tableA a, tableB b

where
a.INDEXED_COLUMN1 IN ('A','B','C','D') and
a.INDEXED_COLUMN2=b.INDEXED_COLUMN1 and
b.INDEXED_COLUMN2 > SYSDATE

TableA has a composite index for column2 and column1
(a.INDEXED_COLUMN2, a.INDEXED_COLUMN1). TableB has a composite index
for column1 and column2 (b.INDEXED_COLUMN1, b.INDEXED_COLUMN2)

The optimizer does not use the index in tableB when the 2 tables
joined together. Instead, it does a full scan.
Since the index on tableB is column1,column2 but your query has no
value for the beginning of that index (ie the where clause has no
values for tableB.column1). So the optimizer cannot compute a value or
even a range to use in the index.
... The trace is listed
below:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=4 Bytes=124)
1 0 HASH JOIN (Cost=5 Card=4 Bytes=124)
2 1 INDEX (FAST FULL SCAN) OF 'IX_TABLE_A_COL2_COL1' (NON
-UNIQUE) (Cost=1 Card=4 Bytes=24)

3 1 TABLE ACCESS (FULL) OF 'TABLE_B' (Cost=3 Card=16 Bytes=4
00)

Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
111 consistent gets
0 physical reads
0 redo size
854 bytes sent via SQL*Net to client
1039 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
4 rows processed

Thanks

Given the plan shown above, as long as tableB remains the smaller of
the two tables, the hash join will be plenty fast. (You're going to
need a LOT more rows than that to have any concern about performance.)

lastly, consider doing a more detailed design and make some primary
keys (you get the index automatically.

HTH,
ed