Oracle上的查询优化

Question

我不是查询性能专家，我正在学习Oracle优化器如何处理不同的查询并针对其使用进行调优。下面是我的项目中的这样的查询，我一直在为大数据集优化它(它在大数据集上变慢了)。

SELECT
     v1.id,
     v1.date_created,
     v1.name,
     v1.size
 FROM
      ver v1
     INNER JOIN (
         SELECT
             id,
             MAX(date_created) AS last_date_created
         FROM
             ver
         WHERE
             id IN (
                 ...500 ids
             )
             AND active = 'Y'
             AND archived = 'N'
         GROUP BY
             id
     ) v2 ON v1.date_created = v2.last_date_created
             AND v1.id = v2.id

我尝试过SQL developer查询调优顾问，没有建议。这里的问题是，它将对查询的两个部分进行全表扫描，而不使用任何索引和包含近100万条记录的ver表。下面是版本表脚本

create table ver 
 (  "Ver_id" VARCHAR2(36 BYTE) Primary key
    "NAME" VARCHAR2(255 BYTE) 
    "ACTIVE" VARCHAR2(1 BYTE) 
    "ARCHIVED" VARCHAR2(1 BYTE) 
    "DESCRIPTION" VARCHAR2(255 BYTE), 
    "ID" VARCHAR2(36 BYTE) 
    "DATE_CREATED" NUMBER(*,0)
    "CREATED_BY_USER" VARCHAR2(64 BYTE) 
    "SIZE" NUMBER(*,0)
    "LAST_MODIFIED" NUMBER(*,0))

索引是id上的一个非唯一索引和(id，name)上的一个唯一索引，以及last_modified上的非惟一索引。

现在执行查询大约需要2-3分钟。关于它的任何建议。

Answer 1

如果从1M行表中仅选择2到3K行，则可以从使用索引中获益。

基本上，您希望a)选择具有已定义的ID列表的所有行，以及b)使用MAX date_created对每个ID只筛选记录。

您只需要在ID上建立索引

create index ver_idx on ver(id);

以下是两种可供选择的策略：

使用解析函数获取最近的行

在子查询中，您将获得具有指定ID的所有行，并使用RANK分析函数定义行的顺序。主查询只选择带有rn = 1的行，即具有最大值(Date_created)的行。

注意，我使用RANK来获得与您的查询相同的结果。如果在最大数据上有联系，你会得到更多的记录。如果您只想要一条记录，即使是领带，也可以使用ROW_NUMBER。

with dt as (
select 
 id,date_created, name, "SIZE",
rank() over (partition by id order by date_created desc) rn
from ver
where id between 1 and 500
AND active = 'Y'
AND archived = 'N')
select 
  id,date_created, name, "SIZE"
from dt
where rn = 1;

您可以使用索引来获取具有选定日期的所有行，并使用额外的筛选器来仅获取具有最大日期的行。

使用相关子查询

您可以使用相关子查询来筛选具有最大日期的行：

select 
 id,date_created, name, "SIZE"
from ver a
where id between 1 and 500  
AND active = 'Y'
AND archived = 'N'
AND date_created in (select max(date_created) 
                     from ver where id = a.id and active = a.active 
                              and archived = a.archived)

不可能说哪种方法是最好的。这取决于表中的数据。

简单的测试，检查execution plans并找到性能最好的查询。

Answer 2

首先，您不需要在id上建立索引，因为您已经在(id,name)对上建立了索引。

您可以在ver(id，date_created)上使用Btree索引，在ver(active,Archived)上使用位图索引

您也没有在SELECT语句中使用v2，因此上面的查询可以重写为

SELECT
     v1.id,
     v1.date_created,
     v1.name,
     v1.size
 FROM
      ver v1
 WHERE EXISTS (
     SELECT 1
     FROM ver v2
     WHERE v1.id = v2.id 
       and v2.id IN (
            ...500 ids
       )
       AND v2.active = 'Y'
       AND v2.archived = 'N'
     GROUP BY v2.id
     HAVING MAX(v2.date_created) = v1.date_created
 )