DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: application tunning

  1. #1
    Join Date
    Sep 2001
    Posts
    120

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    14

    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!!!

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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
  •  


Click Here to Expand Forum to Full Width