sql – 更快’从oracle中的table1’中选择distinct thing_id,thing

我有这个问题：

select distinct id,name from table1

对于给定的ID,名称将始终相同.这两个字段都已编入索引.没有单独的表将id映射到名称.该表非常大(数百万行),因此查询可能需要一些时间.

此查询非常快,因为它已编入索引：

select distinct name from table1

同样对于此查询：

select distinct id from table1

假设我无法更改数据库结构(一个非常安全的假设),构建第一个查询性能的更好方法是什么？

编辑以添加表的已清理的desc：

 
Name                           Null     Type
------------------------------ -------- ----------------------------
KEY                            NOT NULL NUMBER
COL1                           NOT NULL NUMBER
COL2                           NOT NULL VARCHAR2(4000 CHAR)
COL3                           VARCHAR2(1000 CHAR)
COL4                           VARCHAR2(4000 CHAR)
COL5                           VARCHAR2(60 CHAR)
COL6                           VARCHAR2(150 CHAR)
COL7                           VARCHAR2(50 CHAR)
COL8                           VARCHAR2(3 CHAR)
COL9                           VARCHAR2(3 CHAR)
COLA                           VARCHAR2(50 CHAR)
COLB                           NOT NULL DATE
COLC                           NOT NULL DATE
COLD                           NOT NULL VARCHAR2(1 CHAR)
COLE                           NOT NULL NUMBER
COLF                           NOT NULL NUMBER
COLG                           VARCHAR2(600 CHAR)
ID                             NUMBER
NAME                           VARCHAR2(50 CHAR)
COLH                           VARCHAR2(3 CHAR)
 
20 rows selected

解决方法

[最新编辑]

关于在(name,id)上创建适当的索引以替换(name)上的索引,我的原始答案如下. (这不是原始问题的答案,它不允许任何数据库更改.)

以下是我尚未测试过的陈述.可能有一些明显的原因,这些不起作用.我从来没有真正建议写这样的陈述(冒着被这种荒谬的建议彻底打鼓的风险.)

如果这些查询甚至返回结果集,则ressult集将仅与OP查询的结果集类似,几乎是偶然的,利用了对Don提供给我们的数据的古怪保证.此语句不等同于原始sql,这些语句是针对Don所描述的特殊情况而设计的.

select m1.id,m2.name
   from (select min(t1.rowid) as min_rowid,t1.id
           from table1 t1
          where t1.id is not null
          group by t1.id
        ) m1,(select min(t2.rowid) as min_rowid,t2.name from table1 t2
         where t2.name is not null
         group by t2.name
        ) m2
  where m1.min_rowid = m2.min_rowid
  order
     by m1.id

让我们解压一下：

> m1是一个内联视图,它为我们提供了一个不同的id值列表.
> m2是一个内联视图,它为我们提供了不同名称值的列表.
>实现视图m1和m2
>匹配m1和m2的ROWID以匹配id与名称

其他人提出了索引合并的想法.我之前已经驳回了这个想法,一个优化器计划可以匹配数百万个rowid,而不会消除任何一个.

id和name的基数足够低,并且具有正确的优化器计划：

select m1.id,( select m2.name
            from table1 m2
           where m2.id = m1.id
             and rownum = 1
        ) as name
   from (select t1.id
           from table1 t1
          where t1.id is not null
          group by t1.id
        ) m1
  order
     by m1.id

让我们打开它

> m1是一个内联视图,它为我们提供了一个不同的id值列表.
>实现视图m1
>对于m1中的每一行,查询table1以从单行获取名称值(stopkey)

重要的提示

这些语句与OP查询的基本不同.它们旨在返回与OP查询不同的结果集.由于对数据的古怪保证,碰巧返回所需的结果集.唐告诉我们,名字由身份确定. (反过来是真的吗？ID是否由名称确定？我们是否有一个STATED GUARANTEE,不一定由数据库强制执行,但保证我们可以利用？)对于任何ID值,具有该ID值的每一行都将具有相同的NAME值. (并且我们也保证反过来是正确的,对于任何NAME值,具有该NAME值的每一行将具有相同的ID值？)

如果是这样,也许我们可以利用这些信息.如果ID和NAME出现在不同的对中,我们只需要找到一个特定的行. “pair”将具有匹配的ROWID,这可方便地从每个现有索引中获得.如果我们获得每个ID的最小ROWID,并获得每个NAME的最小ROWID,该怎么办？我们不能根据包含该对的ROWID将ID与NAME匹配吗？考虑到足够低的基数,我认为这可能有用. (也就是说,如果我们只处理数百个ROWID而不是数百万个.)

[/最新编辑]

[编辑]

现在使用有关表的信息更新问题,它显示ID列和NAME列都允许NULL值.如果Don可以在结果集中没有返回任何NULL的情况下生存,那么在这两列上添加IS NOT NULL谓词可能会启用索引. (注意：在Oracle(B-Tree)索引中,NULL值不会出现在索引中.)

[/编辑]

原始答案：

创建一个合适的索引

create index table1_ix3 on table_1 (name,id) ... ;

好的,这不是你问的问题的答案,但它是解决性能问题的正确答案. (您未指定对数据库进行任何更改,但在这种情况下,更改数据库是正确的答案.)

