Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MS SQL Server > like '%sometextfragment%'

Автор: FINANSIST 1.6.2012, 12:49

День добрый. есть условие запроса Код ------------------------- where  field1 = val1 and   field2 <> val2 and   field3 between val1 and  val2 and  field 4 in (val1,val2) and  field5  like '%somestuff%' ---------------------------- при этом все поля индексированны но ясен пень индекс по field5  сервером не используется. вопрос: как поступает сервер - понимает ли он что издержки выборки по условию field5 самые значимые и соответственно делает ли предварительное урезонное подмножество с условиями field1 - field 4 и только потом применяет условие field 5 или хреначит все пять условий для каждой записи всей таблицы? Пытался сравнить время возврата view этого запроса с запросом(условие по field5) из подзапроса(условия field1 по field4), время отклика очень разное ввиду крайне неравномерной загрузки сервера ( сидят 2 аксапты) - ничего не понятно. Прошу простого краткого ответа без ссылок на просмотр плана или гугла

Автор: Akina 1.6.2012, 13:14

Цитата(FINANSIST @  1.6.2012,  13:49 ) понимает ли он что издержки выборки по условию field5 самые значимые  И да, и нет. Но он точно понимает, что индекс использовать не удастся. Цитата(FINANSIST @  1.6.2012,  13:49 ) делает ли предварительное урезонное подмножество с условиями field1 - field 4 и только потом применяет условие field 5 или хреначит все пять условий для каждой записи всей таблицы? Он выберет подсет записей, используя один из четырёх индексов - скорее всего наиболее селективный из списка (1, 2, 4),- а остальные условия будет проверять прямым сканированием. А при небольшом количестве записей в таблице может вообще отказаться от использования индексов и сканировать всю таблицу. Цитата(FINANSIST @  1.6.2012,  13:49 ) Пытался сравнить время возврата view этого запроса с запросом(условие по field5) из подзапроса(условия field1 по field4) Из подзапроса - дольше. И чем больше по размеру запись, и чем ниже селективность подзапроса, тем оно заметнее.

Автор: Zloxa 1.6.2012, 14:41

Цитата(FINANSIST @  1.6.2012,  13:49 ) Прошу простого краткого ответа без ссылок на просмотр плана или гугла  Точный ответ на вопрос Цитата(FINANSIST @  1.6.2012,  13:49 ) вопрос: как поступает сервер Можно увидеть только в плане. Цитата(FINANSIST @  1.6.2012,  13:49 )  все поля индексированны Это как мертвому припарка. Индексный доступ, афайк, может быть осуществлен только по одному индексу. По идее, сервер должен выбрать индекс, который обеспечит большую селективность. Для этого запроса, скорее всего, наиболее эффективно может быть использован составной индекс  по (field1,field4,field3[,....]). Говорю "скорее всего" потому что не уверен на счет того, как будет работать отбор по field4.  Если бы в предикате по field4 было строгое равенство val1, отбор бы происходил примерно так: ищется точное соответствие field1 = :val1 and field4=:val1 and field3=:val1, затем бы сканировался диапазон, последовательным перебором, пока стрельнет условие field1 != :val1 or field4!=:val1 or field3>:val2.  Что будет происходить, когда у нас значение field4 отбирается не однозначно - не зна. Скорее всего, для заведомо известного количества значений (в данном случе - двух), индекс сможет быть использован, но для уверенности, надо смотреть в плане. Если не сможет, надо смотреть какую селективность смогут обеспечить индексы по (field1,field3[,....]), (field1,field4[,....]) и выбрать то, что даст большую селективность.

Автор: Zloxa 1.6.2012, 16:32

