Oracle where子句的性能不佳

I have the following Oracle table:

我有以下Oracle表:

create table my_table(
   start int,
   end int
);

insert into my_table values(1, 3);
insert into my_table values(5, 7);
insert into my_table values(11, 200);
insert into my_table values(311, 5000);
insert into my_table values(60004, 60024);
insert into my_table values(123213, 12312312);

This table has 1M rows and stores number range ('start', 'end'), all numbers are unique, it has no duplicate range and any numbers can only be in one range in this table, I have the following query which passes a variable my_number to identity the 'start' of the range.

这个表有1M行并存储数字范围('start','end'),所有数字都是唯一的,它没有重复的范围,任何数字只能在这个表的一个范围内,我有以下查询通过一个变量my_number用于标识范围的“开始”。

 execute immediate 
    'select start from my_table where :1 between start and end' using my_number

I have built combine index on the two fields. the question is when my_number is small, the performance of query is good, but when the my_number is increasing, the query time is increasing continuously. if the my_number is much bigger, it take big considerable time to finish. anybody has way to improve this query? the way can includes re-design my_table. thanks.

我在两个字段上构建了组合索引。问题是当my_number很小时,查询的性能很好,但是当my_number增加时,查询时间会不断增加。如果my_number更大,那么完成需要相当长的时间。有人有办法改进这个查询吗?方式可以包括重新设计my_table。谢谢。

5 个解决方案

#1

If you change your schema to this:

如果您将架构更改为:

create table my_table(
   start int,
   range_size int
);

insert into my_table values(1, 2);
insert into my_table values(5, 2);
insert into my_table values(11, 189);
insert into my_table values(311, 4689);
insert into my_table values(60004, 20);
insert into my_table values(123213, 12300001);

Then you can index only on the start column.

然后,您只能在开始列上编制索引。

execute immediate 
    'select start from (select start, range_size from my_table where start < :1 order by start asc limit 1) tmp where :1 < start+range_size' using my_number

This may have some performance increase.

这可能会有一些性能提升。

#2

Do you have this done?

你做完了吗?

create table my_table(
start int,
end int
constraint PK_comp primary key (start, end)
) ;

#3

I think you should make 2 indexes, one on the start column and one on the end column. Then select not with the between option but where greater then start and smaller then end. Then you will use an index for each where clause.

我认为你应该制作两个索引,一个在开始列,一个在结束列。然后选择不使用between选项,但选择大于start然后小然后结束。然后,您将为每个where子句使用索引。

I hope this helps out on performance.

我希望这有助于提高性能。

#4

This is a case of trying to fool Oracle into behaving like its competitors, and without access to Oracle I'm just guessing. Maybe a self-join can do this? With indexes on each column separately,

这是一个试图欺骗甲骨文表现得像竞争对手的情况,而且无法访问甲骨文,我只是在猜测。也许自我加入可以做到这一点?每个列的索引分开,

SELECT t1.start
FROM my_table t1 JOIN my_table t2
ON t1.start=t2.start AND t2."end"=t1."end"
AND t1.start <= :1
AND t2.end >= :1

This is silly looking, but the straightforward solution is Joe Frambach's. It fools Postgres, which I do have, to doing only index searches.

这看起来很傻,但直截了当的解决方案是Joe Frambach。它愚弄Postgres,我确实只做索引搜索。

BTW, Postgres is very unhappy about end as a column name. I hope your real table doesn't use a reserved word there.

顺便说一句,Postgres非常不满意作为专栏名称的结尾。我希望你的真实桌子不会在那里使用保留字。

#5

Create an index for each column and use this query:

为每列创建索引并使用此查询:

select start_num
from my_table
where
    start_num =
    (
        --Last start <= number
        select start_num
        from
        (
            select start_num
            from my_table
            where :1 >= start_num
            order by start_num desc
        )
        where rownum = 1
    ) and
    end_num =
    (
        --First end >= number
        select end_num
        from
        (
            select end_num
            from my_table
            where :1 <= end_num
            order by end_num
        )
        where rownum = 1
    );

Yuck. There's probably a better way to write this. Or you may want to wrap this in a function.

呸。写这个可能有更好的方法。或者您可能希望将其包装在一个函数中。

The problem

Test data (with non-reserved word column names):

