CftClub.ru
Клуб специалистов ЦФТ-Банк

Структуры как временные таблицы

 
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle
Предыдущая тема :: Следующая тема  
Автор Сообщение
DALLUS
Участник


Вступление в Клуб: 03.06.2019
СообщениеВт Июн 04, 2019 07:15   Структуры как временные таблицы Ответить с цитатой
Полезность: Нет оценки
При проведении задачи по массовым вычислениям (в данном случае неважно к каким) столкнулся с некоторыми проблемами:
1) Конструкция WITH AS не поддерживается PLP/SQL
-- begin pl/sql -- end pl/sql использовать можно, но неудобно.
2) Если использовать структуру как временную таблицу, то её нельзя создавать с идентификацией по ROWID, т.е. добавляются левые поля SN,SU, ID, и создается индекс на ID.

ЗАЧЕМ?!? Question Exclamation Question
Зачем временной таблице (срок жизни которой одна транзакция) "левые" поля, с "левым" индексом?
Почему конструкция WITH AS не поддерживается?

Заранее спасибо.
Alkov
Профи


Вступление в Клуб: 23.09.2010
СообщениеВт Июн 04, 2019 10:17    Ответить с цитатой
Полезность: Нет оценки
На сколько я понял Админ1 вообще последние 2 года не развивается, только ошибки правят...
kai
Профи


Вступление в Клуб: 16.08.2012
СообщениеВт Июн 04, 2019 12:27    Ответить с цитатой
Полезность: 1
Alkov пишет:
На сколько я понял Админ1 вообще последние 2 года не развивается, только ошибки правят...

развитие транслятора PL+ происходит независимо от развития АРМов.
Например, ANSI JOIN синтаксис не так давно стал поддерживаться.
Цитата:
Если использовать структуру как временную таблицу, то её нельзя создавать с идентификацией по ROWID, т.е. добавляются левые поля SN,SU, ID, и создается индекс на ID.


При разработке 2MCA по результатам первичного анализа...
В 2MCA временные таблицы не поддерживаются (в связи с тем, что сессия для соединения с БД может меняться - используется по необходимости из пула).

SN, SU используются в поддержке прикладной блокировки. Да, скорее всего, их присутствие во временных таблицах не оправдано.

Сделать запрос с синтаксисом, который не поддерживает транслятор, можно по крайней мере ещё 2-мя способами:
1. использовать в SQL-представлении (в Администраторе словаря данных: меню Словарь -> Системные справочники -> Представления).
А обращаться потом в PL+ коде через %rowtype.
Ничем не отличается от создания представления непосредственно в Oracle, но может быть выгружено в хранилище.
2. использовать динамику. Например,

Код:

function open_cur
         ( P_OWNER      varchar2(30)
         , P_PREFIX      varchar2(3)
         )
   return tp_cur
is
   vSQL         varchar2(32767);
   cur            tp_cur;
   vLF      const   varchar2(1)   := LF$;
   isHit         boolean;
begin
   vSQL   := 'WITH'
      ||vLF||'W_CONSTRAINTS as'
      ||vLF||' ( select /*+ RESULT_CACHE */  a.CONSTRAINT_NAME, a.TABLE_NAME, a.CONSTRAINT_TYPE, a.R_CONSTRAINT_NAME'
      ||vLF||'     from DBA_CONSTRAINTS a'
      ||vLF||'    where a.STATUS = ''ENABLED'''
      ||vLF||'      and a.OWNER = :OWNER'
      ||vLF||' )'
      ||vLF||'select k.TABLE_NAME, k.CONSTRAINT_NAME'
      ||vLF||'  from W_CONSTRAINTS k'
      ||vLF||' where k.CONSTRAINT_TYPE = ''R'''
      ||vLF||'    and x.C_OWNER = :OWNER'
      ||vLF||')';

   isHit   := [UTILS].OPEN_CURSOR               -- Аналог на pl/sql: open rc for vSQL using v1;
                     ( p_cursor   == cur
                     , p_select   == vSQL
               --      , p_raise   == [False]|True
                     , p_vars   == 3
                     , p_value1   == P_OWNER
                     , p_value2   == P_OWNER
                     , p_value3   == P_OWNER
               --      , p_value4
               --      , p_value5
                     );
   return cur;
end;


(!) текст запроса приведён не полностью

Код:

   -- можно так, но лучше объявлять тип в виде записи
   type tp_q is
         select k( k.[TABLE_NAME]            : TABLE_NAME
               , k.[CONSTRAINT_NAME]         : CONSTRAINT_NAME
               )
            in   dba_constraints%rowtype
         where   rownum < 2;

   type tp_cur is ref cursor return tp_q;


