查询未使用索引

Question

我的问题涉及Oracle 11gr2和在SQL查询中使用索引。请帮我解决下面的问题，我无法在此查询中使用索引：

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. 创建索引

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

索引已创建。

1. 收集统计信息

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

PL/SQL程序执行成功。

虽然在Listingmanager Column上创建了索引，但执行计划仍显示全表扫描。我不知道为什么还会发生全表扫描。

ListingManagerID是该表的主键，我可以修改由主键创建的索引吗？

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

谓词信息（由操作ID标识）：

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

谢谢

- Aryan johan

---

我猜这是Oracle，因为提到了PL/SQL，但我不应该必须去猜测。请添加适当的标签来标识您的关系型数据库管理系统（尤其是索引和调优高度依赖于具体的数据库）。 - Damien_The_Unbeliever

如果您从WHERE子句的表侧删除'trim'，它是否有效？您不能将表数据规范化为已修剪吗？如果您添加一个计算的修剪+小写列，然后索引和查询它，这是否有帮助？ - Rup

@Rup - 是的，如果去掉“trim”，它会使用索引。 - Aryan johan

@Damein- 对不起...刚刚添加了。 - Aryan johan

将TRIM添加到您的函数基于索引中或在语句中使用RTRIM都可以解决问题。 - Lieven Keersmaekers

我肯定会考虑在插入行时将函数应用于listingmanagerid，以确保它被修剪并转换为小写（或大写），并添加约束以强制执行它。 - David Aldridge

Answer 1

Oracle的函数索引非常敏感。你必须在查询中使用与创建索引时几乎完全相同的内容。我不是100%确定，但这可能甚至包括空格和括号。无论如何，由于您的索引未被修剪，因此它不会用于修剪谓词。

将常量/参数修剪并转换为小写，而不是对列进行修剪（和/或存储修剪后的小写副本），这样您就根本不需要使用基于函数的索引。如果对列有一个函数，那么使用索引将变得非常困难。

- LoztInSpace

---

成功了，非常感谢...我使用了CREATE INDEX IDX_LISTINGMANAGER_ID ON listingmanager (trim(LOWER(listingmanagerid))); - Aryan johan

很高兴听到它起作用了，但我仍然认为将其保存为预修剪的形式是一个好主意。请记住，默认情况下，varchar比较不区分大小写并且会自动修剪右侧空格。 - LoztInSpace

Answer 2

你需要创建这个索引，而不是你已经创建的那个：

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

案例是函数索引应该与WHERE子句中的条件完全相同。

此外，您可以使用此技巧使您的索引起作用：

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

但它是被牵着耳朵走的。同时，您应该知道有时优化器认为使用FULL TABLE SCAN比使用索引检索数据更有效率，但我认为这不适用于您的情况（要让优化器使用索引，您应该使用提示）。

例如，使用提示的查询：

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'

- whatsupbros