Oracle查询在索引编号列上使用'like'，性能不佳

本文介绍了Oracle查询在索引编号列上使用'like'，性能不佳的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 在查询1上，即使id是索引列，也正在执行全表扫描。查询2实现了相同的结果，但速度更快。如果运行查询1返回索引列，则它会快速返回，但如果返回非索引列或整个行则查询需要更长时间。 在查询3中它运行得很快但是代码列是VARCHAR2（10）而不是NUMBER（12）并且索引方式与'id相同'。 为什么查询1没有意识到它应该使用索引？是否应该更改某些内容以允许索引编号列更快地执行？ [查询1] 从人a1 中选择a1。* 其中a1.id喜欢'119％'和rownum< 5 解释计划 选择语句ALL_ROWS 成本：67字节：2,592基数：4 2 COUNT STOPKEY      1 TABLE ACCESS FULL TABLE people    &NBSP;&NBSP;费用：67字节：3,240基数：5 [查询2] 从人a1中选择a1。* ，人a2 其中a1.id = a2.id 和a2.id如'119％'和rownum< 5 解释计划 选择语句ALL_ROWS 成本：11字节：2,620基数：4 5 COUNT止损      4表格访问按行ROWID表格人物     费用：3字节：648基数：1          3 NESTED LOOPS   &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;成本：11字节：2620基数：4结果，&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP; 1个索引快速全扫描索引people_IDX3结果，&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;成本：2字节：54796基数： 7828，点击&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 2索引范围扫描INDEX people_IDX3结果，&NBSP;&NBSP;&NBSP ;         费用：2基数：1 [查询3] sel a1。* 来自人a1 其中a1.code喜欢'119％'和rownum< 5 解释计划 选择语句ALL_ROWS 成本：6字节：1,296基数：2     3 COUNT STOPKEY        2 TABLE INDCESS BY INDEX ROWID TABLE人物       费用：6字节：1,296基数：2         &NBSP;&NBSP; 1个索引范围扫描INDEX people_IDX4结果，&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;成本：3基数：2 解决方案 LIKE模式匹配条件期望将字符类型视为左侧和右侧操作数。当遇到NUMBER时，它会隐式将其转换为char。您的查询1基本上默默地​​重写为： SELECT a1。* FROM people a1 WHERE TO_CHAR（a1.id）LIKE'119％' AND ROWNUM< 5 这种情况发生在您的情况下，这有两个原因： 每行都执行转换，这很慢; 由于WHERE中的函数（虽然是隐含的）谓词，Oracle无法在 A1.ID 列上使用索引。 要绕过它，您需要执行以下操作之一： 创建基于函数的索引 A1.ID 列： CREATE INDEX people_idx5 ON人（TO_CHAR（id））; 如果您需要匹配ID列的前3个字符的记录，请创建另一个NUMBER类型的列，其中只包含这3个字符，并在其上使用普通的 = 运算符。 创建类型为 VARCHAR2 单独列 ID_CHAR c $ c>和f生病了 TO_CHAR（id）。将其编入索引并在 WHERE 条件中使用而不是 ID 。 当然，如果您选择基于现有ID列创建其他列，则需要保持这两个列同步。您可以批量执行此操作作为单个UPDATE，或在ON-UPDATE触发器中，或添加该列到代码中相应的INSERT和UPDATE语句。 On Query 1 a full table scan is being performed even though the id is an indexed column. Query 2 achieves the same result but much faster. If Query 1 is run returning an indexed column then it returns quickly but if non-indexed columns are returned or the entire row is then the query takes longer.In Query 3 it runs fast but the column 'code' is a VARCHAR2(10) in stead of a NUMBER(12) and is indexed the same way as 'id'.Why does Query 1 not pick up that it should use the index? Is there something that should be changed to allow indexed number columns to perform quicker?[Query 1]select a1.*from people a1where a1.id like '119%'and rownum < 5Explain PlanSELECT STATEMENT ALL_ROWSCost: 67 Bytes: 2,592 Cardinality: 4 2 COUNT STOPKEY    1 TABLE ACCESS FULL TABLE people     Cost: 67 Bytes: 3,240 Cardinality: 5[Query 2]select a1.*from people a1, people a2where a1.id = a2.idand a2.id like '119%'and rownum < 5Explain PlanSELECT STATEMENT ALL_ROWSCost: 11 Bytes: 2,620 Cardinality: 45 COUNT STOPKEY    4 TABLE ACCESS BY INDEX ROWID TABLE people    Cost: 3 Bytes: 648 Cardinality: 1        3 NESTED LOOPS        Cost: 11 Bytes: 2,620 Cardinality: 4            1 INDEX FAST FULL SCAN INDEX people_IDX3            Cost: 2 Bytes: 54,796 Cardinality: 7,828            2 INDEX RANGE SCAN INDEX people_IDX3            Cost: 2 Cardinality: 1[Query 3]select a1.*from people a1where a1.code like '119%'and rownum < 5Explain PlanSELECT STATEMENT ALL_ROWSCost: 6 Bytes: 1,296 Cardinality: 2   3 COUNT STOPKEY      2 TABLE ACCESS BY INDEX ROWID TABLE people      Cost: 6 Bytes: 1,296 Cardinality: 2         1 INDEX RANGE SCAN INDEX people_IDX4         Cost: 3 Cardinality: 2 解决方案 LIKE pattern-matching condition expects to see character types as both left-side and right-side operands. When it encounters a NUMBER, it implicitly converts it to char. Your Query 1 is basically silently rewritten to this:SELECT a1.* FROM people a1 WHERE TO_CHAR(a1.id) LIKE '119%' AND ROWNUM < 5That happens in your case, and that is bad for 2 reasons:The conversion is executed for every row, which is slow;Because of a function (though implicit) in a WHERE predicate, Oracle is unable to use the index on A1.ID column.To get around it, you need to do one of the following:Create a function-based index on A1.ID column:CREATE INDEX people_idx5 ON people (TO_CHAR(id));If you need to match records on first 3 characters of ID column, create another column of type NUMBER containing just these 3 characters and use a plain = operator on it.Create a separate column ID_CHAR of type VARCHAR2 and fill it with TO_CHAR(id). Index it and use instead of ID in your WHERE condition.Of course if you choose to create an additional column based on existing ID column, you need to keep those 2 synchronized.You can do that in batch as a single UPDATE, or in an ON-UPDATE trigger, or add that column to the appropriate INSERT and UPDATE statements in your code. 这篇关于Oracle查询在索引编号列上使用'like'，性能不佳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！