测试数据(带有非保留字列名称):

drop table my_table;
create table my_table(
   start_num int,
   end_num int
);
insert into my_table select level*2,level*2+1 from dual connect by level <= 1000000;
commit;
create index my_table_index on my_table(start_num, end_num);
begin
    dbms_stats.gather_table_stats(user, 'MY_TABLE', no_invalidate => false);
end;
/

Low numbers are almost instantaneous - 0.015 seconds

低数字几乎是瞬间的 - 0.015秒

select start_num from my_table where 2 between start_num and end_num;

Larger numbers are slower - 0.125 seconds

较大的数字较慢 - 0.125秒

select start_num from my_table where 1000000 between start_num and end_num;

There's a single point between range scan and full table scan.

范围扫描和全表扫描之间有一个点。

explain plan for select start_num from my_table where 402741 between start_num and end_num;
select * from table(dbms_xplan.display);

Plan hash value: 3804444429

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   160K|  1570K|   622   (2)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| MY_TABLE |   160K|  1570K|   622   (2)| 00:00:08 |
------------------------------------------------------------------------------

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

   1 - filter("START_NUM"<=402742 AND "END_NUM">=402742)


explain plan for select start_num from my_table where 402742 between start_num and end_num;
select * from table(dbms_xplan.display);


Plan hash value: 3804444429

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   160K|  1570K|   622   (2)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| MY_TABLE |   160K|  1570K|   622   (2)| 00:00:08 |
------------------------------------------------------------------------------

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

   1 - filter("START_NUM"<=402742 AND "END_NUM">=402742)

But the problem isn't that Oracle doesn't use the index. Using the index in a naive way does not help. In fact, this is even slower, at 0.172 seconds:

但问题不在于Oracle不使用索引。以天真的方式使用索引并没有帮助。事实上,这甚至更慢,在0.172秒:

select /*+ index(my_table my_table_index) */ start_num
from my_table
where 1000000 between start_num and end_num;

Solution

Create new indexes:

创建新索引:

drop index my_table_index;
create index my_table_index1 on my_table(start_num);
create index my_table_index2 on my_table(end_num);
begin
    dbms_stats.gather_table_stats(user, 'MY_TABLE', no_invalidate => false);
end;
/

Results are instantaneous again, for any number:

对于任何数字,结果都是即时的:

select start_num
from my_table
where
    start_num =
    (
        --Last start <= number
        select start_num
        from
        (
            select start_num
            from my_table
            where 1000000 >= start_num
            order by start_num desc
        )
        where rownum = 1
    ) and
    end_num =
    (
        --First end >= number
        select end_num
        from
        (
            select end_num
            from my_table
            where 1000000 <= end_num
            order by end_num
        )
        where rownum = 1
    );

The plan looks great - this is probably the best performance you can get.

该计划看起来很棒 - 这可能是您可以获得的最佳表现。

Plan hash value: 522166032

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |    10 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | MY_TABLE        |     1 |    10 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | MY_TABLE_INDEX2 |     1 |       |     3   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY               |                 |       |       |            |          |
|   4 |     VIEW                       |                 |     3 |    39 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | MY_TABLE_INDEX2 |     3 |    18 |     3   (0)| 00:00:01 |
|*  6 |   COUNT STOPKEY                |                 |       |       |            |          |
|   7 |    VIEW                        |                 |     2 |    26 |     3   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN DESCENDING| MY_TABLE_INDEX1 |   500K|  2929K|     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - filter("START_NUM"= (SELECT "START_NUM" FROM  (SELECT "START_NUM" "START_NUM" FROM 
              "MY_TABLE" "MY_TABLE" WHERE "START_NUM"<=1000000 ORDER BY "START_NUM" DESC) 
              "from$_subquery$_002" WHERE ROWNUM=1))
   2 - access("END_NUM"= (SELECT "END_NUM" FROM  (SELECT "END_NUM" "END_NUM" FROM 
              "MY_TABLE" "MY_TABLE" WHERE "END_NUM">=1000000 ORDER BY "END_NUM") "from$_subquery$_004" 
              WHERE ROWNUM=1))
   3 - filter(ROWNUM=1)
   5 - access("END_NUM">=1000000)
   6 - filter(ROWNUM=1)
   8 - access("START_NUM"<=1000000)