在oracle中更快地“从表1中选择不同的对象id和对象名称”

[最新编辑]

我的 关于在(name,id)上创建适当的索引以替换(name)上的索引,请参见下文。(这不是对原始问题的回答,因为原始问题不允许任何数据库更改。)

以下是我的陈述 还没有测试过。可能有一些明显的原因,这些都不起作用。事实上我从来没有这样做过 建议 写这样的陈述(冒着为这种荒谬的建议而被彻底抨击的风险。)

偶然地 ,利用 关于Don提供给我们的数据。此语句不等同于原始SQL,这些语句是为 特例 正如唐所描述的那样。

 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
• 平方米 是一个内联视图,它为我们提供一个不同名称值的列表。
• 使观点具体化 和
• 匹配来自的ROWID m1 和 id 具有 name

还有人提出了索引合并的想法。我之前已经放弃了这个想法,一个优化器计划在不消除任何rowid的情况下匹配上千万个rowid。

 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
• 具体化视图
• 中的每一行 m1 ,查询表1以从单行中获取名称值(stopkey)

这些语句与OP查询有根本的不同。它们旨在返回与OP查询不同的结果集。这个 发生 返回所需的结果集,因为数据有一个奇怪的保证。唐告诉我们 决定于 身份证件 身份证件 决定于 ? 我们是否有声明的保证(不一定由数据库强制执行,但我们可以利用的保证?) ID 值,包含该值的每一行 值将具有相同的值 NAME 价值(我们还保证反之亦然,即对于任何 名称 值,包含该值的每一行 名称 身份证件 价值?)

如果是这样,也许我们可以利用这些信息。如果 身份证件 名称 ,并获取每个的最小ROWID 名称 身份证件 到 名称 基于包含该对的ROWID?考虑到基数足够低,我认为它可能会起作用。(也就是说,如果我们只处理数百个rowid而不是1000万个rowid的话。)

[/最新编辑]

[编辑]

身份证件 名称 列都允许空值。如果Don可以在结果集中不返回任何NULL的情况下生存,那么在这两列上添加IS NOT NULL谓词可以使用索引。(注意:在Oracle(B树)索引中,索引中不显示空值。)

原始答复:

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

好的,那是 不 问题的答案 ,但这是解决性能问题的正确答案。(您没有指定对数据库的更改,但在本例中,更改数据库是正确的答案。)

请注意,如果在上定义了索引 (name,id) ,那么您(很可能)就不需要索引了 (name) 名称 另一个索引中的列。

重新评估您对结果集的需求。。。你需要回来吗 身份证件 ,还是会回来 名称 足够了。

select distinct name from table1 order by name;

对于特定名称,可以提交第二个查询以获取关联的 ,如果您需要,请在需要时。。。

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;

更新:正如其他人敏锐地指出的,使用这种方法,我们正在测试和比较替代查询的性能,这是一种命中或未命中的方法。(我不同意这是随机的,但我同意这是偶然的。)

不用说,表上的统计信息应该是最新的,我们应该使用SQL*Plus AUTOTRACE,或者至少解释一下查看查询计划的计划。

但是,没有一个建议的替代查询真正解决了性能问题。

提示可能会影响优化器选择不同的计划,基本上通过索引满足订单,但我对此不抱太大希望。(我不认为第一行提示适用于GROUP BY,而索引提示可能适用。)我可以看到这种方法的潜力,在这样一种场景中,有大量空的、稀疏的数据块,而ny通过索引访问数据块,实际上可能会大大减少拉入内存的数据块。。。但这种情况将是例外,而不是常态。

更新:正如Rob van Wijk指出的,使用Oracle跟踪工具是识别和解决性能问题的最有效方法。

没有解释计划或SQL*加自动跟踪输出的输出,我只是在这里猜测。

我怀疑您现在遇到的性能问题是,必须引用表数据块才能获得指定的结果集。

这是无法回避的,查询不能仅从一个索引中得到满足,因为没有一个索引同时包含 名称 和 列,其中 身份证件 或 名称 列作为前导列。另外两个“快速”OP查询可以从索引中得到满足,而无需引用行(数据块)。

即使查询的优化器计划使用其中一个索引,它仍然必须从数据块中检索关联的行,以便获取另一列的值。如果没有谓词(no WHERE子句),优化器可能会选择全表扫描,并且可能会执行排序操作(<10g)。(同样,解释计划将显示优化器计划,AUTOTRACE也是如此。)

这里我还假设(大假设)两个列都被定义为NOTNULL。

您还可以考虑将表定义为索引组织表(IOT),特别是如果这些表是表中仅有的两列的话。(物联网不是万灵药,它有自己的一系列性能问题。)

您可以尝试重新编写查询(除非在数据库环境中是一个数据库更改,这也是一个冗长的数据库),我们认为查询与表和索引一样是数据库的一部分。

