Query not using Index --

3
votes

My question concerns Oracle 11gr2 and the use of indexes in SQL queries. Please help me with issue below, I am unable to use index on this query:

SELECT ListingManagerID,
       FirstName,
       LastName,
       PrimaryPhone,
       AlternatePhone,
       Email,
       UserName,
       Password,
       RecallGuid,
       CustomerSince,
       PWChangeRequired,
       PWExpireDate,
       CallingHours,
       CreateDate,
       CreateIPAddress,
       LastLogin,
       AutoRenewFlag,
       LastUpdated,
       UpdatedBy,
       AutoRenewRemovedDate,
       AutoRenewDate,
       isSupplier,
       PrefferedLanguage,
       AboutMe,
       PictureFilePath,
       IsProfilePictureDisplay,
       LocaleID,
       Address1,
       Address2,
       CityID,
       CountryID,
       StateProvinceID,
       PostalCode,
       RegistrationSource,
       PPCTypeID,
       GhostUser,
       ProfileId,
       TimezoneID,
       OCA
FROM ListingManager
WHERE trim(lower(LISTINGMANAGERID)) = 'e.kkagacoe4aaae7rnr9lua5'
  1. Created Index

SQL> CREATE INDEX IDX_LISTINGMANAGER_ID ON listingmanager (LOWER(listingmanagerid));

Index created.

  1. Gather Statistics

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('INTERSHOP', 'LISTINGMANAGER' , CASCADE=>TRUE);

PL/SQL procedure successfully completed.

Index was created on Listingmanager Column, But still Execution plan is showing full table scan. I don't know why full table scan happening still.

ListingManagerID is the primary key of the table, can I modify index created by primary key?

PLAN_TABLE_OUTPUT
--------------------------------------------------
| Id | Operation        | Name           | Rows | Bytes | Cost (%CPU)|    
--------------------------------------------------
| 0  | SELECT STATEMENT |                | 2013 | 487K| 1784 (1)|    
|* 1 | TABLE ACCESS FULL| LISTINGMANAGER | 2013 | 487K| 1784 (1)|

Predicate Information (identified by operation id):

1 - filter(TRIM(LOWER("LISTINGMANAGERID"))='e.kkagacoe4aaae7rnr9lua5' )

Thanks

2
sqldatabaseoracleindexing
I would guess this is Oracle, given the reference to PL/SQL - but I shouldn't have to guess. Please add an appropriate tag for your RDBMS (especially since indexing and tuning is highly DB specific) – Damien_The_Unbeliever
Does it work if you remove the 'trim' from the table-side of the WHERE clause? Can't you normalise your table data to be trimmed? Does it help if you add a computed trimmed + lowered column then index and query that? – Rup
@Rup - Yes, Its uses the Index if remove "trim" – Aryan johan
@Damein- sorry about that... just added – Aryan johan
Adding TRIM to your function based index or using RTRIM in your statement should both do the trick. – Lieven Keersmaekers

2 Answers

5
votes

Oracle's function indexes are extremely sensitive. You have to use pretty much exactly what you used to create the index in the query. I'm not 100% sure but that may even extend to whitespace and brackets. In any case, your index was not trimmed, so it won't be used on a trimmed predicate.

Trim & lowercase your constants/parameters rather than the column (and/or store a trimmed, lowered copy) so you don't have to use function based indexes at all. If you have a function on the column, you make it very hard to use an index.

3
votes

Instead of the index you've created you need to create this one:

CREATE INDEX IDX_LISTINGMANAGER_ID ON listingmanager (trim(LOWER(listingmanagerid)));

The case is that functional index should be EXACTLY the same with the condition in the WHERE clause.

Also you could use this trick in order to make your index work:

SELECT 
  <fields>
FROM ListingManager
WHERE lower(LISTINGMANAGERID) like '%e.kkagacoe4aaae7rnr9lua5%'
AND trim(lower(LISTINGMANAGERID)) = 'e.kkagacoe4aaae7rnr9lua5'

But it's pulled by it's ears. Also you should be awared that sometimes optimizer consider FULL TABLE SCAN as a more efficient way to retrieve data than to use an index, but I think not in your case (to make optimizer use an index, you should use hints).

For example, your query using hints:

SELECT /*+ INDEX (ListingManager IDX_LISTINGMANAGER_ID)*/
       ListingManagerID,
       FirstName,
       LastName,
       PrimaryPhone,
       AlternatePhone,
       Email,
       UserName,
       Password,
       RecallGuid,
       CustomerSince,
       PWChangeRequired,
       PWExpireDate,
       CallingHours,
       CreateDate,
       CreateIPAddress,
       LastLogin,
       AutoRenewFlag,
       LastUpdated,
       UpdatedBy,
       AutoRenewRemovedDate,
       AutoRenewDate,
       isSupplier,
       PrefferedLanguage,
       AboutMe,
       PictureFilePath,
       IsProfilePictureDisplay,
       LocaleID,
       Address1,
       Address2,
       CityID,
       CountryID,
       StateProvinceID,
       PostalCode,
       RegistrationSource,
       PPCTypeID,
       GhostUser,
       ProfileId,
       TimezoneID,
       OCA
FROM ListingManager
WHERE trim(lower(LISTINGMANAGERID)) = 'e.kkagacoe4aaae7rnr9lua5'