暂无图片
Oracle本地分区索引查询慢
我来答
返回问答列表
分享
手机用户1685
2022-05-17
Oracle本地分区索引查询慢
暂无图片 100M
oracle分区索引慢查询优化

数据库为oracle11g, 配置8U32G.

我有一张订单表order,总数据5kw. 按天分区, 每天大概10w~20w.

order{
     id varchar(32)//  订单号,8位日期+随机数, 建本地分区索引,唯一, ORDER_UK_ID
    mno varchar(12) //商户号, 建本地分区索引,非唯一,   IDX_MNO_LOCAL
    part as to_number(substr(id,1,8)) //虚列, 分区列, 截id的前8位日期.
    ........其它大约20个字段.
}
同时建全局索引IDX_PART_MNO(part,mno), 测试时根据需要开启.
以下是查询大商户m111111和小商户m222222一周的交易量的查询计划:


#########################################
使用全局索引(part,mno):
#########################################
select count(MNO) from ORDER t where t.part between 20210202 and 20210209 and t.MNO='m1' ;

COUNT(MNO)
-------------------
54040

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 558 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | INDEX RANGE SCAN| IDX_PART_MNO | 83306 | 1301K| 558 (1)| 00:00:07 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."PART">=20210202 AND "T"."MNO"='m1' AND
"T"."PART"<=20210209)
filter("T"."MNO"='m1')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
865 consistent gets
0 physical reads
0 redo size
539 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

>>>>>>>>>>>>>>>select count(MNO) from ORDER t where t.part between 20210202 and 20210209 and t.MNO='m2' ;

COUNT(MNO)
-------------------
2141

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 558 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | INDEX RANGE SCAN| IDX_PART_MNO | 8 | 128 | 558 (1)| 00:00:07 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."PART">=20210202 AND "T"."MNO"='m2' AND
"T"."PART"<=20210209)
filter("T"."MNO"='m2')


Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
854 consistent gets
2 physical reads
0 redo size
538 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

#########################################
使用本地分区索引(part,mno):
#########################################
>>>>>>>>>>>>>>>select count(MNO) from ORDER t where t.part between 20210202 and 20210209 and t.MNO='m1' ;

COUNT(MNO)
-------------------
54040

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 7368 (1)| 00:01:29 | | |
| 1 | SORT AGGREGATE | | 1 | 16 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 83306 | 1301K| 7368 (1)| 00:01:29 | 19792 | 19799 |
|* 3 | TABLE ACCESS FULL | ORDER | 83306 | 1301K| 7368 (1)| 00:01:29 | 19792 | 19799 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T"."MNO"='m1' AND "T"."PART">=20210202 AND "T"."PART"<=20210209)


Statistics
----------------------------------------------------------
147 recursive calls
0 db block gets
20581 consistent gets
18337 physical reads
0 redo size
539 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

>>>>>>>>>>>>>>>select count(MNO) from ORDER t where t.part between 20210202 and 20210209 and t.MNO='m2' ;

COUNT(MNO)
-------------------
2141

---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 947 (0)| 00:00:12 | | |
| 1 | SORT AGGREGATE | | 1 | 16 | | | | |
| 2 | PARTITION RANGE ITERATOR | | 8 | 128 | 947 (0)| 00:00:12 | 19792 | 19799 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| ORDER | 8 | 128 | 947 (0)| 00:00:12 | 19792 | 19799 |
|* 4 | INDEX RANGE SCAN | IDX_MNO_LOCAL | 1595 | | 9 (0)| 00:00:01 | 19792 | 19799 |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T"."PART">=20210202 AND "T"."PART"<=20210209)
4 - access("T"."MNO"='m2')


Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2033 consistent gets
797 physical reads
0 redo size
538 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


结论:
大商户m1 小商户m2
全局索引 快 快
本地分区索引 慢,全表扫描 快,但慢于全局索引

按照我的理解:分区+本地分区索引==分表+索引.
因此使用全局索引查询的耗时应只是略优于本地分区索引.
但实际执行的时候,查询大商户m1走了全表扫描,产生了很多逻辑读和物理读.
而如果建全局索引(part,merchantNo)后,上述两个查询都比较快.

在下百思不得其解, 想请教各位Oracle大神:
1. 为什么使用本地分区索引的时候要走全表扫描?而全局索引不会.
2. 为什么本地分区索引产生了这么多consistent get和physical reads?
3. 分区建分区索引和分表建索引是否一回事?能否从原理上解释一下.
4. 怎样优化本地分区索引查询到像分表一样高效?

我来答
添加附件
收藏
分享
问题补充
9条回答
默认
最新
赵勇

以下为个人的理解和认识：

  1. 为什么使用本地分区索引的时候要走全表扫描?而全局索引不会.
    答：本地分区索引可以看做是每个表分区一个索引。加之你的分区键列是part as to_number(substr(id,1,8))，即一天一个分区。所以，当你按(part,mno)来建分区时，每个索引分区内，其PART列中的值是完全一样的。如果MNO的条件值在表中的存量较多时（比如为M1时），索引范围扫的成本可能比对指定表分区的全表扫的成本更高（这一点可以通过添加INDEX的提示来验证），从而使用了全表扫。反之，当MNO的条件值在表中的存量较小时（比如为M2时），走索引的成本更低，所以，会选择走索引。

  2. 为什么本地分区索引产生了这么多consistent get和physical reads?
    答：因为走本地分区索引的执行计划，有回表的步骤（TABLE ACCESS BY LOCAL INDEX ROWID）。physical reads可能在多次执行后，就会减少，甚至为0.这主要取决于数据库对数据块的缓存算法。你可以加/*+ gather_plan_statistics */ 提示后执行，然后使用 select * from table(dbms_xplan.display_cursor(’’,’’,‘allstats last cost’))来查看索引所处步骤的逻辑读和物理读开销。

  3. 分区建分区索引和分表建索引是否一回事?能否从原理上解释一下.
    答：对于本地分区索引，可以这么理解。即，每一个表分区，就是一个个小表，而对应的本地分区索引， 就是在这一个个小表上创建的索引。

  4. 怎样优化本地分区索引查询到像分表一样高效?
    答：这需要具体问题具体分析。但从创建复合索引的一般原则上来说，我们应该把等值条件的列放到前边，过滤性好的放到前边，最常用的列放到前边。具体到你的这条SQL，把MNO列放前面，可能就会有不一样的表现。另外，如果不改索引的列的顺序，由于你的分区键列是part as to_number(substr(id,1,8))，即一天一个分区。且分区内只有这一个值，可以尝试换成LIST分区试试效果。

暂无图片 评论
暂无图片 有用 1
打赏 0
暂无图片
手机用户1685

更正: 文末的(part,merchantNo)应为(part,mno)

暂无图片 评论
暂无图片 有用 0
打赏 0
薛晓刚

分区索引建立以后，就要用分区去查。比如按照时间分区一个月一分区，那么查询条件就要带时间。而分区以后如果where后面没有时间或者时间范围跨了多个分区甚至全部，那么就会扫描多个或者所有分区。性能反而下降。

所以分区性能主要看查询范围是不是落在一个分区内。

暂无图片 评论
暂无图片 有用 0
打赏 0
手机用户1685
题主
2022-05-17
您看我的sql,全都是带了分区键的, 只扫描那七个分区. 问题是,为什么没用分区索引,走到了全表扫描,导致奇慢.
薛晓刚

一共几个分区？一般来说我们认为超过2个分区都不好了。如果一共12个分区扫描7个，那和全部没区别了。

暂无图片 评论
暂无图片 有用 0
打赏 0
手机用户1685
题主
2022-05-17
每天一个分区,共两年,指定扫描了8个, 20210202~20210209
赵勇

