Помогите, кто может! GROUP BY + ROWNUM
Timofey VG
хочу выбрать первые 5 строк из таблички
отсортированной до этого ORDER BY
а Оракс наоборот делает
берет первые 5 и сортирует...
как етого избежать?
и как можно выбрать первые 5 записей
после ORDER BY
хотелось иметь что-то типа
select * from a order by a.a where rownum<=5
...
имеем 8.0.5
такой select не проходит
SELECT * FROM (
SELECT a,ROWNUM b FROM а ORDER BY a.a)
WHERE b<=5;
говорит
ERROR at line 2:
ORA-00907: missing right parenthesis
помогите!
Спасибо
Dmitry
> SELECT a,ROWNUM b FROM а ORDER BY a.a)
> WHERE b<=5;
Дело в том, что ROWNUM присваивается ДО выполнения ORDER BY...
Поэтому я подобное сделал след.образом:
SELECT *
FROM (
SELECT *, ROWNUM b
FROM (
SELECT a
FROM а
ORDER BY a.a
)
)
WHERE b<=5;
Т.е. первый внутренний селект сортирует, второй присваивает записям
номера,последний накладывает ограничения на эти номера...
Работает.
Дмитрий.
--
Отправлено через сервер Talk.Ru - http://www.talk.ru
Vadim Vernigor
news:9j4gg4$u0f$1...@host.talk.ru...
> > SELECT * FROM (
> > SELECT a,ROWNUM b FROM а ORDER BY a.a)
> > WHERE b<=5;
>
> Дело в том, что ROWNUM присваивается ДО выполнения ORDER BY...
> Поэтому я подобное сделал след.образом:
>
> SELECT *
> FROM (
> SELECT *, ROWNUM b
> FROM (
> SELECT a
> FROM а
> ORDER BY a.a
> )
> )
> WHERE b<=5;
>
> Т.е. первый внутренний селект сортирует, второй присваивает записям
> номера,последний накладывает ограничения на эти номера...
> Работает.
Dmitry
А в чем на 8.0.5 возникает проблема?
Нет возможности проверить не на 8i...
Vadim Vernigor
> VV> Работает только на 8i, а у человека 8.0.5.
>
> А в чем на 8.0.5 возникает проблема?
> Нет возможности проверить не на 8i...
Sergey Gerasimov
> хочу выбрать первые 5 строк из таблички
> отсортированной до этого ORDER BY
> а Оракс наоборот делает
> берет первые 5 и сортирует...
> как етого избежать?
> и как можно выбрать первые 5 записей
> после ORDER BY
> хотелось иметь что-то типа
> select * from a order by a.a where rownum<=5
> имеем 8.0.5
Я знаю два способа:
1. Использование distinct во вложенном подзапросе
select *
from
(select distinct a.a, a.b, ... from a )
where rownum<=5;
2. Во вложенном подзапросе сунуть хинт на использование индекса по тому полу
(полям) по которым необходима сортировка
Best regards
Sergey Gerasimov
g...@baltros.ru
Ковальчук Виталий
TV> хочу выбрать первые 5 строк из таблички
TV> отсортированной до этого ORDER BY
Не понятно только зачем это все.
Запрос получается не однозначный. Скажем две идентичных таблицы с
одинаковыми записями только в разных местах.
И у тебя скажем 6 записей с одинаковым минимальным значением выражения
в ORDER BY. Какие тогда 5 из 6 показать.
TV> имеем 8.0.5
TV> такой select не проходит
TV> SELECT * FROM (
TV> SELECT a,ROWNUM b FROM а ORDER BY a.a)
TV> WHERE b<=5;
TV> говорит
TV> ERROR at line 2:
TV> ORA-00907: missing right parenthesis
В данной версии Оракла выражение ORDER BY не может быть во вложенных
запросах. С точки зрения реляционной теории это правильно. Так как
порядок строк в таблице не имеет значения.
Ну да ладно раз надо так выбрать значит так надо.
Напиши примерно такое :-))
SELECT * FROM T1 a where 5 >
(select count(*) from T1 b where b.a < a.a);
--
Kovalchuk Vitaliy Oracle Developer & DBA
Vadim Vernigor
"Ковальчук Виталий" <ve...@bcz.com.ua> сообщил/сообщила в новостях
следующее: news:9j5vvc$uu4$1...@host.talk.ru...
> TV> Возникла следующая проблема
> TV> хочу выбрать первые 5 строк из таблички
> TV> отсортированной до этого ORDER BY
>
> Не понятно только зачем это все.
Если просят к примеру рейтинг (TOP5, TOP10) то такой способ первое что
приходит в голову.
Eugene Brodskiy
Для 8.0.5 я делал что-то вроде:
select * from
(select t1.a, count(t2.a) c from a ta1, a ta2
where ta2.a <= ta1.a // Условие ставиться по необходимой сортировке
group by t1.a
Having (count(t2.a)<6))
order by c
BR,
Eugene
Vladimir Begun
P
---------
3
10
6
12
60
1
3
7 rows selected.
SQL> SELECT p
2 FROM (
3 SELECT DISTINCT p, ROWNUM r
4 FROM m
5 )
6 WHERE ROWNUM < 6
7 /
P
---------
1
3
3
6
10
Устраивает?
--
Vladimir Begun | War doesn't prove who's right, just who's
http://vbegun.net/ | left.
http://vbegun.net/wap/ |
m...@vbegun.net |
Отправлено через сервер Talk.Ru - http://www.talk.ru
Timofey VG
"Vadim Vernigor" <va...@udcm.cbsd.donetsk.ua>
>"Dmitry" <dmitry....@cern.ch> сообщил/сообщила в новостях следующее:
>news:9j5uit$mcr$1...@host.talk.ru...
>> VV> Работает только на 8i, а у человека 8.0.5.
>>
>> А в чем на 8.0.5 возникает проблема?
>> Нет возможности проверить не на 8i...
>Не работают сортировки во вложенных запросах .
Timofey VG
>1. Использование distinct во вложенном подзапросе
>select *
> from
> (select distinct a.a, a.b, ... from a )
> where rownum<=5;
>2. Во вложенном подзапросе сунуть хинт на использование индекса по тому полу
>(полям) по которым необходима сортировка
вот вот
и я примерно так думаю,
подробнее можно...???
Timofey VG
>В данной версии Оракла выражение ORDER BY не может быть во вложенных
>запросах. С точки зрения реляционной теории это правильно. Так как
>порядок строк в таблице не имеет значения.
>Ну да ладно раз надо так выбрать значит так надо.
>Напиши примерно такое :-))
>SELECT * FROM T1 a where 5 >
>(select count(*) from T1 b where b.a < a.a);
а сортировка?
не сортирует!!!
Timofey VG
>SQL> SELECT p
> 2 FROM (
> 3 SELECT DISTINCT p, ROWNUM r
> 4 FROM m
> 5 )
> 6 WHERE ROWNUM < 6
> 7 /
> P
>---------
> 1
> 3
> 3
> 6
> 10
>Устраивает?
в принципе да
но хотелось еще и в обратном порядке...
Stax
У меня есть статья (очень маленькая, ее бы куда-то в FAQ)
РУССКОЕ ИЗДАНИЕ ORACLE MAGAZINE - ╧2(4) 1997г.
╚ГОРЯЧАЯ╩ ДЕСЯТКА
ИГОРЬ ФИЛИМОНОВ
Там анализ разных походов
Timofey VG <t...@itos.dn.ua> пишет:
TV> Vladimir Begun <jes...@sunbay.com>
TV>>SQL> SELECT p
TV>> 2 FROM (
TV>> 3 SELECT DISTINCT p, ROWNUM r
TV>> 4 FROM m
TV>> 5 )
TV>> 6 WHERE ROWNUM < 6
TV>> 7 /
TV>> P
TV>>---------
TV>> 1
TV>> 3
TV>> 3
TV>> 6
TV>> 10
TV>>Устраивает?
TV> в принципе да
TV> но хотелось еще и в обратном порядке...
Поставте минус p
--
С уважением, Stax.
... неприятности приходят патчами ...
Vladimir Begun
А подумать? =;)
SELECT -p
FROM (
SELECT DISTINCT -(p) p, ROWNUM r
FROM m
)
WHERE ROWNUM < &limit
/
--
Vladimir Begun | Don't look now, but the man in the moon is
http://vbegun.net/ | laughing at you.
http://vbegun.net/wap/ |
m...@vbegun.net |
Dmitry
> У меня есть статья (очень маленькая, ее бы куда-то в FAQ)
>
> РУССКОЕ ИЗДАНИЕ ORACLE MAGAZINE - ?2(4) 1997г.
> ?ГОРЯЧАЯ? ДЕСЯТКА
> ИГОРЬ ФИЛИМОНОВ
>
> Там анализ разных походов
Очень хотелось бы посмотреть...
Можно по почте? Или отдельным постингом? Или ссылку?
Дмитрий.
--
Vladimir Begun
http://vbegun.net/oramag/order/order.sql
И давайте завяжем с этой темой, которая возникает 12 раз в году,
т.е. каждый месяц.
--
Vladimir Begun | It should be illegal to yell "Y2K" in a
http://vbegun.net/ | crowded economy. :-)
http://vbegun.net/wap/ | -- Larry Wall in
m...@vbegun.net | <1998112423...@wall.org>
Stax
Все ето было на
oracle.ru
а с их перездом, могое интересное ушло в небытее
Вот скопировал (talk.ru покорявит)
http://newsite.oracle.ru/press/oramag/97_2/dev2.html
http://oramag.ru/default.htm?rub=3&ID=162
http://oramag.ru/default.htm?rub=3&ID=167
http://oramag.ru/default.htm?rub=3&ID=189
РУССКОЕ ИЗДАНИЕ ORACLE
MAGAZINE - ╧2(4) 1997г.
МАСТЕРСКАЯ РАЗРАБОТЧИКА
╚ГОРЯЧАЯ╩ ДЕСЯТКА
ИГОРЬ ФИЛИМОНОВ
E-MAIL GA...@PROMSTAL.RU
От научного редактора Виктора Абрамова (a...@ivcais.msk.ru):
Всем нам иногда приходится строить нетривиальные запросы. В
этом номере мы
предлагаем Вашему вниманию задачу, которую прислал Игорь
Филимонов.
Попытайтесь найти свое решение этой задачи и сравните его с
вариантами
решений, приведенными ниже. (свои решения присылайте в редакцию
и/или
автору).
Если у Вас есть свои интересные задачи, присылайте их ≈ мы
будем рады
предложить их читателям нашего журнала.
ВЫ НИКОГДА НЕ ПОЛУЧАЛИ ЗАДАНИЕ выдать список десяти наиболее
оплачиваемых
сотрудников? Или десяти клиентов с наибольшим (наименьшим) оборотом?
Мне очень
часто
приходится строить запросы такого рода. Первое время я выбирал все
записи,
высчитывал
критерий и повторял запрос, ограничивая его выбранным значением. В
конце концов
мне это
порядком надоело, и я задумался, а не существует ли приемлемого
решения этой
задачи? Эта задачка
усложняется еще и тем, что таблица может иметь повторяющиеся
значения столбца,
по которому
строится критерий поиска. Что делать, если, например, оказывается,
что зарплата
одиннадцатого
сотрудника совпадает с зарплатой десятого?
Давайте рассмотрим всем известную таблицу SCOTT.EMP:
ENAME SAL
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
SMITH 800
Из этой таблицы нужно выбрать пять вариантов наборов данных:
1) Пять сотрудников с наибольшими окладами:
ENAME SAL
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
2) Пять сотрудников с наименьшими окладами:
ENAME SAL
SMITH 800
JAMES 950
ADAMS 1100
MARTIN 1250
WARD 1250
3) Пятый сверху:
ENAME SAL
BLAKE 2850
4) Пятый снизу:
ENAME SAL
WARD 1250
5) Выбрать сотрудников с пятью наибольшими окладами (это уже
несколько другая
задача):
ENAME SAL
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
Ценя время читателей, я сразу покажу, как не надо решать эту задачу:
⌠ОЧЕВИДНОЕ■, НО НЕПРАВИЛЬНОЕ РЕШЕНИЕ :
Решение, которое является наиболее очевидным, по крайней мере для
человека,
недавно начавшего
работу с Oracle и увидевшего псевдостолбец ROWNUM (впрочем, к моему
удивлению,
при
обсуждении этой проблемы в конференции comp.databases.oracle, это
решение привели
довольно
многие, подписавшиеся, как Oracle DBA или Oracle Consultant)
select ename,sal from emp where
rownum<=5 order by sal desc;
ENAME SAL
KING 5000
SCOTT 3000
CLARK 2450
MILLER 1300
JAMES 950
Несмотря на совпадение первых двух записей это не то, что нужно.
Почему же не
работает столь
╚очевидное╩ решение? Дело в том, что колонка ROWNUM принимает
значение после
того, как
результирующая запись отобрана и добавлена к набору записей, и до
группировок и
упорядочения.
Проще говоря, выражение WHERE, где ROWNUM определяется,
обрабатывается до
ORDER.
╚КЛАССИЧЕСКОЕ╩ РЕШЕНИЕ :
select ename, sal from emp a
where 1 in /* или как вариант EXIST */
(select 1 from emp b where b.sal > a.sal
having count(distinct b.sal)
< 5 )
order by sal desc
;
Есть и более изящный вариант:
select ename, sal from emp a
where 5 > (select count(distinct b.sal)
from emp b where b.sal > a.sal)
order by sal desc
;
В действительности это решение несколько другой задачи (5), а именно
⌠выбрать
сотрудников с 5
наибольшими зарплатами■, и этот запрос не всегда будет возвращать 5
строк, хотя
иногда и такое
решение может оказаться достаточным.
ENAME SAL
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
НЕДОСТАТКИ:
Запустив этот запрос на таблице, содержащей всего около 10000
записей, я не смог
дождаться
ответа в течении двадцати минут. Почему? Внутренний запрос
выполняется в цикле
для каждой
записи охватывающего запроса. Т.е. получаем 10001 полных просмотров
таблицы
(только не
предлагайте проиндексировать поле SAL).
PL/SQL РЕШЕНИЕ :
DECLARE
i NUMBER := 1 ;
BEGIN
dbms_output.enable(9000);
dbms_output.put_line( ▒ENAME
SAL▓ );
dbms_output.put_line( ▒≈≈≈≈≈≈≈≈▓ );
FOR c IN (select ename,sal from emp
order by sal desc) LOOP
dbms_output.put_line(
rpad(c.ename,15)||to_char(c.sal,
▒99999▓) );
EXIT WHEN i = 5 ;
i := i+1 ;
END LOOP;
END;
/
НЕДОСТАТКИ:
Во-первых, это все-таки не SQLзапрос. Во-вторых, хоть это и самый
быстрый способ,
он не везде
применим.
В SQL*Plus, например, невозможно использовать внутренние функции
форматирования или
подсчета итогов. То же самое можно сказать о других продуктах, где
требуется
запрос для
формирования наборов записей - Oracle Reports, Oracle Graphics,
Delphi, Optima++ и др. (в
первых двух
для этой цели служат внутренние ограничители).
ДРУГИЕ РЕШЕНИЯ :
В конференции (comp.databases.oracle) предлагались и другие варианты
решения.
Например, Джон
Эвенс (John Evans) предложил использовать для подобных задач
OLAP-продукты
горячо нами
любимой корпорации Oracle.
Я же предлагаю Вам решить эту задачу, используя только язык SQL.
РЕШЕНИЕ АВТОРА:
Для решения я применил свойство Oracle, позволяющее использовать
подзапрос в
выражении FROM.
Впрочем, в этом подзапросе не допускается использование выражения
ORDER BY.
Поэтому в
качестве решения проблемы предлагается использовать побочный эффект
упорядочения,
возникающий при использовании ключевого слова DISTINCT. К
недостаткам этого
способа
относится невозможность упорядочить по убыванию символьные величины
(хотя
можно написать
хранимую функцию, делающую реверсию заданного значения).
Итак: 1) Первые 5:
select ename,-sal sal
from (select distinct -sal sal,ename,empno from emp)
where rownum<=5 /* N */
;
2) Последние 5:
select ename,sal
from (select distinct sal,ename,empno from emp)
where rownum<=5 /* N */
;
3) Пятый сверху:
select ename,sal
from (select distinct -sal sal, ename
from (select distinct -sal sal,ename,empno from
where rownum<=5 /* N */)
where rownum=1
;
4) Пятый снизу:
select ename,-sal sal
from (select distinct -sal sal, ename
from (select distinct sal,ename,empno from emp)
where rownum<=5 /* N */)
where rownum=1
;
5) Сотрудники с 5-ю наивысшими окладами:
select ename,sal from emp
where sal>=(select sal from
(select distinct -sal sal
from (select distinct -sal sal from emp)
where rownum<=5 /* N */)
where rownum=1)
order by sal desc
;
Dmitry <dmitry....@cern.ch> пишет:
> Добрый день, Stax
>> У меня есть статья (очень маленькая, ее бы
> куда-то в FAQ)
>>
>> РУССКОЕ ИЗДАНИЕ ORACLE MAGAZINE - ?2(4) 1997г.
>> ?ГОРЯЧАЯ? ДЕСЯТКА
>> ИГОРЬ ФИЛИМОНОВ
>>
>> Там анализ разных походов
> Очень хотелось бы посмотреть...
> Можно по почте? Или отдельным постингом? Или
> ссылку?
> Дмитрий.
--
С уважением, Stax.
... неприятности приходят патчами ...
Отправлено через сервер Talk.Ru - http://www.talk.ru
Ковальчук Виталий
TV>>Напиши примерно такое :-))
TV>>SELECT * FROM T1 a where 5 >
TV>>(select count(*) from T1 b where b.a < a.a);
TV> а сортировка?
TV> не сортирует!!!
Ради бога дальше сортируй как хочеш.
SELECT * FROM T1 a where 5 >
(select count(*) from T1 b where b.a < a.a)
Order BY тралялялялля траляля;
К томуже во внешнем запросе ты получаеш не просто 5 цифер с наименьшими
значениями а даже 5 записей у которых эти значения наменьши.
--
Kovalchuk Vitaliy Oracle Developer & DBA
Timofey VG
Имеем много интересных методов добиться желаемых результато!
Ув. Stax, сcылки-то не работают :((
переезд...
вот тут попробовал еще одним методом.
create table a(a date);
create index a_i on a(a);
select /*+ INDEX_DECS(a a_i)*/ a FROM a where rownum<6;
выдает, по идее, желаемое,но
в MS там чего-то не так было при повышении параллелизма
сканирования индекса, при 2-х и более процессорных машинах
порядок выборки индексных страниц не гарантироуется.
поэтому необходимо использовать ORDER BY...
а как в Оракле?
при выборке первых 10... тоже самое?
(если все данные не на одной страничке)
Igor Lemeshko
> Спасибо всем ОГРОМНОЕ!
> Имеем много интересных методов добиться желаемых результато!
>
> Ув. Stax, сcылки-то не работают :((
> переезд...
>
> вот тут попробовал еще одним методом.
>
> create table a(a date);
> create index a_i on a(a);
>
> select /*+ INDEX_DECS(a a_i)*/ a FROM a where rownum<6;
>
> выдает, по идее, желаемое,но
> в MS там чего-то не так было при повышении параллелизма
> сканирования индекса, при 2-х и более процессорных машинах
> порядок выборки индексных страниц не гарантироуется.
>
> поэтому необходимо использовать ORDER BY...
>
Правильное решение.
Это избитая тема, rownum в Oracle это не set rowcount в Sybase/MSSQL.
Обычно в этих дискуссиях про этот вариант почему-то не говорят, но
по-моему, это единственный, и РЕАЛЬНО РАБОТАЮЩИЙ в проектах SQL
запрос, НЕ ДЕЛАЮЩИЙ НИ ОДНОГО full_scan.
Например, очень часто бывает надо выдать 100 последних/первых строк из
журнала/таблицы, первые 100 клиентов отсортированных по фамилии, ...
Не используйте стоимостной оптимизатор (очень часто, но не всегда -
горе от ума),
(alter session set optimizer_goal=rule) и/или используйте хинты.
Можно без ORDER BY обойтись.
SELECT --+ INDEX (table_name, index_name)
FROM a
WHERE n < 10
--или просто
SELECT /*+ RULE */
FROM log_table
WHERE rownum < 100
ORDER BY log_seq DESC/ASC
--если там один индекс.
Многие из предложенных сейчас и ранее вариантов, конечно очень
интересны с академической точки зрения, но если посмотреть, что они
делают с сервером ... (по моему за это судить надо(+++), тем более
когда этим занимаются в группе, с вложенными вариантами(подзапросами),
с завидной, для любого маньяка переодичностью :) ). Ей-богу из года в
год одно и тоже, и все время какая-та SQL-камасутра (если на планы
выполнения посмотреть), решение же на поверхности (повторюсь, всем
известное, реально работающее во многих проектах).
Поэтому тут было очень разумное предложение закрыть этот скучный
сабжик.
> а как в Оракле?
> при выборке первых 10... тоже самое?
> (если все данные не на одной страничке)
все работает. (не по теории, но кого это беспокоит?)
Если у вас это не просто задача отображения информации, я бы
использовал PL/SQL курсор c order by, опять же с хинтами, но без
rownum( PL/SQL РЕШЕНИЕ МАСТЕРСКАЯ РАЗРАБОТЧИКА ), но поле SAL(или
точнее, его аналог в большой таблице) проиндексировал бы перед этим
(конечно в зависимости от размеров таблицы и однородности поля) для
ускорения order by.
С уважением,
Игорь Лемешко.
Vladimir Begun
> Правильное решение.
> Это избитая тема, rownum в Oracle это не set rowcount в Sybase/MSSQL.
> Обычно в этих дискуссиях про этот вариант почему-то не говорят, но
> по-моему, это единственный, и РЕАЛЬНО РАБОТАЮЩИЙ в проектах SQL
> запрос, НЕ ДЕЛАЮЩИЙ НИ ОДНОГО full_scan.
> Например, очень часто бывает надо выдать 100 последних/первых строк из
> журнала/таблицы, первые 100 клиентов отсортированных по фамилии, ...
> Не используйте стоимостной оптимизатор (очень часто, но не всегда -
> горе от ума),
> (alter session set optimizer_goal=rule) и/или используйте хинты.
> Можно без ORDER BY обойтись.
Оригинальная версия: http://vbegun.net/oramag/order/order.sql
Там есть таблица, которая используется в этом примере.
> SELECT --+ INDEX (table_name, index_name)
> FROM a
> WHERE n < 10
Это вовсе не озачает что из таблицы произойдет выборка
первых N записей. Выберутся записи базируясь на наличии
индекса (я правильно понял?)
CREATE INDEX i$p ON m(p);
ALTER SESSION SET optimizer_goal=rule;
SELECT /*+ INDEX(m, i$p) */ * FROM m WHERE p < &limit;
Какой limit у меня должен быть установлен чтобы выбрать первые 5
записей из данной таблицы отсортироваными? Подразумевается что
столбец может содержать неуникальные значения.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=1 Card=100 Bytes=4200)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'M' (Cost=1 Card=100 Byte s=4200)
2 1 INDEX (RANGE SCAN) OF 'I$P' (NON-UNIQUE) (Cost=2 Card=100)
Или
SELECT /*+ INDEX(m, i$p) */ p FROM m WHERE p < &limit;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=1 Card=100 Bytes=2600)
1 0 INDEX (RANGE SCAN) OF 'I$P' (NON-UNIQUE) (Cost=2 Card=100 Bytes=2600)
> --или просто
>
> SELECT /*+ RULE */
> FROM log_table
> WHERE rownum < 100
> ORDER BY log_seq DESC/ASC
>
> --если там один индекс.
SELECT /*+ RULE */
p, v, d
FROM m
WHERE ROWNUM < 6
ORDER BY p
Execution Plan
-----------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (ORDER BY)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'M'
И этот statement выдает не совсем корректный результат.
Возможно я не совсем понял как это происходит... Я хотел
бы тебя попросить объяснить как этот интересный подход
работает.
Спасибо за ответ.
--
Vladimir Begun | Some people only open up to tell you that
http://vbegun.net/ | they're closed.
http://vbegun.net/wap/ |
m...@vbegun.net |
Edward
"Vladimir Begun" <jes...@sunbay.com> wrote in message
news:9jgrbe$gn6$1...@host.talk.ru...
> On Jul 19, 2001 at 11:45:12AM, Igor Lemeshko wrote:
> > Правильное решение.
> > Это избитая тема, rownum в Oracle это не set rowcount в Sybase/MSSQL.
> > Обычно в этих дискуссиях про этот вариант почему-то не говорят, но
> > по-моему, это единственный, и РЕАЛЬНО РАБОТАЮЩИЙ в проектах SQL
> > запрос, НЕ ДЕЛАЮЩИЙ НИ ОДНОГО full_scan.
> > Например, очень часто бывает надо выдать 100 последних/первых строк из
> > журнала/таблицы, первые 100 клиентов отсортированных по фамилии, ...
> > Не используйте стоимостной оптимизатор (очень часто, но не всегда -
> > горе от ума),
> > (alter session set optimizer_goal=rule) и/или используйте хинты.
такое сочетание будет использовать "горе от ума" :)
Timofey VG
> Возможно я не совсем понял как это происходит... Я хотел
> бы тебя попросить объяснить как этот интересный подход
> работает.
CREATE TABLE m (p NUMBER, v VARCHAR2(10), d DATE);
потом берем
CREATE INDEX m_p ON m (p) ;
SELECT /*+INDEX(m m_p)*/ p FROM m where rownum<5 ORDER BY p;
также и с другими полями...
"замечательно входит"
все
Или нет?
Vladimir Begun
"Возникла следующая проблема хочу выбрать первые 5 строк из таблички
отсортированной до этого ORDER BY, а Оракс наоборот делает
берет первые 5 и сортирует... как етого избежать?
"
Версия 8.0.5.2.1
SQL> SELECT p FROM m;
P
---------
3
10
6
12
14
1
3
SQL> alter session set optimizer_goal=rule -- что с энтим, что без...
SQL> SELECT /*+INDEX(m m_p)*/ p FROM m where rownum<5 ORDER BY p;
P
---------
3
6
10
12
4 rows selected.
SQL> SELECT p
2 FROM (
3 SELECT DISTINCT p, ROWNUM r
4 FROM m
5 )
6 WHERE ROWNUM < 5
7 /
P
---------
1
3
3
6
Я вижу разницу. Ты нет?
--
Vladimir Begun | I'm sure that that could be indented more
http://vbegun.net/ | readably, but I'm scared of the awk parser.
http://vbegun.net/wap/ | -- Larry Wall in
m...@vbegun.net | <68...@jpl-devvax.JPL.NASA.GOV>
Timofey VG
> SQL> alter session set optimizer_goal=rule -- что с энтим, что без...
> Я вижу разницу. Ты нет?
вижу, каюсь
мне нужно было по дате...
я и проверял только для даты...
вот:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';
DROP TABLE a;
CREATE TABLE a(a NUMBER NOT NULL,d DATE NOT NULL);
CREATE INDEX A_D ON A ("D") ;
INSERT INTO a VALUES(2,'03.01.2001');
INSERT INTO a VALUES(7,'10.01.2001');
INSERT INTO a VALUES(3,'06.01.2001');
INSERT INTO a VALUES(8,'12.01.2001');
INSERT INTO a VALUES(2,'14.01.2001');
INSERT INTO a VALUES(1,'01.01.2001');
INSERT INTO a VALUES(10,'03.01.2001');
SQL>SELECT * FROM a;
A D
--------- ----------
2 03.01.2001
7 10.01.2001
3 06.01.2001
8 12.01.2001
2 14.01.2001
1 01.01.2001
10 03.01.2001
SQL>SELECT /*+ INDEX(a a_d)*/ * FROM a;
A D
--------- ----------
1 01.01.2001
2 03.01.2001
10 03.01.2001
3 06.01.2001
7 10.01.2001
8 12.01.2001
2 14.01.2001
SQL>SELECT /*+ INDEX(a a_d)*/ * FROM a WHERE ROWNUM<6;
A D
--------- ----------
1 01.01.2001
2 03.01.2001
10 03.01.2001
3 06.01.2001
7 10.01.2001
SQL>SELECT /*+ INDEX(a a_d)*/ * FROM a WHERE ROWNUM<6 order by d;
A D
--------- ----------
1 01.01.2001
2 03.01.2001
10 03.01.2001
3 06.01.2001
7 10.01.2001
какбы работает,
если хочешь последние 5
SELECT /*+ INDEX_DESC(a a_d)*/ * FROM a WHERE ROWNUM<6 order by d desc;
но
вопрос - ставить ли order by?
в MSSQL 2000 говорят - ставить!
что говорят здесь?
если не использовать order by - данный подход начинает
работать и для number...
"вот и верь после этого людям..."
что скажешь?
Vladimir Begun
>
> Vladimir Begun wrote:
> > SQL> alter session set optimizer_goal=rule -- что с энтим, что без...
> ничего не могу сказать - не мой постинг
>
> > Я вижу разницу. Ты нет?
> вижу, каюсь
> мне нужно было по дате...
> я и проверял только для даты...
Вот, видишь... не хорошо так делать... это раздражает. :)
> вот:
>
> ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';
> DROP TABLE a;
> CREATE TABLE a(a NUMBER NOT NULL,d DATE NOT NULL);
^^^^^^^^ ^^^^^^^^
Если у тебя всегда NOT NULL, можешь использовать
эти приколы и индексами. Я специально написал generic
solution (разумеется, не пытаясь доказать всем что других
способов нет), но ты бы долго искал почему у тебя что-то
там не работает... если бы позабыл NOT NULL constraint
использовать...
Я сделаю update order.sql...
Я еще хочу добавить вот что, насколько я понимаю
7.X устарела давно, 8.X устарела (или в процессе
для некоторых платформ). Под "устарела", я понимаю
не поддерживается... это означает что лучше быстренько
купить новые версии... Это конечно философия, но
только русский народ :) может юзать в продакшене
версию, которая не куплена да еще и ту которая
уже не поддерживается. На этот абзац можно не отвечать.
> но
> вопрос - ставить ли order by?
>
> в MSSQL 2000 говорят - ставить!
А это что такое "mssql 2000", это СУБД такая?
> что говорят здесь?
Не знаю, я все сказал уже.
> если не использовать order by - данный подход начинает
> работать и для number...
>
> "вот и верь после этого людям..."
И опыт... сын ошибок трудных...
> что скажешь?
Я уже сказал, вроде...
Спасибо.
Удачи!
--
Vladimir Begun | echo "Your stdio isn't very std."
http://vbegun.net/ | -- Larry Wall in Configure from
http://vbegun.net/wap/ | the perl distribution
m...@vbegun.net |
Igor Lemeshko
> > Правильное решение.
> > Это избитая тема, rownum в Oracle это не set rowcount в Sybase/MSSQL.
> > Обычно в этих дискуссиях про этот вариант почему-то не говорят, но
> > по-моему, это единственный, и РЕАЛЬНО РАБОТАЮЩИЙ в проектах SQL
> > запрос, НЕ ДЕЛАЮЩИЙ НИ ОДНОГО full_scan.
> > Например, очень часто бывает надо выдать 100 последних/первых строк из
> > журнала/таблицы, первые 100 клиентов отсортированных по фамилии, ...
> > Не используйте стоимостной оптимизатор (очень часто, но не всегда -
> > горе от ума),
> > (alter session set optimizer_goal=rule) и/или используйте хинты.
> > Можно без ORDER BY обойтись.
>
> Оригинальная версия: http://vbegun.net/oramag/order/order.sql
лучший план
выполнения, надо бы на первое место поместить. На второе место PLSQL
версию(вам
ее добавить надо), пишется обычный запрос с order by в LOOP и
прерывается
после первых/последних строк
по счетчику. На самом деле, других вариантов и не надо. Я вот к чему
пытаюсь свести разговор. Можно еще конечно поизобретать "вечный
двигатель" - но зачем?
Хотя глядя на это безобразие (использование хинтов и индексов) может
быть Оракл
сделает аналог set rowcount Sybase.
> Там есть таблица, которая используется в этом примере.
в каком примере? Эту фитчу мы уже не один год используем.
> > SELECT --+ INDEX (table_name, index_name)
> > FROM a
> > WHERE n < 10
Здесь "очепятка"(д.б. rownum < 100), это же видно по следующему за
этим
примером:
--или просто
--если там один индекс.
Лимит по rownum, если INDEX (RANGE SCAN) в плане.
>
> > --или просто
> >
> > SELECT /*+ RULE */
> > FROM log_table
> > WHERE rownum < 100
> > ORDER BY log_seq DESC/ASC
> >
> > --если там один индекс.
>
> SELECT /*+ RULE */
> p, v, d
> FROM m
> WHERE ROWNUM < 6
> ORDER BY p
>
> Execution Plan
> -----------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: RULE
> 1 0 SORT (ORDER BY)
> 2 1 COUNT (STOPKEY)
> 3 2 TABLE ACCESS (FULL) OF 'M'
>
> И этот statement выдает не совсем корректный результат.
Предполагается достаточная квалификация разработчика, которая
позволяет ему
свести план выполнения к сканированию по индексу:
SELECT STATEMENT Cost =
COUNT STOPKEY
INDEX FULL SCAN M_P
В данном случае, при создании таблицы, как Вы правильно заметили, в
следующих
сообщениях надо указать not null в индексируемых полях. Думаю идея
всем ясна,
серверный процесс должен начать выборку строк по rowid полученным из
индекса,
тогда COUNT STOPKEY сработает после выборки нужного кол-ва(rownum)
уже отсортированных строк в индексе. И еще раз повторюсь, что этот
вариант
имеет лучший план выполнения из всех возможных(скажем так, лучше не
может быть
по определению). Поэтому рекомендуется, используется, и непонятно
почему
возникает столько вопросов на эту тему( и зачем нужны закрученные
варианты, с
жуткими планами?). К примеру, вам нужны последние сто строк из
журнала, в
котором лимон строк, и предлагается сначала отсортировать весь этот
лимон
(distinct), а потом выдать эти сто строк? Разница заметна(в
1000000/100), и
зачем нам кузнец?
>
> Возможно я не совсем понял как это происходит... Я хотел
> бы тебя попросить объяснить как этот интересный подход
> работает.
Широко юзаемый, обычный метод.
Никто не знает, может в 9i Оракл сделал что-нибудь подобное set
rowcount в
Sybase? Когда запрос сначала полностью выполняется, потом выдается
результат
по stopkey?
>
> Спасибо за ответ.
Vladimir Begun
Igor Lemeshko wrote:
> > > Правильное решение.
Не совсем правильное...
> > Оригинальная версия: http://vbegun.net/oramag/order/order.sql
Я знал как работает этот метод, но этот метод не покрывает всех
значений. Он не работает для NULL (да у Oracle проблемы с этим).
То что сделал я работает для всех значений... Согласен, что NULLs
должны будут особым образом проверятся (для этого в 8i введены
NULLS FIRST, NULLS LAST), а иногда это нужно.
> Доброе дело вы сделали, только этот вариант(С ХИНТАМИ), как имеющий
> лучший план
> выполнения, надо бы на первое место поместить. На второе место PLSQL
Да, совершенно согласен. Но не общий вариант решения задачи.
> по счетчику. На самом деле, других вариантов и не надо. Я вот к чему
> пытаюсь свести разговор. Можно еще конечно поизобретать "вечный
> двигатель" - но зачем?
> Хотя глядя на это безобразие (использование хинтов и индексов) может
> быть Оракл сделает аналог set rowcount Sybase.
Oracle уже все сделал, я только думаю что вам следует версию поменять.
...
> Предполагается достаточная квалификация разработчика, которая
> позволяет ему
> свести план выполнения к сканированию по индексу:
> SELECT STATEMENT Cost =
> COUNT STOPKEY
> INDEX FULL SCAN M_P
... и понимание как работают индексы в Oracle. Я сообственно
вас только об этом и спрашивал, не больше. Я слукавил, потому
что спросил о том что знал -- прошу меня за это извинить. Но,
как я сказал выше, метод не покрывает всех возможных значений --
он "неполон"...
...
> жуткими планами?). К примеру, вам нужны последние сто строк из
> журнала, в
> котором лимон строк, и предлагается сначала отсортировать весь этот
> лимон
> (distinct), а потом выдать эти сто строк? Разница заметна(в
> 1000000/100), и
> зачем нам кузнец?
Oracle8i Data Warehousing Guide Release 2 (8.1.6)
17 SQL for Analysis. TOP_N. Кузнец не нужен, нужен
upgrade.
Я согласен что это индексы -- это круто и быстро работает, есть одно "но".
Можно очень сильно "попасть" если индекс "вдруг" перестанет работать...
Это кажется философией, но опыт показывает что такое бывает, редко, но
бывает (бага в оптимизаторе). Особенно это может быть "больно" в финансовых
приложениях, где лучше медленно, но верно, чем быстро и... **Речь лишь о
компромиссах**. Иногда очень неудобно говорить "ой", например CEO, о том
что разработчик наш умный, но вот он тут стал использовать что-то, и это
что-то покорябило годовой отчет, который мы уже отослали на проверку в
"гос.учреждение", а сегодня ревизия придет... вообщем нужно спасаться...
Я не люблю говорить такие вещи. :) Думаю, кто был в такой ситуации, тот
разделит это мнение.
Еще раз, метод -- хорош, эта функциональность была включена в пример,
вы меня убедили, что два файла с примерами нужно сделать одним...
--
Vladimir Begun | Let us be charitable, and call it a
http://vbegun.net/ | misleading feature :-)
http://vbegun.net/wap/ | -- Larry Wall in
m...@vbegun.net | <26...@jato.Jpl.Nasa.Gov>
Stanislav Shydlovskyy
Немножко добавлю
Вопрос возникает с завидной переодичность
причины
а)с первыми/последними n у оракла проблемы (до 8и)
б)решение отражено в документации,
но не выделено, так вскользь упоминвется
в)для 8и
законное решение
1)
SELECT column_list ROWNUM FROM
(SELECT column_list FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N
Подчеркиваю, ето не просто ORDER BY в подзапросе
Оптимизатор понимает
Что нодо выброть 100 первых из 10000000
упорядочиватся будут 100 записей,
а не 10000000
(SORT (ORDER BY STOPKEY))
2) использовать аналитические функции
(не поддерживаются в pl/sql)
Vladimir Begun <jes...@sunbay.com> wrote in message news:<3B5C3AB5...@sunbay.com>...
> Timofey VG wrote:
> >
> > Vladimir Begun wrote:
> > > SQL> alter session set optimizer_goal=rule -- что с энтим, что без...
> > ничего не могу сказать - не мой постинг
Почему ничего, если выбрано optimizer_goal=rule
Или хинтом только /*+ RULE */
тогда не надо прописывать хинт для индекса
да и не было их тогда во времена RULE
а надо обязательно order by n
При етом ортимизатор при регулярном подходе
должен выбрать 14 ранг
14 - ORDER BY по индексированным столбцам
(понятно что должен быть NOT NULL)
Оракл уже давно предупреждает,
что optimizer_goal=rule
поддерживатся не будет
> >
> > > Я вижу разницу. Ты нет?
> > вижу, каюсь
> > мне нужно было по дате...
> > я и проверял только для даты...
Вы не в выборе по дате ошиблись,
NOT NULL для поля NUMBER не прописали,
как ниже заметил Владимир
(возможна и опечатка)
>
> Вот, видишь... не хорошо так делать... это раздражает. :)
>
> > вот:
> >
> > ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';
> > DROP TABLE a;
> > CREATE TABLE a(a NUMBER NOT NULL,d DATE NOT NULL);
> ^^^^^^^^ ^^^^^^^^
>
> Если у тебя всегда NOT NULL, можешь использовать
> эти приколы и индексами. Я специально написал generic
> solution (разумеется, не пытаясь доказать всем что других
> способов нет), но ты бы долго искал почему у тебя что-то
> там не работает... если бы позабыл NOT NULL constraint
> использовать...
>
> Я сделаю update order.sql...
>
> Я еще хочу добавить вот что, насколько я понимаю
> 7.X устарела давно, 8.X устарела (или в процессе
> для некоторых платформ). Под "устарела", я понимаю
> не поддерживается... это означает что лучше быстренько
> купить новые версии... Это конечно философия, но
> только русский народ :) может юзать в продакшене
> версию, которая не куплена да еще и ту которая
> уже не поддерживается. На этот абзац можно не отвечать.
>
> > но
> > вопрос - ставить ли order by?
для "регулярного" подхода нужно обязательно
для "стоимостного"
в зависимости что получить
например
выбрать первые 5 по дате -хинт на индекс по дате
а результат отсортировать по varchar2 - order by v
если выражения в индексе и
order by совпадают то можно не писать
Vladimir Begun
> Вы не в выборе по дате ошиблись,
> NOT NULL для поля NUMBER не прописали,
> как ниже заметил Владимир
> (возможна и опечатка)
Нет это не опечатка :). Специально подчеркнул и написал:
> > Если у тебя всегда NOT NULL, можешь использовать
> > эти приколы и индексами. Я специально написал generic
> > solution (разумеется, не пытаясь доказать всем что других
> > способов нет), но ты бы долго искал почему у тебя что-то
> > там не работает... если бы позабыл NOT NULL constraint
> > использовать...
Ты целый абзац обозвал опечаткой. Не стыдно? :)
--
Vladimir Begun | It's easy to solve the halting problem with
http://vbegun.net/ | a shotgun. :-)
http://vbegun.net/wap/ | -- Larry Wall in
m...@vbegun.net | <1998011518...@wall.org>
Igor Lemeshko
День добрый Владимир,
> Уважаемый, Игорь
>
> Igor Lemeshko wrote:
> > > > Правильное решение.
>
> Не совсем правильное...
>
> > > Оригинальная версия: http://vbegun.net/oramag/order/order.sql
>
> Я знал как работает этот метод, но этот метод не покрывает всех
> значений. Он не работает для NULL (да у Oracle проблемы с этим).
> То что сделал я работает для всех значений... Согласен, что NULLs
> должны будут особым образом проверятся (для этого в 8i введены
> NULLS FIRST, NULLS LAST), а иногда это нужно.
>
> > Доброе дело вы сделали, только этот вариант(С ХИНТАМИ), как имеющий
> > лучший план
> > выполнения, надо бы на первое место поместить. На второе место PLSQL
>
> Да, совершенно согласен. Но не общий вариант решения задачи.
Да конечно, но общий может состоять из меньшего числа примеров.
Сэнкс за ссылку в доку, да Data Warehousing похоже вкусная штука - это
же,
наверное, не совсем upgrade?
Я посмотрел (RANK() OVER(PARTITION - рулез ) :
SELECT region, product, sum_s_amount FROM (SELECT r_regionkey AS
region, p_
product_key AS product, SUM(s_amount) AS sum_s_amount, RANK()
OVER(PARTITION BY
r_region_key ORDER BY SUM(s_amount) DESC AS rank1,
FROM product, region, sales
WHERE r_region_key = s_region_key AND p_product_key = s_product_key
GROUP BY r_region_key ORDER BY r_region_key)
WHERE rank1 <= 4;
>
> Я согласен что это индексы -- это круто и быстро работает, есть одно "но".
> Можно очень сильно "попасть" если индекс "вдруг" перестанет работать...
> Это кажется философией, но опыт показывает что такое бывает, редко, но
> бывает (бага в оптимизаторе). Особенно это может быть "больно" в финансовых
> приложениях, где лучше медленно, но верно, чем быстро и... **Речь лишь о
> компромиссах**. Иногда очень неудобно говорить "ой", например CEO, о том
> что разработчик наш умный, но вот он тут стал использовать что-то, и это
> что-то покорябило годовой отчет, который мы уже отослали на проверку в
> "гос.учреждение", а сегодня ревизия придет... вообщем нужно спасаться...
> Я не люблю говорить такие вещи. :) Думаю, кто был в такой ситуации, тот
> разделит это мнение.
Это про опыт с сыном?
Тремя руками за, я так и написал, что если задача не стоит как просто
отображение данных (с хвоста, или наооборот, - не отчет, но журнал,
список клиентов, ...), то лучше использовать PL/SQL решение. В
крайнем случае, можно
завернуть в процедуру, и если надо рисовать таблицы, можно возвращать
result
set на клиента, (еще один интересный пример в вашу коллекцию).
>
> Еще раз, метод -- хорош, эта функциональность была включена в пример,
> вы меня убедили, что два файла с примерами нужно сделать одним...
Да я, вообще-то, в другом пытаюсь вас убедить.
РУССКОЕ ИЗДАНИЕ ORACLE MAGAZINE - ¦2(4) 1997г.
МАСТЕРСКАЯ РАЗРАБОТЧИКА LГОРЯЧАЯ¦ ДЕСЯТКА
PL/SQL РЕШЕНИЕ :
DECLARE
i NUMBER := 1 ;
BEGIN
dbms_output.enable(9000);
dbms_output.put_line( -ENAME SAL- );
dbms_output.put_line( -????????- );
FOR c IN (select ename,sal from emp order by sal desc) LOOP
dbms_output.put_line(rpad(c.ename,15)||to_char(c.sal,
-99999-) );
EXIT WHEN i = 5 ;
i := i+1 ;
END LOOP;
END;
/
Или так (выдать первых 10 толстосумов):
DECLARE
i NUMBER := 1 ;
amount NUMBER(10);
nCount NUMBER(10);
BEGIN
dbms_output.enable(100000);
FOR c IN (SELECT id, sum(amount$) amounts, count(*) as Counts
FROM operations
GROUP BY id
ORDER BY 2 desc )
LOOP
dbms_output.put_line('ID = '|| c.id ||',
amount = '||c.amounts || ', Count =
'||c.Counts);
EXIT WHEN i = 10 ;
i := i+1 ;
END LOOP;
END;
Какие области сабжевой проблемы не покрывает этот вариант?
С уважением,
Игорь Лемешко.
Vladimir Begun
> Да конечно, но общий может состоять из меньшего числа примеров.
У меня место на диске не лимитировано.
:)
> Сэнкс за ссылку в доку, да Data Warehousing похоже вкусная штука - это
> же, наверное, не совсем upgrade?
Что значит "не совсем"? Ну назовите это
переносом вашей системы на более новую
версию.
> Я посмотрел (RANK() OVER(PARTITION - рулез ) :
Хорошо, если рулез.
...
> Это про опыт с сыном?
С каким "сыном"?
> Тремя руками за, я так и написал, что если задача не стоит как просто
> отображение данных (с хвоста, или наооборот, - не отчет, но журнал,
> список клиентов, ...), то лучше использовать PL/SQL решение. В
> крайнем случае, можно
> завернуть в процедуру, и если надо рисовать таблицы, можно возвращать
> result
> set на клиента, (еще один интересный пример в вашу коллекцию).
Спасибо.
> > Еще раз, метод -- хорош, эта функциональность была включена в пример,
> > вы меня убедили, что два файла с примерами нужно сделать одним...
> Да я, вообще-то, в другом пытаюсь вас убедить.
Не зачем. PL/SQL -- это хорошо и никто с этим не спорил.
Если с меня нужно признение о том что что-то можно сделать
эффективнее чем я написал, то я с радостью его даю :)
> Какие области сабжевой проблемы не покрывает этот вариант?
Смысл их искать, те проблемы? Они сами появятся.
--
Vladimir Begun | On a paper submitted by a physicist
http://vbegun.net/ | colleague:
http://vbegun.net/wap/ |
m...@vbegun.net | "This isn't right. This isn't even wrong."
| -- Wolfgang Pauli
Igor Lemeshko
День добрый Станислав,
[..]
> Добрий день, Timofey VG
>
> Немножко добавлю
>
> Вопрос возникает с завидной переодичность
> причины
>
> а)с первыми/последними n у оракла проблемы (до 8и)
>
> б)решение отражено в документации,
> но не выделено, так вскользь упоминвется
>
> в)для 8и
> законное решение
> 1)
> SELECT column_list ROWNUM FROM
> (SELECT column_list FROM table
> ORDER BY Top-N_column)
> WHERE ROWNUM <= N
> Подчеркиваю, ето не просто ORDER BY в подзапросе
> Оптимизатор понимает
> Что нодо выброть 100 первых из 10000000
> упорядочиватся будут 100 записей,
> а не 10000000
> (SORT (ORDER BY STOPKEY))
Большой сэнкс, действительно работает на 8i (запрос с ORDER BY во from, да еще
и со STOPKEY ).
> 2) использовать аналитические функции
> (не поддерживаются в pl/sql)
>
>
> Vladimir Begun <jes...@sunbay.com> wrote in message news:<3B5C3AB5...@sunbay.com>...
> > Timofey VG wrote:
> > >
> > > Vladimir Begun wrote:
> > > > SQL> alter session set optimizer_goal=rule -- что с энтим, что без...
> > > ничего не могу сказать - не мой постинг
>
> Почему ничего, если выбрано optimizer_goal=rule
> Или хинтом только /*+ RULE */
> тогда не надо прописывать хинт для индекса
> да и не было их тогда во времена RULE
> а надо обязательно order by n
Т.е. в каждом запросе не надо ставить хинт.
Плюс, см. ниже о Ordered.
> При етом ортимизатор при регулярном подходе
> должен выбрать 14 ранг
> 14 - ORDER BY по индексированным столбцам
> (понятно что должен быть NOT NULL)
>
> Оракл уже давно предупреждает,
> что optimizer_goal=rule
> поддерживатся не будет
В этом режиме проще управлять хинтами планом запроса(точнее, можно иметь больше
уверенности, что план не поменяется с течением времени), надеюсь, все-таки, что
rule останется и надолго.
Хинт ordered в зависимости от режим оптимизатора choose/ rule на
уровне сервера(сессии) дает разные планы(если нет статистики).
К примеру, если задано optimizer_goal=rule на уровне сервера(или сессии), то,
установка хинта STAR не поменяет запрос, если choose, то поменяет.
Я из этих соображений предлагал использовать alter session set
optimizer_goal, т.е. планы разные и это надо учитывать.
[..]
С уважением,
Игорь Лемешко.
Vladimir Begun
> Большой сэнкс, действительно работает на 8i (запрос с ORDER BY во from,
да еще и со STOPKEY ).
Да, Станислав, ты был прав, нужно было это обрисовать,
как ты советовал... :)
> > Оракл уже давно предупреждает,
> > что optimizer_goal=rule
> > поддерживатся не будет
>
> В этом режиме проще управлять хинтами планом запроса(точнее, можно иметь больше
> уверенности, что план не поменяется с течением времени), надеюсь, все-таки, что
> rule останется и надолго.
Oracle уже давно имеет возможность использовать outlines,
которые гибче (но от багов никуда не уйти :).
Кроме того план в rule может за нечего делать поменяться,
скажем при восстановлении таблицы из бэкапа или пересоздании
индексов, положим случилось что-то плохое... rule sucks [иногда].
--
Vladimir Begun | She often gave herself very good advice
http://vbegun.net/ | (though she very seldom followed it).
http://vbegun.net/wap/ | -- Lewis Carroll
Stanislav Shydlovskyy
Извените что не в нитку,
что-то странное у меня с NEWS
>Igor Lemeshko wrote:
[..]
>> Почему ничего, если выбрано optimizer_goal=rule
>> Или хинтом только /*+ RULE */
>> тогда не надо прописывать хинт для индекса
>> да и не было их тогда во времена RULE
>> а надо обязательно order by n
> Т.е. в каждом запросе не надо ставить хинт.
> Плюс, см. ниже о Ordered.
[..]
Вот из доки по семерке
RULE
Совет RULE явно выбирает регулярный подход для оптимизации блока
предложения. Этот совет также заставляет оптимизатор
игнорировать любые другие советы, которые специфицированы для
этого блока предложения. Например, для следующего предложения
будет выбран регулярный подход:
SELECT --+ RULE
empno, ename, sal, job
FROM emp
WHERE empno = 7566
Совет RULE, так же, как и регулярный подход, не будет доступен в
будущих версиях ORACLE.
Написано прямо, что
" оптимизатор игнорирует любые другие советы"
В 8и
http://technet.oracle.com/doc/server.815/a67775/ch7_opti.htm#4415
The RULE hint explicitly chooses rule-based
optimization for a statement block. It also makes
the optimizer ignore other hints
specified for the statement block.
Написано ведь прямо
"optimizer ignore other hints"
На самом деле в 8.1.6.0
другие хинты принимаются (рассматриваются/NOT ignore)
почему так я не знаю
> С уважением,
> Игорь Лемешко.
--
С уважением,
Stax
Vitaliy N. Romanuk
Была создана таблица с определенными полями, а затем написана
процедура, в которой использовались параметры как Таблица.Поле%Type. После
того, как в таблицу добавили пару полей (через Alter Table Table_Name
Add (Новое_Поле описание_поля))при использовании в процедуре параметров как
Таблица.Новое_Поле%Type Oracle ругается на эти параметры и пишет, что
Новое_Поле must be declared.
Подскажите, плиз, в чем может быть проблема.
Igor Lemeshko
>
> Извените что не в нитку,
> что-то странное у меня с NEWS
у меня тоже, точнее у google
Получается, что не совсем прямо там написано. В приводимом тогда
примере был
еще запрос
с хинтом INDEX. У меня нет уверенности, что при установленных
choose/rule/all_rows/first_rows сервер даст одинаковый план
выполнения,
как это происходит с ORDERED. В доке в описании некоторых хинтов, не
указывается, что план будет зависеть еще и от общего режима
оптимизатора для
сессии. Да еще и зависит от наличия статистики(даже в rule, при
собранной
статистике, ordered может выполниться как в all_rows ).
Теоретически, сервер лучше знает свои ресурсы, и должен построить
более оптимальный план. Реально же, очень часто приходиться колдовать
с хинтами(особенно это касается порядка обхода таблиц),
и при этом надо учитывать общий режим оптимизатора в котором работает
сессия.
Поэтому в примере я и отметил использование optimizer_goal. Поправьте,
плз, в
чем я не прав.
>
> > С уважением,
> > Игорь Лемешко.
Igor Lemeshko
Vladimir Begun <jes...@sunbay.com> wrote in message news:<3B5D76A9...@sunbay.com>...
> Igor Lemeshko wrote:
> > Да конечно, но общий может состоять из меньшего числа примеров.
>
> У меня место на диске не лимитировано.
> :)
В свете последнего сообщения Станислава, можно оставить два примера.
C ORDER BY во FROM(для 8i), и второй с планом по индексу.
Журналы, большие списки можно(и наверное нужно)
просматривать вторым вариантам, деньги считать первым.
Зачему нужны остальные я не знаю.
> > Сэнкс за ссылку в доку, да Data Warehousing похоже вкусная штука - это
> > же, наверное, не совсем upgrade?
>
> Что значит "не совсем"? Ну назовите это
> переносом вашей системы на более новую
> версию.
>
> > Я посмотрел (RANK() OVER(PARTITION - рулез ) :
>
> Хорошо, если рулез.
>
> ...
> > Это про опыт с сыном?
>
> С каким "сыном"?
ошибок, блин, трудных.
>
> > Тремя руками за, я так и написал, что если задача не стоит как просто
> > отображение данных (с хвоста, или наооборот, - не отчет, но журнал,
> > список клиентов, ...), то лучше использовать PL/SQL решение. В
> > крайнем случае, можно
> > завернуть в процедуру, и если надо рисовать таблицы, можно возвращать
> > result
> > set на клиента, (еще один интересный пример в вашу коллекцию).
>
> Спасибо.
Пример с возвратом result set из процедуры на клиента интересен сам по себе(и
его надо бы привести - часто возникает этот вопрос),
но уже не для этого случая(order by во FROM работает).
> > > Еще раз, метод -- хорош, эта функциональность была включена в пример,
> > > вы меня убедили, что два файла с примерами нужно сделать одним...
> > Да я, вообще-то, в другом пытаюсь вас убедить.
>
> Не зачем. PL/SQL -- это хорошо и никто с этим не спорил.
> Если с меня нужно признение о том что что-то можно сделать
> эффективнее чем я написал, то я с радостью его даю :)
>
> > Какие области сабжевой проблемы не покрывает этот вариант?
>
> Смысл их искать, те проблемы? Они сами появятся.
Это точно.
Igor Lemeshko
Vladimir Begun <jes...@sunbay.com> wrote in message news:<3B5D84BE...@sunbay.com>...
> Igor Lemeshko wrote:
> > Большой сэнкс, действительно работает на 8i (запрос с ORDER BY во from,
> да еще и со STOPKEY ).
>
> Да, Станислав, ты был прав, нужно было это обрисовать,
> как ты советовал... :)
Что обрисовать?
>
> > > Оракл уже давно предупреждает,
> > > что optimizer_goal=rule
> > > поддерживатся не будет
> >
> > В этом режиме проще управлять хинтами планом запроса(точнее, можно иметь больше
> > уверенности, что план не поменяется с течением времени), надеюсь, все-таки, что
> > rule останется и надолго.
>
> Oracle уже давно имеет возможность использовать outlines,
> которые гибче (но от багов никуда не уйти :).
>
> Кроме того план в rule может за нечего делать поменяться,
> скажем при восстановлении таблицы из бэкапа или пересоздании
> индексов, положим случилось что-то плохое... rule sucks [иногда].
Точнее будет при их несоздании(индексов), но тут и choose не поможет :).
Можно пару слов о outlines я в доке видал(давно), но "не едал"?
Сложно их заводить поддерживать, какова гибкость в создании плана, можно его
задать явным образом?
Vladimir Begun
> Vladimir Begun <jes...@sunbay.com> wrote in message news:<3B5D84BE...@sunbay.com>...
> > Igor Lemeshko wrote:
> > > Большой сэнкс, действительно работает на 8i (запрос с ORDER BY во from,
> > да еще и со STOPKEY ).
> >
> > Да, Станислав, ты был прав, нужно было это обрисовать,
> > как ты советовал... :)
> Что обрисовать?
Он понял о чем я.
> > > > Оракл уже давно предупреждает,
> > > > что optimizer_goal=rule
> > > > поддерживатся не будет
> > >
> > > В этом режиме проще управлять хинтами планом запроса(точнее, можно иметь больше
> > > уверенности, что план не поменяется с течением времени), надеюсь, все-таки, что
> > > rule останется и надолго.
> >
> > Oracle уже давно имеет возможность использовать outlines,
> > которые гибче (но от багов никуда не уйти :).
> >
> > Кроме того план в rule может за нечего делать поменяться,
> > скажем при восстановлении таблицы из бэкапа или пересоздании
> > индексов, положим случилось что-то плохое... rule sucks [иногда].
> Точнее будет при их несоздании(индексов), но тут и choose не поможет :).
Нет, точнее будет так как я написал.
> Можно пару слов о outlines я в доке видал(давно), но "не едал"?
> Сложно их заводить поддерживать, какова гибкость в создании плана, можно его
> задать явным образом?
В документации есть примеры. На metalinke тоже.
--
Vladimir Begun | Coming up with the correct pattern is left
http://vbegun.net/ | as an exercise for the exorcists.
http://vbegun.net/wap/ | -- Larry Wall in
m...@vbegun.net | <2000020317...@kiev.wall.org>
Vladimir Begun
> Vladimir Begun <jes...@sunbay.com> wrote in message news:<3B5D76A9...@sunbay.com>...
> > Igor Lemeshko wrote:
> > > Да конечно, но общий может состоять из меньшего числа примеров.
> > У меня место на диске не лимитировано.
> > :)
> В свете последнего сообщения Станислава, можно оставить два примера.
> C ORDER BY во FROM(для 8i), и второй с планом по индексу.
Я благодарю за совет. Но это уже давно там, с самого начала разговора.
Невнимаение -- это причина споров.
> Журналы, большие списки можно(и наверное нужно)
> просматривать вторым вариантам, деньги считать первым.
> Зачему нужны остальные я не знаю.
Up to you.
> Пример с возвратом result set из процедуры на клиента интересен сам
> по себе
Мне он не особенно интересен -- это есть в документации. Ее просто никто
не читает.
> (и его надо бы привести - часто возникает этот вопрос),
Все в ваших руках. :)
--
Vladimir Begun | New members urgently required for SUICIDE
http://vbegun.net/ | CLUB, Watford area.
http://vbegun.net/wap/ | -- Monty Python's Big Red
m...@vbegun.net | Book
Vitaliy N. Romanuk
Никто не подскажет как можно в процедуру передавать различное кол-во
параметров (одинакового типа NUMBER), чтобы потом в процедуре можно было
использовать это множество в запросе типа:
SELECT * FROM TABLE_NAME WHERE FIELD1 IN (данное множество параметров) ?
Может быть есть какой-нибудь перечислимый тип, который можно использовать в
данном примере запроса?
Используется сервер Oracle 8.1.6.
Заранее спасибо, Виталий.
Vladimir Begun
Отвечаю на 2 постинга:
1.
> Хинт ordered в зависимости от режим оптимизатора choose/ rule на
> уровне сервера(сессии) дает разные планы(если нет статистики).
> К примеру, если задано optimizer_goal=rule на уровне сервера(или сессии), то,
> установка хинта STAR не поменяет запрос, если choose, то поменяет.
Разумеется, потому что, choose (если найдет статискику) будет использовать
ALL_ROWS [by default]. Кроме того "поменяет" -- это вовсе на означает
что все будет сделано эффективно. Вы говорите "если нет статистики",
но ведь есть default statistics -- косая, кривая, но есть. И вполне
возможно что на некоторых версиях Oracle и каком-то наборе данных иногда
предпочтение может быть отдано RULE, чем скажем STAR c default statistics.
Это предположение. Смотреть детально нужно.
Oracle8i Release 2 (8.1.6) Designing and Tuning for Performance
Features that Require the CBO. The use of any of the following features
requires the use of the CBO:
^^^^^^^^^^^^^^^^^^^^^^^^^^^
...
* Star transformations
* Star joins
...
Note: Even if the parameter OPTIMIZER_MODE is set to RULE, the
use of these features enables the CBO.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Optimizing Star Queries
...
The CBO recognizes star queries and generates efficient
execution plans for them. (Star queries are not recognized by
the RBO.)
> Я из этих соображений предлагал использовать alter session set
> optimizer_goal, т.е. планы разные и это надо учитывать
Да, но довод о STAR join немного странный. Я не говорю
о багах, я говорю о том как это задумано.
Кроме того. Статистика нужна для CBO. Без нее он не работает
как это задумывалось.
2.
> Получается, что не совсем прямо там написано. В приводимом тогда
> примере был еще запрос с хинтом INDEX. У меня нет уверенности, что
> при установленных choose/rule/all_rows/first_rows сервер даст
> одинаковый план выполнения, как это происходит с ORDERED. В доке в
> описании некоторых хинтов, не указывается, что план будет зависеть
> еще и от общего режима оптимизатора для сессии. Да еще и зависит от
> наличия статистики(даже в rule, при собранной статистике, ordered
> может выполниться как в all_rows ). Теоретически, сервер лучше знает
ALL_ROWS -- это default mode.
If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you
do not add hints to your SQL statements, then your statements use
^^^^^^^^^^
the RBO. You can use the RBO to access both relational data and object
types. If OPTIMIZER_MODE=FIRST_ROWS or ALL_ROWS and no statistics
exist, then the CBO uses default statistics. You should migrate your
^^^^^^^^^^^^^^^^^^^^^^^ -- это вызывает проблемы иногда
existing applications to use the cost-based approach.
You can enable the CBO on a trial basis simply by collecting statistics.
You can then return to the RBO by deleting the statistics or by setting
either the value of the OPTIMIZER_MODE initialization parameter or the
OPTIMIZER_MODE clause of the ALTER SESSION statement to RULE. You can
also use this value if you want to collect and examine statistics for
your data without using the cost-based approach.
> свои ресурсы, и должен построить более оптимальный план. Реально же,
> очень часто приходиться колдовать с хинтами(особенно это касается
> порядка обхода таблиц), и при этом надо учитывать общий режим
> оптимизатора в котором работает сессия.
Поэтому подход, подходу рознь. И называть что-то "правильным решением"
или нет, можно лишь базируясь на том опыте, который есть -- а он
индивидуален -- это относится к задачке о SQL-based TOP-выборке. :)
Как сказал Эд:
> > (alter session set optimizer_goal=rule) и/или используйте хинты.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
такое сочетание будет использовать "горе от ума" :)
Как придумано:
Note:35934.1:
Hints
- Any hint, except RULE, causes CBO to be used.
It is very important to note that a HINT cannot be 'turned
off' by any parameter settings.
Note:10626.1:
The rule based Optimizer can be influenced in many ways. The query
could be rearranged with the tables ordered differently to force
a certain join order, functions could be put around indexed columns
to avoid using the index, etc. These techniques were developed by
users over a period of time and were valuable in acheiving desired
performance. However, this is hard to document and difficult to
generalize. To avoid these difficulties, the cost optimizer
incorporates a mechanism for users to directly influence the
behaviour of the optimizer in cases where the optimizer does not
produce the best result. Users can specify directives which include
access methods, join processing techniques, join orders, etc.
through hints.
> Поэтому в примере я и отметил использование optimizer_goal. Поправьте,
> плз, в чем я не прав.
Еще раз, я не говорю о багах, только о том что задумывалось.
--
Vladimir Begun | The code also assumes that it's difficult to
http://vbegun.net/ | misspell "a" or "b". :-)
http://vbegun.net/wap/ | -- Larry Wall in
m...@vbegun.net | <1997102217...@wall.org>
Dmitry Rogulin
> параметров (одинакового типа NUMBER), чтобы потом в процедуре можно было
> использовать это множество в запросе типа:
>
> SELECT * FROM TABLE_NAME WHERE FIELD1 IN (данное множество параметров) ?
А просто передавать строку, в которой через запятую указаны необходимые
параметры и использовать динамический SQL ?
Дмитрий.
--
Зайцев Олег
Vitaliy N. Romanuk <VRom...@unf.kiev.ua> сообщил в новостях
следующее:2.07b7.OV...@unf.kiev.ua...
Это он ругается при компиляции или при работе? Если при компиляции, то это
интересно. Я один раз наблюдал такой глюк - таблица модифицировалась из
той-же сессии, из которой затем шла перекомпиляция процедуры. И наблюдалось
подобное явление, причем после переконнекта глюк исчез. Согласно теории
такого не должно быть .... но это было в Orcale 8.0, в 8i такого глюка не
разу не наблюдалось.
С уважением, Зайцев Олег
Vitaliy N. Romanuk
>> параметров (одинакового типа NUMBER), чтобы потом в процедуре можно было
>> использовать это множество в запросе типа:
>>
>> SELECT * FROM TABLE_NAME WHERE FIELD1 IN (данное множество параметров) ?
> А просто передавать строку, в которой через запятую указаны необходимые
> параметры и использовать динамический SQL ?
Хорошее решение, спасибо.
Только есть еще такая проблемка: я в процедуре описываю курсор с похожей
структурой запроса и, как я понимаю, динамически это никак нельзя решить.
Vitaliy N. Romanuk
> С уважением, Зайцев Олег
У нас Oracle 8.1.5, и ругается он при компиляции. Глюк пока никуда не
делся :(
Dmitry Rogulin
> структурой запроса и, как я понимаю, динамически это никак нельзя решить.
Не совсем понял вопрос...
Vitaliy N. Romanuk
wrote:
>> Только есть еще такая проблемка: я в процедуре описываю курсор с похожей
>> структурой запроса и, как я понимаю, динамически это никак нельзя решить.
> Не совсем понял вопрос...
В процедуре предполагается описание и использование курсора
CURSOR CURSOR_NAME IS SELECT * FROM TABLE_NAME WHERE FIELD_NAME IN (список),
где список - это список параметров, которые должны передаваться в процедуру.
Ведь нельзя динамически как-то построить курсор, а затем его использовать в
курсорном цикле FOR ?
Зайцев Олег
Тогда непонятно ! У нас тоже 8.1.5, я не поленился проверить - все работает
! А поля точно добавились в таблицу ?? (Имеется в виду, что может быть, у
них что-то с именами - буква русская вкралась или еще что нибудь подобное).
Я рекомендую сделать
SELECT * FROM ALL_TAB_COLUMNS
WHERE owner = 'схема'
AND table_name = 'таблица'
и скопировать имена столбцов из результатов ее работы через буфер. Если и
тогда не сработает, то это верояно глюк сервера.
С уважением, Зайцев Олег
Dmitry Rogulin
> CURSOR CURSOR_NAME IS SELECT * FROM TABLE_NAME WHERE FIELD_NAME IN
(список),
> где список - это список параметров, которые должны передаваться в
процедуру.
> Ведь нельзя динамически как-то построить курсор, а затем его использовать
в
> курсорном цикле FOR ?
Это почему же нельзя?
DBMS_SQL именно этим и занимается, от и до...
Дмитрий.
Vitaliy N. Romanuk
wrote:
>> Ведь нельзя динамически как-то построить курсор, а затем его использовать
> в
>> курсорном цикле FOR ?
> 8-0 !
> Это почему же нельзя?
> DBMS_SQL именно этим и занимается, от и до...
Vitaliy N. Romanuk
(zai...@smolen.elektra.ru) wrote:
> Тогда непонятно ! У нас тоже 8.1.5, я не поленился проверить - все
> работает
> ! А поля точно добавились в таблицу ?? (Имеется в виду, что может быть, у
> них что-то с именами - буква русская вкралась или еще что нибудь
> подобное).
> Я рекомендую сделать
> SELECT * FROM ALL_TAB_COLUMNS
> WHERE owner = 'схема'
> AND table_name = 'таблица'
> и скопировать имена столбцов из результатов ее работы через буфер. Если и
> тогда не сработает, то это верояно глюк сервера.
Я уже так делал, не катит :(
Тогда получается действительно какой-то глюк с сервером.
Спасибо.
Gluchar
Добрый день ,Виталий .
Используется сервер
Oracle8i Enterprise Edition Release 8.1.7.0.1
---------------------------------------------
DECLARE
TYPE RCT_CURSOR IS REF CURSOR;
L_RC_C1 RCT_CURSOR;
L_NAME VARCHAR2(30);
L_ID NUMBER;
L_WHERECLAUSE VARCHAR2 (200) := '(103,105)';
BEGIN
OPEN L_RC_C1 FOR 'SELECT id,name FROM
table_name WHERE id IN '
|| L_WHERECLAUSE;
LOOP
FETCH L_RC_C1 INTO L_ID, L_NAME;
EXIT WHEN L_RC_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ( ' Name --> '
|| L_NAME);
END LOOP;
END;
Igor Lemeshko
Vladimir Begun <jes...@sunbay.com> wrote in message news:<3B5E7798...@sunbay.com>...
> Igor Lemeshko wrote:
>
> Отвечаю на 2 постинга:
Аналогично.
>
> 1.
> > Хинт ordered в зависимости от режим оптимизатора choose/ rule на
> > уровне сервера(сессии) дает разные планы(если нет статистики).
Здесь же не говориться, что это неправильно. Просто констатация факта.
> > К примеру, если задано optimizer_goal=rule на уровне сервера(или сессии), то,
> > установка хинта STAR не поменяет запрос, если choose, то поменяет.
Пример не верен, повторить мне его не удалось. STAR требует CBO, и
план
совпадает с CBO'ошным независимо от статистики(все по доке, но тоже
демонстрирует(как и любой хинт) зависимость плана от optimizer_goal).
>
> Разумеется, потому что, choose (если найдет статискику) будет использовать
> ALL_ROWS [by default]. Кроме того "поменяет" -- это вовсе на означает
> что все будет сделано эффективно. Вы говорите "если нет статистики",
> но ведь есть default statistics -- косая, кривая, но есть. И вполне
> возможно что на некоторых версиях Oracle и каком-то наборе данных иногда
> предпочтение может быть отдано RULE, чем скажем STAR c default statistics.
> Это предположение. Смотреть детально нужно.
Я только это и пытался сказать, что план запроса зависит от того, в
каком режиме работатет сессия/сервер. Т.е. хинты не определяют
одназначно план
выполнения(как это может показаться).
План выполнения еще зависит от режима сервера/сессии(rule, choose,
all_rows,first_rows) + от наличия статистики, плюс от фитчей.
> Oracle8i Release 2 (8.1.6) Designing and Tuning for Performance
>
> Features that Require the CBO. The use of any of the following features
> requires the use of the CBO:
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^
> ...
> * Star transformations
> * Star joins
> ...
>
> Note: Even if the parameter OPTIMIZER_MODE is set to RULE, the
> use of these features enables the CBO.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> Optimizing Star Queries
> ...
> The CBO recognizes star queries and generates efficient
> execution plans for them. (Star queries are not recognized by
> the RBO.)
>
> > Я из этих соображений предлагал использовать alter session set
> > optimizer_goal, т.е. планы разные и это надо учитывать
>
> Да, но довод о STAR join немного странный. Я не говорю
> о багах, я говорю о том как это задумано.
Вы правы, согласен.
Не знаю о чем вы? Я использую проверенное годами PL/SQL решение,
и хинты в задачах отображения первых последних строк.
Я действительно не знал, что в 8i можно использовать order by в
запросе вложенном во FROM, точно не помню, но по-моему на 8.0*,
у меня тоже нe получилось выполнить такой запрос.
Да и потом нужды не было, те места где это было нужно, уже отлажены
и работают.
Вы, кстати, собирались что-то обрисовать по поводу этого решения:
>> Да, Станислав, ты был прав, нужно было это обрисовать,
>> как ты советовал... :)
>Что обрисовать?
Может есть какие-то ограничения(кроме версии Oracle)?
http://vbegun.net/oramag/order/order.sql
-- 8i is pretty clever now...
SELECT v
FROM (
SELECT v
FROM m
ORDER BY 1 DESC
)
WHERE ROWNUM <= &limit
/
В восьмерке, есть фитчи, которые особо не
рекламируются(или скорее, я плохо искал), но очень удобны. К примеру,
можно использовать подзапрос вместо имени поля, это очень удобно
порой,
и узнал об этом тоже случайно (в каждой версии проверял, потому, что у
Билли это давно уже работало).
>
> Как сказал Эд:
>
> > > (alter session set optimizer_goal=rule) и/или используйте хинты.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> такое сочетание будет использовать "горе от ума" :)
Ну это эмоции.
Что тут скажешь? Честно говоря я не хотел отвечать на эту
мессагу(может выйти
диспут на тему кто лучше, грузины или армяне(делают шашлык) ),
но раз народ требует продолжения банкета, то прийдется.
Итак, могу сказать, что это сочетание (RULE на сервере+иногда
хинты)используют
очень многие опытные админы и разработчики. Это несмотря на то, что
Oracle
очень рекомендует в новых разработках использовать CBO, и пугает
удалением RDO ( не знаете в 9i удалили RDO - будет "очень страшное
кино 3" )?
Используется RULE на сервере и не собранная статистика на большенстве
таблиц+иногда хинты.
Видимо, они(опытные админы и разработчики) исходят из того, что
отлаженный
на каком-то объеме данных запрос, должен давать гарантированное время
отклика.
Хорошо, если оптимизатор собрал/не собрал/собрал мало(админ плохой)
статистики и улучшил запрос, но ведь он может сделать и наоборот.
Да и еще, есть новые версии и патчи Оракла, а в части оптимизации
RULE уже не меняется. Т.е. опять же, разработчику не хочется слышать,
что его детище глюкает или тормозит на новой версии оракла.
Кроме того, сбор статистики напрягает сервер, и это тоже не всегда
приемлемо.
Далее, если нужно использовать какие-то новые фитчи, или просто
добиться лучшего плана, используют хинты(с собранной или нет
статистикой по таблицам в запросе), т.е. использованием хинта юзают
CBO когда это надо(для некоторых хинтов с собранной статистикой,
иногда без онной).От себя могу сказать, что имею опыт работы с
коммерческими системами
(большие и очень быстро растущие базы) в обоих режимах, и
RULE-режим+(хинты
когда надо) более предсказуем.
>
> Как придумано:
>
> Note:35934.1:
>
> Hints
> - Any hint, except RULE, causes CBO to be used.
> It is very important to note that a HINT cannot be 'turned
> off' by any parameter settings.
>
> Note:10626.1:
> The rule based Optimizer can be influenced in many ways. The query
> could be rearranged with the tables ordered differently to force
> a certain join order, functions could be put around indexed columns
> to avoid using the index, etc. These techniques were developed by
> users over a period of time and were valuable in acheiving desired
> performance. However, this is hard to document and difficult to
> generalize. To avoid these difficulties, the cost optimizer
> incorporates a mechanism for users to directly influence the
> behaviour of the optimizer in cases where the optimizer does not
> produce the best result. Users can specify directives which include
> access methods, join processing techniques, join orders, etc.
> through hints.
>
> > Поэтому в примере я и отметил использование optimizer_goal. Поправьте,
> > плз, в чем я не прав.
>
> Еще раз, я не говорю о багах, только о том что задумывалось.
Спасибо за ссылки, они лишний раз подтверждают то, что
надо учитывать режим оптимизации, в котором работатет сервер.
При optimizer_goal=rule любой (кроме RULE) хинт дает ALL_ROWS
режим(независимо от наличия статистики).
Хотя это не всегда срабатывает, похоже что Вы правы, что возможно с
дефолтной
статистикой есть проблемы. Возможно, что в отсутствии статистики,
Оракл делает
еще какие-то предположения кроме анализа дефолтной статистики. Пример
с ORDERED
это демонстрирует, т.е. план запроса с использованием ORDERED хинта
(при отсутствии статистики) не совпадает ни с ALL_ROWS ни с
FIRST_ROWS,
т.е. не работает в CBO или как-то хитро это делает.
Повторюсь, я не уверен, что на других хинтах, в каких-то версиях,
нет аналогичных проблем. Еще не разбирался, но заметил, SQL PLUS тоже
показывает серверный/сессионный режим оптимизатора,
хотя должен для примера с set optimizer_goal=rule и запроса с хинтами
должен показывать ALL_ROWS в плане выполнения. Это тоже сбивает с
толку.
[..]
От:Vladimir Begun (jes...@sunbay.com)
Число:2001-07-25 00:21:24 PST
>> Пример с возвратом result set из процедуры на клиента интересен сам
>> по себе
>Мне он не особенно интересен -- это есть в документации. Ее просто
никто
>не читает.
Но ведь и пример со сведением плана запроса к индексному
сканированию(нахождением первыч/последних записей) тоже есть в
документации. Еще в семерке в тьюнинговой книжке был пример со снятием
последних показаний с датчика меньше определенной даты.
Но я имел ввиду здесь пример проскакивающий в эхе пару лет назад,
где результат на клиента можно было выбирать с помощью обычного
SELECT.
Пример мне тогда показался очень интересным(автор по-моему Belov).
Кроме того в 8i, в этой части, по-моему есть что-то новенькое.
А как Вы правильно заметили, документацию мало кто читает, особенно по
новым
версиям(по старым я почти всю прочел), и такие вот "нечитальщики"
были бы вам очень признательны за интересный пример.
С уважением,
Игорь Лемешко.
--не смогу быстро ответить
Vladimir Begun
> Я только это и пытался сказать, что план запроса зависит от того, в
> каком режиме работатет сессия/сервер. Т.е. хинты не определяют
> одназначно план выполнения(как это может показаться).
Я могу сказать одно: когда хочется что-то узнать... то почти
всегда это можно сделать.
> План выполнения еще зависит от режима сервера/сессии(rule, choose,
> all_rows,first_rows) + от наличия статистики, плюс от фитчей.
+ багов.
> > > свои ресурсы, и должен построить более оптимальный план. Реально же,
> > > очень часто приходиться колдовать с хинтами(особенно это касается
> > > порядка обхода таблиц), и при этом надо учитывать общий режим
> > > оптимизатора в котором работает сессия.
> >
> > Поэтому подход, подходу рознь. И называть что-то "правильным решением"
> > или нет, можно лишь базируясь на том опыте, который есть -- а он
> > индивидуален -- это относится к задачке о SQL-based TOP-выборке. :)
>
> Не знаю о чем вы? Я использую проверенное годами PL/SQL решение,
> и хинты в задачах отображения первых последних строк.
PL/SQL решение имеет свои ограничения, вызов функции не всегда
возможно сделать.
> Я действительно не знал, что в 8i можно использовать order by в
> запросе вложенном во FROM, точно не помню, но по-моему на 8.0*,
> у меня тоже нe получилось выполнить такой запрос.
Это я уже понял. :)
> Да и потом нужды не было, те места где это было нужно, уже отлажены
> и работают.
> Вы, кстати, собирались что-то обрисовать по поводу этого решения:
Это был частный разговор со Станиславом, он посоветовал "объяснить"
как и что работает в 8i мой ответ был таким":
> можо подчеркнуть, что ето не просто
> ORDER BY 1 DESC в подзапросе,
> а оптимизатор понимает
> WHERE ROWNUM < &limit
Это подчеркнуто в документации. Уже одно то что это
выделено "For Oracle 8i" говорит о разнице...
Такие "ремарки" можно дописывать до бесконечности.
> В восьмерке, есть фитчи, которые особо не рекламируются(или скорее,
> я плохо искал), но очень удобны. К примеру, можно использовать
Да, скорее второе, чем первое. :)
> подзапрос вместо имени поля, это очень удобно порой, и узнал об
Да, и это сделано просто потому что появилая clause CASE (или
в тех версиях где её нет, код затачивался под её присутствие).
Моё ИМХО, никаких ссылок по этому поводу я дать не могу.
> этом тоже случайно (в каждой версии проверял, потому, что у Билли
> это давно уже работало).
Ну, это рынок и каждый что-то лямзит.
> > Как сказал Эд:
> >
> > > > (alter session set optimizer_goal=rule) и/или используйте хинты.
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > такое сочетание будет использовать "горе от ума" :)
>
> Ну это эмоции.
> Что тут скажешь? Честно говоря я не хотел отвечать на эту
> мессагу(может выйти
> диспут на тему кто лучше, грузины или армяне(делают шашлык) ),
> но раз народ требует продолжения банкета, то прийдется.
Никто не требует.
> Итак, могу сказать, что это сочетание (RULE на сервере+иногда
> хинты)используют
> очень многие опытные админы и разработчики. Это несмотря на то, что
Тут не хватает ключевого слова, а именно. "ПРИЧИНА такого
использования -- это..." Я пока не совсем понял причину, именно
так как понимаете это вы. Видимо, это и есть принципиальный
момент нашего разговора.
> Oracle
> очень рекомендует в новых разработках использовать CBO, и пугает
> удалением RDO ( не знаете в 9i удалили RDO - будет "очень страшное
> кино 3" )?
Нет, в 9i есть RBO.
> Используется RULE на сервере и не собранная статистика на большенстве
> таблиц+иногда хинты.
> Видимо, они(опытные админы и разработчики) исходят из того, что
> отлаженный
> на каком-то объеме данных запрос, должен давать гарантированное время
> отклика.
> Хорошо, если оптимизатор собрал/не собрал/собрал мало(админ плохой)
Сейчас можно статистику на лету собирать. См. документацию.
> статистики и улучшил запрос, но ведь он может сделать и наоборот.
Для этого и придумали hints.
> Да и еще, есть новые версии и патчи Оракла, а в части оптимизации
> RULE уже не меняется. Т.е. опять же, разработчику не хочется слышать,
> что его детище глюкает или тормозит на новой версии оракла.
> Кроме того, сбор статистики напрягает сервер, и это тоже не всегда
> приемлемо.
См. выше. Сейчас "крутые парни" :) всё собирают на лету.
> Далее, если нужно использовать какие-то новые фитчи, или просто
> добиться лучшего плана, используют хинты(с собранной или нет
> статистикой по таблицам в запросе), т.е. использованием хинта юзают
> CBO когда это надо(для некоторых хинтов с собранной статистикой,
> иногда без онной).От себя могу сказать, что имею опыт работы с
> коммерческими системами
> (большие и очень быстро растущие базы) в обоих режимах, и
> RULE-режим+(хинты
> когда надо) более предсказуем.
Хорошо.
> > Еще раз, я не говорю о багах, только о том что задумывалось.
>
> Спасибо за ссылки, они лишний раз подтверждают то, что
> надо учитывать режим оптимизации, в котором работатет сервер.
> При optimizer_goal=rule любой (кроме RULE) хинт дает ALL_ROWS
> режим(независимо от наличия статистики).
> Хотя это не всегда срабатывает, похоже что Вы правы, что возможно с
> дефолтной
> статистикой есть проблемы. Возможно, что в отсутствии статистики,
> Оракл делает
> еще какие-то предположения кроме анализа дефолтной статистики. Пример
> с ORDERED
> это демонстрирует, т.е. план запроса с использованием ORDERED хинта
> (при отсутствии статистики) не совпадает ни с ALL_ROWS ни с
> FIRST_ROWS,
> т.е. не работает в CBO или как-то хитро это делает.
Можно посмотреть на этот пример? Лучше частным письмом.
Я бы еще хотел знать структуру таблиц и индексов для
таблиц учавствующих в запросе -- вообщем чем больше
информации тем лучше. Просто интересно. Спасибо.
> Повторюсь, я не уверен, что на других хинтах, в каких-то версиях,
> нет аналогичных проблем. Еще не разбирался, но заметил, SQL PLUS тоже
> показывает серверный/сессионный режим оптимизатора,
Можно поинтересоваться, чтобы понять о чем речь... где именно
и что показывает SQL PLUS.
> хотя должен для примера с set optimizer_goal=rule и запроса с хинтами
> должен показывать ALL_ROWS в плане выполнения. Это тоже сбивает с
> толку.
--
Vladimir Begun | It would be possible to optimize some forms
http://vbegun.net/ | of goto, but I haven't bothered.
http://vbegun.net/wap/ | -- Larry Wall in
m...@vbegun.net | <1997090419...@wall.org>