范围搜索在oracle中更快地检索数据

Question

我有两个表。

create table person
(
ID integer,
a_number varchar(9),
first_name varchar(25),
last_name varchar(25),
etc ...
);

create table number_in_ranges_mv
(   range_id number(9,0) , 
begin_range number(9,0), 
end_range number(9,0)   
)

我需要检索特定范围内的所有a_number。

我有以下查询

select nums.range_id, count(p. a_number)
from number_in_ranges nums
left join person p on to_number(p. a_number)
between nums.begin_range and nums.end_range 
group by nums.range_id;

但是由于CCD_ 2表具有大约100个mill记录。

这是查询计划

Plan hash value: 497207773

-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                    |  8899 |   234K|       |   594K (32)| 00:00:24 |
|   1 |  HASH GROUP BY           |                    |  8899 |   234K|       |   594K (32)| 00:00:24 |
|   2 |   MERGE JOIN OUTER       |                    |  1918M|    48G|       |   520K (22)| 00:00:21 |
|   3 |    SORT JOIN             |                    |  8899 |   147K|       |    28   (4)| 00:00:01 |
|   4 |     MAT_VIEW ACCESS FULL | NUMBER_IN_RANGES_MV|  8899 |   147K|       |    27   (0)| 00:00:01 |
|*  5 |    FILTER                |                    |       |       |       |            |          |
|*  6 |     SORT JOIN            |                    |    86M|   822M|  2642M|   412K  (1)| 00:00:17 |
|   7 |      INDEX FAST FULL SCAN| PERSON_ANBR_IDX    |    86M|   822M|       | 67694   (1)| 00:00:03 |
-------------------------------------------------------------------------------------------------------

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

5 - filter("NUMS"."END_RANGE">=TO_NUMBER("A_NUMBER"(+)))
6 - access("NUMS"."BEGIN_RANGE"<=TO_NUMBER("A_NUMBER"(+)))
filter("NUMS"."BEGIN_RANGE"<=TO_NUMBER("A_NUMBER"(+)))

如何改进此查询？

谢谢！

Answer 1

如果每个范围在person表中的相关行百分比较低(小于5%，理想情况下小于1%(，则函数索引可以帮助提高查询性能。a_number上的直接索引根本没有帮助。

最直接的解决方案是在转换表达式上添加索引。例如：

create index ix1 on person (to_number(a_number));

现在，如果对于每个范围，匹配行的百分比都高于5%，那么这个索引可能没有帮助。在这种情况下，合并加入仍然有希望，但情况不同。

Answer 2

虽然您可以在range_id、a_number等基础列上密集使用索引，但您也可以在左联接中仅从下面的人员中选择a_nnumber列，以在一定程度上提高现有性能

select nums.range_id, count(p. a_number)
from number_in_ranges nums
left join (Select distinct a_number from person) p on 
to_number(p. 
a_number)
between nums.begin_range and nums.end_range 
group by nums.range_id;