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]

Re: How to use index

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" <wingkei_leung@ hotmail.comwrot e in message
news:63029411.0 311051846.55f74 06e@posting.goo gle.com...

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]

Re: How to use index

Actually the whole sql is a join with 2 tables:

select
a.INDEXED_COLUM N1,
b.INDEXED_COLUM N2,
b.COLUMN2
b.COLUMN3
b.COLUMN4

from
tableA a, tableB b

where
a.INDEXED_COLUM N1 IN ('A','B','C','D ') and
a.INDEXED_COLUM N2=b.INDEXED_CO LUMN1 and
b.INDEXED_COLUM N2 SYSDATE

TableA has a composite index for column2 and column1
(a.INDEXED_COLU MN2, a.INDEXED_COLUM N1). TableB has a composite index
for column1 and column2 (b.INDEXED_COLU MN1, b.INDEXED_COLUM N2)

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=CHOOS E (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_COL 2_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]

Re: How to use index

wingkei_leung@h otmail.com (Ken) wrote in message news:<63029411. 0311062300.493a d399@posting.go ogle.com>...

Actually the whole sql is a join with 2 tables:
>
select
a.INDEXED_COLUM N1,
b.INDEXED_COLUM N2,
b.COLUMN2
b.COLUMN3
b.COLUMN4
>
from
tableA a, tableB b
>
where
a.INDEXED_COLUM N1 IN ('A','B','C','D ') and
a.INDEXED_COLUM N2=b.INDEXED_CO LUMN1 and
b.INDEXED_COLUM N2 SYSDATE
>
TableA has a composite index for column2 and column1
(a.INDEXED_COLU MN2, a.INDEXED_COLUM N1). TableB has a composite index
for column1 and column2 (b.INDEXED_COLU MN1, b.INDEXED_COLUM N2)
>
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=CHOOS E (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_COL 2_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