请注意,如果您在(name,id)上定义了索引,那么您(很可能)不需要(name)上的索引,正则优化程序将考虑另一个索引中的前导名称列.

(更新：作为比我指出的更精明的人,我甚至没有考虑过现有索引是位图索引而不是B树索引的可能性……)

重新评估您对结果集的需求…您是否需要返回id,或者返回名称就足够了.

select distinct name from table1 order by name;

对于特定名称,您可以提交第二个查询以获取相关ID,如果您需要它…

select id from table1 where name = :b1 and rownum = 1;

如果您确实需要指定的结果集,可以尝试一些替代方案来查看性能是否更好.我对这些中的任何一个都不抱太大希望：

select /*+ FIRST_ROWS */ DISTINCT id,name from table1 order by id;

要么

select /*+ FIRST_ROWS */ id,name from table1 group by id,name order by name;

要么

select /*+ INDEX(table1) */ id,min(name) from table1 group by id order by id;

更新：正如其他人已经明确指出的那样,通过这种方法,我们正在测试和比较替代查询的性能,这是一种命中或错过的方法. (我不同意它是随机的,但我同意它的命中或错过.)

更新：汤姆建议ALL_ROWS提示.我没有考虑过,因为我真的专注于使用INDEX获取查询计划.我怀疑OP查询正在进行全表扫描,并且它可能不是花费时间的扫描,而是需要时间的排序唯一操作(< 10g)或散列操作(10gR2). (没有定时的统计数据和事件10046追踪,我只是在这里猜测.)但话又如此,也许是扫描,谁知道,桌子上的高水位标记可以在广阔的空块中出路. 几乎不言而喻,表上的统计数据应该是最新的,我们应该使用sql * Plus AUTOTRACE,或者至少使用EXPLAIN PLAN来查看查询计划. 但是,建议的替代查询都没有真正解决性能问题. 提示可能会影响优化器选择不同的计划,基本上从索引中满足ORDER BY,但我并没有抱太大希望. (我不认为FIRST_ROWS提示适用于GROUP BY,INDEX提示可能.)我可以看到这样一种方法的可能性,在这种情况下,数据块空洞并且稀疏地填充,并且无法访问数据通过索引阻塞,实际上可以将更少的数据块拉入内存……但是这种情况将是例外而不是常态. 更新：正如Rob van Wijk所指出的,利用Oracle跟踪工具是识别和解决性能问题的最有效方法. 如果没有EXPLAIN PLAN或sql * Plus AUTOTRACE输出的输出,我只是在这里猜测. 我怀疑你现在遇到的性能问题是必须引用表数据块才能获得指定的结果集. 没有解决它,只能从索引中解决查询,因为没有包含NAME和ID列的索引,ID或NAME列作为前导列.可以从索引满足另外两个“快速”OP查询,而不需要引用行(数据块). 即使查询的优化器计划是使用其中一个索引,它仍然必须从数据块中检索关联的行,以获取另一列的值.并且没有谓词(没有WHERE子句),优化器可能选择全表扫描,并且可能进行排序操作(< 10g). (同样,EXPLAIN PLAN会显示优化器计划,AUTOTRACE也是如此.) 我也假设这里(很大的假设)两列被定义为NOT NULL. 您还可以考虑将表定义为索引组织表(IOT),尤其是如果这些是表中的仅有两列. (IOT不是灵丹妙药,它带有它自己的一组性能问题.) 您可以尝试重新编写查询(除非这是一个也是verboten的数据库更改)在我们的数据库环境中,我们认为查询与表和索引一样是数据库的一部分.) 同样,没有谓词,优化器可能不会使用索引.您可以通过添加提示,测试以下组合来使查询计划使用其中一个现有索引来快速返回第一行：

select /*+ INDEX(table1) */ ...
select /*+ FIRST_ROWS */ ...
select /*+ ALL_ROWS */ ...
 
  distinct id,name from table1;
  distinct id,name from table1 order by id;
  distinct id,name from table1 order by name;
  id,name order by id;
  id,min(name) from table1 group by id order by id;
  min(id),name from table1 group by name order by name;

通过提示,您可能能够影响优化器使用索引,这可能会避免排序操作,但总体而言,它需要更多时间来返回整个结果集.

(更新：有人指出优化器可能会选择基于ROWID合并两个索引.这是可能的,但没有谓词来消除某些行,这可能是一个更昂贵的方法(匹配数百万ROWID)来自两个索引,特别是当根据匹配不排除任何行时.)

但是如果没有一些性能统计数据,所有理论化并不等于蹲下.

如果没有更改数据库中的任何其他内容,加速查询的唯一其他希望(我能想到)就是确保调整排序操作,以便可以在内存中执行(必需的)排序操作,而不是磁盘.但那不是真正正确的答案.优化器可能根本不进行排序操作,它可能正在进行散列操作(10gR2),应调整.根据过去使用Oracle 7.3,8,8i,9i的经验,排序操作只是我的一个猜测.)

与创建正确索引的SORT_AREA_SIZE和/或HASH_AREA_SIZE参数相比,严肃的DBA会让您遇到更多问题. (对于10g自动内存管理魔术之前的版本,这些会话参数是“旧学校”.)

向DBA显示结果集的规范,让DBA对其进行调整.

sql – 更快’从oracle中的table1’中选择distinct thing_id,thing_name

解决方法

猜你在找的MsSQL相关文章