Структуры как временные таблицы
|
| Предыдущая тема :: Следующая тема |
| Автор |
Сообщение |
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.
ЗАЧЕМ?!?  
Зачем временной таблице (срок жизни которой одна транзакция) "левые" поля, с "левым" индексом?
Почему конструкция 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.
Но все это хранится исключительно в оперативке.
Были случае когда её не хватало и сервер уходил на файл подкачки и зависал.
С таблицами ОРАКЛ при нехватки оперативки поступает по другому: табличку выложит на физ. диск, другие сессии при этом не пострадают.
В данном случае это утверждение из личного опыта, поскольку теоретически ОРАКЛ в этом плане не был достаточно изучен.
 |
|
|
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 тут. |
|
|
|
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
|
|
О проекте
Поиск
Отзывы
Регистрация
Вход