同样,如果没有谓词,优化器可能不会使用索引。通过添加提示、测试以下各项的组合,您可以让查询计划使用现有索引之一快速获取返回的第一行:

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 from table1 group by 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合并两个索引。这是一种可能性,但如果没有谓词来消除某些行,这可能是一种更昂贵的方法(匹配100万个ROWID)来自两个索引,尤其是在匹配的基础上不排除任何行时。)

但是,如果没有一些性能统计数据,所有这些理论都不等于蹲起。

在不改变数据库中任何其他内容的情况下,加快查询速度的唯一希望(我能想到)是确保对排序操作进行了调整,以便(必需的)排序操作可以在内存中执行,而不是在磁盘上执行。但这并不是正确的答案。优化器可能根本没有执行排序操作,而是执行哈希操作(10gR2+),在这种情况下,应该进行调优。排序操作只是我的一个猜测,基于过去使用Oracle 7.3、8、8i、9i的经验。)

一个严肃的DBA会对你的未来产生更多的问题 SORT_AREA_SIZE 和/或 HASH_AREA_SIZE 您会话的参数比他在创建正确索引时使用的参数要多。(对于10g automatic memory management magic之前的版本,这些会话参数是“老派”的。)

查询不能通过查看或随机建议一些等价的查询来优化,不管它们的意图有多好。

您、我们或优化器需要了解有关数据的统计信息。然后,您可以使用解释计划或SQL之类的工具进行测量 Trace/tkprof,甚至是来自SQL的简单自动跟踪工具 加

set serveroutput off
select /*+ gather_plan_statistics */ distinct id,name from table1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

你的整个桌子看起来怎么样?请显示一个描述输出。

当做

“该表非常大(有1000万行)” 如果无法更改数据库(添加索引等)。那么您的查询将别无选择,只能读取整个表。因此,首先,确定这需要多长时间(即从表1中选择ID和名称的时间)。你不会比那更快得到它的。 它必须做的第二步是区分。在10g+中,应使用哈希分组。在此之前,它是一个排序操作。前者更快。如果您的数据库是9i,那么您可以通过将1000万行复制到10g数据库中并在那里进行操作来获得改进。 或者,分配内存(google ALTER SESSION SET SORT\u AREA\u SIZE)。这可能会损害数据库上的其他进程,但DBA不会给您太多选择。

select id, max(name) from table1 group by id

这肯定会使用id上的索引,但如果它执行得快,则必须尝试。

select id, name from table1 group by id, name

我对一个群体有着模糊的记忆,因为它比一个独特的群体快得令人费解。

SELECT name, id FROM table WHERE id in (SELECT DISTINCT id FROM table)?

不知道这是否有帮助。。。

DISTINCT 从查询中删除。如果没有-也许它需要一个新名字?是的,我知道,无法更改模式。。。

你可以试试类似的东西

Select Distinct t1.id, t2.name
FROM (Select Distinct ID From Table) As T1
INNER JOIN table t2 on t1.id=t2.id

Select distinct t1.id, t2.name from table t1
inner Join table t2 on t1.id=t2.id

不确定这是否会比原来的慢或快,因为我不完全了解您的表是如何设置的。如果每个ID总是有相同的名称,并且ID是唯一的,我真的看不出区别的意义。

有开发环境/数据库来测试这些东西吗?

数据必须多及时?

如果表的副本已经按id和名称进行了分组,并有适当的索引,该如何处理?可以将批处理作业配置为每晚刷新一次新表。

将所有的id和名称对导出到一个备用数据库中,在那里您可以根据自己的利益进行分组和索引,并让DBA保持所有自以为是的僵硬,怎么样?

这可能表现更好。正如您所说,它假定给定id的名称始终相同。

WITH id_list AS (SELECT DISTINCT id FROM table1)
SELECT id_list.id, (SELECT name FROM table1 WHERE table1.id = id_list.id AND rownum = 1)
  FROM id_list;

如果为某个给定的 id 相同的 name

SELECT  (
        SELECT  name
        FROM    table1
        WHERE   id = did
                AND rownum = 1
        )
FROM    (
        SELECT  DISTINCT id AS did
        FROM    table1
        WHERE   id IS NOT NULL
        )

这两个查询都将使用上的索引 身份证件 .

如果你还需要 NULL 值,运行以下命令:

SELECT  (
        SELECT  name
        FROM    table1
        WHERE   id = did
                AND rownum = 1
        )
FROM    (
        SELECT  DISTINCT id AS did
        FROM    table1
        WHERE   id IS NOT NULL
        )
UNION   ALL
SELECT  NULL, name
FROM    table1
WHERE   id IS NULL
        AND rownum = 1

无效的

有关性能详细信息,请参见我的博客中的条目:

• Distinct pairs