Чем заменить distinct в sql

ALL, DISTINCT, DISTINCTROW, TOP predicates (Microsoft Access SQL)

Инструкция SELECT, содержащая эти предикаты, состоит из следующих частей.

ALL: предполагается, если вы не включаете один из предикатов. Ядро СУБД Microsoft Access выбирает все записи, соответствующие условиям в инструкции SQL. Следующие два примера эквивалентны и возвращают все записи из таблицы Employees:

 SELECT ALL * FROM Employees ORDER BY EmployeeID; 

 SELECT * FROM Employees ORDER BY EmployeeID; 

 SELECT DISTINCT LastName FROM Employees; 

 SELECT DISTINCTROW CompanyName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY CompanyName; 

 SELECT TOP 25 FirstName, LastName FROM Students WHERE GraduationYear = 1994 ORDER BY GradePointAverage DESC; 

Если не включить предложение ORDER BY, запрос вернет произвольный набор из 25 записей из таблицы Students, которые удовлетворяют предложению WHERE. Предикат TOP не выбирает между равными значениями. В предыдущем примере, если двадцать пятый и двадцать шестой средние балл высшей оценки совпадают, запрос вернет 26 записей. Вы также можете использовать зарезервированное слово PERCENT для возврата определенного процента записей, которые попадают в верхнюю или нижнюю часть диапазона, заданного предложением ORDER BY. Предположим, что вместо первых 25 учащихся вам нужны нижние 10 процентов класса:

 SELECT TOP 10 PERCENT FirstName, LastName FROM Students WHERE GraduationYear = 1994 ORDER BY GradePointAverage ASC; 

Пример

В этом примере создается запрос, который объединяет таблицы Customers и Orders в поле CustomerID. Таблица Customers не содержит повторяющихся полей CustomerID, но таблица Orders делает это, так как у каждого клиента может быть много заказов. С помощью DISTINCTROW создается список компаний, имеющих по крайней мере один заказ, но без каких-либо сведений об этих заказах.

Sub AllDistinctX() Dim dbs As Database, rst As Recordset ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' Join the Customers and Orders tables on the ' CustomerID field. Select a list of companies ' that have at least one order. Set rst = dbs.OpenRecordset("SELECT DISTINCTROW " _ & "CompanyName FROM Customers " _ & "INNER JOIN Orders " _ & "ON Customers.CustomerID = " _ & "Orders.CustomerID " _ & "ORDER BY CompanyName;") ' Populate the Recordset. rst.MoveLast ' Call EnumFields to print the contents of the ' Recordset. Pass the Recordset object and desired ' field width. EnumFields rst, 25 dbs.Close End Sub 

См. также

• Форум для разработчиков Access
• Помощь при работе с Access на support.office.com
• Форумы Access на UtterAccess
• Справочный центр (FMS) для разработки и VBA программирования для Access
• Публикации по Access на StackOverflow

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Чем заменить COUNT(DISTINCT. )?

Дано: таблица с несколькими столбцами. Мы хотим посчитать количество уникальных значений в каждом столбце. Например:

SELECT COUNT(DISTINCT A) A_CNT, COUNT(DISTINCT B) B_CNT, COUNT(DISTINCT C) C_CNT FROM MY_TABLE; -- MY_TABLE конечно не таблица в реальности, а другой запрос 

Но вот беда, импала не умеет отрабатывать больше одного COUNT(DISTINCT. ). Как бы так это хитро заменить, сложились ли у кого наиболее удачные практики?

Отслеживать
задан 26 дек 2021 в 21:38
Виталий Яндулов Виталий Яндулов
2,474 2 2 золотых знака 16 16 серебряных знаков 43 43 бронзовых знака
A CTE твоя импала понимает? или и тут у неё проблемы?
27 дек 2021 в 4:54
Попробовать вложенными запросами, в каждом считать один count?
27 дек 2021 в 5:50

1) CTE понимает =) 2) Вложенные запросы. вариант, конечно, держу как запасной, но по цене это обойдется в какие-то не реальные значения. В оригинальной задаче 7 каунт дистинктов из запроса в пол тысячи строк 🙂

27 дек 2021 в 8:16

@Akina Есть, правда, нюанс. Не поддерживаются подзапросы в SELECT-листе. Например, в оракле отработает запрос: WITH T AS ( SELECT 5 A, 4 B, 7 C FROM DUAL UNION ALL SELECT 3 A, 5 B, 1 C FROM DUAL UNION ALL SELECT 1 A, 6 B, 2 C FROM DUAL UNION ALL SELECT 2 A, 3 B, null C FROM DUAL UNION ALL SELECT 3 A, 3 B, 7 C FROM DUAL ) SELECT (SELECT COUNT(DISTINCT A) FROM T) A_CNT, (SELECT COUNT(DISTINCT B) FROM T) B_CNT, (SELECT COUNT(DISTINCT C) FROM T) C_CNT FROM DUAL; А в импале нет (только «FROM DUAL» в импале не нужен).

