sql 账号查询一个表查询权限_一个查询语句引发的问题以及巨型表相关操作探索与思考...

背景：

关于这个标题想了试了好几个总觉得欠那么点意思。大致情况是，在某服务支持中，1张大表4.5T左右，该表也是分区表。其中一个执行频繁的SQL写法有很大问题，导致巨表全量扫描，造成IO负载很大，业务收到严重影响。

 由于线上阶段基本上没有改SQL的可能， 只能通过其他途径优化。现场想到一种可行的方案，周末实在手痒，决定测试测试该方案理论上的可行性。

基本情况：

数据库：Oracle 11G

表情况：4.5T左右，二级分区表，3.9万分区，分区如果有数据的话，数据量不大大概是2-3G左右。

分区字段：数据日期+XXX字段。

分区:range-range(应该是range-range这边有点模糊)

SQL情况

SQL比较长，也有好多张表关联，这边只重点说出现性能瓶颈的地方，

SQL：where trunc(分区字段)=trunc(XX, yyyy-mm-dd) and XX like :B ;

导致识别不了分区，只能全分区扫描。

本机测试

表：par_range_range

分区类型:range-range; 

分区字段:data_dt (DATE类型);  hash_flag (number类型)

SQL：模拟生产环境出现的问题

select  count(1) from   par_range_range t

where  hash_flag <1.5 and  hash_flag>=0.1

andtrunc(data_dt) = trunc(to_date('2020-11-01', 'yyyy-mm-dd'));

问题就是 trunc(data_dt) 无法识别那个分区只能走全分区扫描，有经验的人立马知道怎么搞，实际中把搞成 trunc(to_date('2020-11-01', 'yyyy-mm-dd'))<= data_dt < to_date('2020-11-02','yyyy-mm-dd')。现场调整后也是秒杀的，但是甲方需要不改SQL，就能优化。

实验课题：

通过建立索引优化该SQL,(该问题引发一系列思考)

探索巨表建立索引方案(其实这里更感兴趣)

1. 通过建立合适索引把全表扫描转换成索引扫描

2. 巨表怎么建立索引？保证不会占用太多系统资源，比如IO，内存，temp. 甚 至做到系统无感知。因为已经出现了扫描巨表数据时候IO超负载。

3. 前两个可行基础上加快建索引速度

这怎么看都是一个不可能完成的任务。

这里说下最终方案：

建立需要分区索引，建索引时候也要讲究，最初建索引的语句要带上unusable 关键字，这样建索引时候就不会创建索引段，自然也不会扫描表数据。所以这边建立索引能很快完成。

随即按照分区，重建分区索引。因为这边只需要扫描单个表分区，就能很快完成对应分区索引的重建，毕竟扫描数据量小了很多。因此整体方案理论上可行。

数据量：445万，722M ，本机测试环境，数据量有限。这边测试原理

建立索引语句：

create   index idx_prr_dt_flag  on 

par_range_range(trunc(data_dt),hash_flag) local  unusable;

测试环境中很快完成。

建立后查询索引分区：

重建分区索引。

重建完成后，这边只查询到重建的索引分区的segement。说明符合预期，只有在rebulid时候才创建索引segement。

优化效果(重建完所有分区后)：

优化前：全表扫描 4.3万逻辑读/次，

优化后：索引范围扫描，414万逻辑读/次 效率提升了近100倍

总结：

该方案精彩的地方在于把复杂的大表建立索引，成功转换成更加细微力度的建立分区索引,解决了大表建立索引过程中带来的IO，内存等系统资源占用率超大等问题。而重建分区索引，单个分区的数据量要小得多，当然重建也容易的多。最终把问题SQl成功优化。

结束语：

本文就是利用延迟加载，完成超大表建立索引，然后再实践中总结知识点。用论语中一句话结尾吧。论语：知之为知之，不知为不知。我在想是不是可以这样“探索”，知之，为，知之！，不知为，不知。可以这样解读：掌握一门知识，并且赋予实践。然后在实践中再总结知识。不了解或者不掌握一门知识，直接干，最终是不能总结出知识的。