Альтернативы для временных таблиц в Oracle. Oracle временные таблицы
Альтернативы для временных таблиц в Oracle
В чем проблема бизнеса, которую вы пытаетесь решить? Исключительно редко вам нужно использовать временные таблицы в Oracle. Почему бы вам не просто
SELECT * FROM employees WHERE id = p_id_passed_in;В других базах данных, часто создавать временные таблицы, потому что читатели блокируют писателей, так что вы хотите создать отдельную копию данных для того, чтобы избежать блокирования каких-либо других сессий. Однако в Oracle читатели никогда не блокируют писателей, поэтому, как правило, нет необходимости сохранять отдельную копию данных.
В других базах данных вы создаете временные таблицы, потому что вы не хотите делать грязные чтения. Однако Oracle не допускает грязных чтений. Согласованность нескольких версий означает, что Oracle всегда будет показывать вам данные, как они существовали при запуске запроса (или когда транзакция началась, если вы установили уровень изоляции транзакции для сериализации). Поэтому нет необходимости создавать временную таблицу, чтобы избежать грязных чтений.
Если вы действительно хотели использовать временные таблицы в Oracle, вы бы не создали таблицу динамически. Перед созданием хранимой процедуры вы создадите глобальную временную таблицу. Структура таблицы будет видна для всех сеансов, но данные будут видны только для сеанса, который ее вставил. Вы должны заполнить временную таблицу в процедуре, а затем запросить таблицу. Что-то вроде
CREATE GLOBAL TEMPORARY TABLE temp_emp ( empno number, ename varchar2(10), job varchar2(9), mgr number, sal number(7,2) ) ON COMMIT PRESERVE ROWS; CREATE OR REPLACE PROCEDURE populate_temp_emp AS BEGIN INSERT INTO temp_emp(empno, ename, job, mgr, sal) SELECT empno, ename, job, mgr, sal FROM emp; END; / SQL> begin 2 populate_temp_emp; 3 end; 4/ PL/SQL procedure successfully completed. SQL> select * 2 from temp_emp; EMPNO ENAME JOB MGR SAL ---------- ---------- --------- ---------- ---------- 7623 PAV Dev 7369 smith CLERK 7902 800 7499 ALLEN SALESMAN 7698 1600 7521 WARD SALESMAN 7698 1250 7566 JONES MANAGER 7839 2975 7654 MARTIN SALESMAN 7698 1250 7698 BLAKE MANAGER 7839 2850 7782 CLARK MANAGER 7839 2450 7788 SCOTT ANALYST 7566 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 7698 1500 7876 ADAMS CLERK 7788 1110 7900 SM0 CLERK 7698 950 7902 FORD ANALYST 7566 3000 7934 MILLER CLERK 7782 1300 1234 BAR 16 rows selected.stackoverrun.com
database - Временные таблицы Oracle 10g
Я пытаюсь преобразовать постоянные таблицы, используемые в хранимой процедуре, в глобальные временные таблицы. Я просмотрел статистику на этих постоянных таблицах, а некоторые из них имеют десятки миллионов строк данных и находятся на заказ, если размер гигабайта (до 10 ГБ).
Итак,
CREATE TABLE my_table ( column1 NUMBER, column2 NUMBER, etc... ) TABLESPACE BIGTABLESPACE NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;должен стать
CREATE GLOBAL TEMPORARY TABLE my_table ( column1 NUMBER, column2 NUMBER, etc.. ) ON COMMIT PRESERVE ROWS;Я создаю эквивалентную глобальную временную таблицу со строками, которые должны быть сохранены до конца сеанса для каждой существующей постоянной таблицы. Эта глобальная таблица temp будет использоваться в процедуре вместо постоянной таблицы. (EXECUTE IMMEDIATE 'TRUNCATE ...' в начале и INSERT /*+ APPEND */ INTO в некоторой более поздней точке)
Все постоянные таблицы были созданы в большом табличном пространстве BIGTABLESPACE
В документах Oracle указано, что глобальная временная таблица будет создана в табличном пространстве temp пользователя (я предполагаю, что это TEMP). Проблема заключается в том, что табличное пространство TEMP мало, и экстенты не установлены, чтобы расти до размера, в котором они мне нужны, чтобы расти во время процедуры.
Табличное пространство TEMP было создано во время создания базы данных
create database "$oracle\_sid" user sys identified by "$sys\_password" user system identified by "$system\_password" set default bigfile tablespace controlfile reuse maxdatafiles 256 maxinstances $maxinstances maxlogfiles 16 maxlogmembers 3 maxloghistory 1600 noarchivelog character set WE8MSWIN1252 national character set AL16UTF16 datafile '$oracle\_home/oradata/$oracle\_sid/system01.dbf' size 512M logfile '$oracle\_home/oradata/$oracle\_sid/redo01.log' size 1G, '$oracle\_home/oradata/$oracle\_sid/redo02.log' size 1G, '$oracle\_home/oradata/$oracle\_sid/redo03.log' size 1G sysaux datafile '$oracle\_home/oradata/$oracle\_sid/sysaux01.dbf' size 256M default temporary tablespace temp tempfile '$oracle\_home/oradata/$oracle\_sid/temp01.dbf' size 5G undo tablespace "UNDOTBS1" datafile '$oracle\_home/oradata/$oracle\_sid/undotbs01.dbf' size 5G;Первичные индексы таблиц изначально были созданы в табличном пространстве BIGTABLESPACE
-- 20G bigfile datafile size create bigfile tablespace "BIGINDXSPACE" datafile '$oracle\_home/oradata/$oracle\_sid/btsindx01.dbf' size 20G extent management local segment space management auto;- Является ли замена этих постоянных таблиц доступными глобальными временными таблицами?
- В табличном пространстве TEMP возникает проблема, расширяющая табличное пространство TEMP. Есть ли способ создания глобальных временных таблиц и их индексов в табличных пространствах BIGTABLESPACE и BIGINDXSPACE?
- Если нет, как я могу заставить табличное пространство TEMP вести себя как табличное пространство bigfile и добиться разделения индекса/таблицы?
- Можно ли создать два табличных пространства TEMP bigfile и создать индексы в один и таблицы в другой?
Я хочу использовать глобальные временные таблицы, но объем данных, которые я обрабатываю в процедуре, по-видимому, выше и выше стандартного дизайна глобальных временных таблиц. Любые предложения?
задан user78706 09 дек. '09 в 20:01
qaru.site
oracle - Альтернативы для временных таблиц в Oracle
Какую бизнес-проблему вы пытаетесь решить? Исключительно редко вам нужно использовать временные таблицы в Oracle. Почему бы вам просто не
SELECT * FROM employees WHERE id = p_id_passed_in;В других базах данных часто создаются временные таблицы, потому что читатели блокируют записи, поэтому вы хотите создать отдельную копию данных, чтобы избежать блокировки любых других сеансов. Однако в Oracle читатели никогда не блокируют писателей, поэтому, как правило, нет необходимости сохранять отдельную копию данных.
В других базах данных вы создаете временные таблицы, потому что вы не хотите делать грязные чтения. Однако Oracle не допускает грязных чтений. Согласованность нескольких версий означает, что Oracle всегда будет показывать вам данные, как они существовали при запуске запроса (или когда транзакция началась, если вы установили уровень изоляции транзакции для сериализации). Поэтому нет необходимости создавать временную таблицу, чтобы избежать грязных чтений.
Если вы действительно хотели использовать временные таблицы в Oracle, вы бы не создали таблицу динамически. Перед созданием хранимой процедуры вы создадите глобальную временную таблицу. Структура таблицы будет видна для всех сеансов, но данные будут видны только для сеанса, который ее вставил. Вы должны заполнить временную таблицу в процедуре, а затем запросить таблицу. Что-то вроде
CREATE GLOBAL TEMPORARY TABLE temp_emp ( empno number, ename varchar2(10), job varchar2(9), mgr number, sal number(7,2) ) ON COMMIT PRESERVE ROWS; CREATE OR REPLACE PROCEDURE populate_temp_emp AS BEGIN INSERT INTO temp_emp( empno, ename, job, mgr, sal ) SELECT empno, ename, job, mgr, sal FROM emp; END; / SQL> begin 2 populate_temp_emp; 3 end; 4 / PL/SQL procedure successfully completed. SQL> select * 2 from temp_emp; EMPNO ENAME JOB MGR SAL ---------- ---------- --------- ---------- ---------- 7623 PAV Dev 7369 smith CLERK 7902 800 7499 ALLEN SALESMAN 7698 1600 7521 WARD SALESMAN 7698 1250 7566 JONES MANAGER 7839 2975 7654 MARTIN SALESMAN 7698 1250 7698 BLAKE MANAGER 7839 2850 7782 CLARK MANAGER 7839 2450 7788 SCOTT ANALYST 7566 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 7698 1500 7876 ADAMS CLERK 7788 1110 7900 SM0 CLERK 7698 950 7902 FORD ANALYST 7566 3000 7934 MILLER CLERK 7782 1300 1234 BAR 16 rows selected.qaru.site
ORACLE & временная таблица?
Форум: "Базы";Поиск по всему сайту: www.delphimaster.net;Текущий архив: 2002.12.26;Скачать: [xml.tar.bz2];Вниз
ORACLE & временная таблица?
chak (2002-12-05 10:29) [0]Как лучше и проще удалить временную таблицу в ORACLE по прошествии допустим 10 минут? Можна ли сделать это используя только средства самого ORACLE?
Повесьте JOB
chak (2002-12-05 10:41) [2]==> Johnny Smith © (05.12.02 10:35) можна это сделать из Delphi?
Sergey13 (2002-12-05 10:41) [3]2chak © (05.12.02 10:29) А что ты понимаешь под "временной таблицой" и зачем ее удалять?
Johnny Smith (2002-12-05 10:50) [4]2chak © (05.12.02 10:41) То есть все-таки средствами Оракла или из Дельфей?
chak (2002-12-05 11:01) [5]==> Sergey13 © (05.12.02 10:41) временную таблицу хочу сделать для того, чтобы сохранить в ней результати нескольких селектов, и дать возможность юзеру потом их использовать(сортировать например), ато второй раз делать опять тотже запрос и отсортировать его еще при этом .... если за 10 минут(ну условно) ему надоест - убить таблицу(зачем она потом кому нужна будет)
==> Johnny Smith © (05.12.02 10:50) Создать JOB средствами DELPHI, что-то я совсем запутался - потом ж и его придется уничтожать!!!
что мне делать?
ЮЮ (2002-12-05 11:09) [6]2chak © (05.12.02 11:01) Средствами Оракла создаете JOB, который будет квасить каждые 10 минут вашу временную таблицу. Что еще не понятно? :))))) И еще - о какой временной таблице Вы говорите? О переменной типа Table? Или просто о табличке, которую Вы будете считать временной?
Sergey13 (2002-12-05 11:17) [8]2chak © (05.12.02 11:01) В Оракле (начиная с 8i кажется) существует понятие временная таблица. Она может хранить временные(свои для каждой сессии) данные или пока действует транзакция ИЛИ пока есть сессия. Синтаксиса для создания под рукой нет к сожалению. Т.е. получается что таблица ПОСТОЯННАЯ (создается один раз для ВСЕХ), но для хранения ВРЕМЕННЫХ данных. Покопай в этом направлении - это то что тебе надо. А создавать простую таблицу и убивать ее постоянно - не есть гуд.
chak (2002-12-05 12:11) [9]==> Johnny Smith © (05.12.02 11:10) Да - просто таблица - и я ее считаю "временной".
==> Sergey13 © (05.12.02 11:17) Дело в том, что в сессии она должна создается, данные из нее передаются пользователю, и сессия закрывается. Пользователь не остается на связи с базой постоянно.
Ну тогда вообще не убивай таблицу, предоставь юзеру возможность сотрировать её несколько дней, если не можешь написать нормальный запрос на нормальном сервере :-)
Johnny Smith (2002-12-05 12:53) [11]2chak © (05.12.02 12:11) ==> Johnny Smith © (05.12.02 11:10) Да - просто таблица - и я ее считаю "временной". Это слишком дорогостоящий в плане ресурсов вариант... Я бы посоветовал от него отказаться.
dimis (2002-12-05 14:28) [12]можно повесить на начло и конец сессии тригерры (Oracle это позволяет) и еще поищи в хелпе об AUTONOMOUS_TRANSACTION Pragma поможет
Sergey13 (2002-12-06 09:50) [13]2chak © (05.12.02 12:11) >Дело в том, что в сессии она должна создается, данные из нее передаются пользователю, и сессия закрывается. Пользователь не остается на связи с базой постоянно.
А почему она должна создаваться именно в сессии? Она не имеет постоянной структуры? Структура произвольная? Или набор таблиц с определенной структурой? Смысл временной таблицы в Оракле - ты ОДИН раз определяешь структуру для хранения временных данных и при этом не создаешь обычную таблицу. Данные в этой структуре хранятся пока действует транзакция или сеанс. Потом АВТОМАТОМ удаляются. Данные РАЗНЫХ пользователей обрабатываются НЕЗАВИСИМО в ОДНОЙ таблице. Чего тебе еще надо? ИМХО достаточно. А вешать на тригеры/ХП DDL операторы - вещь не очень хорошая, ИМХО.
Вот нашел описание синтаксиса создания ------------- Создание временных таблиц Вы создаете временную таблицу, используя для этого специальные ключевые слова ANSI. Чтобы указать, что данные определены на время сеанса, вы должны использовать ключевые слова ON COMMIT PRESERVE ROWS. Чтобы указать, что данные определены на время транзакции, вы должны использовать ключевые слова ON COMMIT DELETE ROWS. CREATE GLOBAL TEMPORARY TABLE ... [ON COMMIT PRESERVE ROWS ] CREATE GLOBAL TEMPORARY TABLE ... [ON COMMIT DELETE ROWS ] -------------------
Форум: "Базы";Поиск по всему сайту: www.delphimaster.net;Текущий архив: 2002.12.26;Скачать: [xml.tar.bz2];Наверх
EMAIL={{}};LOGIN={{chak}};TITLE={{ORACLE & временная таблица?}};ATTRIBUTES={{}};ID_MSG={{288085}};ID_GROUP={{3}};ID_NNTP={{57065}};ID_DM={{85796}};REPLYNR={{0}};ID_DM_AUTHOR={{0}};ARCHIVE={{2002.12.26}};REPLYDATE={{2002-12-05 10:29}};CNT={{13}};
Память: 0.77 MBВремя: 0.023 cwww.delphimaster.net
временная таблица внутри процедуры oracle
It depends on why you are creating the temporary tables in MySQL.
Frequently, people that are creating temporary tables in other databases are doing so in order to work around limitations that don't exist in Oracle where readers don't block writers and writers don't block readers. In other databases, you commonly copy data from a permanent table to a temporary table so that your process doesn't block some other process that needs the same data. Since Oracle provides multi-version read consistency, however, this isn't necessary (or beneficial) in Oracle-- your process can process the data sitting in the real tables without worrying that it is going to block someone else. If that's the situation you're in, the proper response is simply to remove the temporary tables and process the data from the permanent tables.
Assuming that you really need a temporary copy of the data, you can create global temporary tables. You would create these tables outside of your code just like a permanent table and use them just like a permanent table inside your code. The global temporary table ensures that each session can only see the data that session has inserted. The only difference is that you're not dropping and recreating the structure of the table inside your procedure.
Another alternative would be to pull the data into a PL/SQL collection that you work with rather than using a temporary table. PL/SQL collections are stored in the server's PGA (one of Oracle's memory structures) so you generally want to limit the size of the collection particularly if there is a lot of data to process or there could be many sessions processing data simultaneously. You can do something like
DECLARE TYPE emp_tbl IS TABLE OF emp%rowtype; l_emps emp_tbl; CURSOR emp_cur IS SELECT * FROM emp; BEGIN OPEN emp_cur; LOOP -- Fetch 10 rows at a time from the cursor into the collection. -- You'd realistically want a larger limit, something between 100 and 1000 generally FETCH emp_cur BULK COLLECT INTO l_emps LIMIT 10; EXIT WHEN l_emps.COUNT = 0; -- An example of manipulating the collection in memory FOR i IN 1 .. l_emps.COUNT LOOP l_emps(i).sal := l_emps(i).sal * 2; END LOOP; -- And an example of using the collection to update a table FORALL i IN 1 .. l_emps.COUNT UPDATE emp SET sal = l_emps(i).sal WHERE empno = l_emps(i).empno; END LOOP; END;Of course, in the example above, it would be far simpler (and more efficient) to simply issue an UPDATE statement that would double every employee's salary.
dba.stackovernet.com
