Oracle - 了解 no_index 提示

发布于 2024-10-13 15:27:38 字数 903 浏览 2 评论 0原文

我试图了解 no_index 实际上如何加速查询，但无法在网上找到文档来解释它。

例如，我有一个运行极其慢的查询

select  * 
    from    <tablename>
    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString' and
            Action_='_someAction_' and 
            Timestamp_ >= trunc(sysdate - 2)

，我们的一位 DBA 通过这样做能够显着加快它的速度

select  /*+ NO_INDEX(TAB_000000000019) */ * 
    from    <tablename>
    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString' and
            Action_='_someAction_' and 
            Timestamp_ >= trunc(sysdate - 2)

，但我不明白为什么？我想弄清楚为什么这是有效的，这样我就可以看看是否可以将它应用到另一个查询（这个查询是一个连接）来加速它，因为它需要更长的时间来运行。

谢谢！

** 更新 ** 这是我对示例中的表的了解。

这是一个“分区表”
TAB_000000000019 是表而不是其中的列
field1 已索引

原文

I'm trying to understand how no_index actually speeds up a query and haven't been able to find documentation online to explain it.

For example I have this query that ran extremely slow

select  * 
    from    <tablename>
    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString' and
            Action_='_someAction_' and 
            Timestamp_ >= trunc(sysdate - 2)

And one of our DBAs was able to speed it up significantly by doing this

select  /*+ NO_INDEX(TAB_000000000019) */ * 
    from    <tablename>
    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString' and
            Action_='_someAction_' and 
            Timestamp_ >= trunc(sysdate - 2)

And I can't figure out why? I would like to figure out why this works so I can see if I can apply it to another query (this one a join) to speed it up because it's taking even longer to run.

Thanks!

** Update **
Here's what I know about the table in the example.

It's a 'partitioned table'
TAB_000000000019 is the table not a column in it
field1 is indexed

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南街女流氓 2024-10-20 15:27:38

Oracle 的优化器对如何最好地运行查询进行判断，为此，它使用收集的有关表和索引的大量统计信息。例如，根据这些统计数据，它决定是否使用索引，或者仅进行表扫描。

至关重要的是，这些统计数据不会自动更新，因为收集它们的成本可能非常昂贵。如果统计信息不是最新的，优化器可能会做出“错误”的决定，并且可能会在表扫描实际上更快的情况下使用索引。

如果 DBA/开发人员知道这一点，他们可以向优化器提供提示（这就是 NO_INDEX），告诉它不要使用给定的索引，因为已知它会减慢速度，通常是由于到过时的统计数据。

在您的示例中， TAB_000000000019 将引用索引或表（我猜测是索引，因为它看起来像自动生成的名称）。

老实说，这有点黑魔法，但据我了解，这就是它的要点。

免责声明：我不是 DBA，但我涉足过该领域。

回复收藏 0 原文

凉墨 2024-10-20 15:27:38

根据您的更新：如果 field1 是唯一的索引字段，则原始查询可能会对该索引进行快速完整扫描（即读取索引中的每个条目并检查 field1 上的过滤条件），然后使用这些结果查找表中的行并根据其他条件进行筛选。 field1 上的条件使得索引唯一扫描或范围扫描（即在索引中查找特定值或值范围）是不可能的。

优化器可能选择此路径是因为 field1 上有两个过滤谓词。优化器将计算其中每一个的估计选择性，然后将它们相乘以确定它们的组合选择性。但在许多情况下，这会大大低估符合条件的行数。

NO_INDEX 提示从优化器的考虑中消除了这个选项，因此它本质上与它认为是下一个最佳的计划相一致——在这种情况下可能使用基于查询中其他过滤条件之一的分区消除。

回复收藏 0 原文

蓝梦月影 2024-10-20 15:27:38

如果与使用索引查询表相比，使用索引会导致更多磁盘IO，那么它会降低查询性能。

这可以用一个简单的表来演示：

create table tq84_ix_test (
  a number(15) primary key,
  b varchar2(20),
  c number(1)
);

以下块将 100 万条记录填充到该表中。每 250 条记录在 b 列中填充一个稀有值，而所有其他记录都填充频繁值：

declare
  rows_inserted number := 0;
begin

  while rows_inserted < 1000000  loop

        if mod(rows_inserted, 250) = 0 then

           insert into tq84_ix_test values (
               -1 * rows_inserted, 
               'rare value',
                1);

            rows_inserted := rows_inserted + 1;

        else

           begin
              insert into tq84_ix_test values (
                 trunc(dbms_random.value(1, 1e15)),
                'frequent value',
                 trunc(dbms_random.value(0,2))
               );
               rows_inserted := rows_inserted + 1;

           exception when dup_val_on_index then 
               null;
           end;

        end if;

  end   loop;

end;
/

在该列上放置一个索引

create index tq84_index on tq84_ix_test (b);

相同的查询，但一次使用索引和没有索引的性能有所不同。自己检查一下：

set timing on


select /*+ no_index(tq84_ix_test) */
    sum(c)
  from 
    tq84_ix_test
  where
    b = 'frequent value';


select /*+ index(tq84_ix_test tq84_index) */
    sum(c)    
  from 
    tq84_ix_test
  where
    b = 'frequent value';

为什么会这样？在没有索引的情况下，将按顺序读取所有数据库块。通常，这样做成本高昂，因此被认为是不好的。在正常情况下，有了索引，这样的“全表扫描”可以减少为读取2到5个索引数据库块加上读取包含索引所指向的记录的一个数据库块。对于这里的示例，情况完全不同：读取整个索引，并且对于索引中的（几乎）每个条目，也读取数据库块。因此，不仅读取整个表，还读取索引。请注意，如果 c 也在索引中，则此行为会有所不同，因为在这种情况下，Oracle 可以选择从索引获取 c 的值，而不是绕道桌子。

因此，概括一下这个问题：如果索引没有选择很少的记录，那么不使用它可能是有益的。

Using an index degrades query performance if it results in more disk IO compared to querying the table with an index.

This can be demonstrated with a simple table:

create table tq84_ix_test (
  a number(15) primary key,
  b varchar2(20),
  c number(1)
);

The following block fills 1 Million records into this table. Every 250th record is filled with a rare value in column b while all the others are filled with frequent value:

declare
  rows_inserted number := 0;
begin

  while rows_inserted < 1000000  loop

        if mod(rows_inserted, 250) = 0 then

           insert into tq84_ix_test values (
               -1 * rows_inserted, 
               'rare value',
                1);

            rows_inserted := rows_inserted + 1;

        else

           begin
              insert into tq84_ix_test values (
                 trunc(dbms_random.value(1, 1e15)),
                'frequent value',
                 trunc(dbms_random.value(0,2))
               );
               rows_inserted := rows_inserted + 1;

           exception when dup_val_on_index then 
               null;
           end;

        end if;

  end   loop;

end;
/

An index is put on the column

create index tq84_index on tq84_ix_test (b);

The same query, but once with index and once without index, differ in performance. Check it out for yourself:

set timing on


select /*+ no_index(tq84_ix_test) */
    sum(c)
  from 
    tq84_ix_test
  where
    b = 'frequent value';


select /*+ index(tq84_ix_test tq84_index) */
    sum(c)    
  from 
    tq84_ix_test
  where
    b = 'frequent value';

Why is it? In the case without the index, all database blocks are read, in sequential order. Usually, this is costly and therefore considered bad. In normal situation, with an index, such a "full table scan" can be reduced to reading say 2 to 5 index database blocks plus reading the one database block that contains the record that the index points to. With the example here, it is different altogether: the entire index is read and for (almost) each entry in the index, a database block is read, too. So, not only is the entire table read, but also the index. Note, that this behaviour would differ if c were also in the index because in that case Oracle could choose to get the value of c from the index instead of going the detour to the table.

So, to generalize the issue: if the index does not pick few records then it might be beneficial to not use it.

回复收藏 0 原文

雪化雨蝶 2024-10-20 15:27:38

关于索引需要注意的一点是，它们是基于行顺序和字段中的数据预先计算的值。在这种特定情况下，您说 field1 已建立索引，并且您在查询中使用它，如下所示：

    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString'

在上面的查询片段中，过滤器既位于可变数据块上，因为百分比 (%) 字符包含字符串，然后位于另一个数据块上特定字符串。这意味着不使用优化器提示的默认 Oracle 优化将首先尝试查找索引字段内的字符串，并查找该数据是否是该字段中数据的子字符串，然后它将检查数据与另一个特定字符串不匹配。检查索引后，然后检查其他列。如果重复的话，这是一个非常缓慢的过程。

DBA 提出的 NO_INDEX 提示消除了优化器使用索引的偏好，并且可能允许优化器首先选择更快的比较，而不必先强制索引比较，然后再比较其他列。

下面的代码很慢，因为它比较字符串及其子字符串：

            field1_ like '%someGenericString%'

而下面的代码更快，因为它是特定的：

            field1_ like 'someSpecificString'

因此，使用 NO_INDEX 提示的原因是如果您对索引进行比较会减慢速度。如果将索引字段与更具体的数据进行比较，则索引比较通常会更快。

我说通常是因为当索引字段包含更多冗余数据时，就像@Atish上面提到的示例一样，在返回正比较之前，它必须经过一长串比较负数。提示会产生不同的结果，因为数据库设计和表中的数据都会影响查询的执行速度。因此，为了应用提示，您需要知道您向优化器提示的单独比较在您的数据集上是否会更快。这个过程没有捷径。应用提示应该在编写正确的 SQL 查询之后进行，因为提示应该基于真实数据。

查看此提示参考：http://docs.oracle.com /cd/B19306_01/server.102/b14211/hintsref.htm

Something to note about indexes is that they are precomputed values based on the row order and the data in the field. In this specific case you say that field1 is indexed and you are using it in the query as follows:

    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString'

In the query snippet above the filter is on both a variable piece of data since the percent (%) character cradles the string and then on another specific string. This means that the default Oracle optimization that doesn't use an optimizer hint will try to find the string inside the indexed field first and also find if the data it is a sub-string of the data in the field, then it will check that the data doesn't match another specific string. After the index is checked the other columns are then checked. This is a very slow process if repeated.

The NO_INDEX hint proposed by the DBA removes the optimizer's preference to use an index and will likely allow the optimizer to choose the faster comparisons first and not necessarily force index comparison first and then compare other columns.

The following is slow because it compares the string and its sub-strings:

            field1_ like '%someGenericString%'

While the following is faster because it is specific:

            field1_ like 'someSpecificString'

So the reason to use the NO_INDEX hint is if you have comparisons on the index that slow things down. If the index field is compared against more specific data then the index comparison is usually faster.

I say usually because when the indexed field contains more redundant data like in the example @Atish mentions above, it will have to go through a long list of comparison negatives before a positive comparison is returned. Hints produce varying results because both the database design and the data in the tables affect how fast a query performs. So in order to apply hints you need to know if the individual comparisons you hint to the optimizer will be faster on your data set. There are no shortcuts in this process. Applying hints should happen after proper SQL queries have been written because hints should be based on the real data.

Check out this hints reference: http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm

回复收藏 0 原文