Why index is not being used in this query?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Why index is not being used in this query?

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Why index is not being used in this query?

    Hi,

    Database is 8i. Foll. qry does full table scan. Can't say why.

    select
    to_char(gl.effective_date,'DDMMRRRR') Ref_Date,
    SUBSTR(gl.description,1,18) Ref_doc,
    cc.segment1 Ref_Fund
    from
    gl_je_lines gl,
    gl_code_combinations cc
    where
    cc.code_combination_id = gl.code_combination_id

    Explain plan
    --------------
    Code:
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Hint=CHOOSE		3 M	 	81355  	 	      	             	 
      HASH JOIN		3 M	276 M	81355  	 	      	             	 
        TABLE ACCESS FULL	GL_CODE_COMBINATIONS	182 K	1 M	682  	 	      	             	 
        TABLE ACCESS FULL	GL_JE_LINES	3 M	250 M	27660
    gl_code_combinations has a unique index on code_combination_id and gl_je_lines has a composite non unique index on a code_combination_id plus other column.

    Pl. advise.

    Thanks in Adv.
    Last edited by marist89; 08-19-2004 at 11:57 AM.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Good link, I even bookmarked it.

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Thanks Jeff.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

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