-
edited
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]
Saurabh Garg
OCP 9i
-
what u want to no
r u sure u posted corrected sql ,
as there is no condition in where clause for class_master_cbsc.
definition of index sub_master_cbsc_pk1
( i mean columns ???)
waht is ur problem , is the query taking more time ????
yeah!!!
-
You are joining three tables but there is only one where condition, you will get cartesian results.
Sanjay
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|