Код:

   cur   tp_cur;

   x   tp_q;

   type tp_tab   is table of tp_q;
   tab   tp_tab;
begin
   cur   := open_cur( pOWNER, pPrefix );

   if cur%isopen then
      loop
         cur.fetch_limit(100, tab);

         exit when tab.count = 0;

         for k in tab.first .. tab.last loop
            x   := tab(k);

         end loop;

         exit when cur.notfound;
      end loop;

      cur.close;
   end if;
end;


с курсором и fetch_limit - лучшее решение при обработке больших объёмов.

Из-за этих вариантов острота проблемы отсутствия поддержки, увы, не настолько большая, чтобы сделать поддержку. Хотя, imho, это можно было бы сделать на основе существующего способа объявления подзапросов

Код:

type Q1 is select ...;

type main is select ... in Q1;
DALLUS
Участник


Вступление в Клуб: 03.06.2019
СообщениеСр Июн 05, 2019 09:24    Ответить с цитатой
Полезность: Нет оценки
Добрый день!
Спасибо за оперативный и квалифицированный ответ.

1)Ошибка при попытке поставить галку "идентификация по ROWID":

Администратор словаря данных
Версия 6.332.0.166
При попытке сменить на "идентификацию по ROWID"

"Для ТБП[...] c идентификацией по ROWID нельзя устанавливать признак "Время жизни" в состояние, отличное от "Постоянный""

Код ошибки - CHECK_ROWID.

Возможно, версия не та, возможно я что-то не так делаю.
Прошу разобраться.

2) Цель моих изысканий: в ни записи "больших" данных, а в использовании трудновычисляемых данных в отчете несколько раз, поэтому ни представление, ни курсор не подходят.

а) Конструкция WITH AS + hint Materialize
во многих случаях то что надо, есть ограничения.
б) Есть неплохой вариант со временными таблицами, но у них есть ограничения: например, они не поддерживают параллельное начитывание, а также присутствуют "левые" поля и "левый" индекс.
в) Есть вариант со "Временными" таблицами созданными как постоянные
CREATE TABLE TEMP_(несколько символов, полученных рандомно, так чтобы название таблицы было не более 30 символов)
AS
SELECT * FROM ...
А потом DROP этой таблицы.
Способ хороший, но и он не без проблем, и кроме того, тогда все таблицы будут вне ИБСО, что не хорошо.

Текущие возможности я описал, но нет того чтобы мне полностью подходило.
kai
Профи


Вступление в Клуб: 16.08.2012
СообщениеСр Июн 05, 2019 14:17    Ответить с цитатой
Полезность: Нет оценки
Цитата:
Ошибка при попытке поставить галку "идентификация по ROWID"
Прошу разобраться.


да, ошибка есть - и я откорректировал свой первоначальный вариант ответа. Сначала не заметил всплывшее окно о необходимости перестройки, а потом, соответственно, не нажал кнопку "продолжить".
Ограничение описано в документации без объяснений. Объяснений причин такой реализации тоже нет, кроме банального: отсутствие достаточного времени при разработке поддержки в 2MCA.

Цитата:
трудновычисляемых данных в отчете несколько раз

Каков объём таких данных: количество записей, количество колонок?

Всё таки, динамический курсор можно использовать и с одиночными fetch.

А ведь ещё есть вариант использования переменных SQL типа: Nested Table.
DALLUS
Участник


Вступление в Клуб: 03.06.2019
СообщениеЧт Июн 06, 2019 09:09    Ответить с цитатой
Полезность: Нет оценки
1) "Каков объём таких данных: количество записей, количество колонок? "

максимум 3 млн строк, 6 "легких" полей (DATE, NUMBER),будем считать по 2 байта.
Итого: 3 млн * 6 * 2 байта = 12млн байт или около 12МБ

+ "левые поля" SN,SU, ID - тоже примерно 6МБ

+ системные поля типа ROWID

Если мои расчеты верны, то не так много, но треть данных вообще "лишняя" и издержки на формирование и хранение "Левого" индекса.

2) "А ведь ещё есть вариант использования переменных SQL типа: Nested Table."
ДА такой вариант есть.
Вариант , весьма, подходит, для манипуляциями с небольшими выборками как, вообщем, и вариант с WITH AS.
Но все это хранится исключительно в оперативке.
Были случае когда её не хватало и сервер уходил на файл подкачки и зависал.
С таблицами ОРАКЛ при нехватки оперативки поступает по другому: табличку выложит на физ. диск, другие сессии при этом не пострадают.
В данном случае это утверждение из личного опыта, поскольку теоретически ОРАКЛ в этом плане не был достаточно изучен.
Embarassed
kai
Профи