Цитата(Zloxa @  1.6.2012,  15:41 ) но для уверенности, надо смотреть в плане. Практика - критерий истины.    Для оракла DDL: Код create table test_table(   field1 number   ,field2 number   ,field3 number   ,field4 number   ,field5 varchar2(200)   ,val    number ); create index test_table$idx on test_table(field1,field4,field3); план: Код SQL> explain plan for   2    select *   3    from test_table   4    where field1 = :val1   5      and field2 <> :val2   6      and field3 between :val3 and  :val4   7      and field4 in (:val5,:val6)   8      and field5 like '%somestuff%';   Explained SQL> select * from table(dbms_xplan.display);   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3470946313 -------------------------------------------------------------------------------- | Id  | Operation                     | Name           | Rows  | Bytes | Cost (% -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |                |     1 |   167 |     1 |*  1 |  FILTER                       |                |       |       | |   2 |   INLIST ITERATOR             |                |       |       | |*  3 |    TABLE ACCESS BY INDEX ROWID| TEST_TABLE     |     1 |   167 |     1 |*  4 |     INDEX RANGE SCAN          | TEST_TABLE$IDX |     1 |       |     3 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_NUMBER(:VAL3)<=TO_NUMBER(:VAL4))    3 - filter("FIELD5" LIKE '%somestuff%' AND "FIELD2"<>TO_NUMBER(:VAL2))    4 - access("FIELD1"=TO_NUMBER(:VAL1) AND ("FIELD4"=TO_NUMBER(:VAL5) OR               "FIELD4"=TO_NUMBER(:VAL6)) AND "FIELD3">=TO_NUMBER(:VAL3) AND               "FIELD3"<=TO_NUMBER(:VAL4))   Здесь мы видим, что все поля, пречисленные в индексе, участвуют в отборе. на field5, field2, предикаты по которым в принципе не позволяют использовать индексный доступ, сверху отбора накладывается фильтр. Сверху накладывается фильтр :val3 < :val4. Это условие уже исключено условиями, перечисленными в предикатах доступа. Наверняка здесь фильтр нужен для того, чтобы даже не пытаться выполнять отбор, если это условие стрельнет. Поменяем порядок следования полей в индексе: Код drop index test_table$idx; create index test_table$idx on test_table(field1,field3,field4); план: Код -------------------------------------------------------------------------------- Plan hash value: 1859777282 -------------------------------------------------------------------------------- | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%C -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                |     1 |   167 |     1 |*  1 |  FILTER                      |                |       |       | |*  2 |   TABLE ACCESS BY INDEX ROWID| TEST_TABLE     |     1 |   167 |     1 |*  3 |    INDEX RANGE SCAN          | TEST_TABLE$IDX |     1 |       |     2 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_NUMBER(:VAL3)<=TO_NUMBER(:VAL4))    2 - filter("FIELD5" LIKE '%somestuff%' AND "FIELD2"<>TO_NUMBER(:VAL2))    3 - access("FIELD1"=TO_NUMBER(:VAL1) AND "FIELD3">=TO_NUMBER(:VAL3) AND               "FIELD3"<=TO_NUMBER(:VAL4))        filter("FIELD4"=TO_NUMBER(:VAL5) OR "FIELD4"=TO_NUMBER(:VAL6)) Здесь мы видим, что для предиката по feild4 уже не сможет быть использован индексный доступ, используется фильтрация.

Автор: Zloxa 1.6.2012, 16:54

Прежде чем принимать решение о построении такого большого составного индекса, следует определиться а поможет ли он вообще. Можно расчитать фактор селективности Код select    sum (case when field1 = :val1              and field3 between :val1 and  :val2               and field 4 in (:val1,:val2)            then 1          else 0        end        )   / count(*) from my_table   Чем ближе полученное значение будет к еденице, тем меньше смысла в построении такого индекса. Есть мнение, что полное сканирование, в общем случае, будет эффективнее индексного доступа, если фактор селективности превышает 0.2

Автор: FINANSIST 4.6.2012, 07:45

Цитата(Zloxa @  1.6.2012,  16:54 ) Чем ближе полученное значение будет к еденице, тем меньше смысла в построении такого индекса.  наверно все-таки к нулю? Цитата(Zloxa @  1.6.2012,  16:54 ) Есть мнение, что полное сканирование, в общем случае, будет эффективнее индексного доступа, если фактор селективности превышает 0.2  то есть использование этого коэф.даст возможность принять решение о том - стоит ли заливать составной индекс? И еще - я правильно понял что составной индекс по field1-field4 даст некоторый выигрыш при формировании подсета?

Автор: Akina 4.6.2012, 07:53

Цитата(FINANSIST @  4.6.2012,  08:45 ) наверно все-таки к нулю? Именно к единице. Цитата(FINANSIST @  4.6.2012,  08:45 ) то есть использование этого коэф.даст возможность принять решение о том - стоит ли заливать составной индекс? Очень приблизительно. Практические измерения надёжнее. Цитата(FINANSIST @  4.6.2012,  08:45 ) я правильно понял что составной индекс по field1-field4 даст некоторый выигрыш при формировании подсета?  Может, но не обязан.

Автор: Zloxa 4.6.2012, 08:44

Цитата(FINANSIST @  4.6.2012,  08:45 ) составной индекс по field1-field4 Цитата(Zloxa @  1.6.2012,  15:41 ) (field1,field4,field3[,....]). Именно в этой последовательности. Мне казалось я это очень наглядно показал :facepalm Добавлено через 7 минут и 39 секунд Цитата(Akina @  4.6.2012,  08:53 ) Именно к единице. Да Цитата(Akina @  4.6.2012,  08:53 ) Очень приблизительно. Практические измерения надёжнее. Да Цитата(Akina @  4.6.2012,  08:53 ) Может, но не обязан.  Да

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)