oracle order by 运行速度非常慢

发布于 2024-12-14 16:03:02 字数 1164 浏览 5 评论 0原文

我正在执行这样的查询，需要 6 秒才能完成：

select * 
from ( select aaa."ID" 
      from "aaa"  
      where aaa."DELETED" is null 
      order by aaa."CREATED" desc ) 
where rownum <= 15;

我的表中有大约 160 万条记录，我尝试向已删除的列和创建的列添加单独的索引，我尝试添加一个包含创建和删除的列的索引，我尝试以不同的顺序创建相同的索引。似乎没有什么帮助。我可以做什么来加快速度？

我无法对查询进行太多更改，因为它是由休眠

编辑生成的：即使没有 aaa."DELETED" is null 查询运行速度也非常慢。

编辑2：查询计划

编辑3：添加我的索引定义。老实说，我不知道这些数字中的大多数意味着什么，我正在使用 sqldeveloper 创建索引。甚至不知道每个索引有这么多配置选项，我现在将查看文档。

CREATE INDEX "aaa"."aaa_CREATED_ASC" ON "aaa"."aaa"
  (
    "CREATED"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "SYSTEM" ;
CREATE INDEX "aaa"."aaa_CREATED_DESC" ON "aaa"."aaa"
  (
    "CREATED" DESC
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "SYSTEM" ;

原文

i'm doing a query like this one and it takes 6 seconds to complete :

select * 
from ( select aaa."ID" 
      from "aaa"  
      where aaa."DELETED" is null 
      order by aaa."CREATED" desc ) 
where rownum <= 15;

I've got about 1.6 million records in my table and I've tried adding a separate index to deleted column and to the created column, I tried adding an index containing both created and deleted colunms and I've tried to create the same index in different order. Nothing seems to help. What can I do to speed this up?

I can't much change the query cause it's generated by hibernate

Edit:
even without aaa."DELETED" is null the query is running very slow.

Edit 2:
Query plan

Edit 3:
adding my index definition. i honestly don't know what most of these numbers mean, i'm using sqldeveloper for creating indexes. Didn't even know there's so much configuration options for each index, i'll now look into the documentation.

CREATE INDEX "aaa"."aaa_CREATED_ASC" ON "aaa"."aaa"
  (
    "CREATED"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "SYSTEM" ;
CREATE INDEX "aaa"."aaa_CREATED_DESC" ON "aaa"."aaa"
  (
    "CREATED" DESC
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "SYSTEM" ;

分享到QQ

分享到微博

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

发布评论

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

旧城空念 2024-12-21 16:03:06

尝试看看这里

http://www.dba-oracle.com/oracle_tips_null_idx.htm 。

另外（尽管我怀疑这在这里会有帮助），休眠中有本机查询，以防生成的查询太慢。

回复收藏 0 原文

坚持沉默 2024-12-21 16:03:04

您需要了解 Oracle 如何访问数据库中的记录。索引读取是一个操作，表读取是另一个操作。因此，要从表中检索一条索引记录至少需要两次读取。

您的查询使用三部分信息：

DELETED 列（限制条件）
CREATED 列（排序条件）
ID（结果集）

Oracle 不索引 NULL 值，因此 DELETED 上有单列索引不会帮助你的。这是全表扫描，并不比没有索引好。

CREATED 上的索引本身更好，因为访问路径将变为：

INDEX FULL SCAN DESCENDING

也就是说，它从索引中的最新日期开始并向后工作。但是，查询仍然需要读取表来查找 ID 和 DELETED 值。这可能是大量的表读取，具体取决于 DELETED 为空的频率。

现在，(CREATED, DELETED) 上的复合索引按此顺序应该更有用，因为 Oracle 现在将索引 DELETED 中的 NULL柱子。 Oracle 可以使用索引来确保它只查找表记录来获取 ID 值。这将是十五次表读取。

最后，您可以在 (CREATED, DELETED, ID) 上构建复合索引，并通过索引为整个查询提供服务。这是迄今为止最快的选择。

然后您只需决定性能优势是否值得维护索引的开销。就其价值而言，维护复合索引的成本只占维护单列索引成本的一小部分。

顺便说一句，像这样可怕的查询是使用逻辑删除不是一个好主意的原因之一。物理删除您的记录，并在需要时使用日志表检索表的历史状态。

You need to understand how Oracle accesses records in the database. A index read is one action, a table read is another action. So to retrieve one indexed record from a table is a minimum of two reads.

Your query uses three pieces of information:

the DELETED column (restriction criterion)
the CREATED column (sort criterion)
the ID (result set)

Oracle doesn't index NULL values, so a single column index on DELETED won't help you. That's a full table scan, and no better than no index at all.

An index on CREATED on its own is better because the access path will become:

INDEX FULL SCAN DESCENDING

That is, it starts are the most recent dates in trhe index and works backwards. However, the query will still need to read the table to find the ID and the DELETED values. That may be a lot of table reads, depending on how often DELETED is null.

Now, a compound index on (CREATED, DELETED) in that order should be more useful, because Oracle will now index the NULLs in the DELETED column. Oracle can use the index to ensure it only looks up table records to get the ID values. That will be fifteen table reads.

Finally, you could build a compound index on (CREATED, DELETED, ID) and service the entire query from the index. That is the speediest option yet.

Then you just have to decide whether performance benefits justify the overhead of maintaining the index. For what it's worth, the cost of maintaining a compound index adds a small fraction to the cost of maintining a single column index.

Incidentally, horrible queries like this are one reason why it is a bad idea to use logical deletion. Physically delete your records, and use journally tables to retrieve the historical state of your table when you need it.

回复收藏 0 原文

~没有更多了~