Вступление в Клуб: 16.08.2012
СообщениеЧт Июн 06, 2019 11:51    Ответить с цитатой
Полезность: Нет оценки
Цитата:
треть данных вообще "лишняя" и издержки на формирование и хранение "Левого" индекса

Лучше бы, чтобы этого не было - но без заявки не исправится. Но лично я пока не планирую её делать.
Не пробовал и вряд ли получиться без проблем сделать (на уровне словаря Oracle изменить таблицу) удалить индекс и лишние колонки.
Проще уж тогда сразу вне словаря ТЯ сделать временную таблицу.

И из объяснений я не увидел причин отказываться от варианта динамического формирования запроса с "WITH AS".

p.s.
Цитата:
+ системные поля типа ROWID

ROWID - это адрес строки - в таблице информация не хранится, это ж псевдостолбец.
Значения ROWID хранятся в индексах.
DALLUS
Участник


Вступление в Клуб: 03.06.2019
СообщениеЧт Июн 06, 2019 12:22    Ответить с цитатой
Полезность: Нет оценки
ROWID псевдостолбец. И он такой не один (их по-моему три таких).
(Это зашифрованное место хранения на диске.)
SELECT ROWID FROM DUAL

Он создается без привязки к индексу, всегда.

Вот если индекс создается, то он тоже там участвует.
Индексированные поля(выражения, функции) + ROWID.

Способ его хранения и данные сколько он весит мне не известны.
kai
Профи


Вступление в Клуб: 16.08.2012
СообщениеПт Июн 07, 2019 07:41    Ответить с цитатой
Полезность: Нет оценки
Цитата:
он такой не один (их по-моему три таких).

их больше, например:
Код:
select rownum, level, ora_rowscn, rowid from dual connect by level < 10;


Цитата:
Он создается без привязки к индексу, всегда.

приведу описание Тома Кайта
Цитата:
Каждая строка в любой таблице имеет либо ROWID, либо UROWID, ассоциирован­ный с ней. При извлечении из таблицы они рассматриваются как псевдостолбцы -в том смысле, что в действительности они не хранятся вместе со строкой, а являются ее производными атрибутами. ROWID генерируется на основе физического местопо­ложения строки и с ней не сохраняется.


и вот ещё интересно у него же
Цитата:
Раньше считалось, что для строк с ROWID (наиболее распространенный тип строк в Oracle; за исключением строк в индекс-таблицах все строки имеют ROWID) зна­чения ROWID должны быть неизменяемыми. Когда строка вставлялась, она долж­на была ассоциироваться с ROWID-адресом- и этот адрес должен был быть свя­занным с ней до тех пор, пока строка физически не удалялась из базы данных. Со временем это становится все менее верно, т. к. теперь есть операции, которые могут вызвать изменение ROWID строки; их примеры перечислены ниже.

    - Обновление ключа секционирования строки в секционированной таблице, в результате чего строка должна переместиться из одной секции в другую.
    - Использование команды FLASHBACK TABLE для восстановления таблицы базы данных в состояние, которое она имела в какой-то момент времени в прошлом.
    - Выполнение операций MOVE, а также многих операций с секциями, таких как разделение и слияние секций.
    - Применение команды ALTER TABLE SHRINK SPACE для усечения сегмента.


Теперь с учетом того, что ROWID могут со временем изменяться (они больше не являются неизменяемыми), физически хранить их в виде столбцов таблиц базы данных не рекомендуется. То есть использование ROWID в качестве типа данных для столбца считается плохой практикой, которой следует избегать. Взамен должен применяться первичный ключ строки (который должен быть неизменяемым), и для поддержания целостности данных может быть определено ограничение ссылочной целостности.

...

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


Цитата:
Способ его хранения и данные сколько он весит мне не известны.


см. Figure 12-9 ROWID Format

Ещё есть сопутствующий пакет DBMS_ROWID
kai
Профи


Вступление в Клуб: 16.08.2012
СообщениеВт Июн 25, 2019 05:23    Ответить с цитатой
Полезность: 1
Ещё статья про форматы rowid тут.
Показать сообщения:   
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle Часовой пояс: GMT + 3
Страница 1 из 1

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Рейтинг@Mail.ru