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

Форум программистов > Oracle > Join индексирование

Автор: setnull 16.6.2009, 11:49

Все здравствуйте! Есть внешний ключ. Допустимо пустое значение. Пустое значение логически соответствует принадлежности всем записям родительской таблицы. Условие присоединения Код ... PAR_TABLE INNER JOIN CHILD_TABLE ON          CHILD_TABLE.PAR IS NULL OR CHILD_TABLE.PAR = PAR_TABLE."ID" делает выборку по таблице CHILD_TABLE FULL SCAN и она получается не индексированной. Что можно сделать? Спасибо!!!!

Автор: Zloxa 16.6.2009, 13:25

Цитата(setnull @  16.6.2009,  11:49 ) Пустое значение логически соответствует принадлежности всем записям родительской таблицы. кхе...  в топку бы такое  Цитата(setnull @  16.6.2009,  11:49 ) Что можно сделать? под стать дизайну и решение. Для того чтобы значение индекса не принимало не определенные значение - используем составной индекс, в который добавляем любое нот нулл поле, в результате получаем возможность индексного доступа по null. Код Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0  create table par(id primary key) as select level from dual connect by level <=1e5;   Table created create table child(id primary key,par_id) as select level,decode(mod(level,1e5),0,cast(null as number),mod(level,1e5)+1) from dual connect by level <= 1e6;   Table created create index child$par_id on child(par_id,id);   Index created explain plan for  select *    from par    inner join child on child.par_id is null                        or child.par_id = par.id  where par.id = :val;   Explained select * from table(dbms_xplan.display);   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2063622911 -------------------------------------------------------------------------------- | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |              |   430 | 16770 |    78   (2)| 00:00: |   1 |  CONCATENATION      |              |       |       |            | |   2 |   NESTED LOOPS      |              |    13 |   507 |     3   (0)| 00:00: |*  3 |    INDEX UNIQUE SCAN| SYS_C0034093 |     1 |    13 |     1   (0)| 00:00: |*  4 |    INDEX RANGE SCAN | CHILD$PAR_ID |    13 |   338 |     2   (0)| 00:00: |   5 |   NESTED LOOPS      |              |   417 | 16263 |    75   (2)| 00:00: |*  6 |    INDEX UNIQUE SCAN| SYS_C0034093 |     1 |    13 |     1   (0)| 00:00: |*  7 |    INDEX RANGE SCAN | CHILD$PAR_ID |   417 | 10842 |    74   (2)| 00:00: -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("PAR"."ID"=TO_NUMBER(:VAL))    4 - access("CHILD"."PAR_ID"="PAR"."ID")   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------    6 - access("PAR"."ID"=TO_NUMBER(:VAL))    7 - access("CHILD"."PAR_ID" IS NULL)        filter(LNNVL("CHILD"."PAR_ID"="PAR"."ID")) Note -----    - dynamic sampling used for this statement   27 rows selected   SQL>

Автор: setnull 16.6.2009, 14:44

Пример работает Делаю все точно так же!!!! В моей таблице - FULLL!!!! Может еще нужно планы как-то инвалиднуть?

Автор: Zloxa 16.6.2009, 15:26

может для твоего случая фуллскан эффективнее? ) Для примера я специально выбирал именно такое сочетание данных чтобы на моем окружении индексный доступ  был эффективнее. Добавлено через 45 секунд Статистику после индексирования собирал?

Автор: setnull 16.6.2009, 15:34

Собирать - это строить или смотреть? как это сделать?

Автор: Zloxa 16.6.2009, 15:42

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#sthref6748 http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm

Автор: setnull 18.6.2009, 10:03

Цитата(Zloxa @ 16.6.2009,  15:26) может для твоего случая фуллскан эффективнее? ) Для примера я специально выбирал именно такое сочетание данных чтобы на моем окружении индексный доступ  был эффективнее. Статистику еще не собирал... Но у меня возник вопрос. Это сарказм, или такое действительно может быть, что полное сканирование эффективнее? Делаю выборку по значению -  индексирует По is null - индексирует по значению или is null - full scan, при этом выбираются 18,33% записей

Автор: DimW 18.6.2009, 10:44

Цитата(setnull @  18.6.2009,  10:03 ) Это сарказм, или такое действительно может быть, что полное сканирование эффективнее? Цитата(setnull @  18.6.2009,  10:03 ) по значению или is null - full scan, при этом выбираются 18,33% записей  если 18% от 1000 записей в таблице, то скорей всего так и есть. если их все же на порядок больше, то можно предположить что оптимайзер об этом незнает, в этом случае стоит собрать статистику, а потом разбираться.

Автор: Zloxa 18.6.2009, 11:12

Цитата(setnull @  18.6.2009,  10:03 ) Это сарказм, или такое действительно может быть, что полное сканирование эффективнее? Это не сарказм. При принятии решения о том, что выгоднее - фуллскан+хэш джойн или нестед луп +  доступ по индексу учитывается очень большое количества параметров. Как то: количество блоков в таблице, стоимость операции многблочного чтения, стоимость одноблочного чтения, кластеризация индекса, отношение стоимостей IO и CPU... Мой опыт показывает, что я ошибаюсь в своем представлении об эффективности плана намного чаще чем оптимизатор при наличии достоверной статистики. Самостоятельно влиять на план выполнения приходится только тогда, когда я заведомо знаю больше оптимизатора. При этом, до сих пор в продуктивных решениях мне удавалось обходиться лишь двумя хинтами - first_rows & cardinality. При том, оба эти хинта - хинты оптимизатора. Добавлено @ 11:27 Цитата(setnull @  18.6.2009,  10:03 ) по значению или is null - full scan, при этом выбираются 18,33% записей  Следует учитывать что оптимизатор смотрит на значение при расчете селективности, только в сулчае, если это значение присутсвтует в гистограмме, в противном случае он принимает за селективность количество уникальных/ количество всего. Как в этом случае будет оцениваться Null, мне не известно. Вообще,я уже говорил что исползование null как значения - дурная практика, со всеми вытекающими. Null таки это "отсутствие значения"

Автор: v2v 19.6.2009, 13:59

У Тома Кайта видел пример сканирования по индеку при условии is (not) null, при том что не осуществляется сбор статистики. Он предлагает индексировать не колонку, а функцию:  nvl(НАЗВАНИЕ_КОЛОНКИ,АЛЬТЕРНАТИВНОЕ_ЗНАЧЕНИЕ) . Я попробовал воспроизвести такой пример, но без дополнительной хинтовки, он всё равно делает селект без индекса: Код create table t1(f1 number, f2 number); alter table t1 add constraint t1#pk primary key(f1); create index t1#i1 on t1 (nvl(f2,-1)); select /*+index(t1 t1#i1)*/* from t1 where nvl(f2,-1) = nvl(:a,-1);

Автор: Zloxa 19.6.2009, 14:35

Цитата(v2v @  19.6.2009,  13:59 ) Я попробовал воспроизвести такой пример, но без дополнительной хинтовки, он всё равно делает селект без индекса: Для твоего примера у меня индекс используется и без хинта и без статистики (10.2.0.3, 9.2.0.8). Однако отбор - это отбор. А ты вот попробуй join по FBI сделать  У меня лично не получилось. Как грится хинтуй - не хинтуй, а в плане один .... )

Автор: Zloxa 19.6.2009, 15:11

Цитата(Zloxa @  19.6.2009,  14:35 ) У меня лично не получилось Гоню... получилось ;) Код Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0    create table par(id primary key check (id != -1)) as select level from dual connect by level <=1e5;   Table created create table child(id primary key,par_id) as select level,decode(mod(level,1e5),0,cast(null as number),mod(level,1e5)+1) from dual connect by level <= 1e6;   Table created create index child$par_id$fbi on child (nvl(par_id,-1));   Index created analyze table par compute statistics;   Table analyzed analyze table child compute statistics for all columns;   Table analyzed explain plan for  select    *    from par    inner join child on nvl(child.par_id, -1) in(par.id,-1)  where par.id = :val;   Explained select * from table(dbms_xplan.display);   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 158516727 -------------------------------------------------------------------------------- | Id  | Operation                         | Name             | Rows  | Bytes | C -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                  |                  |    32 |   384 | |   1 |  NESTED LOOPS                     |                  |    32 |   384 | |*  2 |   INDEX UNIQUE SCAN               | SYS_C0034125     |     1 |     4 | |   3 |   TABLE ACCESS BY INDEX ROWID     | CHILD            |    32 |   256 | |   4 |    BITMAP CONVERSION TO ROWIDS    |                  |       |       | |   5 |     BITMAP OR                     |                  |       |       | |   6 |      BITMAP CONVERSION FROM ROWIDS|                  |       |       | |*  7 |       INDEX RANGE SCAN            | CHILD$PAR_ID$FBI |       |       | |   8 |      BITMAP CONVERSION FROM ROWIDS|                  |       |       | |*  9 |       INDEX RANGE SCAN            | CHILD$PAR_ID$FBI |       |       | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------    2 - access("PAR"."ID"=TO_NUMBER(:VAL))    7 - access("PAR"."ID"=NVL("PAR_ID",(-1)))    9 - access(NVL("PAR_ID",(-1))=(-1)) Note -----    - dynamic sampling used for this statement   27 rows selected

Автор: v2v 19.6.2009, 18:05

Цитата(Zloxa @  19.6.2009,  14:35 ) Для твоего примера у меня индекс используется и без хинта и без статистики (10.2.0.3, 9.2.0.8). 10.2.0.4 - фул скан и ничего не могу поделать. Добавлено @ 18:07 Цитата(Zloxa @  19.6.2009,  15:11 ) Index created analyze table par compute statistics; но если со статитикой , как  у тебя по джойну то получается ...

Автор: setnull 23.6.2009, 13:00

Код inner join child on nvl(child.par_id, -1) in(par.id,-1) я такое, если не ошибаюсь, поробовал раньше. При это в плане это разивается на два условия nvl(child.par_id, -1) = par.id or nvl(child.par_id, -1) =-1 но при этом все равно остается фулскан... Цитата analyze table par compute statistics; analyze table child compute statistics for all columns; Это директивы или консольный вывод? И вообще, где можно достать полезную информацию о механизмах планирования и оптимизации. Все, с чем я сталкивался до этого -  очень общие понятия (Оракл делает планы, и потом ими пользуется). Но при этом я не имею целостного представления о том, что, где и когда делает Оракл? Что, где и когда может анализировать разработчик, а также чем, где и когда он может пользоваться, чтоб работа всего этого механизма происходила эффективно ? Спасибо!!!

Автор: Zloxa 23.6.2009, 13:38

Цитата(setnull @  23.6.2009,  13:00 ) И вообще, где можно достать полезную информацию о механизмах планирования и оптимизации. http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm Цитата(setnull @  23.6.2009,  13:00 ) Это директивы или консольный вывод? http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4005.htm#SQLRF01105 Цитата Note: Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Цитата(setnull @  23.6.2009,  13:00 ) поробовал раньше. 1)Индекс по функции при том строил? 2)Статистика по индексу,(таблице) после построения индекса собиралась? 3)http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41496 вообще когданибудь собиралась? Цитата(setnull @  23.6.2009,  13:00 ) но при этом все равно остается фулскан... 4)Ты уверен в том что фуллскан МЕНЕЕ эффективен? Случается, порой, я ПРИНУЖДАЮ ораклю использовать full scan, потому как дождаться результата при использовании индексного доступа - терпения не хватает. Ты упоминал ранее что селективность твоего запроса 18%. Это достаточно высокая селективность. На такой селективности я вполне могу поверить что фуллскан будет эффективнее. Все зависит от отношения стоимости операций ввода вывода к стоимости операций CPU. Может так статься что сотни многоблочных чтений с полной загрузкой ЦПУ будут выполняться много быстрее тысяч одноблочных чтений при низкой загурзке ЦПУ. если ответы на вопросы 1,2,3,4 утвердительные, используй хинт http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50405, как это делает v2v в своем примере.

Автор: setnull 23.6.2009, 15:15

Спаисбо за сылки! 1) - да. Разве что, я использовал выражение (как в индексе, так и в запросе) DECODE(FILED, null, 0, FILED). Это же не принципиально? 2) и 3) Скорей всего нет, если это не делается автоматически 4) Ничего не могу сказать вообще... Т.е. буду читать теорию... Только еще хочу уточнить, приведенные ссылки могут дать понимание на уровне Цитата Все зависит от отношения стоимости операций ввода вывода к стоимости операций CPU. Может так статься что сотни многоблочных чтений с полной загрузкой ЦПУ будут выполняться много быстрее тысяч одноблочных чтений при низкой загурзке ЦПУ. Или до этого еще очень далеко?

Автор: Zloxa 23.6.2009, 15:31

1) Нет, не принципиально. 4) Дык а попробовать? )) Цитата(setnull @  23.6.2009,  15:15 ) дать понимание на уровне Указанный уровень весьма абстрактен. ) Многие хвалят книгу Льюиса "Основы стоимостной оптимизации". Я читал лишь пару глав... (кста, надо найти и перечесть ). Кашу в мозгах сортирует хорошо. Но особого прозрения не ожидай, там лишь описано как, почему и на основе чего оптимизатор выбирает план. Однако что именно дает понимание этого, ясно весьма не сразу. ;) Понимание принципов работы оптимизатора приводит лишь к тому что начинаешь чаще соглашаться с результатами его работы

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