Oracle расширяет строку до столбца

Обновлено: 21.11.2024

Когда вы устанавливаете высоту строки, вы указываете максимальную высоту строки в подготовленном отчете с разбивкой на страницы. Однако по умолчанию текстовые поля в строке настроены на рост по вертикали для размещения данных во время выполнения, и это может привести к расширению строки за пределы указанной вами высоты. Чтобы задать фиксированную высоту строки, необходимо изменить свойства текстового поля, чтобы они не расширялись автоматически.

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

Если ячейка в строке или столбце содержит прямоугольник или область данных, минимальная высота и ширина ячейки определяются высотой и шириной содержащегося в ней элемента. Дополнительную информацию см. в разделе Режимы отрисовки (построитель отчетов и службы SSRS).

Вы можете создавать и изменять файлы определений отчета с разбивкой на страницы (.rdl) в построителе отчетов Microsoft, построителе отчетов Power BI и в конструкторе отчетов в инструментах данных SQL Server.

Чтобы изменить высоту строки, перемещая маркеры строки

В представлении «Дизайн» щелкните в любом месте области данных табликса, чтобы выбрать ее. Маркеры строк серого цвета отображаются на внешней границе области данных табликса.

Наведите указатель мыши на край маркера строки, который вы хотите развернуть. Появится двунаправленная стрелка.

Нажмите, чтобы захватить край строки и переместить его выше или ниже, чтобы отрегулировать высоту строки.

Чтобы изменить высоту строки, задав свойства ячейки

В представлении «Дизайн» щелкните ячейку в строке таблицы.

На открывшейся панели свойств измените свойство Высота, а затем щелкните в любом месте за пределами панели свойств.

Чтобы предотвратить автоматическое расширение строки по вертикали

Нажмите на маркер строки, чтобы выбрать строку.

На панели свойств задайте для CanGrow значение False.

Если вы не видите панель "Свойства", в меню "Вид" нажмите "Свойства".

Чтобы изменить ширину столбца

В представлении «Дизайн» щелкните в любом месте области данных табликса, чтобы выбрать ее. Маркеры столбцов серого цвета отображаются на внешней границе области данных табликса.

Наведите указатель мыши на край маркера столбца, который вы хотите развернуть. Появится двунаправленная стрелка.

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

Вывод: в этом руководстве вы узнаете, как использовать оператор столбца Oracle ALTER TABLE MODIFY для изменения определения существующих столбцов.

Чтобы изменить определение столбца в таблице, используйте синтаксис столбца ALTER TABLE MODIFY следующим образом:

Утверждение простое. Чтобы изменить столбец таблицы, вам нужно указать имя столбца, имя таблицы и действие, которое вы хотите выполнить.

Oracle позволяет выполнять множество действий, но основными являются следующие:

Изменить видимость столбца
Разрешить или запретить пустые значения
Сократите или увеличьте размер столбца.
Изменить значение столбца по умолчанию
Изменить выражение виртуальных столбцов

Чтобы изменить несколько столбцов, используйте следующий синтаксис:

Примеры столбца Oracle ALTER TABLE MODIFY

Сначала создайте новую таблицу с именем account для демонстрации:

Во-вторых, вставьте несколько строк в таблицу учетных записей:

В-третьих, проверьте операцию вставки, используя следующую инструкцию SELECT:

A) Изменить видимость столбца

В Oracle Database 12c столбцы таблицы можно определить как невидимые или видимые. Невидимые столбцы недоступны для запроса, например:

Или утверждение типа:

Однако вы можете запросить невидимые столбцы, явно указав их в запросе:

По умолчанию столбцы таблицы видны. Вы можете определить невидимый столбец при создании таблицы или с помощью оператора столбца ALTER TABLE MODIFY.

Например, следующий оператор делает невидимым столбец full_name:

Следующий оператор возвращает данные из всех столбцов таблицы account, кроме столбца full_name:

Это связано с тем, что столбец full_name невидим.

Чтобы изменить столбец с невидимого на видимый, используйте следующий оператор:

B) Разрешить или запретить нулевой пример

Следующий оператор изменяет столбец электронной почты, чтобы он принимал ненулевые значения:

Однако Oracle выдал следующую ошибку:

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

Чтобы исправить это, мы сначала обновляем значения для столбца электронной почты:

Обратите внимание, что функция LOWER() преобразует строку в нижний регистр.

А затем измените ограничение столбца:

Теперь все работает как положено.

C) Увеличить или сократить пример столбца

Допустим, мы хотим добавить к телефонным номерам международный код. Прежде чем сделать это, мы должны увеличить размер столбца phone с помощью следующего оператора:

Теперь мы можем обновить номера телефонов:

Следующий оператор проверяет обновление:

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

Например, мы пытаемся сократить размер столбца телефона до 12 символов:

База данных Oracle выдала следующую ошибку:

Чтобы это исправить, во-первых, мы должны удалить международный код из телефонных номеров:

Функция REPLACE() заменяет подстроку новой подстрокой. В этом случае он заменяет «+1-» пустой строкой.

А затем уменьшите размер столбца телефона:

D) Изменить виртуальный столбец

Допустим, мы полное имя в следующем формате:

Для этого мы можем изменить выражение виртуального столбца full_name следующим образом:

Следующий оператор проверяет модификацию:

E) Изменить значение столбца по умолчанию

Давайте добавим новый столбец с именем status в таблицу учетных записей со значением по умолчанию 1.

После выполнения оператора значения в столбце состояния устанавливаются равными 1 для всех существующих строк в таблице учетных записей.

Чтобы изменить значение столбца состояния по умолчанию на 0, используйте следующую инструкцию:

Мы можем добавить новую строку в таблицу учетных записей, чтобы проверить, является ли значение столбца состояния по умолчанию равным 0 или 1:

Запрос данных из таблицы учетных записей:

Как видите, значение в столбце статуса для учетной записи с идентификатором 4 равно 0, как и ожидалось.

В этом руководстве вы узнали, как использовать оператор столбца Oracle ALTER TABLE MODIFY для изменения определения существующих столбцов в таблице.

Решение

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

Часто используется термин "развертка таблицы", поскольку небольшой набор диапазонов может привести к большому количеству строк. Если у вас есть одна строка с 01.01.2020 в качестве даты начала и 31.12.2020 в качестве даты окончания, это уже приведет к 366 строкам. Представьте, что вам нужно выполнить аналогичный расчет для миллионов клиентов. Поскольку выходные данные могут быть большими, важно, чтобы решение было быстрым и масштабируемым. Это исключает циклы и курсоры в T-SQL, поскольку они основаны на строках и не подходят для больших объемов строк. Другая терминология включает «звездообразование» или «распаковку отношения на временном интервале».

В этом совете решение представлено с использованием "числовой таблицы", которую иногда также называют "счетной таблицей".Дополнительные сведения об этом типе таблиц см. в следующих замечательных советах от Аарона Бертрана:

Функция подсказки SQL Server для возврата диапазона дат делает что-то похожее, но использует рекурсивное CTE, которое не масштабируется, а также имеет ограничение на максимальное количество рекурсий.

Пример данных

С помощью следующей инструкции SQL мы можем создать простую таблицу для хранения образцов данных:

Давайте вставим в эту таблицу 2 строки. Два сотрудника в отпуске с указанием даты начала и окончания.

Это желаемый конечный результат, одна строка для каждого дня в диапазоне дат начала и окончания:

Создание таблицы номеров SQL Server

Существуют различные методы создания таблицы чисел, например перекрестное соединение двух больших наборов (см. советы Аарона, если вам интересны примеры), но вы также можете создать "виртуальную" таблицу, используя обычные табличные выражения (CTE). Следующий синтаксис придуман Ициком Бен-Ганом, гуру T-SQL:

В первом CTE E00 две строки соединены вместе. В следующем CTE E02 этот первый CTE перекрестно соединен сам с собой. Это дает нам 4 строки. Этот процесс повторяется пару раз. Последний CTE E32 вернет 2 ^ 32 строки, что также является максимальным числом, которое может содержать целое число в SQL Server. Но это CTE возвращает только строки со значением 1.

Используя оконную функцию ROW_NUMBER, мы можем присвоить номера каждой строке. Для ORDER BY в предложении OVER мы используем подзапрос (SELECT NULL). Это заставляет SQL Server не сортировать набор данных. Если это так, это может быть серьезной проблемой производительности. Выполнение всей инструкции SQL возвращает уникальный порядковый номер для каждой строки, начиная с цифры 1:

Создание таблицы из миллиона строк занимает на моем компьютере всего пару секунд. Но в этом случае нам нужны даты, а не числа. Мы можем преобразовать числа в даты, используя функцию DATEADD. Оператор SQL теперь выглядит следующим образом:

Это сгенерирует все даты 2020 года (имейте в виду, что это високосный год).

Раскрытие диапазона в таблице номеров SQL Server

Используя таблицу чисел (точнее, таблицу дат), мы, наконец, можем "взорвать" наши выборочные данные. Нам просто нужно соединить итоговую таблицу с образцом таблицы с помощью соединения по диапазону.

Это дает нам желаемый набор результатов:

Начиная с 01.01.2020 и всего 366 дней для сгенерированной таблицы дат довольно мало. Однако вы можете расширить эти ограничения, и это не должно сильно повлиять на производительность. Возьмем за отправную точку 01.01.2000 и сгенерируем даты за 100 лет (примерно 365 000 строк):

Теперь запрос выполняется менее чем за 3 секунды.

Тестирование с дополнительными данными

Давайте воспользуемся тем же вариантом использования, но теперь воспользуемся таблицей Fact Internet Sales из хранилища данных AdventureWorks 2017. Мы взорвем даты между датой заказа и датой выполнения. Таблица фактов содержит 60 398 строк. Развернутый набор результатов будет содержать 785 174 строки. Адаптированный SQL-запрос:

В этом тесте я не возвращаю строки в SSMS, поскольку отображение всех строк в сетке заняло бы слишком много времени. Скорее я вставляю данные во временную таблицу, а затем возвращаю количество строк из этой временной таблицы. На моем компьютере создание таблицы фактов о продажах через Интернет заняло 20 секунд:

Обновление совета — альтернативный подход

В комментариях Джефф Моден предложил альтернативный способ решения проблемы. Вместо того, чтобы жестко задавать ограничение в 5000 строк, ограничение вычисляется динамически с использованием DATEDIFF заказа и срока выполнения.Это значение применяется к предложению TOP, выбирая только те строки итоговой таблицы, которые действительно необходимы. Результирующие строки затем присоединяются к таблице фактов с помощью CROSS APPLY, эффективно расширяя диапазон данных.

Вот адаптированный код:

Скажем, например, что в одной конкретной строке таблицы фактов о продажах через Интернет количество дней между ними равно 5 (это означает, что нам нужно 6 развернутых строк, поскольку нам нужно включить первую дату). Это приведет к следующему подзапросу:

Это вернет 6 строк итоговой таблицы (1,2,3,4,5,6). Используя CROSS APPLY, эти 6 строк соединяются с таблицей фактов, повторяя исходную строку таблицы фактов 6 раз, но для каждой строки добавляя (N-1) дней к дате заказа.

Решение более эффективно, поскольку в базе данных tempdb не создается рабочая таблица, а итоговая таблица не материализуется в памяти. Для получения дополнительной информации ознакомьтесь с комментариями.

Заключение

В этом совете мы увидели, как разбить диапазон дат с помощью таблицы "числа". Например, это обычный сценарий для анализа временных рядов. Код можно оптимизировать, используя постоянную таблицу дат вместо того, чтобы генерировать ее на лету. Однако цель этого совета состояла в том, чтобы показать вам методы быстрого создания таблицы больших чисел для улучшения вашего кода T-SQL с использованием логики на основе наборов.

Дальнейшие шаги

Если вы еще этого не сделали, ознакомьтесь с советами Аарона об основах таблицы чисел:
- Таблица номеров SQL Server, объяснение – часть 1
- Таблица номеров SQL Server, объяснение — часть 2
Статьи по теме

Популярные статьи

Об авторе

Коэн Вербек (Koen Verbeeck) — специалист по бизнес-аналитике, специализирующийся на стеке бизнес-аналитики Microsoft и особенно любящий службы SSIS.

Последнее обновление статьи: 15 июля 2020 г.

Комментарии к этой статье

Спасибо за очень теплый отзыв. Вы сделали отличный анализ в обновлении статьи. Я определенно не ожидал обновления статьи на основе того, что я опубликовал, и еще раз благодарю вас за то, что вы нашли время, чтобы помочь другим. Вы не просто хорошо разбираетесь в SQL Server, вы настоящий профессионал. Конечно, после того, как я «знал» вас много лет, я уже знал обе эти вещи.

давно не виделись :)
Спасибо за ваш ценный вклад! Я рад, что вы нашли время, чтобы прочитать мою статью и написать такой исчерпывающий отзыв.
Честно говоря, я раньше не видел трюк CROSS APPLY, поэтому я рад узнать что-то новое. Я взял свой экземпляр книги Ицика об оконных функциях, и он действительно использует предложение TOP в разделе об итоговой таблице, но не в сочетании с CROSS APPLY.

В любом случае ваш запрос выполняется быстрее. Странно, однако, что когда вы запускаете мой запрос и свой в одном пакете, в фактическом плане выполнения будет сказано, что мой запрос составляет около 7% стоимости всего пакета, а ваш — 93%. Странно.

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

Еще раз спасибо за ваш вклад и будьте осторожны!
Коэн

Я не могу определить, был ли этот ответ отправлен ранее, поэтому я отправляю его повторно. Если это подделка к моей предыдущей отправке, пожалуйста, не стесняйтесь игнорировать ее.

Во-первых, я должен сказать, что я очень ценю всех, кто находит время, чтобы собрать статью, чтобы поделиться знаниями, и большое спасибо за это, Коэн.

Ваше предложение использовать cCTE Ицика Бен-Гана (каскадные CTE, не путать с rCTE или рекурсивными CTE) верно, но в вашем последнем примере есть пара проблем с реализацией. Давайте сначала посмотрим, в чем заключаются проблемы.

Если мы обернем ваш код в SET STATISTICS, но также включим ввод-вывод, мы получим намек на первую проблему.

Таблица «Рабочий стол». Количество сканирований 5000, логических операций чтения 1543429, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения больших объектов 0, физических операций чтения больших объектов 0, операций упреждающего чтения больших объектов 0.
Таблица 'FactInternetSales'. Счетчик сканирований 1, логических операций чтения 1249, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Время выполнения SQL Server:
время ЦП = 34359 мс, истекшее время = 34368 мс.

(затронуто 785174 строки)

Если вы посмотрите на план выполнения, вы обнаружите, что «Рабочий стол» взят из «Eager Spool», который находится в базе данных TempDB. Это создает «фактическое» количество строк более 226 миллионов строк, которое вы предсказали, но нам на самом деле не нужно материализовать эти строки.Кроме того, если мы разделим 1543429 логических операций чтения на 128 для преобразования в мегабайты чтения, мы обнаружим, что более 12 058 мегабайт (более 12 гигабайт) должны были быть прочитаны из памяти. Какой бы быстрой ни была память, это очень много ненужных операций ввода-вывода.

Другие проблемы включают в себя тот факт, что ваш метод требует предварительных тайных знаний о том, какой может быть самая низкая дата в таблице, и тот факт, что вы включили ограничение. 5000 дней — это всего около 13,68 лет, и существует множество промежутков дат, которые легко превзойдут это значение. Большая проблема с обоими заключается в том, что вы не обязательно знаете, какой будет диапазон дат или какая самая низкая начальная дата.

Все это мы можем решить. Главное не материализовать 5000 строк для cteTally. В дальнейшем я также использую «Base 16» версию прекрасного кода Itzik «Base 2», просто чтобы сделать код намного короче и легче запомнить. Однако это НЕ то, откуда берется скорость. Скорость достигается за счет использования TOP в CROSS APPLY, который является предпочтительным методом реализации для cCTE Ицика (в чем вы убедитесь, как только запустите следующий код).

Вот результаты этого запуска. да. он действительно запускался. :D
Таблица 'FactInternetSales'. Количество сканирований 9, логических операций чтения 1315, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения 0 объектов.

Время выполнения SQL Server:
время ЦП = 452 мс, истекшее время = 82 мс.

(затронуто 785174 строки)

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

Обучение Oracle
Советы Oracle
Форум Oracle
Каталог классов

Удаленный администратор баз данных
Настройка Oracle
Экстренный вызов 911
Поддержка RAC
Поддержка приложений
Анализ
Дизайн
Внедрение
Поддержка Oracle

Настройка SQL
Безопасность
Oracle UNIX
Oracle Linux
Мониторинг
Удаленная поддержка
Удаленные планы
Удаленные услуги
Сервер приложений
Приложения
Формы Oracle
Портал Oracle
Обновления приложений
SQL Server
Концепции Oracle
Поддержка программного обеспечения
Удаленная поддержка
Разработка
Внедрение

Сотрудники консультантов
Цены на консалтинг
Требуется помощь!

Постеры Oracle
Книги Oracle
Скрипты Oracle
Ion
Excel-DB

Технологии Oracle и экспертных систем

Oracle – сокращение связанных строк

Связанные строки — одна из самых сложных проблем при настройке Oracle. Когда строка Oracle связана с несколькими блоками данных, дисковый ввод-вывод для извлечения строки удваивается, что приводит к серьезному снижению времени отклика.

По сути, "сцепленная" строка – это строка, охватывающая более одного блока данных. Связанные строки могут возникать при двух условиях:

1 — когда длина строки превышает размер db_block_size для базы данных. - Это происходит, когда строки имеют типы данных LONG или LONG RAW. Единственный способ исправить этот тип связанных строк — увеличить размер блока для всей базы данных.

2 — когда строка, содержащая столбец VARCHAR, расширяется с помощью инструкции SQL UPDATE. Это происходит, когда строка хранится с пустым столбцом VARCHAR, а последующие обновления вызывают расширение строк. Если в блоке данных недостаточно свободного места (как указано PCTFREE), Oracle свяжет строку с несколькими блоками данных.

Если строка представляет собой цепочку, количество операций ввода-вывода увеличится.

1 — Oracle выполнит логический ввод-вывод, чтобы получить строку в блоке данных.

2 – если строка связана, Oracle получит цепочку ROWID и выполнит еще один логический ввод-вывод для доступа к остальной части строки.

Если у нас есть тысячи строк цепочки, объем операций ввода-вывода в Oracle резко возрастет.

Лучший способ предотвратить появление связанных строк – никогда не использовать типы данных VARCHAR или VARCHAR2. Другой альтернативой является сохранение пустых значений в столбцах, где вы позже планируете обновить их реальными данными.

Обнаружение связанных строк

К счастью, обнаружить связанные строки очень просто. Если вы запустите команду ANALYZE для своей таблицы, вы заполните столбец CHAINED_ROWS представления DBA_TABLES информацией о цепочке строк. Затем вы можете запустить следующий скрипт, чтобы увидеть цепочки.

Будьте осторожны, если вы используете optimzer_mode=choose (по умолчанию) и АНАЛИЗИРУЕТЕ таблицы. Это может изменить ваши запросы для использования режима оптимизатора на основе затрат.

Давайте попробуем эту симуляцию:

катушка chain.lst;
установить страницы 9999;

столбец с1 заголовок "Владелец" формата а9;
колонка с2 заголовок "Таблица" формата а12;
столбец с3 заголовок "PCTFREE" формат 99;
колонка с4 заголовок "PCTUSED" формат 99;
столбец c5, заголовок "средняя строка", формат 99 999;
колонка c6 заголовок "Строки" формат 999,999,999;
колонка с7 заголовок "Цепи" формат 999,999,999;
колонка c8 заголовок "Pct" формат .99;

убрать курс;
выберите "Таблицы со связанными строками и без столбцов RAW". из двойного;
включить заголовок;

выберите
владельца c1,
имя_таблицы c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
из dba_tables,
где
владелец не в ('SYS', 'SYSTEM')
и
table_name не в
(выберите table_name из dba_tab_columns,
где
data_type НЕ в ('RAW', 'LONG RAW')
)
и
chain_cnt > 0
/>упорядочить по описанию chain_cnt
;

Вот результат

Владелец Таблица PCTFREE PCTUSED avg row Rows Chains Pct
--------- ------------ ------- ------ - ------- ------------ ------------ ----
SAPR3 ZG_TAB 10 40 80 5 003 1 487 .30 < br />САПР3 ZMM 10 40 422 18 309 509 ,03
САПР3 Z_Z_TBLS 10 40 43 458 53 ,12
САПР3 УСР03 10 40 101 327 46 ,14
САПР3 Z_BURL 10 40 116 25 1,802 1,802 01
SAPR3 ZGO_CITY 10 40 56 1 133 10 .01

Выбрано 6 строк.

Итак, здесь мы видим таблицы со связанными строками. Обратите внимание, что скрипт пропускает таблицы с типами данных RAW или LONG RAW, поскольку они обычно образуют цепочку, если длина строки превышает размер блока.

К счастью, средство довольно простое. Отдельные таблицы можно реорганизовать с помощью экспорта-импорта или с помощью CTAS для копирования таблицы. После этого все связанные ряды соберутся вместе. Однако всегда помните об устранении причины связанных строк:

Теперь давайте взглянем на таблицы свободных списков и последствия дисбаланса свободных списков для производительности.

Если вам нравится настройка Oracle, прочтите книгу "Настройка Oracle: исчерпывающий справочник" с 950 страницами советов по настройке и сценариев.

Вы можете купить его напрямую у издателя со скидкой 30 % и получить мгновенный доступ к хранилищу кода сценариев настройки Oracle. Щелкните здесь, чтобы просмотреть другие книги Дональда К. Берлесона.

Бурлесон — американская команда

Примечание. Эта документация по Oracle была создана в качестве справочника по поддержке и обучению Oracle для использования нашими специалистами-консультантами по настройке производительности администраторов баз данных. Не стесняйтесь задавать вопросы на нашем форуме Oracle.

Проверьте опыт! Любой, кто рассматривает возможность использования услуг эксперта службы поддержки Oracle, должен самостоятельно проверить свои полномочия и опыт, а не полагаться на рекламу и самопровозглашенный опыт. Все законные эксперты Oracle публикуют свои квалификации Oracle.

Ошибки? Технология Oracle меняется, и мы стараемся обновлять нашу информацию о поддержке BC Oracle. Если вы обнаружите ошибку или у вас есть предложение по улучшению нашего контента, мы будем признательны за ваш отзыв. Просто электронная почта:

и укажите URL-адрес страницы.

Burleson Consulting

Оракул поддержки баз данных

Читайте также: