Как узнать количество строк в таблице oracle

Обновлено: 03.07.2024

Шесть тысяч слов помогут вам понять статистику Oracle и план выполнения

Источник | JiekeXu Road (ID: JiekeXu_IT)

Свяжитесь с авторизацией для перепечатки | (WeChat ID: xxq1426321293)

Всем привет, я JiekuXu, я очень рад снова встретиться с вами, поделитесь этим сегодня Статистика Oracle и план выполнения. Эта статья была впервые опубликована в общедоступном аккаунте WeChat [JiekeXu Road], пожалуйста, нажмите на синюю букву выше, чтобы подписаться на меня!

Предисловие

Несколько дней назад я получил приглашение от г-на Ян Цзяньжун, автора «Рабочих заметок Oracle DBA» и «MySQL DBA Work Notes» и соучредителя сообщества DBAplus на WeChat, и сказал, что поделится технической, на рабочем месте и идеи в его группе QQ От таких статей я сразу почувствовал искренность и ужас.Мне было честью, что обмен опытом - это тоже процесс обучения, поэтому я с радостью согласился на приглашение Учителя Яна. Думая, что вы также изучаете в последнее время вещи, связанные с оптимизацией, вы можете обобщить и поделиться во время обучения.Если в статье есть другие недостатки, сообщите об этом.

1. Статистика

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

Использоватьgather_stats_job Автоматический сбор данных создается автоматически, когда база данных создается и управляется планировщиком. Он собирает статистику для всех объектов в базе данных, для которых отсутствует или устаревшая статистика оптимизатора.

Использоватьdbms_stats Пакет вручную собирает системную статистику.

Просмотр статуса задач автоматического сбора статистики

Имя задачи автоматического сбора статистической информации в oracle 11g - автоматический сбор статистики оптимизатора. Окно времени выполнения по умолчанию для автоматических задач в 11g (введение в временное окно Oracle):

С понедельника по пятницу начинается в 22:00 и заканчивается в 14:00.

Выходные - шесть часов утра и продолжаются 20 часов.

В этот период нагрузка на сервер, как правило, относительно невелика. Ресурсы сервера можно оставить для сбора статистической информации, а сбор статистической информации более ресурсоемкий. Это можно увидеть в следующем утверждении:

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

- Остановить и запустить отдельную задачу (то есть остановить задачу в определенный день)

Связанные просмотры:

Вот и все, что касается автоматического сбора. Для получения дополнительной информации проверьте официальные документы или Doc ID 1300313.1
How to Create an Own Maintenance Window for Autotask Jobs in 11g (Doc ID1300313.1) . Позволь мне поговорить об этом dbms_stats Связанный с пакетом.

dbms_stats

Пакет DBMS_STATS в основном предоставляет методы для сбора (сбора), удаления (удаления), экспорта (экспорта), импорта (импорта) и изменения (установки) статистической информации. Говоря оdbms_stats Тогда нужно поговорить о команде анализа.

Разница между dbms_stats и анализировать: Dbms_stats - это пакет, используемый для сбора статистики в Oracle9i и более поздних версиях.Хотя команда анализа была всегда доступна, больше не рекомендуется использовать команду анализа для сбора статистики. Вместо этого используйте dbms_stats. Между ними существует большая разница. Dbms_stats может правильно собирать статистику таблицы разделов, что означает, что он может собирать глобальную статистику, в то время как анализ может собирать статистику только объектов нижнего уровня, а затем выводить и суммировать более высокие -уровневые объекты.Статистика: если таблица разделов собирает только статистику разделов, то суммирует статистику всех разделов, чтобы получить статистику на уровне таблицы. По сути, Analyze устарел. Он использовался более семи или восьми лет назад. Oracle и эксперты рекомендуют пакет dbms_stats.

Пакет dbms_stats может собирать статистику о базах данных, словарях данных, индексах, таблицах и т. Д.

dbms_stats.gather_table_ststs параметр

1、 cascade:

true: указывает, что статистика собирается вместе с индексом при подсчете

2、 no_invalidate:

true: после сбора статистики исходный план выполнения не становится недействительным.

false: после сбора статистики исходный план выполнения становится недействительным.

По умолчанию DBMS_STATS.AUTO_INVALIDATE, Oracle решает, когда сделать недействительным план выполнения.

3、 method_opt:

FOR ALL [INDEXED | HIDDEN] COLUMNS[size_clause]

FOR COLUMNS [size clause] column[size_clause] [,column [size_clause]. ]

Когда данные поля распределены неравномерно, создайте гистограмму (гистограмму):

Статистика гистограммы: значения столбца индексного поля для создания статистики

Многоколоночная статистика: статистика создания столбца составного индекса

Статистика выражений: создание статистики по клавишам индекса функций

Примечание. Случай двух приведенных выше строк отличается, при использовании exec его нужно записать в одну строку. Анонимные блоки могут быть записаны в несколько строк. Кроме того, значение Size равно 1-254, но после 12c значение становится 1-2048.

Просмотр статистики:

Примечание. Обычные пользователи могут проверить user_tab_col_statistics, а пользователи DBA могут проверить dba_tab_col_statistics. Конечно, вы также можете использовать dba_tab_statistics для просмотра времени последнего сбора статистической информации.

Вот и все статистические данные, давайте перейдем к сегодняшней теме: План реализации 。

2. План реализации

План выполнения: описание пути доступа или процесса выполнения оператора SQL в базе данных.Oracle через оптимизатор Optimizer (Оптимизатор здесь относится к оптимизатору на основе затрат [Cost Based Optimizer, CBO]) для поиска оптимального плана выполнения для выполнения. Затем мы сначала понимаем, как выполняется следующий SQL: обычно он проходит через три этапа: синтаксический анализ (Parse), выполнение (Execute) и получение (Fetch), которые выполняются различными компонентами Oracle. Подробная информация должна быть быть объясненным из архитектуры Oracle. Я не буду говорить об этом здесь.

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

В-третьих, просмотрите план выполнения

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

Функция SQL COUNT () возвращает количество строк в таблице, удовлетворяющих критериям, указанным в предложении WHERE. Он устанавливает количество строк или ненулевых значений столбцов.
COUNT () возвращает 0, если не было совпадающих строк.

Синтаксис:

Приведенный выше синтаксис является общим стандартным синтаксисом SQL 2003 ANSI. Это помогает понять, как используется функция SQL COUNT (). Но разные поставщики баз данных могут по-разному применять функцию COUNT ().

Ниже вы можете видеть, что MySQL, PostgreSQL и Microsoft SQL Server используют тот же синтаксис, что и приведенный выше. Но DB2 и Oracle немного отличаются.

В целом, вы можете использовать * или ALL или DISTINCT или какое-либо выражение вместе с COUNT, чтобы СЧИТАТЬ число строк по какому-либо условию или по всем строкам, в зависимости от аргументов, которые вы используете вместе с функцией COUNT ().

Поддержка СУБД: функция COUNT ()

СУБД	команда
MySQL	поддержанный
PostgreSQL	поддержанный
SQL Server	поддержанный
оракул	поддержанный

Синтаксис DB2 и Oracle:

Параметры:

название	Описание
ВСЕ	Относится ко всем значениям. ALL возвращает количество ненулевых значений.
DISTINCT	Игнорируемые повторяющиеся значения и COUNT возвращает количество уникальных ненулевых значений.
выражение	Выражение состоит из одной константы, переменной, скалярной функции или имени столбца, а также может быть фрагментами запроса SQL, которые сравнивают значения с другими значениями. Выражение любого типа, кроме текста или изображения. Агрегатные функции и подзапросы не допускаются.
*	СЧИТЫВАЕТ все строки в целевой таблице независимо от того, содержат ли они значения NULL.

Синтаксическая диаграмма - функция COUNT ()

На последующих страницах мы обсудили, как применять COUNT () с различными предложениями SQL. Для этих приложений мы использовали Oracle 10g Express Edition.

Важная вещь о функции COUNT ():

Когда * используется для COUNT (), все записи (строки) считаются COUNTed, если некоторое содержимое NULL, но COUNT (column_name) не считает COUNT запись, если ее поле имеет значение NULL. Смотрите следующие примеры:

SQL COUNT строки в таблице

В следующем примере используется символ звездочки (*), за которым следует SQL COUNT (), который указывает все строки таблицы, даже если есть какое-либо значение NULL.

Пример таблицы: заказы

Код SQL:

Иллюстрированная презентация:

Выберите COUNT (*) из нескольких таблиц

Следующий запрос COUNT количество строк из двух разных таблиц (здесь мы используем сотрудников и отделов) с помощью команды COUNT (*).

Код SQL:

SQL COUNT () с именем столбца

В этом примере функция SQL COUNT () исключает значения NULL для определенного столбца, если столбец указан в качестве аргумента в скобках функции COUNT.

Образец таблицы: listofitem

Чтобы получить количество строк в таблице listofitem со следующим условием:

1. COUNT количество строк для столбца «coname»

можно использовать следующий оператор SQL:

Код SQL:

Объясните:

Приведенный выше оператор СЧИТАЕТ те строки для столбца coname, которые не равны NULL.

Строки SQL COUNT с определенным пользователем заголовком столбца

