таблица dual
Таблица dual очень проста. Она состоит из одного поля и содержит одно значение.
SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY VARCHAR2(1) SQL> select * from dual; D - X
Однако это значение никого не интересует. Таблица используется для получения данных, не имеющих отношения к dual. Например:
SQL> select user from dual; USER ------------------------------ SCOTT
Чтобы получить значение user, мы с таким же успехом могли бы выполнить запрос
select user from emp where rownumDual используется для таких выборок просто потому, что она всегда существует. Это объект под пользователем sys и он появляется при создании базы. И никто (за исключением любопытных DBA) не может его изменить. Однако, если задаться целью поиграться с dual (исключительно на тестовой базе), то можно обнаружить интересные вещи:
SQL> connect / as sysdba; Connected. SQL> insert into dual values('X'); 1 row created. SQL> insert into dual values('X'); 1 row created. SQL> select count(*) from dual; COUNT(*) ---------- 3 SQL> select * from dual; D - XНесмотря на то, что dual теперь содержит 3 строки, select выбирает только одну. Что это -- у базы "поехала крыша" от того, что мы внесли изменения в словарь (ведь формально dual -- часть словаря)?
Строго говоря, нет.SQL> delete from dual; 1 row deleted. SQL> select * from dual; D - X SQL> select count(*) from dual; COUNT(*) ---------- 2Мы хотели удалить все строки из dual, но удалилась только одна.
Объяснение этому следующее: оптимизатор подразумевает, что таблица dual в любых условиях будет содержать только одну строку.
Впрочем, в каком-то смысле, dual не совсем обычная таблица. Вот пример:
SQL> select * from dual; D - X 1 row selected. SQL> . ; Statement processed. SQL> select * from dual; ADDR INDX INST_ID D -------- ---------- ---------- - 26683298 0 1 X 1 row selected.Что за магическая команда была выполнена, которая привела к таким изменениям в таблице dual?
Ответ: alter database close.
Дело здесь вот в чём: RMAN'у нужен доступ к dual даже когда база закрыта, поэтому при закрытии базы dual остаётся доступна, но с изменённой структурой и содержимым.
В конце нашей коротенькой заметки хочется повторить следующее: помните, что таблица dual -- часть системного словаря и Вы ни в коем случае не должны её каким-либо образом изменять на базе, где работают пользователи.
Примеры работы с датами в Oracle
Блин, что же может быть проще?
Почему я все время забываю эту команду.select TO_DATE('16/10/2016', 'dd/mm/yyyy') from dual;Вычисления текущего дня, месяца и т.д. с помощью SQL
Текущая неделя
-- Сегодня select trunc (SYSDATE) from dual;-- Вчера select trunc (SYSDATE-1) from dual;-- Завтра select trunc (SYSDATE+1) from dual;-- Первый день недели select trunc(SYSDATE, 'DAY') from dual; -- Output: 30.05.2016-- Последний день недели select trunc(SYSDATE, 'DAY')+6 from dual; -- Output: 05.06.2016Прошедшая неделя
-- Первый день прошедшей недели select trunc(SYSDATE, 'DAY') -7 from dual; -- Output: 23.05.2016-- Последний день прошедшей недели select trunc(SYSDATE, 'DAY')-1 from dual; -- Output: 29.05.2016Следующая неделя
-- Первый день следующей недели select trunc(SYSDATE, 'DAY') +7 from dual; -- Output: 06.06.2016-- Последний день следующей недели select trunc(SYSDATE, 'DAY') +13 from dual; -- Output: 12.06.2016Текущий месяц
-- Первый день месяца select trunc (SYSDATE, 'MM') from dual;-- Последний день месяца select trunc (last_day(sysdate)) from dual;Прошедший месяц
-- Первый день прошлого месяца select trunc(ADD_MONTHS(SYSDATE, -1), 'MM') from dual;-- Последний день прошлого месяца select trunc (SYSDATE, 'MM') -1 from dual;Следующий месяц
-- Первый день следующего месяца select trunc (last_day(sysdate)) +1 from dual;-- Последний день следующего месяца select trunc(LAST_DAY(ADD_MONTHS(SYSDATE, 1))) from dual;Текущий квартал
-- Первый день квартала select trunc (SYSDATE, 'Q') from dual; -- Последний день квартала select add_months(trunc(sysdate,'q'),3)-1 from dual;Прошлый квартал
-- Первый день прошлого квартала select trunc(add_months(sysdate,-3),'q') from dual; -- Последний день прошлого квартала select add_months(trunc(add_months(sysdate,-3),'q'),3)-1 from dual;Следующий квартал
-- Первый день следующего квартала select add_months(trunc(sysdate,'q'),3) from dual; -- Последний день следующего квартала select add_months(trunc(add_months(sysdate,3),'q'),3)-1 from dual;Текущий год
-- Первый день года select trunc (SYSDATE, 'Y') from dual; -- Последний день года select ADD_MONTHS(trunc (SYSDATE, 'YEAR'),12)-1 FROM DUAL;Прошедший год
-- Первый день прошлого года select ADD_MONTHS (trunc (SYSDATE, 'YEAR'), -12) FROM DUAL; -- Последний день прошлого года select ADD_MONTHS (trunc (SYSDATE, 'YEAR'), -1 ) +30 FROM DUAL;Следующий год
-- Первый день следующего года select ADD_MONTHS(trunc (SYSDATE, 'YEAR'),12) FROM DUAL; -- Последний день следующего года select ADD_MONTHS(trunc (SYSDATE, 'YEAR'),24)-1 FROM DUAL;Нужно будет упростить вычисления, если это возможно.
Я писал на лету и сам эти вычисления не использую. Т.е. допускаю, что где-то закралась ошибка. Если что, поправлю.Tags: Oracle, sql, вычисление дат
PL/SQL
tags: Администрирование Oracle DataBase || SQL & PL/SQL
Исходные коды проекта хранятся на github. Можете заводить Issue и Discussions, при необходимости.
Чтобы задать вопрос, добавить свои знания, исправить ошибки и неточности, пишите в телеграм чате.Интересная особенность Oracle SQL
Предлагаю Вашему вниманию перевод интересного на мой взгляд поста про неочевидную особенность Oracle.
Создаем таблицу FRUITS.
CREATE TABLE fruits (fruit_name varchar2(30));
Заполняем таблицу данными: 5 бананов, 7 яблок, 3 черники.
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'banana' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'apple' );
INSERT INTO fruits VALUES ( 'blueberry' );
INSERT INTO fruits VALUES ( 'blueberry' );
INSERT INTO fruits VALUES ( 'blueberry' );Чтобы знать сколько раз запускалась наша функция, создаем сиквенс.
CREATE SEQUENCE seq START WITH 1;
Напишем функцию, которая возвращает цвет фрукта (входной параметр) и инкрементирует сиквенс, как индикатор своей работы.
CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)
RETURN varchar2
IS
l_num number;
BEGIN
SELECT seq.nextval INTO l_num FROM dual;CASE p_fruit_name
WHEN 'banana' THEN RETURN 'yellow' ;
WHEN 'apple' THEN RETURN 'green' ;
WHEN 'blueberry' THEN RETURN 'blue' ;
END CASE ;
END get_colour;
/Узнаем цвет каждого фрукта в нашей таблице
SELECT get_colour(fruit_name) FROM fruits;
Вопрос: Что вернет этот запрос?
SELECT seq.nextval FROM dual;
Так, в таблице 15 записей, значит функция будет вызвана 15 раз. И поскольку мы выполняем seq.nextval, то можем ожидать, что результат будет 16. Давайте сбросим сиквенс для проведения еще одного эксперимента
DROP SEQUENCE seq;
CREATE SEQUENCE seq START WITH 1;
И опять используем нашу функцию, чтобы получить цвет фруктов в таблице, но на этот раз обернем ее выражением SELECT FROM dual.
SELECT ( SELECT get_colour(fruit_name) FROM dual)
FROM fruits;Вопрос: что на этот раз вернет запрос?
SELECT seq.nextval FROM dual;
Можно предположить, что как и в предыдущий раз функция будет выполнена 15 раз и запрос опять вернет 16. Однако, это не так.
Мы обнаруживаем, что возвращается число 4, а это означает, что функция была вызвана всего 3 раза.
Что же произошло?
Почему функция выполняется всего 3 раза, хотя мы передаем ей каждую запись таблицы, а это 15 фруктов, и при этом в целом запрос возвращает верные данные?
Ответ заключается в механизме кеширования результатов подзапросов — Scalar Subquery Caching.
Результат запроса SELECT some_function(x) FROM dual будет сохранен для каждого значения параметра x.
Таким образом, фактически функция будет выполняться только для разных входных параметров, а т.к. у нас всего три разных фрукта (банан, яблоко, черника),
то и функция будет выполнена всего три раза.
А здесь здесь Том Кайт рассказывает об этом.Прим. переводчика.
Для полноты картины следует упомянуть о возможности объявить эту функцию как DETERMINISTIC, тогда и в запросе
SELECT get_colour(fruit_name) FROM fruits; она будет выполнена всего 3 раза.ДВОЙНАЯ таблица - DUAL table
Таблица DUAL - это специальная таблица с одной строкой и одним столбцом таблица, присутствующая по умолчанию в Oracle и других установках базы данных.. В Oracle таблица имеет единственный столбец VARCHAR2 (1) с именем DUMMY, имеющий значение «X». Он подходит для использования при выборе псевдостолбца, такого как SYSDATE или USER.
- 1 Пример использования
- 2 История
- 3 Оптимизация
- 4 В других системах баз данных
- 5 Примечания
Пример использования
Oracle SQL требует наличия предложения FROM, но для некоторых запросов не требуются таблицы - в этих случаях можно использовать DUAL.
ВЫБРАТЬ 1 + 1 ИЗ двойного; ВЫБЕРИТЕ 1 ИЗ двойного; ВЫБРАТЬ ПОЛЬЗОВАТЕЛЯ ИЗ двойного; ВЫБРАТЬ SYSDATE ИЗ двойного; ВЫБРАТЬ * ИЗ двойного;
История
Чарльз Вайс объясняет, почему он создал DUAL:
Я создал таблицу DUAL как базовый объект в словаре данных Oracle. Он никогда не предназначался для того, чтобы его видели, а вместо этого использовался внутри представления, которое, как ожидалось, должно было быть запрошено. Идея заключалась в том, что вы можете выполнить JOIN с таблицей DUAL и создать две строки в результате для каждой строки в вашей таблице. Затем, используя GROUP BY, результирующее соединение можно суммировать, чтобы показать объем памяти для экстента DATA и экстента (ов) INDEX. Имя DUAL казалось подходящим для процесса создания пары строк из одной.
Оптимизация
Начиная с версии 1 10g, Oracle больше не выполняет физический или логический ввод-вывод в таблице DUAL., хотя таблица все еще существует.
DUAL легко доступен для всех пользователей в базе данных.
В других системах баз данных
Некоторые другие базы данных (включая Microsoft SQL Server, MySQL, PostgreSQL, SQLite и Teradata) позволяют полностью опустить предложение FROM, если таблица не требуется. Это избавляет от необходимости использовать какой-либо фиктивный стол.
- Firebird имеет однострочную системную таблицу RDB $ DATABASE, которая используется так же, как Oracle DUAL, хотя и имеет собственное значение.
- IBM DB2 имеет представление, что разрешает DUAL при использовании совместимости с Oracle. В нем также есть таблица с именем sysibm.sysdummy1, которая имеет свойства, аналогичные свойствам таблицы Oracle DUAL.
- Informix : Informix версии 11.50 и более поздних версий содержит таблицу с именем sysmaster: "informix".sysdual с та же функциональность, но с более подробным названием. Вы можете использовать CREATE PUBLIC SYNONYM dual FOR sysmaster: "informix".sysdual , чтобы создать имя dual в текущей базе данных с той же функциональностью.
- Microsoft Access : Таблица с именем DUAL может быть создана, и ограничение одной строки может быть применено через ADO (запрос UNION без таблицы в MS Access )
- Microsoft SQL Server : SQL Server не требует фиктивной таблицы. Запросы типа ' select 1 + 1 'может быть запущен без предложения / имени таблицы «from».
- MySQL позволяет указывать DUAL в виде таблицы в запросах, которым не нужны данные из каких-либо таблиц. Он подходит для использования в выбор функции результата, такой как SYSDATE () или USER (), хотя это и не обязательно.
- PostgreSQL : для упрощения портирования можно добавить DUAL-view от Oracle.
- Snowflake : DUAL поддерживается, но явно не задокументирован. Он появляется в примере SQL для других операций в документации.
- SQLite : VIEW с именем " dual ", который работает так же, как таблица Oracle" dual ", может быть создан как fo llows: CREATE VIEW dual AS SELECT 'x' AS dummy;
- SAP HANA имеет таблицу с именем DUMMY, которая работает так же, как "двойная" таблица Oracle.
- База данных Teradata делает не требуется фиктивный стол. Такие запросы, как 'select 1 + 1', можно запускать без предложения "from" / имени таблицы.