oracle 未使用分区表上的索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4667952/
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
Index on partitioned table not used
提问by gjvdkamp
I have to query a reasonably large table (450M rows) that is partitoned and indexed.
我必须查询一个相当大的表（450M 行），它是分区和索引的。
suppose this structure:
假设这个结构：
- load_num(int)
- cust_id(int)
- ... some more columns ...
- load_num(int)
- cust_id(int)
- ...更多的列...
The table is partitioned over load_num, around 3 loads go into one partition. (so load_num is not unique within a partition)
该表在 load_num 上进行分区，大约 3 个负载进入一个分区。（所以 load_num 在分区内不是唯一的）
There there are three indexes, two of them who have load_num, cust_id as the first two columns (int that order)
共有三个索引，其中两个将 load_num、cust_id 作为前两列（按顺序排列）
When I issue this query:
当我发出此查询时：
select *
from fact
where load_num = 100
and cust_id = 12345
It takes quite long to return, so I hit explain plan and it gets the right partition but then does a FULL table scan on it.
返回需要很长时间，所以我点击了解释计划，它得到了正确的分区，但随后对其进行了全表扫描。
Why isn't oracle using one of the two indexes to do a ROWID scan on the partition to get the rows?
为什么oracle 不使用两个索引之一对分区进行ROWID 扫描以获取行？
The cust_id should be fairly unique and the statistics on the table are up to date. We're on 10g Entreprise.
cust_id 应该相当独特，并且表上的统计信息是最新的。我们在 10g 企业。
Came from MS SQL so I'm not up to speed with Oracle yet.
来自 MS SQL，所以我还没有跟上 Oracle 的速度。
Thanks in advance,
提前致谢，
Gert-Jan
格特-简
** EDIT: Some anonimized DDL:
** 编辑：一些匿名的 DDL：
CREATE TABLE FACT
(
LOAD_NUM NUMBER
... columns ..
, CUSTOMER_ID VARCHAR2(20 BYTE)
.. columns
)
TABLESPACE "TS_A"
PCTFREE 0
INITRANS 1
STORAGE
(
BUFFER_POOL DEFAULT
)
PARALLEL 12
PARTITION BY LIST (LOAD_NUM)
(
PARTITION FACT_46 VALUES (46) TABLESPACE FACT_PART_DATA_46
COMPRESS
, PARTITION FACT_52 VALUES (52) TABLESPACE FACT_PART_DATA_52
COMPRESS
, PARTITION FACT_56 VALUES (56) TABLESPACE FACT_PART_DATA_56
COMPRESS
... more partitions ...
)CREATE INDEX SOMESCHEMA.FACT_IDX2 ON SOMESCHEMA.FACT (LOAD_NUM ASC, CUSTOMER_ID ASC, OUTSTANDING_ID ASC)
LOCAL
(
PARTITION FACT_DATA_46
LOGGING
TABLESPACE "FACT_DATA_46"
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
NOCOMPRESS
, PARTITION FACT_DATA_52
LOGGING
TABLESPACE "FACT_DATA_52"
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
NOCOMPRESS
,
... etc etc ..
)
回答by Quassnoi
Why isn't oracle using one of the two indexes to do a ROWID scan on the partition to get the rows?
为什么oracle 不使用两个索引之一对分区进行ROWID 扫描以获取行？
It's hard to tell exactly why Oracledoesn't use the index, since there is nothing in your setup that would prevent it from doing so.
很难确切说明为什么Oracle不使用索引，因为您的设置中没有任何内容可以阻止它这样做。
Most probably, cust_iddistribution is skewed so that Oracle considers PARTITION SCANmore efficient.
最有可能的是，cust_id分布是倾斜的，因此 Oracle 认为PARTITION SCAN效率更高。
Could you please try adding the hint explicitly:
您能否尝试明确添加提示：
SELECT /*+ INDEX (f FACT_IDX2) */
*
FROM fact f
WHERE load_num = 100
AND cust_id = 12345
Make sure that it's used in the plan and check that this method is really faster.
确保在计划中使用它并检查此方法是否真的更快。
Also, please post what does this query return:
另外，请发布此查询返回什么：
SELECT COUNT(*), COUNT(DECODE(cust_id, 12345, 1))
FROM fact f
WHERE load_num = 100