27 дек 2021 в 8:29

@Akina Вместо этого можно использовать запрос WITH T AS ( SELECT 5 A, 4 B, 7 C FROM DUAL UNION ALL SELECT 3 A, 5 B, 1 C FROM DUAL UNION ALL SELECT 1 A, 6 B, 2 C FROM DUAL UNION ALL SELECT 2 A, 3 B, null C FROM DUAL UNION ALL SELECT 3 A, 3 B, 7 C FROM DUAL ) SELECT A_CNT, B_CNT, C_CNT FROM (SELECT COUNT(DISTINCT A) A_CNT FROM T) A_CNT, (SELECT COUNT(DISTINCT B) B_CNT FROM T) B_CNT, (SELECT COUNT(DISTINCT C) C_CNT FROM T) C_CNT; p.s. Жаль в комментах форматирование ломается)

Глава 3. ИСПОЛЬЗОВАНИЕ SQL ДЛЯ ИЗВЛЕЧЕНИЯ ИНФОРМАЦИИ ИЗ ТАБЛИЦ

В этой главе мы покажем вам, как извлекать информацию из таблиц. Вы узнаете, как пропускать или переупорядочивать столбцы и как автоматически устранять избыточность данных в вашем выводе. В заключение вы узнаете, как устанавливать условие (проверку), которую вы можете использовать, чтобы определить, какие строки таблицы используются в выводе. Эта последняя особенность будет далее описана в более поздних главах и является одной из наиболее изящных и мощных в SQL.

СОЗДАНИЕ ЗАПРОСА

Как мы говорили ранее, SQL это Структурированный Язык Запросов. Запросы, вероятно, наиболее часто используемый аспект SQL. Фактически маловероятно, для категории SQL-пользователей, чтобы этот язык использовался для чего-то другого. По этой причине мы будем начинать наше обсуждение SQL с обсуждения запроса и того, как он выполняется на этом языке.

ЧТО ТАКОЕ ЗАПРОС?

Запрос это команда, которую вы даёте вашей программе базы данных и которая сообщает ей, что нужно вывести определённую информацию из таблиц в память. Эта информация обычно посылается непосредственно на экран компьютера или терминала, которым вы пользуетесь, хотя в большинстве случаев её можно также послать на принтер, сохранить в файле (как объект в памяти компьютера) или предоставить как вводную информацию для другой команды или процесса.

ГДЕ ПРИМЕНЯЮТСЯ ЗАПРОСЫ?

Запросы обычно рассматриваются как часть языка DML. Однако, так как запрос не меняет информацию в таблицах, а просто показывает её пользователю, мы будем рассматривать запросы как самостоятельную категорию среди команд DML, которые производят действия, а не просто показывают содержание базы данных (БД). Любой запрос SQL имеет в своём составе одну команду. Структура этой команды обманчиво проста, потому что вы можете расширять её так, чтобы выполнить сложные оценки и обработку данных. Эта команда называется SELECT (ВЫБРАТЬ).

КОМАНДА SELECT

В самой простой форме команда SELECT просто инструктирует БД, чтобы извлечь информацию из таблицы. Например, вы могли бы вывести таблицу Продавцов, напечатав следующее:

SELECT snum, sname, city, comm FROM Salespeople;

Вывод для этого запроса показан на Рисунке 3.1.

=============== SQL Execution Log ============ | | | SELECT snum, sname, city, comm | | FROM Salespeople; | | | | ==============================================| | snum sname city comm | | ------ ---------- ----------- ------- | | 1001 Peel London 0.12 | | 1002 Serres San Jose 0.13 | | 1004 Motika London 0.11 | | 1007 Rifkin Barcelona 0.15 | | 1003 Axelrod New York 0.10 | =============================================== Рисунок 3.1 Команда SELECT

Другими словами, эта команда просто выводит все данные из таблицы. Большинство программ будут также давать заголовки столбца, как выше, а некоторые позволяют определить детальное форматирование вывода, но это уже вне стандартной спецификации. Вот объяснение каждой части этой команды: SELECT Ключевое слово, которое сообщает базе данных, что эта команда — запрос. Все запросы начинаются этим словом с последующим пробелом. snum, sname Это список столбцов из таблицы, которые выбираются запросом. Любые столбцы, не перечисленные здесь, не будут включены в вывод команды. Это, конечно, не значит, что они будут удалены или их информация будет стёрта из таблиц, ведь запрос не воздействует на информацию в таблицах; он только показывает данные. FROM Salespeople FROM — ключевое слово, подобное SELECT, которое должно быть представлено в каждом запросе. Оно сопровождается пробелом и именем таблицы, используемой в качестве источника информации. В данном случае это таблица Продавцов (Salespeople). ; Точка с запятой используется во всех интерактивных командах SQL, чтобы сообщать базе данных, что команда записана и готова к выполнению. В некоторых системах индикатором конца команды является обратный слэш (\) в строке. Естественно, запрос такого характера не обязательно будет упорядочивать вывод любым указанным способом. Та же самая команда, выполненная с теми же самыми данными, но в другое время, не сможет вывести тот же самый заказ. Обычно строки обнаруживаются в том порядке, в котором они найдены в таблице, поскольку, как мы установили в предыдущей главе, этот порядок произволен. Это не обязательно будет тот порядок, в котором данные вводились или сохранялись. Вы можете упорядочивать вывод непосредственно командами SQL с помощью специального предложения. Позже мы покажем, как это делается. А сейчас просто запомните, что, в отсутствие явного упорядочивания, в вашем выводе нет никакого определенного порядка. Использование возврата каретки (клавиша ENTER) является произвольным. Мы должны точно установить, как удобнее составить запрос — в несколько строк или в одну строку — следующим образом:

