hi ,
i am doing application tunning and i have generated the explain plan for the query

query is

explain plan set statement_id='TEST' for select class_code,CLASS_NAME,SUBJECT_NAME,CHAPTER_NAME,CHAPTER_DETAIL,FILE_LOCATION,DECODE(FILE_LOCATION,nu ll,'Not Available','Available') from class_master_cbse,class_subject_chapter_cbse,subject_master_cbse where subj
ect_master_cbse.SUBJECT_CODE = class_subject_chapter_cbse.SUBJECT_CODE and CHAPTER_NAME like '%'||(upper(substr('universe',1,1)) || substr('universe',2))||'%' order by CLASS_CODE

the explain plan is

OPERATION OPTIONS OBJECT_NAME
------------------------------ ------------------------------ ------------------------------
SELECT STATEMENT
SORT ORDER BY
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL CLASS_SUBJECT_CHAPTER_CBSE
TABLE ACCESS BY INDEX ROWID SUBJECT_MASTER_CBSE
INDEX UNIQUE SCAN SUBJECT_MASTER_CBSE_PK1
TABLE ACCESS FULL CLASS_MASTER_CBSE

what i have interpreted is that there is full table scan of
CLASS_SUBJECT_CHAPTER_CBSE
and
CLASS_MASTER_CBSE

there is an index on CLASS_SUBJECT_CHAPTER_CBSE.chapter_code and
CLASS_MASTER_CBSE.class_code

please help

saurabh

[Edited by saurabhg29 on 02-27-2002 at 01:15 AM]