Оракл?

[dil]

Я раньше думал, что Оракл умный. И сам умеет оптимизировать выполнение запросов. А тут наткнулся на ситуацию, когда простейший
SELECT count(*) FROM однатаблица WHERE поле = 'foo' AND дата_добавления >= SYSDATE-30/1440
выполнялся по 15 минут, пока в запрос явным образом не добавили /*+ INDEX_JOIN(BAZ) */

Что я делаю неправильно?

Comments

[dma.livejournal.com]

update statistics?

[dil.livejournal.com]

не понял

[dma.livejournal.com]

все эти оптимизации работают при актуальной статистике.
я помню плохо, но что-то типа analyze table update statistics.

[dil.livejournal.com]

а без статистики оракл настолько туп, что не может использовать имеющийся индекс самостоятельно?

[unbe.livejournal.com]

С неправильной статистикой он решил что без индекса или с другим индексом быстрее.

[npocmu.livejournal.com]

А откуда у него еще возьмутся сакральные знания о том, какой индекс предпочтительней? Только из статистики...

[dma.livejournal.com]

Сложно использовать индекс без знания о его кардинальности и селективности.
Потому что можно влететь ещё больше, чем на фуллскане.

за 5 лет работы ора дба ни разу не использовал INDEX_JOIN

[kranov.livejournal.com]

ой дебильный хинт.
видимо ваш разраб. не сделал индекса (поле,дата_добавления)

Re: за 5 лет работы ора дба ни разу не использовал INDEX_JOIN

[kranov.livejournal.com]

вариант объяснения почему дольше такой (считаем что статистика есть):

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

короче покажи
set autotrace on
вариант с хинтом;
вариант без хинта;

и все станет абсолютно ясно.

Re: за 5 лет работы ора дба ни разу не использовал INDEX_JOIN

[dil.livejournal.com]

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

[anatolix.livejournal.com]

Я ораклом не занимался много лет, но у него есть 2 оптимизатора afair - cost based и rule based. Вот поведение которое ты ждешь оно rule based - запрос такого типа выполняется по индексу и все.

Cost based принимает решение вида что "нафиг использовать индекс если мы например сейчас выберем 80% всей таблицы - быстрее full scan". Для этого естественно нужна статистика. Посколько ситуации в которых cost based рулит мне всегда казались очень странными я его всегда вырубал.

Если у тебя есть мнение про то как должны бы вполняться запросы переключи его в rule based (на вскидку не помню как это делается)

[dil.livejournal.com]

больше, чем на фуллскане - это как?

[anatolix.livejournal.com]

это выбрать по индексу 90% записей большой таблицы.

[dil.livejournal.com]

проверяемое поле неиндексированное, это флажок. поэтому другого индекса нет. есть только тот, который пришлось добавить вручную.

[dma.livejournal.com]

полный перебор даже B-дерева - дольше, чем плоской структуры.

Re: за 5 лет работы ора дба ни разу не использовал INDEX_JOIN

[kranov.livejournal.com]

Потому что бы правильно оценить стоимость предиката (>= SYSDATE-30/1440) надо иметь глистограммы, а у тебя их спорю на деньги нет. Т.е. статистику то все собирают, а вот какую это вопрос.

select count(*) from user_histograms where tablename = 'ТВОЯТАБЛИЦАИМЯ'

а оракл кстате какой? 10-ка автоматически начала собирать статистику примерно какую надо.

[dil.livejournal.com]

спасибо, понял.

Re: за 5 лет работы ора дба ни разу не использовал INDEX_JOIN

[dil.livejournal.com]

у меня вообще ничего нет, я в данном случае выступаю в роли прикладного программиста.
а dba - индус в худшем смысле этого слова, от него чего-то внятного можно добиться, только рассказав 90% решения :(

[kranov.livejournal.com]

можно просто SELECT /*+rule*/

>Посколько ситуации в которых cost based рулит мне всегда казались очень странными я его всегда вырубал.
Это очень упрощенный подход к жизни, в общем оракл обещает rbo выкинуть и хинты тоже отменить, и это правильно.

Re: за 5 лет работы ора дба ни разу не использовал INDEX_JOIN

[kranov.livejournal.com]

ну тогда оракл прав: нужен фулскан.

программер тоже может выполнить типа такого и посмотреть как изменится план.
begin

dbms_stats.gather_table_stats(ownname=>'OPKASSA', tabname=>'DAT_KASSA', estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 250');

end;

[anatolix.livejournal.com]

что мой подход упрощенный - согласен.

Но подход выкинуть rbo и хинты тоже упрощенный - все равно будут ситуации где человеку лучше знать как провести оптимизацию. Но как я уже говорил мой oracle остановился на версии 8i (который кстати я незнал-незнал и забыл) - возможно у них есть какая-то другая альтернатива для этого, про которую я просто не знаю.

[kranov.livejournal.com]

единственный нужный хинт +first_rows человек действительно правильно знает что хочет первые строки, в остальных случаях сомнительно. В например DB2 нет хинтов и как-то живут, и я пять лет программировал под информикс, тоже хинтами не пользовался.

[kranov.livejournal.com]

хотя если по честному в оракле я частенько использую хинты или уловки (numrows>=0), что бы сделать план таким каким мне надо, например только что написал:

select *
from (select * from a where func1(a.f0)>0 and numrows>=0), b

но причина лишь в том что проектировщик слишком полагался на функции, а их стоимость современные субд оценить не могут.

[anatolix.livejournal.com]

Я скорее не про хинты, а про rule based. В смысле хинты это просто средство заставить оптимизатор "прекратить умничать и пальцем показать", а rule based это наиболее тупой вариант поведения который вообще не умничает. В Mysql-е хинты помоему даже есть, но я ни разу не пользовался т.к. там всегда понятно что mysql сделаетс запросом при заданных индексах.

[kranov.livejournal.com]

таблица t1 с индексом по f1 и таблица t2 с индексом по f1

select *
from t1,t2
where t1.f0=t2.f0 and t1.f1=0 and t2.f1<0

что сделает mysql ?

[dil.livejournal.com]

я, собственно, потому вопрос и задал, что информикс без всяких хинтов нормально сам оптимизирует. и на этом фоне оракл меня удивил.

[unbe.livejournal.com]

а что такое numrows? Мой оракл такого не знает.

[kranov.livejournal.com]

оптимизатор информикса зажил на гистограммах в 96 году (самый дубовый вариант update statistics high -- собирает все по самое не могу, а в оракле немного сложнее), все базы оракла встречаемые мной сегодня до сих пор без гистограмм, статистику в них или не собирают или собирают неправильно.

Ну и еще оракл позволяет намного более изощеренные варианты и оптимизатор там намного намного сложнее.

вот например
http://www.sql.ru/forum/actualsearch.aspx?search=%CD%E5+%EF%EE%ED%FF%F2%ED%EE%2C+%E2%FB%E1%E8%F0%E0%E5%F2+%EF%EB%E0%ED+%F1+%E1%EE%EB%FC%F8%E5%E9+%F1%F2%EE%E8%EC%EE%F1%F2%FC%FE&sin=0&a=%C6%F3%F0%E0%E2%EB%E5%E2+%C4%E5%ED%E8%F1&ma=0&bid=3&dt=-1&s=1&so=1
оракл лажает на запросах, которые в принципе невозможны в информиксе.

с другой стороны
http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=362323#3689350
оптимизатор оракла строит план очень долго, а информикс за 0.1 сек.

тьфу rownum

[kranov.livejournal.com]

rownum

писал в этот момент в информиксе запрос к systables и переклинило.

для запроса с rownum оптимизатор не может сделать merge
т.е. переписать к виду:
select *
from a , b
where func1(a.f0)>0

Re: тьфу rownum

[unbe.livejournal.com]

ага, ясно, спасибо

Re: тьфу rownum

[kranov.livejournal.com]

Еще фокус в том что оптимизатор не может сделать predicate pushing

Т.е. функция тяжеловесна и ее надо выполнить последним предикатом

имеем запрос
select * from a where a.f1=100 and func1(a.f0)>0
какой предикат проверяется сначала, а фиг знает, а все функции по мнению оракла равен 0
есть хинт ordered_predicates но он неудобен, если предикатов >2

я переписываю запрос так numrows>=0
select *
from (select * from a where ... and numrows>=0)
where func1(a.f0)>0

Re: тьфу rownum

[unbe.livejournal.com]

тоже интересно. я так понимаю, оракл всеми силами хочет сказать, что тяжелые функции над полями использовать не надо

[anatolix.livejournal.com]

mysql по-моему всегда тупо сделает join.

Сам пример хороший аргумент против концепции "ничего кроме rule based не "нужно

[kranov.livejournal.com]

проблема с какой таблицы начать, т.е. надо знать какой предикат отсечет больше записей и какой метод соединения выбрать hashj nl mergej sortj ?

mysql всегда сначала идет в первую (во from) таблицу или как?

[anatolix.livejournal.com]

Да как-то так. Это зависит естественно от того какие индексы созданы.

Я на самом деле понял, что если сделать индексы:
t1: f1, f0
t2: f1, f0
то этот запрос можно выполнить очень быстро проходом по 2 индексам сразу.