SELECT snum, sname, city, comm FROM Salespeople;

С тех пор как SQL использует точку с запятой, чтобы указывать конец команды, большинство программ SQL обрабатывают возврат каретки (через нажатие Возврат или клавиши ENTER ) как пробел. Хорошая идея — использовать возвраты каретки и выравнивание, как мы делали ранее, чтобы сделать ваши команды более лёгкими для чтения и более понятными.

ВЫБИРАЙТЕ ВСЕГДА САМЫЙ ПРОСТОЙ СПОСОБ

Если вы хотите видеть все столбцы таблицы, имеется необязательное сокращение, которое вы можете использовать. Звёздочка (*) может применяться для вывода полного списка столбцов следующим образом:

SELECT * FROM Salespeople;

Это приведет к тому же результату, что и наша предыдущая команда.

ОПИСАНИЕ SELECT

В общем случае команда SELECT начинается с ключевого слова SELECT, сопровождаемого пробелом. После этого должен следовать список имён столбцов, которые вы хотите видеть, отделяемых запятыми. Если вы хотите видеть все столбцы таблицы, вы можете заменить этот список звездочкой (*). Ключевое слово FROM, следующее далее, сопровождается пробелом и именем таблицы, запрос к которой делается. В конце должна использоваться точка с запятой (;) для окончания запроса и указания на то, что команда готова к выполнению.

ПРОСМОТР ТОЛЬКО ОПРЕДЕЛЕННЫХ СТОЛБЦОВ ТАБЛИЦЫ

Команда SELECT способна извлечь строго определенную информацию из таблицы. Сначала мы можем предоставить возможность увидеть только опредёленные столбцы таблицы. Это выполняется легко: простым исключением столбцов, которые вы не хотите видеть, из команды SELECT. Например, запрос

SELECT sname, comm FROM Salespeople;

будет производить вывод, показанный на Рисунке 3.2.

=============== SQL Execution Log ============ | | | SELECT snum, comm | | FROM Salespeople; | | | | ==============================================| | sname comm | | ------------- --------- | | Peel 0.12 | | Serres 0.13 | | Motika 0.11 | | Rifkin 0.15 | | Axelrod 0.10 | =============================================== Рисунок 3.2 Выбор определенных столбцов

Могут иметься таблицы, которые имеют большое количество столбцов, содержащих данные, не все из которых нужны для выполнения поставленной задачи. Следовательно, вы можете найти способ подбора и выбора только полезных для вас столбцов.

ПЕРЕУПОРЯДОЧИВАНИЕ СТОЛБЦА

Даже если столбцы таблицы, по определению, упорядочены, это не означает, что вы будете восстанавливать их в том же порядке. Конечно, звёздочка (*) покажет все столбцы в их естественном порядке, но если вы укажете столбцы отдельно, вы можете получить их в том порядке, в котором хотите. Давайте рассмотрим таблицу Заказов, содержащую дату приобретения (odate), номер продавца (snum), номер заказа (onum) и суммы приобретения (amt):

SELECT odate, snum, onum, amt FROM Orders;

Вывод этого запроса показан на Рисунке 3.3.

============= SQL Execution Log ============== | | | SELECT odate, snum, onum, amt | | FROM Orders; | | | | ------------------------------------------------| | odate snum onum amt | | ----------- ------- ------ --------- | | 10/03/1990 1007 3001 18.69 | | 10/03/1990 1001 3003 767.19 | | 10/03/1990 1004 3002 1900.10 | | 10/03/1990 1002 3005 5160.45 | | 10/03/1990 1007 3006 1098.16 | | 10/04/1990 1003 3009 1713.23 | | 10/04/1990 1002 3007 75.75 | | 10/05/1990 1001 3008 4723.00 | | 10/06/1990 1002 3010 1309.95 | | 10/06/1990 1001 3011 9891.88 | | | =============================================== Рисунок 3.3 Реконструкция столбцов

Как видите, структура информации в таблицах это просто основа для активной перестройки структуры в SQL.