Получить количество строк в таблице «заказов» при следующем условии -

1. результат должен отображаться с заголовком «Количество рядов»,

можно использовать следующий оператор SQL:

Код SQL:

SQL COUNT () с предложением where

Предложение WHERE может использоваться вместе с функцией SQL COUNT (). выбрать конкретные записи из таблицы в соответствии с заданным условием.

Пример:

Пример таблицы: заказы

Чтобы получить количество строк в таблице «заказов» при следующем условии -

1. ord_amount против заказа более 1500,

можно использовать следующий оператор SQL:

Применение функции COUNT ()

На странице COUNT HAVING рассказывается, как применить функцию COUNT с предложением HAVING, а также с HAVING и GROUP BY.

Примечание. Выводы указанного оператора SQL, показанного здесь, взяты с использованием Oracle Database 10g Express Edition.

Вот слайд-презентация всех агрегатных функций.

Упражнения по SQL

Упражнения по SQL, практика, решение
SQL Получить данные из таблиц [33 Упражнения]
Булевы и реляционные операторы SQL [12 упражнений]
Подстановочные знаки SQL и специальные операторы [22 упражнения]
Агрегатные функции SQL [25 упражнений]
Вывод запроса форматирования SQL [10 упражнений]
SQL-запросы к нескольким таблицам [7 упражнений]
ФИЛЬТРАЦИЯ И СОРТИРОВКА в базе данных персонала [38 упражнений]
SQL СОЕДИНЯЕТ
- SQL СОЕДИНЯЕТСЯ [29 упражнений]
- SQL присоединяется к базе данных HR [27 упражнений]
- ПОДПИСИ SQL [39 упражнений]
- SQL ПОДПИСИ по базе данных HR [55 упражнений]
- ОСНОВНЫЕ запросы к базе данных фильмов [10 упражнений]
- ПОДПИСКИ на фильм База данных [16 упражнений]
- ПРИСОЕДИНЯЕТСЯ к базе данных фильма [24 упражнения]
- Вступление
- ОСНОВНЫЕ запросы по футболу базы данных [29 упражнений]
- ПОДПИСКИ по футбольной базе данных [33 упражнения]
- ПРИСОЕДИНЯЕТСЯ к запросам по футбольной базе данных [61 упражнений]
- Вступление
- ОСНОВНЫЕ, ПОДПИСИ И СОЕДИНЕНИЯ [39 упражнений]
- ОСНОВНЫЕ запросы к базе данных сотрудников [115 упражнений]
- БРОНИРОВАНИЕ на сотрудника База данных [77 Упражнения]
Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.

Предыдущая: Агрегатные функции
Следующая: COUNT с отличным

похоже, что count (*) медленнее, чем NUM_ROWS. Могут ли эксперты в этой области пролить свет на это?

по данным документация NUM_ROWS является "количество строк в таблице", поэтому я вижу, как это может быть запутанным. Однако между этими двумя методами существует существенное различие.

этот запрос выбирает количество строк в MY_TABLE из системного представления. Это данные, которые Oracle ранее собирала и хранила.

этот запрос подсчитывает текущее количество строк в MY_TABLE

By определение они являются разностными частями данных. Есть две дополнительные части информации, которые вам нужны о NUM_ROWS.

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

столбцы, отмеченные звездочкой ( * ), заполняются только при сборе статистика по таблице с оператором ANALYZE или DBMS_STATS пакет.

Это означает, что если вы собрались статистика по таблице, то этот столбец не будет иметь никаких данных.

статистика, собранная в 11g+ С по умолчанию estimate_percent , или со 100% оценкой, вернет точное число для этого момента времени. Но статистика, собранная до 11g, или с пользовательским estimate_percent менее 100%, использует динамическую выборку и может быть неправильным. Если вы соберете 99,999%, одна строка может быть пропущена, что в свою очередь означает, что вы получите ответ неправильный.

если ваш стол никогда обновлено, тогда, безусловно, можно использовать ALL_TABLES.NUM_ROWS, чтобы узнать количество строк в таблице. , и это большой, однако, если какой-либо процесс вставляет или удаляет строки из вашей таблицы, это будет в лучшем случае хорошее приближение и в зависимости от того, собирает ли ваша база данных статистику автоматически, может быть ужасно неправильным.

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

1) Получение NUM_ROWS из приведенного ниже запроса означает, что нет.значение строк обновляется на DBMS_STATS. Таким образом, он не содержит текущего количества строк в таблице, но аппроксимация, вычисленная при последнем запуске DBMS_STATS.

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

Давайте начнем с простого… Эти запросы отличаются чем-то друг от друга с точки зрения конечного результата?

Большинство отвечали: «Нет».

Реже старались долее детально формировать ответ: «Запросы вернут идентичный результат, но COUNT вернет значение типа INT, а COUNT_BIG – тип BIGINT».

Если проанализировать план выполнения, то можно заметить различия, которые многие упускают из вида. При использовании COUNT на плане будет операция Compute Scalar:

Если посмотреть в свойства оператора, то мы увидим там:

Это происходит потому, что при вызове COUNT неявно используется COUNT_BIG после чего результат преобразуется в INT.

Не сказал бы, что существенно, но преобразования типов увеличивает нагрузку на процессор. Многие, конечно, могут сказать, что этот оператор ничего не стоит при выполнении, но нужно отметить простой факт – SQL Server очень часто недооценивает Compute Scalar операторы.

Еще я знаю людей, которые любят использовать SUM вместо COUNT:

Такой вариант примерно равнозначен COUNT. Мы также получим лишний Compute Scalar на плане выполнения:

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

Если использовать запросы выше, то чтобы посчитать количество записей SQL Server необходимо выполнить Full Index Scan (или Full Table Scan если таблица является кучей). В любом случае, эти операции далеко не самые быстрые. Лучше всего для получения количества записей использовать системные представления: sys.dm_db_partition_stats или sys.partitions (есть еще sysindexes, но оставлен для обратной совместимости с SQL Server 2000).

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

На AdventureWorks преимущество от применения системных представлений явно не проявляется:

Время выполнения на секционированной таблице с 30 миллионами записей:

В случае если нужно проверить наличие записей в таблице, то использование метаданных как было показано выше не даст особых преимуществ…

И на практике будет даже капельку медленнее, поскольку SQL Server генерирует более сложный план выполнения для выборки из метаданных.

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

Уж очень много я выдал дифирамбов, что системные представления такие хорошие. Однако, при работе с ними нас могут подстерегать «приятные» неожиданности.

Помнится, был такой веселый баг, когда при миграции с SQL Server 2000 на 2005 некоторые системные представления некорректно обновлялись. Особо везучим людям, в таком случае, из метаданных возвращались неверные значения о количестве записей в таблицах. Лечилось это все командой DBCC UPDATEUSAGE.

Вместе с SQL Server 2005 SP1 этот баг исправили и все бы ничего… Но подобную ситуацию я наблюдал еще один раз, когда восстановил бекап с SQL Server 2005 SP4 на SQL Server 2012 SP2. Воспроизвести проблему на реальном окружении увы не смогу, поэтому немного обманув оптимизатор:

расскажу на простом примере.

Самый безобидный запрос начал выполняться дольше чем обычно:

Посмотрел на план запроса и увидел там явно неадекватное значение Estimated number of rows:

Заглянул в статистику по кластерному индексу:

Все было в норме:

Но в системных представления о которых мы говорили ранее:

В запросе не было предикатов для фильтрации и оптимизатор выбрал Full Index Scan. При Full Index/Table Scan ожидаемое количество строк оптимизатор не берет из статистики, а обращается к метаданным (точно не уверен всегда ли это происходит).

Не секрет, что на основе Estimated number of rows SQL Server генерирует план выполнения и вычисляет сколько нужно памяти чтобы его выполнить. Если оценка будет неверной, то может быть выделено больше памяти на выполнение запроса, чем нужно на самом деле.

Вот к чему приводит неверная оценка количества строк:

Проблема решилась достаточно просто:

После рекомпиляции запроса все пришло в норму:

Если системные представления уже не кажутся «спасительной палочкой», то какие варианты у нас остаются? Можно делать все по-старинке:

Но при интенсивной вставке в таблицу я бы не доверял результатам. «Волшебный» хинт NOLOCK тем более не гарантирует правильного значения:

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

И что мы получаем в итоге… монопольную блокировку таблицы на период выполнении запроса. И тут каждый должен решать сам, что ему лучше использовать. Мой выбор — метаданные.

Еще интереснее, когда нужно быстро подсчитать число строк по условию:

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

Для этих запросов оптимизатор будет генерировать идентичный план на основе кластерного индекса вьюхи:

План выполнения с индексным представлением и без:

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

Все тестировалось на SQL Server 2012 SP3 (11.00.6020).

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

Если нужно быстро подсчитать количество строк в разрезе какого-то поля или по условию — то я стараюсь использовать индексированные представления либо фильтрованные индексы. Все зависит от ситуации.

Когда таблица маленькая или вопросы с производительностью не стоят так остро, то проще уж действительно по-старинке написать SELECT COUNT(*)…

Если хотите поделиться этой статьей с англоязычной аудиторией:
What is the fastest way to calculate the record COUNT?

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