我有这个查询：
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
您是否可以在其中添加WHERE子句以快速缩小结果集？尽管所示示例没有谓词逻辑，但我不确定您是否已经这样做。 :)
DISTINCT ID和DISTINCT NAME返回多少行？
每个33行。
@Don：好的，刚检查一下，对于1,000,000行表来说，是0.21秒。
@ Quassnoi：使用选择唯一的ID，来自table1的名称？
@Don：不，使用我的解决方案(请参见下文)
@Quassnoi：我在650K行的表上尝试了一下，大约花了17.8秒。我在3500万行的表上进行了尝试，没有耐心等待回复。但是，您的方法似乎很有希望，似乎有些变体可以解决问题。
@Don：您可以发布优化器给您的查询计划吗？
@Quassnoi：我从来没有一个查询计划。 Robs的建议对我没有用，我还没有开始安装sqlplus。顺便说一句，为进行比较，" SELECT名称，来自table1组的ID(按名称，ID)"在小表(650K行)上以5秒运行。
@Don：看来我理解了。将WHERE id IS NOT NULL条件添加到内部子查询中。您的列接受NULL，这就是为什么优化器不使用索引的原因。
[最新编辑]
好。
关于在(name，id)上创建适当的索引以替换(name)上的索引的我的原始答案如下。 (这不是对原始问题的答案，该问题不允许任何数据库更改。)
好。
以下是我尚未测试的陈述。这些不起作用可能是某些明显的原因。我从不实际建议写这样的声明(冒着被这样荒谬的建议彻底击垮的风险。)
好。
如果这些查询甚至返回结果集，则该礼貌集将几乎完全是偶然地利用了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与name匹配
好。
有人提出了索引合并的想法。我以前曾否定过这个想法，而是一个优化程序计划，以匹配数以千万计的rowid，而不消除其中的任何一个。
好。
具有足够低的ID和名称基数，并具有正确的优化程序计划：
好。
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以从单行中获取名称值(停止键)
好。
重要的提示
好。
这些语句与OP查询完全不同。它们旨在返回与OP查询不同的结果集。由于对数据的古怪保证，偶然会返回期望的结果集。 Don告诉我们name由id (反过来是真的吗？id是否由name确定？我们是否有状态保证(不一定由数据库强制执行，但保证可以利用吗？)对于任何id值，每一行该id值将具有相同的name值。 (而且我们还保证反之亦然，对于任何name值，具有该name值的每一行都将具有相同的id值？)
好。
如果是这样，也许我们可以利用这些信息。如果ID和NAME成对出现，我们只需要找到一个特定的行。"对"将具有匹配的ROWID，可以方便地从每个现有索引中获得该ROWID。如果我们获得每个ID的最小ROWID，并获得每个NAME的最小ROWID，该怎么办。我们是否不能根据包含该对的ROWID将ID与NAME匹配？考虑到基数足够低，我认为它可能会起作用。 (也就是说，如果我们只处理数百个ROWID，而不是一千万个。)
好。
[/最新编辑]
好。
[编辑]
好。
现在，该问题已使用有关表的信息进行更新，它表明ID列和NAME列都允许使用NULL值。如果Don可以在结果集中没有返回任何NULL的情况下生存，则在这两个列上都添加IS NOT NULL谓词可以启用索引。 (注意：在Oracle(B树)索引中，NULL值不会出现在索引中。)
好。
[/编辑]
好。
原始答案：
好。
创建一个合适的索引
好。
CREATE INDEX table1_ix3 ON table_1 (name,id) ... ;
好的，这不是您所提出问题的答案，但这是解决性能问题的正确答案。 (您未指定对数据库进行任何更改，但是在这种情况下，更改数据库是正确的选择。)
好。
请注意，如果您在(name,id)上定义了索引，那么(很可能)不需要在(name)上建立索引，这是因为优化程序将考虑其他索引中的前导NAME列。
好。
(更新：作为比我指出的还要精明的人，我什至没有考虑过现有索引是位图索引而不是B树索引的可能性...)
好。
重新评估对结果集的需求...是否需要返回ID，或者返回NAME就足够了。
好。
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提示可能适用。)在有大量的数据块为空且稀疏填充且ny正在访问数据的情况下，我可以看到这种方法的潜力。通过索引访问数据块，实际上实际上可以减少到内存中的数据块的数量...但是这种情况将是例外，而不是正常情况。
好。
更新：正如Rob van Wijk指出的那样，利用Oracle跟踪工具是识别和解决性能问题的最有效方法。
好。
没有EXPLAIN PLAN或SQL * Plus AUTOTRACE输出的输出，我只是在这里猜测。
好。
我怀疑您现在遇到的性能问题是必须引用表数据块才能获得指定的结果集。
好。
没有解决方法，仅凭索引无法满足查询，因为不存在同时包含NAME和ID列且以ID或NAME列为索引的索引领先的专栏。可以从索引中满足其他两个"快速" OP查询，而无需引用行(数据块)。
好。
即使查询的优化器计划是使用索引之一，它仍必须从数据块中检索关联的行，以便获取另一列的值。而且，由于没有谓词(没有WHERE子句)，优化器可能会选择全表扫描，并且可能会执行排序操作(<10g)。 (同样，EXPLAIN PLAN将显示优化器计划，而AUTOTRACE也会显示。)
好。
我在这里(大假设)也假设两列都定义为NOT NULL。
好。
您可能还考虑将表定义为索引组织表(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合并两个索引。这是一种可能，但是如果没有消除某些行的谓词，那将可能是一种更加昂贵的方法(匹配数以千万计的ROWID)从两个索引中删除，尤其是在没有根据匹配排除任何行的情况下。)
好。
但是，如果没有一些性能统计数据，那么所有这些理论化工作就不会蹲下来。
好。
由于无需更改数据库中的任何其他内容，因此(我能想到的)另一个加快查询速度的希望是确保已调整排序操作，以便可以在内存中而不是在内存上执行(必需的)排序操作。磁盘。但这不是正确的答案。优化器可能根本没有执行排序操作，而是可能正在执行哈希操作(10gR2 +)，在这种情况下，应该对其进行调整。根据Oracle 7.3、8、8i，9i的以往经验，排序操作只是我的一个猜测。)
好。
认真的DBA与您创建会话时使用SORT_AREA_SIZE和/或HASH_AREA_SIZE参数相比，会给您带来更多的麻烦，而不是他创建正确的索引所需要的。 (对于10g自动内存管理魔术之前的版本，这些会话参数是"老派"。)
好。
向DBA显示结果集的规范，让DBA对其进行调整。
好。
好。
是的，它们是列，而不是字段。还要注意，创建索引构成数据库更改。
@Don，是的，创建索引是数据库更改。您定义了错误的索引。 (name)上的索引应替换为(name，id)上的索引。那是正确的答案。
假设我无法改变数据库结构"是我提出的问题的关键要素。您的所有信息都很好，但这对我的情况没有帮助。
@Don，您需要一个不同的上下文。
@Spencer：:)不过，我可以看到DBA会拒绝为使用该表的所有各种应用程序添加所有各种索引组合，因为这将对整个系统造成很大的影响。
@Don：一个好的DBA肯定会研究用(name，id)上的索引替换(name)上的索引。该替换可能不会对性能产生明显的影响(假设id列的更新不如name列更频繁。)
实际上，当您尽快需要所有结果集时，ALL_ROWS提示会更快。仅当您寻找最快的"首页"结果时，FIRST_ROWS才会更快。
@tom，是的，关于ALL_ROWS提示的出色技巧。感谢您指出了这一点。 (我将更新答案以包括ALL_ROWS提示的建议。)我在考虑影响优化器使用索引的方面，我已经看到FIRST_ROWS和INDEX提示做到了，我没有使用ALL_ROWS提示来获取索引进入计划。
@Don：如果您没有正确的索引，没有神奇的SQL技巧可以使查询更快。就像问"需要所有行时如何避免全表扫描"
香港专业教育学院编辑答案，以包括一个荒谬的查询，一个可能"偶然地"返回所需的结果集，基于对ID和名称对ID和名称的唯一性的陈述保证。
不能通过查看查询或随机建议一些等效查询来优化查询，无论它们的含义如何。
您，我们或优化器需要了解有关您的数据的统计信息。然后，您可以使用EXPLAIN PLAN或SQLTrace / tkprof等工具进行测量，甚至可以使用SQLPlus的简单自动跟踪工具进行测量。
您能否向我们展示此输出：
SET serveroutput off
SELECT /*+ gather_plan_statistics */ DISTINCT id,name FROM table1;
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
您的整个table1看起来如何？请显示描述输出。
问候，
抢。
+1 @Rob van Wijk：满分。 Oracle工具使我们能够深入了解性能问题，尤其是跟踪事件10046以进行等待，事件10053用于优化器计划。但请注意，我实际上使用的某些调整辅助工具确实会生成替代查询(不是随机的，而是系统地)，例如，用EXISTS谓词替换IN谓词，用GROUP BY子句替换DISTINCT，添加提示，然后执行替代查询以比较效果。
我使用Oracle SQL Developer运行了三个查询的集合，但是没有输出。也许那个工具正在阻碍。至于第二个，我将添加一个清理过的版本。我只需要重命名字段。
请尝试使用SQL * Plus(位于您的bin文件夹中)
这可能会更好。如您所说，它假定给定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
)
这两个查询都将使用id上的索引。
如果仍然需要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
由于第二个查询不使用索引，因此效率较低，但是它将在遇到的第一个NULL处停止：如果它靠近表的开头，那么您很幸运。
有关性能的详细信息，请参见我的博客中的条目：
不同的对
确实尝试使用DBA解决问题。真。尝试传达收益并减轻他们对性能下降的恐惧。
有开发环境/数据库来测试这些东西？
数据必须多及时？
带有适当索引的已经按ID和名称分组的表的副本又如何呢？可以将批处理作业配置为每晚刷新一次新表。
但是，如果那行不通...
如何将所有ID和名称对导出到备用数据库，在该数据库中您可以分组和索引以使自己受益，并使DBA保持自鸣得意？
是的，我有一个测试环境，我正在尝试人们提出的建议。另外，我不认为DBA是自鸣得意的。好吧，也许是这样，但是在添加每个应用程序想要在表上的每个索引之前，请务必谨慎。因此，在我为此努力之前，我想连续排鸭。
很好添加其他索引可能对读取的影响最小。但是，由于插入/更新/删除，新索引是另一个必须保持最新的对象。对于您的侮辱，我深表歉意。它不是个人的。只是有时候我觉得我们似乎过于专注于技术解决方案，以至于错过了显而易见的非技术解决方案，例如与IT部门中的其他人讨论我们的需求。
"表非常大(千万行)"
如果您不能更改数据库(添加索引等)。然后，您的查询将不得不读取整个表。因此，首先，确定要花费多长时间(即SELECT ID，NAME from TABLE1的时间)。您将不会比这更快。
它要做的第二步是DISTINCT。在10g +中，应使用HASH GROUP BY。在此之前，它是SORT操作。前者更快。如果您的数据库是9i，则可以通过将1000万行复制到10g数据库中并在其中进行操作来获得改进。
或者，分配内存块(google ALTER SESSION SET SORT_AREA_SIZE)。这可能会损害数据库上的其他进程，但是您的DBA却没有给您太多选择。
您可以尝试类似
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总是与相同的名称配对。
id是唯一的吗？如果是这样，您可以从查询中删除DISTINCT。如果不是，也许它需要一个新名字？是的，我知道，无法更改架构...
它不是唯一的。将存在大量具有相同ID和名称的行，而Im试图获取该列表。我想，如果有类似选择first.id，first.name的软件，那将达到目的。
您的意思是，SELECT id，name FROM table WHERE rownum = 1吗？
好吧，除了我想要每个ID之一，而不仅仅是一个
是的，但是想想它有多快！ ;-)
如果给定ID的名称始终相同，为什么还要在子句中包含"名称"？ (nm ...您想要的名称不只是检查存在性)
SELECT name, id FROM TABLE WHERE id IN (SELECT DISTINCT id FROM TABLE)?
不知道这是否有帮助...
为了向用户显示名称。
是的，我想通了，并在括号内加上了...
不希望沉迷于在墙上扔东西直到有东西粘住的做法，请尝试以下操作：
SELECT id, name FROM table1 GROUP BY id, name
我对GROUP BY比DISTINCT莫名其妙地记忆犹新。
抱歉，它没有粘住。无论如何，还是谢谢你，值得一试。
确实如此。 DISTINCT关键字不仅会分组，还会对结果进行排序。
DISTINCT并不总是以10g以上的价格订购
您可以尝试以下方法：
SELECT id, MAX(name) FROM table1 GROUP BY id
这肯定会使用id上的索引，但是您必须尝试一下它的执行速度是否很快。
刚试过这个。不过，它仍然很慢。
195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