补充：“为什么使用本地分区索引的时候要走全表扫描?而全局索引不会.”的后半问：
数据库是基于成本来决定访问路径的。不是不会走全表扫，而是当走全局索引的成本大于对指定分区的全表扫的成本时，也是会弃全局索引，而选择走指定分区的全表扫。在你上面的实验中，当走全局索引时，其成本为558，而对目标分区（8个分区）做全表扫的成本是7368，如果在某种情况下，走全局索引的成本大于7368了，就会选择走分区的全表扫了。

暂无图片 评论
暂无图片 有用 0
打赏 0
手机用户1685

感谢赵大神的耐心解答, 后面我把每个分区的数据都单独建了一站表,然后建索引.  神奇的是全都走了索引,而且开销很小(如下图).

难道分区+本地分区索引和分表+索引, 这两种索引结构有很大区别? 还是oracle 11g的本地分区索引有bug, 求解?



暂无图片 评论
暂无图片 有用 0
打赏 0
赵勇
2022-05-18
1、大家一样才能比。你分区表上的是组合索引（part,mno),而单独建表后的索引，是单列索引（MNO）。这么比是不适合的。2、我前边说的一些方法，比如调整索引列的顺序，和改分区表类型为LIST分区，你可以先试试。
手机用户1685

@赵勇 是一样的, 我的分区表上建立了全局索引(part,mno),测试完后就把它禁用了,  本地分区索引列只有mno, 分表索引列也是mno.  但是两种的效率区别巨大(前者全(分区)扫描耗费几秒,后者索引范围扫描只需几十毫秒), 要是本地分区索引能和分表索引一样效率就符合我的预期和需求了.

暂无图片 评论
暂无图片 有用 0
打赏 0
赵勇
2022-05-18
你前边提供的测试过程，看到本地分区索引也是part,mno两列吧？（“使用本地分区索引(part,mno):”）
手机用户1685

@赵勇 不好意思,那个标题敲错了,是使用本地分区索引(mno).  经过多番尝试后,我想我已经找到方法了. 我把本地分区索引由(mno)改为(mno,part)之后,查询效率瞬间改善,甚至优于分表和全局索引. 所以我思考了一下原因,但不知对不对:

对于我的查询条件: part=20210202 and mno=m1.   如果使用本地分区索引(mno) ,oracle的查询过程为:

1. 先根据part定位到分区20210202,并找到它的索引文件(假如叫idx1), 

2. 找到所有的m1的叶子节点,并取出rowid.

3. 根据rowid读取对应的数据行来对比part字段,即回表.

4.统计总数.

我以前一直以为,根据part定位到分区之后,第3步回表就用不着. 但看来并不是. 但这只是我的推测,不知对不对, 非常感谢您的回答.

同时我试了range分区和list分区效率差不多.

暂无图片 评论
暂无图片 有用 0
打赏 0
赵勇
2022-05-18
是的。通过part=20210202可明确数据在哪个分区，但还需要验证一下MNO='m1’的行中，共part列，是否等于20210202,所以，需要回表。
老虎刘

如果是global index , 需要创建 mno, part 两字段联合, 注意part要放在后面; 

如果是local index , 只需要mno单字段索引, part字段用来定位具体访问哪几个分区上的local index;  

暂无图片 评论
暂无图片 有用 0
打赏 0
回答交流
Markdown