УДАЛЕНИЕ ИЗБЫТОЧНЫХ ДАННЫХ

DISTINCT (ОТЛИЧИЕ) — аргумент, который обеспечивает вас способом устранять дублирующие значения из вашего предложения SELECT. Предположим, что вы хотите знать, какие продавцы в настоящее время имеют свои заказы в таблице Заказов. Под заказом (здесь и далее) будет пониматься запись в таблицу Заказов, регистрирующая приобретения, сделанные в определённый день определённым заказчиком у определённого продавца на определённую сумму. Вам не нужно знать, сколько заказов имеет каждый; вам нужен только список номеров продавцов (snum). Поэтому вы можете ввести:

SELECT snum FROM Orders;

для получения вывода показанного в Рисунке 3.4

=============== SQL Execution Log ============ | | | SELECT snum | | FROM Orders; | | | | ============================================= | | snum | | ------- | | 1007 | | 1001 | | 1004 | | 1002 | | 1007 | | 1003 | | 1002 | | 1001 | | 1002 | | 1001 | ============================================= Рисунок 3.4 SELECT с дублированием номеров продавцов

Для получения списка без дубликатов, для удобочитаемости, вы можете ввести следующее:

SELECT DISTINCT snum FROM Orders;

Вывод для этого запроса показан на Рисунке 3.5. Другими словами, DISTINCT следит за тем, какие значения были ранее, чтобы они не дублировались в списке. Это полезный способ избежать избыточности данных, но важно, чтобы при этом вы понимали, что вы делаете. Если вы не хотите потерять некоторые данные, вы не должны безоглядно использовать DISTINCT, потому что это может скрыть какую-то проблему или какие-то важные данные. Например, вы могли бы предположить, что имена всех ваших заказчиков различны. Если кто-то помещает второго Clemens в таблицу Заказчиков, а вы используете SELECT DISTINCT cname, вы не будете даже знать о существовании двойника. Вы можете получить не того Clemens и даже не знать об этом. Так как вы не ожидаете избыточности, в этом случае вы не должны использовать DISTINCT.

ПАРАМЕТРЫ DISTINCT

DISTINCT может указываться только один раз в данном предложении SELECT. Если предложение выбирает несколько полей,

=============== SQL Execution Log =========== | | | SELECT DISTINCT snum | | FROM Orders; | | | | ============================================= | | snum | | ------- | | 1001 | | 1002 | | 1003 | | 1004 | | 1007 | ============================================= Рисунок 3.5 SELECT без дублирования

DISTINCT опускает строки, где все выбранные поля идентичны. Строки, в которых некоторые значения одинаковы, а некоторые — различны, будут сохранены. DISTINCT фактически приводит к показу всей строки вывода, не указывая полей (за исключением случав, когда он используется внутри агрегатных функций, как описано в Главе 6), так что нет никакого смысла его повторять.

ALL ВМЕСТО DISTINCT

Вместо DISTINCT вы можете указать ALL. Это будет иметь противоположный эффект, дублирование строк вывода сохранится. Так как это — тот самый случай, когда вы не указываете ни DISTINCT ни ALL, то ALL — по существу скорее пояснительный, а не действующий аргумент.

КВАЛИФИЦИРОВАННЫЙ ВЫБОР ПРИ ИСПОЛЬЗОВАНИИ ПРЕДЛОЖЕНИЙ

Таблица имеет тенденцию становиться очень большой, поскольку с течением времени всё большее и большее количество строк в неё добавляется. Поскольку обычно только определённые строки интересуют вас в данное время, SQL дает возможность устанавливать критерии, чтобы определить, какие строки будут выбраны для вывода. WHERE — предложение команды SELECT, которое позволяет устанавливать предикаты, условие которых может быть или верным (true), или неверным (false) для любой строки таблицы. Команда извлекает только те строки из таблицы, для которых такое утверждение верно. Например, предположим, вы хотите видеть имена и комиссионные всех продавцов в Лондоне. Вы можете ввести такую команду:

SELECT sname, city FROM Salespeople; WHERE city = "LONDON";

Когда предложение WHERE предоставлено, программа базы данных просматривает всю таблицу построчно и исследует каждую строку, чтобы определить, верно ли утверждение. Следовательно, для записи Peel программа рассмотрит текущее значение столбца city, определит, что оно равно «London», и включит эту строку в вывод. Запись для Serres не будет включена, и так далее. Вывод для вышеупомянутого запроса показан на Рисунке 3.6.

=============== SQL Execution Log ============ | | | SELECT sname, city | | FROM Salespeople | | WHERE city = 'London' | | ============================================= | | sname city | | ------- ---------- | | Peel London | | Motika London | ============================================= Рисунок 3.6 SELECT с предложением WHERE

Давайте попробуем пример с числовым полем в предложении WHERE. Поле rating таблицы Заказчиков предназначено для того, чтобы разделять заказчиков на группы, основанные на некоторых критериях, которые могут быть получены в итоге через этот номер. Возможно это — форма оценки кредита или оценки, основанной на объёме предыдущих приобретений. Такие числовые коды могут быть полезны в реляционных базах данных как способ подведения итогов сложной информации. Мы можем выбрать всех заказчиков с рейтингом 100 следующим образом:

SELECT * FROM Customers WHERE rating = 100;

Одиночные кавычки не используются здесь, потому что оценка это числовое поле. Результаты запроса показаны на Рисунке 3. 7. Предложение WHERE совместимо с предыдущим материалом в этой главе. Другими словами, вы можете использовать номера столбцов, устранять дубликаты или переупорядочивать столбцы в команде SELECT, которая использует WHERE. Однако вы можете изменять порядок столбцов для имён только в предложении SELECT, но не в предложении WHERE.

============ SQL Execution Log ============== | | | SELECT * | | FROM Customers | | WHERE rating = 100; | | ============================================= | | сnum cname city rating snum | | ------ -------- ------ ---- ------ | | 2001 Hoffman London 100 1001 | | 2006 Clemens London 100 1001 | | 2007 Pereira Rome 100 1001 | ============================================= Рисунок 3.7 SELECT с числовым полем в предикате

РЕЗЮМЕ

Теперь вы знаете несколько способов, как заставить таблицу выдавать вам ту информацию, какую вы хотите, а не просто вываливать наружу всё её содержание. Вы можете переупорядочивать столбцы таблицы или отбрасывать любой из них. Вы можете решать, хотите вы видеть дублированные значения, или нет. Наиболее важно то, что вы можете устанавливать условие, называемое предикатом, которое определяет или не определяет, из тысяч таких же строк, будет ли выбрана для вывода указанная строка. Предикаты могут становиться очень сложными, предоставляя вам высокую точность в решении того, какие строки вам выбирать с помощью запроса. Именно эта способность решать точно, что вы хотите видеть, делает запросы SQL такими мощными. Следующие несколько глав будут посвящены в большей мере особенностям, которые расширяют мощность предикатов. В Главе 4 вам будут представлены операции, иные, нежели те, которые используются в условиях предиката, а также способы объединения многочисленных условий в единый предикат.

РАБОТА СО SQL

Напишите команду SELECT, которая вывела бы номер заказа, сумму и дату для всех строк из таблицы Заказов.

Напишите запрос, который вывел бы все строки из таблицы Заказчиков, для которых номер продавца = 1001.

Напишите запрос, который вывел бы таблицу со столбцами в следующем порядке: city, sname, snum, comm.

Напишите команду SELECT, которая вывела бы оценку (rating), сопровождаемую именем каждого заказчика в San Jose.

Напишите запрос, который вывел бы значения snum всех продавцов в текущем заказе из таблицы Заказов без каких бы то ни было повторений.

Оптимизация distinct и Top N по индексу

Зачастую количество уникальных значений ведущих полей индекса невелико или, по крайней мере, существенно меньше общего количества строк в индексе. В таких случаях, когда индекс весьма большого размера крайне неэффективно получать их через index full scan или index fast full scan. Ведь очевидно, что это можно сделать более эффективно используя структуру ветвей, а не простым проходом по листьям. Далее я покажу как это можно сделать с помощью recursive subquery factoring. Пусть есть таблица с индексом на столбце а:

SQL>create table ttt (owner not null,object_name,created not null,subobject_name,timestamp,object_type) 2 as 3 select 4 o.OWNER 5 ,o.OBJECT_NAME 6 ,o.CREATED 7 ,o.SUBOBJECT_NAME 8 ,o.TIMESTAMP 9 ,o.OBJECT_TYPE 10 from dba_objects o 11 ,xmltable('1,2,3'); Table created. SQL> create index ix_ttt on ttt(owner,created); Index created.

 OWNER NAME BLEVEL LF_BLOCKS NDV NUM_ROWS CLRF kBytes BLOCKS LFB_PER_KEY ---------- ---------- ------ ---------- -------- ---------- -------- -------- -------- ----------- XTENDER IX_TTT 2 719 2269 208251 4067 6144 768 1 

Попробуем получить «distinct owner» обычным способом:

SQL> select distinct owner from ttt; OWNER ------------------------------ OWBSYS_AUDIT . . SYS WMSYS 30 rows selected. Elapsed: 00:00:00.09 SQL> @last PLAN_TABLE_OUTPUT ------------------------- SQL_ID cdgdkqgk56ksk, child number 0 ------------------------------------- select distinct owner from ttt Plan hash value: 321052436 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 30 |00:00:00.08 | 732 | 2 | | 1 | HASH UNIQUE | | 1 | 30 |00:00:00.08 | 732 | 2 | | 2 | INDEX FAST FULL SCAN| IX_TTT | 1 | 208K|00:00:00.04 | 732 | 2 | ------------------------------------------------------------------------------------------ 

Как видите, уникальных значений всего 30, но сколько пришлось прочесть(732 Buffers) и сколько было затрачено времени, чтобы их получить! C IFS и sort unique nosort ситуация не лучше:

SQL> select/*+ index(ttt) */ distinct owner from ttt; . . 30 rows selected. Elapsed: 00:00:00.11 SQL> @last PLAN_TABLE_OUTPUT ------------------------ SQL_ID g9awxq9xvzaat, child number 0 ------------------------------------- select/*+ index(ttt) */ distinct owner from ttt Plan hash value: 1959809473 ------------------------------------------------------------------------------ | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 30 |00:00:00.08 | 723 | | 1 | SORT UNIQUE NOSORT| | 1 | 30 |00:00:00.08 | 723 | | 2 | INDEX FULL SCAN | IX_TTT | 1 | 208K|00:00:00.04 | 723 | ------------------------------------------------------------------------------ 

А теперь смотрите как это можно оптимизировать с recursive subquery factoring:

SQL> with t_unique( a ) as ( 2 select min(owner) 3 from ttt 4 union all 5 select (select min(t1.owner) from ttt t1 where t1.owner>t.a) 6 from t_unique t 7 where a is not null 8 ) 9 select * from t_unique where a is not null; A ------------------------------ APEX_030200 . . XDB XTENDER 30 rows selected. Elapsed: 00:00:00.04

Всего 67 consistent gets:

 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 |00:00:00.01 | 67 | |* 1 | VIEW | | 1 | 30 |00:00:00.01 | 67 | | 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 1 | 31 |00:00:00.01 | 67 | | 3 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 3 | | 4 | INDEX FULL SCAN (MIN/MAX) | IX_TTT | 1 | 1 |00:00:00.01 | 3 | | 5 | SORT AGGREGATE | | 30 | 30 |00:00:00.01 | 64 | | 6 | FIRST ROW | | 30 | 29 |00:00:00.01 | 64 | |* 7 | INDEX RANGE SCAN (MIN/MAX) | IX_TTT | 30 | 29 |00:00:00.01 | 64 | | 8 | RECURSIVE WITH PUMP | | 31 | 30 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A" IS NOT NULL) 7 - access("T1"."OWNER">:B1)

• В первой части union all (3-4 строки плана) мы указываем с чего начать рекурсию, а конкретно выбираем минимальное(первое) значение из индекса.
• Затем с помощью IRS(min/max) (7-6-5 строки плана) выбираем первое значение большее выбранного на предыдущем этапе
• Повторяем рекурсию пока что-то находим

2. Топ N записей для каждого значению ключа

Теперь рассмотрим получение Top 5 наиболее ранних строк для каждого owner. Обычно в таких случаях используют запросы вида:

select * from ( select t.* ,row_number() over(partition by . order by . ) rn from t where . ) where rn
Прежде всего я хотел бы показать проблему использования "partition by" в таких запросах. Для примера возьмем примитивный запрос, который будет возвращать ТОП 5 наиболее ранних объектов у которых владелец - SYS, и попробуем его с partition by и без.
Не обращайте внимания на то что это аналог простого "select * from (select . order by . ) where rownum, т.к. это только пример и на самом деле вместо row_number мог бы быть rank или dense_rank.

1. без partition by:
SQL> select * 2 from ( 3 select 4 ttt.* 5 ,row_number() over(/*partition by ttt.owner*/ order by created) rn 6 from ttt 7 where owner='SYS' 8 ) t 9 where 10 rn OWNER OBJECT_NAME OBJECT_TYPE RN --------------- ----------------- ----------- ---- SYS C_OBJ# CLUSTER 1 SYS C_OBJ# CLUSTER 2 SYS C_OBJ# CLUSTER 3 SYS ICOL$ TABLE 4 SYS ICOL$ TABLE 5 Elapsed: 00:00:00.06 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 |00:00:00.05 | 7 | 5 | |* 1 | VIEW | | 1 | 5 |00:00:00.05 | 7 | 5 | |* 2 | WINDOW NOSORT STOPKEY | | 1 | 5 |00:00:00.05 | 7 | 5 | | 3 | TABLE ACCESS BY INDEX ROWID| TTT | 1 | 6 |00:00:00.05 | 7 | 5 | |* 4 | INDEX RANGE SCAN | IX_TTT | 1 | 6 |00:00:00.04 | 4 | 3 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"<=5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "CREATED")<=5) 4 - access("OWNER"='SYS')
2. c partition by:
SQL> select * 2 from ( 3 select 4 ttt.* 5 ,row_number() over(partition by ttt.owner order by created) rn 6 from ttt 7 where owner='SYS' 8 ) t 9 where 10 rn OWNER OBJECT_NAME OBJECT_TYPE RN --------------- ----------------- ------------------- ---------- SYS C_OBJ# CLUSTER 1 SYS C_OBJ# CLUSTER 2 SYS C_OBJ# CLUSTER 3 SYS ICOL$ TABLE 4 SYS ICOL$ TABLE 5 Elapsed: 00:00:00.20 ------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 |00:00:00.18 | |* 1 | VIEW | | 1 | 5 |00:00:00.18 | |* 2 | WINDOW NOSORT | | 1 | 93183 |00:00:00.16 | | 3 | TABLE ACCESS BY INDEX ROWID| TTT | 1 | 93183 |00:00:00.10 | |* 4 | INDEX RANGE SCAN | IX_TTT | 1 | 93183 |00:00:00.03 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"<=5) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TTT"."OWNER" ORDER BY "CREATED")<=5) 4 - access("OWNER"='SYS')
Заметьте, как все изменилось с добавлением "partition by": 
WINDOW NOSORT STOPKEY 
заменился на просто 
WINDOW NOSORT, 
вследствие чего прочитаны были все строки с owner='SYS'. 
Поэтому по возможности лучше разделять получение Топ N для каждого значения, например, через union all.
Вернемся теперь к начальному вопросу и посмотрим как будет работать стандартный запрос:
select * from ( select ttt.* ,row_number() over(partition by ttt.owner order by created) rn from ttt ) t where rn ------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 148 |00:00:00.95 | 2123 | 2118 | |* 1 | VIEW | | 1 | 148 |00:00:00.95 | 2123 | 2118 | |* 2 | WINDOW SORT PUSHED RANK| | 1 | 208K|00:00:00.91 | 2123 | 2118 | | 3 | TABLE ACCESS FULL | TTT | 1 | 208K|00:00:00.42 | 2123 | 2118 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"<=5) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "TTT"."OWNER" ORDER BY "CREATED")<=5)
Как видите, ради 148 строк пришлось прочитать и отсортировать всю таблицу, хотя у нас есть прекрасно подходящий индекс.
Теперь вооруженные легким получением каждого начального значения, мы легко можем получить Топ для каждого из них, но для этого придется воспользоваться либо XMLtable/xmlsequence, либо недокументированным Lateral() с включением соответствующего ивента, либо более простым и стандартным table(multiset(. )). В случае использования Lateral и table(multiset()) остается проблема только в том, что воспользоваться инлайн вью с row_number/rownum не получится, т.к. предикат с верхнего уровня не просунется, и придется воспользоваться простым ограничением по count stopkey(по rownum) c обязательным доступом по IRS descending (order by там в общем лишний, но дополнительно уменьшает стоимость чтения IRS descending который нам и нужен для неявной сортировки) c подсказкой index_desc, чтобы прибить намертво, иначе сортировка может слететь:
Пример с TABLE и MULTISET:
 SQL> with t_unique( owner ) as ( 2 select min(owner) 3 from ttt 4 union all 5 select (select min(t1.owner) from ttt t1 where t1.owner>t.owner) 6 from t_unique t 7 where owner is not null 8 ) 9 select/*+ use_nl(rids ttt) */ ttt.* 10 from t_unique v 11 ,table( 12 cast( 13 multiset( 14 select/*+ index_asc(tt ix_ttt) */ tt.rowid rid 15 from ttt tt 16 where tt.owner=v.owner 17 and rownum  ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 148 |00:00:00.37 | 190 | 53 | | 1 | SORT ORDER BY | | 1 | 148 |00:00:00.37 | 190 | 53 | | 2 | NESTED LOOPS | | 1 | 148 |00:00:00.37 | 190 | 53 | | 3 | NESTED LOOPS | | 1 | 148 |00:00:00.18 | 157 | 22 | |* 4 | VIEW | | 1 | 30 |00:00:00.18 | 66 | 22 | | 5 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 1 | 31 |00:00:00.18 | 66 | 22 | | 6 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 3 | 3 | | 7 | INDEX FULL SCAN (MIN/MAX) | IX_TTT | 1 | 1 |00:00:00.01 | 3 | 3 | | 8 | SORT AGGREGATE | | 30 | 30 |00:00:00.17 | 63 | 19 | | 9 | FIRST ROW | | 30 | 29 |00:00:00.17 | 63 | 19 | |* 10 | INDEX RANGE SCAN (MIN/MAX) | IX_TTT | 30 | 29 |00:00:00.17 | 63 | 19 | | 11 | RECURSIVE WITH PUMP | | 31 | 30 |00:00:00.01 | 0 | 0 | | 12 | COLLECTION ITERATOR SUBQUERY FETCH | | 30 | 148 |00:00:00.01 | 91 | 0 | |* 13 | COUNT STOPKEY | | 30 | 148 |00:00:00.01 | 91 | 0 | |* 14 | INDEX RANGE SCAN | IX_TTT | 30 | 148 |00:00:00.01 | 91 | 0 | | 15 | TABLE ACCESS BY USER ROWID | TTT | 148 | 148 |00:00:00.19 | 33 | 31 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("V"."OWNER" IS NOT NULL) 10 - access("T1"."OWNER">:B1) 13 - filter(ROWNUM<=5) 14 - access("TT"."OWNER"=:B1)

Пример с Lateral:
SQL> alter session set events '22829 trace name context forever'; Session altered. Elapsed: 00:00:00.00 SQL> with t_unique( owner ) as ( 2 select min(owner) 3 from ttt 4 union all 5 select (select min(t1.owner) from ttt t1 where t1.owner>t.owner) 6 from t_unique t 7 where owner is not null 8 ) 9 select r.* 10 from t_unique v 11 ,lateral( 12 select/*+ index_asc(tt ix_ttt) */ tt.* 13 from ttt tt 14 where tt.owner=v.owner 15 and rownum ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 148 |00:00:00.46 | 191 | 53 | | 1 | SORT ORDER BY | | 1 | 148 |00:00:00.46 | 191 | 53 | | 2 | NESTED LOOPS | | 1 | 148 |00:00:00.46 | 191 | 53 | |* 3 | VIEW | | 1 | 30 |00:00:00.30 | 66 | 22 | | 4 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 1 | 31 |00:00:00.30 | 66 | 22 | | 5 | SORT AGGREGATE | | 1 | 1 |00:00:00.07 | 3 | 3 | | 6 | INDEX FULL SCAN (MIN/MAX) | IX_TTT | 1 | 1 |00:00:00.07 | 3 | 3 | | 7 | SORT AGGREGATE | | 30 | 30 |00:00:00.22 | 63 | 19 | | 8 | FIRST ROW | | 30 | 29 |00:00:00.22 | 63 | 19 | |* 9 | INDEX RANGE SCAN (MIN/MAX) | IX_TTT | 30 | 29 |00:00:00.22 | 63 | 19 | | 10 | RECURSIVE WITH PUMP | | 31 | 30 |00:00:00.01 | 0 | 0 | | 11 | VIEW | | 30 | 148 |00:00:00.16 | 125 | 31 | | 12 | SORT ORDER BY | | 30 | 148 |00:00:00.16 | 125 | 31 | |* 13 | COUNT STOPKEY | | 30 | 148 |00:00:00.16 | 125 | 31 | | 14 | TABLE ACCESS BY INDEX ROWID | TTT | 30 | 148 |00:00:00.16 | 125 | 31 | |* 15 | INDEX RANGE SCAN | IX_TTT | 30 | 148 |00:00:00.01 | 91 | 0 | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ---------------------------------------- 3 - filter("V"."OWNER" IS NOT NULL) 9 - access("T1"."OWNER">:B1) 13 - filter(ROWNUM<=5) 15 - access("TT"."OWNER"="V"."OWNER")
Эффект заметен сразу: 190-191 consistent gets против 2123. 
C XMLtable будет похуже чем у lateral и table-multiset, но все равно гораздо меньше, чем в обычном варианте:
with t_unique( owner ) as ( select min(owner) from ttt union all select (select min(t1.owner) from ttt t1 where t1.owner>t.owner) from t_unique t where owner is not null ) select r.* from t_unique v ,xmltable('/ROWSET/ROW' passing( dbms_xmlgen.getxmltype( q'[select * from ( select/*+ index_asc(tt ix_ttt) */ owner, to_char(created,'yyyy-mm-dd hh24:mi:ss') created from ttt tt where tt.owner=']'||v.owner||q'[' order by tt.created asc ) where rownum ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 148 |00:00:00.28 | 365 | | 1 | SORT ORDER BY | | 1 | 148 |00:00:00.28 | 365 | | 2 | NESTED LOOPS | | 1 | 148 |00:00:00.10 | 365 | |* 3 | VIEW | | 1 | 30 |00:00:00.01 | 66 | | 4 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | | 1 | 31 |00:00:00.01 | 66 | | 5 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 3 | | 6 | INDEX FULL SCAN (MIN/MAX) | IX_TTT | 1 | 1 |00:00:00.01 | 3 | | 7 | SORT AGGREGATE | | 30 | 30 |00:00:00.01 | 63 | | 8 | FIRST ROW | | 30 | 29 |00:00:00.01 | 63 | |* 9 | INDEX RANGE SCAN (MIN/MAX) | IX_TTT | 30 | 29 |00:00:00.01 | 63 | | 10 | RECURSIVE WITH PUMP | | 31 | 30 |00:00:00.01 | 0 | | 11 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | 30 | 148 |00:00:00.10 | 299 | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("V"."OWNER" IS NOT NULL) 9 - access("T1"."OWNER">:B1)
Эффективность этого метода зависит от упомянутого соотношения количества уникальных значений(NUM_DISTINCT столбца) и количества листовых блоков индекса(LEAF_BLOCKS) и, конечно, высоты дерева(BLEVEL).