Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Question Unanswered: [question] Index on Multiple fields - how does it work?

    Let's say I have ONE index on the combined columns: FirstName, LastName

    If I do a SELECT using only LastName in my WHERE clause, will my query use the index?

    -The Le

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    There are probably variations, but in most of the cases I've tested in the past, the index was used even if you're only including the second field in the index

    Code:
    create table test_index (firstnm varchar2(10), lastnm varchar2(10));
    
    insert into test_index values ('chuck','forbes');
    insert into test_index values ('john','smith');
    insert into test_index values ('samuel','jackson');
    
    select * from test_index where lastnm = 'forbes';
    
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	3.00417961656836  	 	      	             	 
      TABLE ACCESS FULL	FORBESC.TEST_INDEX	1  	14  	3.00417961656836
    Code:
      
    create index ix on test_index(firstnm, lastnm);
    
    select * from test_index where lastnm = 'forbesc';
    
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	1.00086158256161  	 	      	             	 
      INDEX FULL SCAN	FORBESC.IX	1  	14  	1.00086158256161
    version 10.1.0.4
    ---=Chuck

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    thats because it is faster to read an index then a table. However, it is still doing a full scan of the index.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2007
    Posts
    6
    This also goes the same with function-based index even if your query won't use the function.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try setting up 2 indexes

    create index ix1 on test_index(firstnm, lastnm);
    create index ix2 on test_index(lastnm,firstnm);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •