Insert all oracle сколько строк можно вставить
Я знаю, что сделал это несколько лет назад, но не могу вспомнить синтаксис и не могу его найти нигде из-за загрузки тонны справочных документов и статей о «массовом импорте».
Вот что я хочу сделать, но синтаксис не совсем правильный . пожалуйста, кто-то, кто делал это раньше, помогите мне :)
Я знаю, что это близко к правильному синтаксису. Мне может понадобиться слово «BULK» или что-то еще, я не могу вспомнить. Есть идеи?
Мне это нужно для базы данных SQL Server 2005. Я пробовал этот код, но безрезультатно:
Я получаю Incorrect syntax near the keyword 'VALUES'.
INSERT INTO @blah (идентификатор, имя), ЦЕННОСТИ (123, «Тимми»), ЦЕННОСТИ (124, «Джонни»), ЦЕННОСТИ (125, «Салли»)
2005 больше не поддерживается. В 2008, 2012 и 2016 годах вы почти можете использовать то, что вы добавили, INSERT INTO @blah (ID, Name) VALUES (123, 'Timmy'), (124, 'Jonny'), (125, 'Sally') «ЗНАЧЕНИЯ» появляются только один раз, и вам нужно запятые между наборами.
Для SQL Server 2008, можете сделать это в одном предложении VALUES точно в соответствии с оператором в вашем вопросе (вам просто нужно добавить запятую для разделения каждого оператора значений) .
@Code Commander: нет, в том, что он дольше компилируется. Да, в том, что у вас есть только одна вставка. Но это отвечает на вопрос: нет повторения INSERT table (columnlist)
@VoidKing Я знаю, что это произойдет через полгода, и вы, возможно, уже поняли это, но это действительно довольно просто. Используя его, select вы создаете набор со столбцами и строками, и, создавая эти строки, они могут быть insert преобразованы в другую таблицу с равным количеством столбцов. Вы даже можете использовать смесь литералов и значений. Например, использование insert with select 'A', ID from ATable будет вставлять «A» в первый столбец каждый раз, а значение столбца ID соответствующей строки ATable во втором столбце.
Это также работает с DB2, которая является важной характеристикой для тех из нас, кто застрял в устаревшей технологии. Значения, разделенные запятым ответом, лучше для тех из вас, кто работает в SQL Server 2008 или новее. ОП может удалить все «значения», кроме первого, и заменить на,
В этом учебном материале вы узнаете, как использовать оператор INSERT в SQL с синтаксисом и примерами.
Описание
SQL оператор INSERT используется для вставки одной или нескольких записей в таблицу. Существует два синтаксиса для оператора INSERT в зависимости от того, вставляете ли вы одну запись или несколько записей.
Получить первые N строки
аналитический
Примеры:
Следующие примеры были процитированы со ссылочной страницы в надежде предотвратить гниение ссылок.
Получить первые N строки, если N й строки имеет связи, получить все связанные строки
Что в таблице?
Верх x % строк
Пример - использование оператора INSERT для вставки одной записи
Самый простой способ использовать оператор INSERT - вставить одну запись в таблицу с помощью ключевого слова VALUES. Давайте рассмотрим пример того, как сделать это в SQL.
В этом примере у нас есть таблица с categories со следующими данными:
category_id | category_name |
---|---|
25 | Deli |
50 | Produce |
75 | Bakery |
100 | General Merchandise |
125 | Technology |
Давайте вставим новую запись в category . Введите следующий SQL оператор:
В обсуждении вставки нескольких строк в Oracle были продемонстрированы два подхода:
Может ли кто-нибудь оспорить предпочтение использования одного над другим?
P.S. Я сам не проводил никаких исследований (даже плана объяснений), поэтому любая информация или мнение будут оценены.
С точки зрения производительности эти запросы идентичны.
UNION ALL не повлияет на производительность, поскольку Oracle оценивает UNION -запрос только тогда, когда это необходимо, а не кэширует результаты сначала.
Синтаксис SELECT является более гибким в том смысле, что вы можете более легко манипулировать запросом SELECT , если хотите что-то изменить.
Например, этот запрос:
Можно переписать как
Заменив 2 на соответствующее число, вы можете получить любое количество строк, какое захотите.
В случае INSERT ALL вам придется продублировать описание целевой таблицы, что менее читабельно, если вам нужно, скажем, 40 строк.
У метода INSERT ALL есть проблема со вставкой большего количества строк в таблицу.
Недавно я хотел вставить 1130 строк в таблицу с помощью одного оператора SQL. Когда я попытался сделать это с помощью метода INSERT ALL , я получил следующую ошибку:
ORA-24335 - не может поддерживать более 1000 столбцов
Когда я использовал подход INSERT INTO .. UNION ALL .. , все прошло нормально.
Кстати. Я не знал о методе UNION ALL до того, как нашел это обсуждение :)
Я подозреваю, что решение 1 - это своего рода хак, который работает и, вероятно, менее эффективен, чем разработанная альтернатива Insert ALL.
Вставка всего действительно предназначена для вставки многих строк в более чем 1 таблицу в результате выбора, например:
Если вы хотите загрузить тысячи строк и их еще нет в базе данных, я не думаю, что это лучший способ сделать это. Если ваши данные находятся в файле, вы хотите посмотреть во внешних таблицах или загрузчике SQL, чтобы эффективно вставлять строки.
Я попробовал несколько тестов, и более быстрое решение должно быть
Буферизация между 300 400 строк (я пробовал с odbc, это значение может зависеть от его конфигурации)
Оператор, использующий UNION ALL , теоретически имеет небольшой недостаток производительности, поскольку он должен объединить результаты всех операторов, прежде чем произойдет вставка. INSERT ALL лишен этого недостатка, так как конечный результат уже может быть обработан построчно.
Но практически оптимизатор внутри Oracle должен сделать разницу незначительной, и выбор способа зависит от ваших предпочтений.
По моему мнению, INSERT ALL лучше читается человеком из двух, в то время как вариант UNION ALL занимает меньше места, когда такая вставка создается автоматически.
Согласен с выводом, но: Придирка: эти операторы INSERT не возвращают никаких значений, поэтому бессмысленно говорить, что что-то происходит «до того, как вставка может произойти» или «построчно». В любом случае, он использует UNION ALL, так что проблем с сортировкой нет, если вы об этом подумали.
Быстрый план «объяснить» показывает, что INSERT ALL с 4 строками int SCOTT.EMP имеет стоимость 2 (1 многотабличная вставка с единственной затратой ЦП при двойном выборе), в то время как вариант UNION ALL имеет стоимость 2 8 (каждый SELECT вызывает еще 2). Это не сортировка, а объединение результатов и одного (по крайней мере FAST DUAL) выбора, которые вызывают затраты.
Если у вас есть операторы вставки, которых больше 1000, поместите все операторы вставки в файл .sql и откройте его в Toad или SQL Developer, а затем выполните. Все записи будут вставлены.
ОП хотел знать, следует ли предпочесть один из упомянутых им подходов другому, речь идет не о том, как/где выполнять SQL.
Вы должны рассмотреть Array-Insert.
- Простой SQL
- нужно некоторое кодирование на стороне клиента для настройки параметров массива
Это способ минимизировать сетевой трафик, если необходимо сделать несколько сотен вставок в пакете.
Will there be any chance of failure because of number of rows ? Or is there a better way to insert values in case of more no of records.
Thanks in Advance
One uses a relational database so that you don't have to repeat data by normalizing it into separate table and join them as needed with filters. Creating a product table is counter to this philosophy. If you want to obfuscate the join, use a VIEW instead of creating another table.
Is this a production system? Does the data need to be recoverable from a previous backup in case of media failure later on? do you have enough disk space?
3 Answers 3
Well, everything is finite inside the machine, so if that select returns too many rows, it for sure won't work (although there must be maaany rows, the number is dependent on your storage and memory size, your OS, and maybe other things).
If you think your query can surpass the limit, then do the insertion in batches, and commit after each batch. Of course you need to be aware you must do something if at 50% of the inserts you decide you need to cancel the process (as a rollback will not be useful here).
I don't see how the maximum size of the result set is dependent on the OS . maybe memory, certainly available disk space.
@DavidAldridge I was thinking of maximum in-memory data that a process can maintain. But maybe I'm wrong about that one as there are workarounds for that problem, it was more of a guess.
My recommended steps are different because performance typically increases when you load more data in one SQL statement using SQL or PL/SQL:
- I would recommend checking the size of your rollback segment (RBS segment) and possibly bring online a larger dedicated one for such transaction.
- For inserts, you can say something like 'rollback consumed' = 'amount of data inserted'. You know the typical row width from the database statistics (see user_tables after analyze table tableB compute statistics for table for all columns for all indexes ).
- Determine how many rows you can insert per iteration.
- Insert this amount of data in big insert and commit.
- Repeat.
- Locking normally is not an issue with insert, since what does not yet exist can't be locked :-)
When running on partitioned tables, you might want to consider different scenarios allowing the (sub)partitions to distribute the work together. When using SQL*Loader by loading from text files, you might use different approach too, such as direct path which adds preformatted data blocks to the database without the SQL engine instead of letting the RDBMS handle the SQL.
Есть ли способ заставить Oracle запрос вести себя так, как будто он содержит MySQL limit предложение?
В MySQL , я могу сделать это:
чтобы получить 21-й по 30-й ряды (пропустите первые 20, дайте следующие 10). Строки выбираются после order by , так что это действительно начинается с 20-го имени в алфавитном порядке.
В Oracle , единственное , что люди уже является rownum псевдо-столбец, но он оценивается до order by того , что означает следующее:
вернет случайный набор из десяти строк, упорядоченных по имени, что обычно не то, что я хочу. Это также не позволяет указывать смещение.
@YaroslavShabalin В частности, выгружаемый поиск использует этот паттерн все время. Практически любое приложение с любой функцией поиска будет использовать его. Другим вариантом использования будет загрузка только части длинного списка или клиентской части таблицы и предоставление пользователю возможности расширения.
@YaroslavShabalin Вы не можете получить другой набор результатов, если базовые данные не изменятся из-за ORDER BY . Вот и весь смысл заказа в первую очередь. Если базовые данные изменяются, и ваш набор результатов изменяется из-за этого, то почему бы не показать пользователю обновленные результаты вместо устаревшей информации? Кроме того, государственное управление - это чума, которую следует избегать, насколько это возможно. Это постоянный источник осложнений и ошибок; вот почему функционал становится таким популярным. И когда бы вы знали, чтобы истечь весь набор результатов в памяти? В Интернете у вас нет возможности узнать, когда пользователь уходит.
Начиная с Oracle 12c R1 (12.1), то есть строка ограничение пункт . Он не использует знакомый LIMIT синтаксис, но он может сделать работу лучше с большим количеством опций. Вы можете найти полный синтаксис здесь . (Также читайте больше о том, как это работает внутри Oracle в этом ответе ).
Чтобы ответить на оригинальный вопрос, вот запрос:
(Для более ранних версий Oracle, пожалуйста, обратитесь к другим ответам в этом вопросе)
Вы можете комбинировать смещение с процентами
Вы можете использовать подзапрос для этого как
Посмотрите также тему О ROWNUM и ограничении результатов в Oracle / AskTom для получения дополнительной информации.
Обновление : чтобы ограничить результат нижними и верхними границами, все становится немного более раздутым
(Скопировано из указанной AskTom-статьи)
Обновление 2 : Начиная с Oracle 12c (12.1), доступен синтаксис, ограничивающий строки или начинающийся со смещений.
Смотрите этот ответ для большего количества примеров. Спасибо Крумии за подсказку.
Это, безусловно, способ сделать это, но имейте в виду (как говорится в статье о спросе), производительность запросов снижается по мере увеличения вашего максимального значения. Это хорошее решение для результатов запросов, когда вы хотите видеть только первые несколько страниц, но если вы используете это в качестве механизма для кодирования страниц по всей таблице, вам было бы лучше выполнить рефакторинг кода
+1 Ваша нижняя / верхняя версия фактически помогла мне обойти проблему, когда простое ограниченное сверху предложение rownum резко замедлило мой запрос.
В статье AskTom также есть подсказка оптимизатора, в которой используется SELECT / * + FIRST_ROWS (n) / a. , rownum rnum Перед косой чертой должна стоять звездочка. ТАК чистит это.
Обратите внимание, что для Oracle 11 внешний SELECT с ROWNUM не позволит вам вызвать deleteRow для UpdatableResultSet (с ORA-01446) - ожидая этого изменения 12c R1!
Я провел тестирование производительности для следующих подходов:
Примечание
- При вставке записей в таблицу с помощью SQL оператора INSERT, необходимо указать значение для каждого столбца NOT NULL. Вы можете опустить столбец из оператора INSERT, только если столбец допускает значения NULL.
Использование смещения, очень полезно для нумерации страниц
Настроить
Asktom
Синтаксис
Синтаксис SQL оператора INSERT при вставке одной записи в таблицу:
Или синтаксис SQL оператора INSERT при вставке нескольких записей в таблицу:
INSERT INTO table
(column1, column2, . )
SELECT expression1, expression2, .
FROM source_tables
[WHERE conditions];
Параметры или аргументы
table Таблица, в которую нужно вставить записи. column1 , column2 Это столбцы в table для вставки значений. expression1 , expression2 Эти значения присваиваются столбцам в таблице. Поэтому column1 будет присвоено значение expression1 , column2 будет присвоено значение expression2 и т.д. , source_tables Используется при вставке записей из другой таблицы. Это исходная таблица при выполнении вставки. WHERE conditions Необязательный. Используется при вставке записей из другой таблицы. Это те условия, которые должны быть соблюдены для вставки записей.
Короткая альтернатива
В таблице было 10 миллионов записей, сортировка осуществлялась по неиндексированной строке даты и времени:
- План объяснения показал одинаковое значение для всех трех вариантов (323168)
- Но победителем является AskTom (с аналитическим следом за ним)
Выбор первых 10 строк занял:
Выбор строк от 100 000 до 100 010:
- AskTom: 60 секунд
- Аналитический: 100 секунд
Выбор строк между 9 000 000 и 9 000 010:
- AskTom: 130 секунд
- Аналитический: 150 секунд
zeldi - На какой версии это было? Oracle внесла аналитические улучшения производительности в 11.1. и 11.2.
Я провел несколько быстрых тестов и получил аналогичные результаты для 12c. Новый offset синтаксис имеет тот же план и производительность, что и аналитический подход.
Аналитическое решение только с одним вложенным запросом:
Rank() может быть заменено, Row_Number() но может вернуть больше записей, чем вы ожидаете, если для имени есть повторяющиеся значения.
Я люблю аналитику. Возможно, вы захотите уточнить, в чем разница в поведении между Rank () и Row_Number ().
Действительно, не уверен, почему я не думал о дубликатах. Таким образом, в этом случае, если есть повторяющиеся значения для имени, тогда RANK может дать больше записей, чем вы ожидаете, поэтому вы должны использовать Row_Number.
При упоминании rank() этого также стоит отметить, dense_rank() что может быть более полезным для управления выводом, так как последний не «пропускает» числа, тогда как rank() может. В любом случае для этого вопроса row_number() лучше всего подходит. Еще один не является этот метод применим к любой БД, которая поддерживает упомянутые функции.
В Oracle 12c (см. Предложение по ограничению строк в справочнике по SQL ):
Очевидно, после того, как LIMIT они сошлись со всеми другими поставщиками, чтобы договориться о SQL: 2008, им пришлось взять листок из книги Microsoft и нарушить стандарт.
Интересно, что недавно я слышал, что самый последний стандарт включает этот синтаксис, поэтому, возможно, Oracle перед этим внедрил его. Возможно, это более гибкий, чем LIMIT . OFFSET
@Derek: Да, несоблюдение стандарта вызывает сожаление. Но недавно представленная функциональность в 12cR1 более мощная, чем просто LIMIT n, m (см. Мой ответ). Опять же, Oracle должен был быть реализован LIMIT n, m как синтаксический сахар, как это эквивалентно OFFSET n ROWS FETCH NEXT m ROWS ONLY .
Запросы на нумерацию страниц с упорядочением действительно сложны в Oracle.
Oracle предоставляет псевдостолбец ROWNUM, который возвращает число, указывающее порядок, в котором база данных выбирает строку из таблицы или набора объединенных представлений.
ROWNUM - это псевдоколонка, которая доставляет многим людям неприятности. Значение ROWNUM не всегда назначается строке (это распространенное недоразумение). Это может сбивать с толку, когда значение ROWNUM фактически назначается. Значение ROWNUM присваивается строке после прохождения предикатов фильтра запроса, но до агрегации или сортировки запроса .
Более того, значение ROWNUM увеличивается только после его назначения.
Вот почему следующий запрос не возвращает строк:
Первая строка результата запроса не передает предикат ROWNUM> 1, поэтому ROWNUM не увеличивается до 2. По этой причине никакое значение ROWNUM не будет больше 1, следовательно, запрос не возвращает строк.
Правильно определенный запрос должен выглядеть так:
Узнайте больше о запросах на нумерацию страниц в моих статьях в блоге Vertabelo :
Читайте также: