Неужели в AX 2009 можно отключить для Оракла функциональные индексы?

[gl00mie]

Читал тут на досуге руководство по написанию скриптов обновления данных и в разделе «Oracle only: Applying NLS_LOWER on String Columns in the WHERE Clause» наткнулся на прелюбопытнейшее замечание:

Цитата:

Note that if customers have de-selected the option in the Server Configuration utility to not use SUBSTR and NLS_LOWER, they will not have functional indexes; they will have regular indexes and thus the SUBSTR and NLS_LOWER is not required.

Что за чудо-настройка, думаю? В конфигурационной утилите ничего такого не видно. Однако, в конфигурации, сохраненной в реестре или в файле, есть в т.ч. параметр hint, аналогичный по смыслу одноименному параметру командной (за исключением того, что значение 0 не отключает все хинты, а приводит к использованию настройки хинтов по умолчанию). Так вот, если в реестре или в текстовом конфиге задать некорректное значение этого параметра, то конфигурационная утилита ругается примерно так:

Отсюда возникло 3 вопроса:

• Действительно ли можно отключить хинтами использование функциональных индексов для Оракла в AX 2009?
• Если да, то каким именно хинтом это можно сделать?
• Где найти описание хинтов для AX 2009?

Простые эксперименты с побитовым включением хинтов (по одному за раз) ничего не дали...

[fed]

Не уверен, но мне кажется, что результатом подобных экспериментов станет тот режим работы с ораклом, который был до версии 3.00. То есть - любое поле, включенное в любой индекс, будет автоматически апперкейсится при записи в БД силами самой аксапты. Поскольку в версии 3.0, помниться, слегка подковыряв конфигурацию, можно было добиться подобного эффекта...
А ты, я полагаю, хочешь включить Case Insensitive collation в самом оракле и работать с ним так, как аксапта с SQL Server работает.

[egorych]

Цитата:

Сообщение от fed

А ты, я полагаю, хочешь включить Case Insensitive collation в самом оракле и работать с ним так, как аксапта с SQL Server работает.

Голубая мечта!

[gl00mie]

Цитата:

Сообщение от fed

А ты, я полагаю, хочешь включить Case Insensitive collation в самом оракле и работать с ним так, как аксапта с SQL Server работает.

Конечно, раз Оракл давно поддерживает case insensitive collation, более того, в упомянутом выше документе написано: "если клиенты отключили использование SUBSTR(NLS_LOWER()), то у них будут обычные, а не функциональные индексы, так что использовать SUBSTR(NLS_LOWER()) будет не нужно". Для меня это означает, что сравнение будет работать без учета регистра, а не то, что надо будет приводить все к верхнему регистру и пользоваться тем же самым регистрозависимым сравнением. В последнем случае должно было бы быть написано "...и тогда нужно будет использовать NLS_UPPER()", поскольку в прямом SQL-запросе можно задать литералы в т.ч. в нижнем и смешанном регистре.

[fed]

Меня вот давно занимает вопрос - нельзя ли эту проблему решить, просто подковыряв нужные переменные в таблице SQLSystemVariables. Там вроде бы были переменные и про функциональные ключи и про lowercase. Только вот Оракла чтобы попробовать нету...

[Logger]

А еще что-то было в переменных ktd файлов.
Что важнее - фиг знает.

[gl00mie]

Цитата:

Сообщение от fed

А ты, я полагаю, хочешь включить Case Insensitive collation в самом оракле и работать с ним так, как аксапта с SQL Server работает.

К слову, как минимум в трешке такая возможность была (как выясняется ): не знаю, интересно ли это кому на фоне разговоров про AX 2012, но в ядре трешки обнаружились как-то хинты, которые позволяют отключить использование SUBSTR() и NLS_LOWER() при работе с оракловой базой. После этого остается только перебить NLS_SORT в ktd-шниках на binary_ci, как было описано здесь, - и готово Правда, в промышленной эксплуатации эта идея мной не опробовалась. Так вот, по поводу хинтов: для эксперимента я тогда использовал нижеприведенный запрос в Х++ и игрался на лету хинтами для текущей сессии с помощью SqlSystem::databasehints().

select firstonly forceliterals RecId
    from    salesTable
    where   salesTable.SalesId == 'ЗаКаЗ012345' 
    join    RecId 
    from    salesLine 
    where   salesLine.SalesId == salesTable.SalesId;

Если использовать в коде forceplaceholders, то ядро не будет создавать новые запросы при изменении хинтов, а будет гнать один и тот же запрос, созданный в первый раз – это на случай, если повторять эксперимент. Вот хинты и запросы, которые при них получаются:

0x000000A7 – хинты для Оракла по умолчанию

PHP код:

SELECT /*+ USE_NL(A) USE_NL(B) */A.RECID,A.RECVERSION,B.RECID,B.RECVERSION
FROM SALESTABLE A,SALESLINE B
WHERE ((SUBSTR(NLS_LOWER(A.DATAAREAID),1,3)=NLS_LOWER('dat'))
AND (SUBSTR(NLS_LOWER(A.SALESID),1,20)=NLS_LOWER('         ЗаКаЗ012345')))
AND ((SUBSTR(NLS_LOWER(B.DATAAREAID),1,3)=NLS_LOWER('dat'))
AND (SUBSTR(NLS_LOWER(B.SALESID),1,20)=SUBSTR(NLS_LOWER(A.SALESID),1,20))) 


0x00000000 – отключено всё

PHP код:

SELECT A.RECID,A.RECVERSION,B.RECID,B.RECVERSION
FROM SALESTABLE A,SALESLINE B
WHERE ((SUBSTR(NLS_LOWER(A.DATAAREAID),1,3)=NLS_LOWER('dat'))
AND (SUBSTR(NLS_LOWER(A.SALESID),1,20)=NLS_LOWER('         ЗаКаЗ012345')))
AND ((SUBSTR(NLS_LOWER(B.DATAAREAID),1,3)=NLS_LOWER('dat'))
AND (SUBSTR(NLS_LOWER(B.SALESID),1,20)=SUBSTR(NLS_LOWER(A.SALESID),1,20))) 


0x00004000 – 14-й бит

PHP код:

SELECT /*+ USE_NL(A) USE_NL(B) */A.RECID,A.RECVERSION,B.RECID,B.RECVERSION
FROM SALESTABLE A,SALESLINE B
WHERE ((A.DATAAREAID=NLS_LOWER('dat'))
AND (A.SALESID=NLS_LOWER('         ЗаКаЗ012345')))
AND ((B.DATAAREAID=NLS_LOWER('dat'))
AND (B.SALESID=A.SALESID)) 


0x00008000 – 15-й бит

PHP код:

SELECT /*+ USE_NL(A) USE_NL(B) */A.RECID,A.RECVERSION,B.RECID,B.RECVERSION
FROM SALESTABLE A,SALESLINE B
WHERE ((A.DATAAREAID='dat')
AND (A.SALESID='         ЗаКаЗ012345'))
AND ((B.DATAAREAID='dat')
AND (B.SALESID=A.SALESID)) 


0x0000C000 – 14-й + 15-й

PHP код:

SELECT /*+ USE_NL(A) USE_NL(B) */A.RECID,A.RECVERSION,B.RECID,B.RECVERSION
FROM SALESTABLE A,SALESLINE B
WHERE ((A.DATAAREAID='dat')
AND (A.SALESID='         ЗаКаЗ012345'))
AND ((B.DATAAREAID='dat')
AND (B.SALESID=A.SALESID)) 


Если с хинтом 32768 (0x8000) пересоздать индексы, то они тоже создаются "как положено" без всяких там функций от строковых полей.

[gl00mie]

Цитата:

Сообщение от gl00mie

• Действительно ли можно отключить хинтами использование функциональных индексов для Оракла в AX 2009?
• Если да, то каким именно хинтом это можно сделать?
• Где найти описание хинтов для AX 2009?

Я тут на досуге долго э... смотрел в хрустальный шар и увидел там ответы на эти вопросы. Ответы сводятся к тому, что штатно никакими хинтами отключить использование функциональных индексов в AX 2009 уже, в отличие от 3.0, нельзя. Ниже - краткая выжимка того, что удалось разглядеть в хрустальном шаре.
За использование функциональных индексов в ядре отвечают, насколько я смог разглядеть, три функции, чьи настоящие имена удалось-таки недавно узнать: MONOCASE_FIELD(), MONOCASE_LITERAL() и MONOCASE_PLACEHOLDER(). Вот, к примеру, как выглядит псевдокод функции MONOCASE_FIELD() в ax32serv.exe версии 3.0 KR3

char* MONOCASE_FIELD(char *pszBuf, CSqlFieldInfo *pSqlFieldInfo, bool forceMonoCase)
{
  if (gDatabaseCLI == DatabaseCLI::OCI)
  {
    if (  !forceMonoCase
       || !pSqlFieldInfo
       || ((fnGetSessionInfo()->getSqlSystem()->DatabaseHints >> 14) & 1)
       || ((fnGetSessionInfo()->getSqlSystem()->DatabaseHints >> 15) & 1) 
       )
    {
      strcpy(pszBuf, "%s");
    }
    else
    {
      sprintf(pszBuf, "SUBSTR(%s,1,%i)", "NLS_LOWER(%s)", pSqlFieldInfo->cbStringFieldBufSize - 1);
    }
  }
  else
  {
    if (!forceMonoCase || ((fnGetSessionInfo()->getSqlSystem()->DatabaseHints >> 15) & 1))
    {
      strcpy(pszBuf, "%s");
    }
    else
    {
      strcpy(pszBuf, "{fn LCASE(%s)}");
    }
  }
  return pszBuf;
}

Тут видно, что параметр forceMonoCase (название придумано мной) может быть "перекрыт" хинтом 0x8000 - при его использовании к строковому полю не будут применены никакие дополнительные функции. А теперь как эта же функция выглядит в ax32serv.exe версии 2009 SP1 RU7:

char* MONOCASE_FIELD(wchar_t *pwszBuf, unsigned int cnBufSize, struSqlFieldInfo *pSqlFieldInfo, bool forceMonoCase)
{
  int len;
  if (sql_dbcli != DatabaseCLI::OCI)
  {
    if (forceMonoCase)
    {
      return StringCchCopyW(pwszBuf, cnBufSize, L"{fn LCASE(%s)}");
    }
    return StringCchCopyW(pwszBuf, cnBufSize, L"%s");
  }
  if (!forceMonoCase || !pSqlFieldInfo)
  {
    return StringCchCopyW(pwszBuf, cnBufSize, L"%s");
  }
  len = pSqlFieldInfo->BaseType == Types::String ? (*&pSqlFieldInfo->cbStringFieldBufSize >> 1) - 1 : 0;
  return StringCchPrintfW(pwszBuf, cnBufSize, L"SUBSTR(%s,1,%i)", L"NLS_LOWER(%s)", len);
}

Все упоминания о хинтах пропали, и определяющим стал параметр forceMonoCase, а этот параметр во всех местах, где вызывается MONOCASE_FIELD(), определяется как

theDatabase()->DatabaseId == DatabaseId::Oracle

исключение составляет лишь SqlSystem.monocaseFmt() - здесь параметр forceMonoCase по умолчанию равен true, но может принять значение false, если при вызове этого метода указать параметр _considerSystemVariables == true и СУБД окажется НЕ Oracle. Аналогичная картина наблюдается для остальных двух функций. Вот псевдокод функции MONOCASE_LITERAL() в ядре 3.0 KR3

char* MONOCASE_LITERAL(char *pszBuf, bool forceMonoCase)
{
  if (gDatabaseCLI == DatabaseCLI::OCI)
  {
    if (forceMonoCase && !((fnGetSessionInfo()->getSqlSystem()->DatabaseHints >> 15) & 1))
    {
      return strcpy(pszBuf, "NLS_LOWER(%s)");
    }
  }
  else
  {
    if (forceMonoCase && !((fnGetSessionInfo()->getSqlSystem()->DatabaseHints >> 15) & 1))
    {
      return strcpy(pszBuf, "{fn LCASE(%s)}");
    }
  }
  return strcpy(pszBuf, "%s");
}

А вот она же в ядре 2009 SP1 RU7:

wchar_t* MONOCASE_LITERAL(wchar_t *pwszBuf, unsigned int cnBufSize, bool forceMonoCase)
{
  if (sql_dbcli != DatabaseCLI::OCI)
  {
    if (forceMonoCase)
    {
      return StringCchCopyW(pwszBuf, cnBufSize, L"{fn LCASE(%s)}");
    }
  }
  else
  {
    if (forceMonoCase)
    {
      return StringCchCopyW(pwszBuf, cnBufSize, L"NLS_LOWER(%s)");
    }  
  }	
  return StringCchCopyW(pwszBuf, cnBufSize, L"%s");
}

Вот MONOCASE_PLACEHOLDER() в ядре 3.0 KR3

void MONOCASE_PLACEHOLDER(char *pszBuf, void *a2, bool forceMonoCase, char *pszInOutBuf)
{
  // ...
  if (*pszInOutBuf)
  {
    v5 = *a2 + 1;
  }
  else
  {
    v5 = *(a2 + 4) + 1;
  }
  if (gDatabaseCLI == DatabaseCLI::OCI)
  {
    strcpy(pszInOutBuf), *pszInOutBuf ? "in" : "out");
    if (!forceMonoCase || ((fnGetSessionInfo()->getSqlSystem()->DatabaseHints >> 15) & 1))
      sprintf(pszBuf, ":%s%i", &pszInOutBuf, v5);
    else
      sprintf(pszBuf, "NLS_LOWER(:%s%i)", &pszInOutBuf, v5);
  }
  else
  {
    if (!forceMonoCase || ((fnGetSessionInfo()->getSqlSystem()->DatabaseHints >> 15) & 1))
      sprintf(pszBuf, "?");
    else
      sprintf(pszBuf, "{fn LCASE(?)}");
  }
}

И в ядре 2009 SP1 RU7:

void MONOCASE_PLACEHOLDER(wchar_t *a1, unsigned int *a2, void *pSqlField, void *pSqlMonocaseParamCnt, bool forceMonoCase, bool bIsIn)
{
  int v6;
  wchar_t v12;
  if (bIsIn)
  {
    ++*pSqlMonocaseParamCnt;
    v6 = *pSqlMonocaseParamCnt;
  }
  else
  {
    ++*(pSqlMonocaseParamCnt + 1);
    v6 = *(pSqlMonocaseParamCnt + 1);
  }
  if (sql_dbcli == DatabaseCLI::OCI)
  {
    StringCchCopyW(&v12, 4, bIsIn ? L"in" : L"out");
    StringCchPrintfExW(a1, *a2, &a1, a2, 0, forceMonoCase ? L"NLS_LOWER(:%s%i)" : L":%s%i", &v12, v6);
  }
  else
  {
    StringCchPrintfExW(a1, *a2, &a1, a2, 0, forceMonoCase ? L"{fn LCASE(?)}" : L"?");
  }
}

Таким образом, если в 3.0 (и, возможно, в 4.0 тоже!) существовал штатный, пусть и не документированный, способ отключить использование функциональных индексов при работе с СУБД Oracle, то в 2009-й из ядра его убрали, а все умоминания про такую возможность в конфигурационной утилите и в документаций - просто не подчищенные хвосты.

[Logger]

Шикарно.
Как считаешь возможно это безболезненно пропатчить ?

[gl00mie]

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

[egorych]

Собственно - в какой-то ветке обещался проверить возможность работы Ах3 без FBI .
Сейчас стоит железяка, которую можно временно потерзать. Чем собственно и зянялся ;-)
Состав - IBM x3650 M3 132Gb RAM 8*Xeon 2.5 + Win2003Server x64 Eng + Oracle 11.2.0.2 x64 + Axapta 3.0 KR2.
Согласно исследованиям gl00mie было выставлено:

Цитата:

-hint=49152

и в ktd

Цитата:

NLS_SORT = 'RUSSIAN_CI'

в этом случае сортировка идет без учета регистра, но поиск все равно осуществляется регистрозависимый. Добавкой в ktd файл -

Цитата:

ALTER SESSION SET NLS_COMP = 'LINGUISTIC'

эта проблема решилась - т.е. поиск работает как положено ;-)
НО, дьявол как всегда в деталях!
Есс-но после установки новых параметров была проведена переиндексация - действительно, индексы создались нормальные - без NLS_LOWER и всяких там SUBSTRINGов !
Казалось-бы вот оно, щасте! Ан нифига - планы генерируются настолько странные, шо ни пером описать! Вроде и индекс используется, но почти везде фул скан его с каким-то странным выражением фильтра, включающим в себя преобразования NLS,,, ! Соответственно скорость ну совсем никакая.
В общем можно считать, что эта фича не была поддержана в ядре косяпты как положено и осталась как рудимент экспериментов разработчиков!
Мог, конечно, что-то упустить в установках - еще поизучаю маленько!

[Logger]

Цитата:

Сообщение от egorych

Вроде и индекс используется, но почти везде фул скан его с каким-то странным выражением фильтра, включающим в себя преобразования NLS,,, !

Странно, откуда им вылезти если у вас все отключено.

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

Цитата:

ALTER SESSION SET NLS_COMP = 'LINGUISTIC'
NLS_SORT = 'RUSSIAN_CI'

проверьте, может у вас оракловый джоб по сбору работает со старыми настройками и собирает статистику для
NLS_SORT = 'RUSSIAN_BI' или еще как.

Она вам бесполезна, поэтому наверно оптимизер и хватает фуллсканы.

[egorych]

Цитата:

Сообщение от Logger

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

Вообще-то начиная с 10 версии CREATE INDEX обновляет и статистику. Но в принципе возможно, что и нужно обновить - попробую

[Logger]

Цитата:

Сообщение от egorych

Вообще-то начиная с 10 версии CREATE INDEX обновляет и статистику. Но в принципе возможно, что и нужно обновить - попробую

Я не спец, но наш админ специально руками её обновляет.
Аксапта кстати, какой-то неоптимальный способ для сбора статистики для оракла использует. Погуглите тему.

[egorych]

Цитата:

Сообщение от Logger

Я не спец, но наш админ специально руками её обновляет.
Аксапта кстати, какой-то неоптимальный способ для сбора статистики для оракла использует. Погуглите тему.

Нормально она обновляет! По крайней мере я пользуюсь - проблем нет.
Я попробую позапускать, конечно, с разными параметрами.

[fed]

Цитата:

Сообщение от Logger

Я не спец, но наш админ специально руками её обновляет.
Аксапта кстати, какой-то неоптимальный способ для сбора статистики для оракла использует. Погуглите тему.

Там встроенный сборщик статистики (по крайней мере в версии 2.50-3.0) использовал какой-то пакет оракловский (для регулярного исполнения задач - типа cron'a), который на версии 8.0.5 работал, а на всех виденных мною версях 8.1.x и кажется даже 9.0.x наглухо разваливался. При этом он постоянно развалившееся задание рестартовал и тут же снова разваливался. В итоге - сервер был сильно нагружен.
Но судя по тому что egorych пишет, в каких-то более поздних версиях оракла это дело починили...

[gl00mie]

Цитата:

Сообщение от egorych

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

От ядра Аксапты требуются только две вещи:

1. отсылать DML/DDL-запросы без SUBST/NLS_LOWER;
2. нормально подкручивать параметры сессии NLS_COMP/NLS_SORT.

Все остальное - уже на совести DBA. Я как бы... ни разу не DBA, но то, что у вас в планах запросов фигурируют странные выражения с преобразованием NLS, говорит скорее всего о том, что NLS-параметры сессии не совпадают с соотв. параметрами схемы и/или базы (в оракловом смысле) - по аналогии с отличием collation'а базы и экземпляра сервера в случае Ms SQL Server (в этом случае как минимум будут тормоза на работе с tempdb). Еще одна засада в случае с ораклом - это NLS-настройки при сборе статистики: если они не совпадают с настройками сессии, в которой выполняются DML-запросы, очевидно, оптимизатор не сможет использовать эту статистику для текстовых полей.
В общем, для чистоты эксперимента я бы переустановил оракл с теми NLS-настройками, которые вы пытаетесь использовать для работы с аксаптовской схемой.

[Logger]

Еще вроде в оракловом клиенте можно делать настройки по умолчанию.
Их тоже лучше на всякий пожарный подкрутить.

[egorych]

В общем не знаю - то-ли я что-то упустил, то-ли еще что, но суть такая - параметры везде поменял, и в базе по умолчанию, и переменные среды выставил и даже триггер на logon нарисовал, где параметры сессии менял, и в ktd тоже - один черт! Все тормозит, форма заказов открывается минут 5, планы запросов совершенно сумасшедшие (пример во вложении)!
Вернув все вздад и перестроив индексы и статистику получил замечательную, шуструю работу!
В общем я пас, господа!
Делаю вывод - пока вариант с человеческими индексами и запросами не работоспособен, ну или у меня не хватило мозгов заставить его работать!