oracle SQL 查询进行全表扫描而不是基于索引的扫描
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5255489/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query going for Full Table scan instead of Index Based Scan
提问by Ajitesh
I have two tables:
我有两个表：
create table big( id number, name varchar2(100));
insert into big(id, name) select rownum, object_name from all_objects;
create table small as select id from big where rownum < 10;
create index big_index on big(id);
On these tables if I execute the following query:
在这些表上，如果我执行以下查询：
select *
from big_table
where id like '45%'
or id in ( select id from small_table);
it always goes for a Full Table Scan.
它总是进行全表扫描。
Execution Plan
----------------------------------------------------------
Plan hash value: 2290496975
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3737 | 97162 | 85 (3)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| BIG | 74718 | 1897K| 85 (3)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| SMALL | 1 | 4 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=45 OR EXISTS (SELECT /*+ */ 0 FROM "SMALL" "SMALL"
WHERE "ID"=:B1))
3 - filter("ID"=:B1)
Are there any ways in which we can rewrite the Query So that it always goes for index Scan.
有什么方法可以重写查询，以便它始终进行索引扫描。
回答by Gary Myers
No, no and no.
不，不，不。
You do NOT want it to use an index. Luckily Oracle is smarter than that.
您不希望它使用索引。幸运的是，Oracle 比这更聪明。
ID is numeric. While it might have ID values of 45,450,451,452,4501,45004,4500003 etc, in the indexes these values will be scattered anywhere and everywhere. If you went with a condition such as ID BETWEEN 450 AND 459, then it may be worth using the index.
ID 是数字。虽然它的 ID 值可能为 45,450,451,452,4501,45004,4500003 等，但在索引中这些值将分散在任何地方。如果您使用诸如 ID BETWEEN 450 AND 459 之类的条件，那么可能值得使用该索引。
To use the index it would have to scan it all the way from top to bottom (converting each ID to a character to do the LIKE comparison). Then, for any match, it has to go off to get the NAME column.
要使用索引，它必须从上到下一直扫描它（将每个 ID 转换为一个字符以进行 LIKE 比较）。然后，对于任何匹配项，它必须关闭以获取 NAME 列。
It has decided that it is easier to and quicker to scan the table (which, with 75,000 rows isn't that big anyway) rather than mucking about going back and forth between the index and the table.
它已经决定更容易和更快地扫描表（无论如何，75,000 行并不是那么大），而不是在索引和表之间来回切换。
回答by Ronnis
The others are right, you shouldn't use a numeric column like that.
其他人是对的，您不应该使用这样的数字列。
However, it is actually, the OR <subquery>construct that is causing a (performance) problem in this case. I don't know if it is different in version 11, but up to version 10gr2, it causes a a filter operation with what is basically a nested loop with a correlated subquery. In your case, the use of a numeric column as a varchar also results in a full table scan.
然而，OR <subquery>在这种情况下，实际上是结构导致了（性能）问题。我不知道它在版本 11 中是否有所不同，但直到版本 10gr2，它都会导致过滤操作，它基本上是一个带有相关子查询的嵌套循环。在您的情况下，使用数字列作为 varchar 也会导致全表扫描。
You can rewrite your query like this:
您可以像这样重写查询：
select *
from big
where id like '45%'
union all
select *
from big
join small using(id)
where id not like '45%';
With your test case, I end up with a row count of 174000 rows in big and 9 small. Running your query takes 7 seconds with 1211399 consistent gets. Running my query 0,7 seconds and uses 542 consistent gets.
对于您的测试用例，我最终得到的行数为 174000 行，大行 9 行。运行您的查询需要 7 秒，1211399 个一致的获取。运行我的查询 0.7 秒并使用 542 个一致的获取。
The explain plans for my query is:
我的查询的解释计划是：
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8604 | 154 (6)|
| 1 | UNION-ALL | | | |
|* 2 | TABLE ACCESS FULL | BIG | 8603 | 151 (4)|
| 3 | NESTED LOOPS | | 1 | 3 (0)|
|* 4 | TABLE ACCESS FULL | SMALL | 1 | 3 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| BIG | 1 | 0 (0)|
|* 6 | INDEX UNIQUE SCAN | BIG_PK | 1 | 0 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_CHAR("ID") LIKE '45%')
4 - filter(TO_CHAR("SMALL"."ID") NOT LIKE '45%')
6 - access("BIG"."ID"="SMALL"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
542 consistent gets
0 physical reads
0 redo size
33476 bytes sent via SQL*Net to client
753 bytes received via SQL*Net from client
76 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1120 rows processed
回答by SunilK
Something like this might work:
像这样的事情可能会奏效：
select *
from big_table big
where id like '45%'
or exists ( select id from small_table where id = big.id);
