Full Table Scan

[ac]

Hi,

I'tuning a view that's doing a full table scan on the three tables it accesses. The columns in the where clause are all indexed. Does anyone know why these indexes are suppressed?

Ac

[sudip]

Hi!
R U using COST based or Rule Based Optimiser.
Can you post the query?

[hrishy]

hi

why not try to embed hints in the query and force the optimizer to use it.

regards
hrishy

[ac]

Hrishy, What hints do U mean?

I'm using cost-based optimization and below is the query:

SELECT
'0000' AS UNI_CODE,
A.UNI_CODE,
40 AS BEST_ID,
C.UNI_SUM,
A.STAFF_NO,
A.MAN_ID,
A.MONTH,
A.YEAR,
sum(A.VALUE * C.FACTOR),
'I',
C.LEVEL_IDX,
C.USAGE
FROM GEN_INFO A,
NEW_DATA B,
ADDED_DATA C
WHERE
(C.USAGE = 23 OR C.USAGE = 24 OR C.USAGE = 26)
AND B.NO = -104
AND C.ITEM_NO = A.ITEM_NO
AND B.START_NO >= A.NUM
AND DECODE(A.NUM, B.START_NO, B.ACTUAL_MONTH, 12) >= A.MONTH
AND C.CUST_ID = A.CUST_ID
AND B.CUST_ID = A.CUST_ID
GROUP BY
A.CUST_ID,
C.UNI_SUM,
A.STAFF_NO,
A.MAN_ID,
A.MONTH,
A.NUM,
C.LEVEL_IDX,
C.USAGE

########################
AC

[Edited by ac on 05-25-2001 at 08:36 AM]

[padmam]

Hi , 25th may 2001 18:17 hrs chennai

Well some info still lacking from your part.

What kind of index

in which order the Index has been done(if it is composite)

synatx of SQL statement(since if you issue inner columns of composite index of no use)==>Well that is pasted the rest please.

What kind of tuning you did ?

Hints he means ALL_ROWS,CHOOSE,FIRST_ROWS etc. due you need more info on this ?

Cheers

Padmam

[ac]

Two B-Tree indexes and one BITMAP. The indexes are composite but all the columns used in "EQUALITY" joins are the laeding columns of the index...USER_IND_COLUMNS.

AC

[padmam]

Hi, 26th May 2001 12:06 hrs chennai

I still dont know which column you have indexed.

Any how the problem should be using the SQL functions on index columns wont allow using the index scanning.

http://www.dbasupport.com/oracle/ora8/fbi.shtml

from the above link you can understand how to use function index.

Let me know the collum order of indexing and the columns indexed the tuning output on this thread.

Cheers

Padmam