retrieval of views

[mkey]

HI all,
In the plan_Table I see that one of the table is doing a full scan to retrive infor.

The view look something like this:

CREATE OR REPLACE VIEW V_DRAWINGS (LINE_ID,
LN_DESIG,LINE_NAME,ROUTE_ID,ROUTE_NAME,SHEET_NAME,
EVENT_ID,EREP_ID,STATION_BEGIN,STATION_END,MEASURE_BEG,
MEASURE_END,SERIES_BEGIN,SERIES_END,SYSTEM,
EDIT_STATUS_CODE,VALIDITY,BEGIN_SERIES_ID,END_SERIES_ID)
AS
SELECT LN.LINE_ID, LN.DESIGNATOR AS LN_DESIG, LN.DESCRIPTION
AS LINE_NAME, RT.ROUTE_ID, RT.DESCRIPTION AS ROUTE_NAME,
EREP.NAME AS SHEET_NAME, ERAN.EVENT_ID, EREP.EVENT_REPORT_ID AS EREP_ID, SP0.STATION AS STATION_BEGIN,
SP1.STATION AS STATION_END, SP0.MEASURE AS MEASURE_BEG, SP1.MEASURE AS MEASURE_END, S0.SERIES AS SERIES_BEGIN,
S1.SERIES AS SERIES_END, LS.DESCRIPTION AS SYSTEM, ERAN.EDIT_STATUS_CODE, ERAN.VALIDITY,
S0.SERIES_ID AS BEGIN_SERIES_ID, S1.SERIES_ID AS END_SERIES_ID
FROM
LINE LN,
EVENT_REPORT EREP,
EVENT_CROSS_REF EXREF,
EVENT_RANGE ERAN,
STATION_POINT SP0,
STATION_POINT SP1,
SERIES S0,
SERIES S1,
ROUTE RT,
LINE_SYSTEM_TYPE_CL LS
WHERE EREP.EVENT_REPORT_ID = EXREF.EVENT_REPORT_ID AND EXREF.EVENT_ID = ERAN.EVENT_ID AND
ERAN.STATION_ID_BEGIN = SP0.STATION_ID AND ERAN.STATION_ID_END = SP1.STATION_ID AND
SP0.SERIES_ID = S0.SERIES_ID AND SP1.SERIES_ID = S1.SERIES_ID AND
S0.ROUTE_ID = RT.ROUTE_ID AND LN.LINE_ID = RT.LINE_ID AND
LN.SYSTEM_TYPE_CL = LS.CODE
AND EREP.EVENT_REPORT_TYPE = 'DRAWING'

ON the station_point table, the table access show its full table scan.
I have checked the indexes on this table.
SQL> select index_name from user_indexes where table_name like 'STATION_POINT';

INDEX_NAME
------------------------------
STATION_MEASURE
STATION_POINT_FK1
STATION_POINT_FK2
STATION_POINT_PK
STATION_ROUTE_ID
STATION_STATION

Should I have to rebuild the index?
Any suggestion or thoughts will be of help!

 

[sem]

Full scan in some cases may be more preferable or even inevitable, but you may try to analyze your table to give your optimizer more fresh information on table data or enforce index using by hints. Regards, Dima