请输入正文
提交
相关推荐
各位大佬，查锁表的时候，查出一个session sid，但是根据V$session里的sql_id查的时候是一个select语句呢？怎么能查到那个session里的update或者delete呢？
回答 1
已采纳
setlinesize260setpagesize1000colsidfor99999colspidfora8coleventfora30colmodulefora35colmachinefora15
ASM下某表空间加了太多的DATAFILE
回答 2
已采纳
第一、最简单，无风险，不影响应用，挨个resize数据文件，保留表空间内数据文件数量不变alterdatabasedatafile'xxxxx'resizexxm；或第二迁移到其他表空间1、新建一个表
Oracle：impdp开并行的时候 如果只有1个文件，这时候进程数量应该是多少个？
回答 1
主要是看你有几个CPU，你开和CPU数量差不多就行。能发挥出来并行。
为什么执行计划中因含有INSTR、LIKE、ERGEXP_LIKE等而导致的嵌套循环，被驱动表不能走索引？
回答 1
已采纳
INSTR、LIKE、ERGEXPLIKE等会匹配所有数据，走索引的访问路径只能是INDEXFULLSCAN。而INDEXFULLSCAN是单块读，全表扫描是多块读。如果INDEXFULLSCAN需要
v$process一直增长
回答 5
这个应该是前台应用程序连接数据库没有正常断开导致，如果vsessuon找不到，就从dbahistactivesesshistory或者vactivesessionhistory视图里面找找是哪个应用。
Oracle 是不是一个表中 只能有一个聚集索引？
回答 3
已采纳
clusterindex是在clustertable里用的，如果是单独的一张表也没必要用到cluster，cluster一般2张表以上，而clusterindex就用两张表相同的列，可以看看官方文档h
请教各位大佬，oracle11.2.04如何收缩表空间呢？
回答 1
在线收缩有高水位的表，其占用的高水位会降低，表空间就会被释放。altertableXXXshrinkspace;收缩表需要重建其索引或直接收缩表空间：altertablespacetempshrink
oracle里面有像mysql那样的按位与 & ，按位或 | 操作吗？
回答 2
已采纳
按位与selectbitand(7,1)fromdual;按位或selectbitor(7,1)fromdual;
京东云上安装的Oracle数据库，官方提供技术支持吗？
回答 1
已采纳
首先问一下要是京东云的问题他们肯定会支持，如果要是数据库的问题，他们估计不会支持吧。如果你们买了Oracle的服务，那就可以找原厂支持。
Oracle中如果建了一个组合索引 当用这个组合索引中的某个字段去做条件查询时， 查询速度会变快吗？
回答 4
已采纳
对于一个组合索引（a,b,c），以（a）、（a，b）、（a，b，c）为where条件都可以加速查询，如果where条件中没有a，只以b或c为查询条件，这个时候oracle可能会走索引跳跃扫描或者全表扫
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
暂无图片墨值悬赏
55M
ORACLE 11.2.0 触发器 获取 空间属性的wkt格式，使用 sde.st_astext 函数，数据错乱
25M
求Cylance杀毒软件安装包测试数据库安全
25M
PostgreSQL ERROR: found xmin 2146512304 from before relfrozenxid 2146512317
10M
clickhouse 查询视图需要给基表的权限，有什么办法能场查视图不能查表呢？
10M
oracle apex是否可以使用nfc功能，以及录像保存功能
10M
视图v$sql_cs_selectivity 同一个RANGE_ID之下出现了同一个微词的重复记录。
10M
一个update 语句行锁升级 触发bug导致死锁myql宕机？请大佬帮忙分析下更具体原因
10M
时区升级
10M
Goldengate源端PDB，被克隆替代后，重新配置麻烦，有无其它方便，高效方法，恢复Goldengate？
10M
OGG使用COLMAP部分字段不同步问题
热门文章
Oracle 同一包中不同谓词的多个视图的行级安全性
[译文] Oracle 将 MySQL HeatWave 置于 Autopilot 上
在Oracle中，CREATE GLOBAL TEMPORARY TABLE（）
oracle如何保证数据一致性读和避免脏读
Oracle 带文件名的UTL_HTTP帖子
ORA-00600[1:dbktDefFileInit], [48005] +ORA-07445+ORA-48216
Oracle 隐含参数（仅参考）
Oracle NetSuite 客户说 | 雷士国际如何点亮统一、高效的全球智能化管理“明灯”？
Oracle 搜索产生巨大重做的前10个查询
在Oracle中，什么是ORAchk工具？