Скахин Алексей / pihel: 1Z0-047

Продолжение. Новые вопросы с сентября 2014

Нельзя писать алиас у колонки в select по которой происходит join в using (или * с алиасом таблицы). Это же относится к natural соединениям.
```
SELECT key
FROM A FULL JOIN B
USING (key)
```
Вставка в несколько таблиц
* INSERT ALL - может быть без WHEN
* INSERT ALL - поумолчанию
* INSERT FIRST - при совпадении WHEN, остальные не просматриваются
* INSERT ALL - просматриваются все ветвления WHEN
* Может быть несколько INTO в одном WHEN
* Нельзя использовать sequence в SELECT запросе множественном INSERT (но можно в VALUES)
```
INSERT [ALL|FIRST]
 WHEN <..> THEN
  INTO A (a1,a2) VALUES(b1,b2)
 WHEN <..> THEN
  INTO C (c1,c2) VALUES(b2,b1)
 ELSE
  INTO D (d1,d2) VALUES(b1,b2)
SELECT
 b1, b2, b3 FROM B
```
MERGE dml операция:
* Преимущество - один проход
* D/I/U - возможные операции
* WHERE можно писать только по таблице из USING
```
MERGE INTO <..>
USING A
  <..>
WHEN MATHED THEN UPDATE SET <..>
[WHERE <..>]
```
WHERE выполняется до SET.
Это значит, что в SET можно писать невалидные значения, если WHERE их отсечет.
AND имеет больший приоритет, чем OR
Арифметические операции имеют больший приоритет, чем SQL операторы сравнения
FROM необязателен в DELETE
CLOB, BLOB, TIMESTAMP WITH TIMEZONE (далее TS WITH TZ) - нельзя использовать в PK
, а TS WITH LOCAL TZ - можно
Размеры полей:
* CHAR -> CHAR(1)
* INTERVAL DAY -> INTERVAL DAY(2)
* NUMBER(2,-3) - округлит! (не обрежет) до 3 знака перед запятой
* VARCHAR - обязательно указывать размерность
* VARCHAR(1 CHAR), если база в cp1251 - это 1 байт, если UTF8 , то от 2 байт
UNION, MINUS, INTERSECT - set операторы
Сортировка в них возможна только по алиасу или позиции в самом конце.
Допускается только двойное вложение агрегирующих функций (3ий нельзя)
MAX(SUM(<..>))
Типы соединений:
* cartesian -> cross
* nonequijoin -> < , >
* full -> outer
* inner -> natural
FK можно создавать по полям:
* Одного типа, но разных разных размерностей
* FK может указывать на PK и на UNIQUE index поля

UNUSED поле - поле аналогично дропнутому, но физически из таблицы не вычищено:

SET UNUSED COUMN <..>; --помечаем неиспользуемым
ALTER TABLE T DROP UNUSED COUMNS; --физическое удаление неиспользуемых колонок

По V$ представлениям нельзя делать запросы, т.к. их структура может меняться со временем.
Если же в этом есть необходимость, то нужно сперва создать копию.
Нет отдельных прав на создание FK, constraints, index
Такие права даются вместа с правами на таблицу.
Non-schema objects - это users, roles, public synonims
Допускается сравнение systimestamp и date типов
Если в CREATE SEQUENCE задана опция CYCLE, то MAXVALUE может быть отрицательным (т.к. START тоже может быть отрицательным)
Нельзя создавать NOT NULL constraint отдельно от создания таблицы (alter)
UNIQUE в SELECT аналогичен DISTINCT
ROWNUM нельзя использовать с * без алиса
```
SELECT ROWNUM, * --нельзя
```
* NULL поля при ASC сортировке идут последними
* при DESC первыми
Можно указать принудительно:
```
ORDER BY <..> nulls last[first]
```
При выборке из TS WITH LOCAL TZ к дате вставки добавляется разность между зоной вставки и зоной выборки.
MONTHS_BETWEEN(большая дата, меньшая дата) = Дробное чило
Если наоборот, то отрицательное число.
MEDIAN (среднее по порядку) и AVG не приминают в параметре строку
Все групповые (агрегирующие) функции принимают только NOT NULL значения.
```
RANK( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )
```
Ранк группы, если записей с одним ранком несколько, то им проставляется один номер.
HAVING может использоваться только в SELECT после WHERE (можно даже без GROUP BY)
* > ALL (<..>) - TRUE, если все строки подзапроса больше, или подзапрос ничего не! вернул
* > SOME (<..>) - TRUE, если хотябы одна строка подзапроса больше, FALSE - подрапрос ничего не вернул
Нельзя делать сложные (составные) столбцы в VIEW и Create table as select без алиасов (общие правила именования столбцов таблиц)
Обычные synonim (не public) также распространяется на всю бд, но права даны только текущему пользователю.
В случае public synonim права автоматически даны всем пользовалям.
Перекомпиляция VIEW:
```
ALTER <..> compile;
```
```
ALTER TABLE T ENABLE NOVALIDATE CONSTRAINT <..>;
```
Не проверять constraint у уже сущесвующих записей (будет проверяться только у новых)
```
SET CONSTRAINT <..> DEFERRED;
```
* Не проверяет constraint до первого commit;
* После прохождения commit, constraint автоматом сменяется на immdiate - проверять все данные сразу.
Создание constraint с INDEX
```
CREATE TABLE T (
 <..>,
 constraint <..> UNIQUE (<...>)
  USING INDEX (CREATE INDEX <..> ON <..> (<..>) )
)
```
* Такой индекс можно создать только по UNIQUE и PK constraint
* NOT NULL ограничение нельзя использовать в USING INDEX и CHECK (только в inline)
Перманентно дропнуть таблицу без возможности восстановить из корзины.
```
PURGE TABLE <..>;
```
Восстановление таблицы на состояние в прошлом (из корзины).
* SCN - Номер коммита. Можно получить используя пакет FLASHBACK.get_system_change_number
```
FLASHBACK TABLE <..> TO SCN <..>
```
* TIMESTAMP - на определенное время
```
FLASHBACK TABLE <..> TO TIMESTAMP <..>
```
* RESTORE POINT - точка восстановления ( CREATE RESTORE POINT <..> )
```
FLASHBACK TABLE <..> TO RESTORE POINT <..>
```
* BEFORE DROPS - восстановит таблицу, индексы, гранты, constraints ( все, кроме FK )
```
FLASHBACK TABLE <..> TO BEFORE DROPS;
```

* Получение данных на определенное время (нельзя использовать подзапросы)

SELECT * FROM <..> 
  * as of timestamp('дата','формат'); --на точное время
  * as of timestamp systimestamp - intreval '0 00:01:30' DAY TO SEC; --полторы минуты назад
  * VERSION BETWEEN [TIMESTAMP 'TS1' AND 'TS2'] --между датами
                    [SCN '1' AND '2'] --между коммитами

* Показ версии ( в историю попадают записи перед commit)

SELECT t.*, version_operation, RAWTOHEX(version_xid)
FROM T
VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue;

Включение FlashBack
* DDL (может указана в настройках бд)
```
ALTER SESSION SET RECYCLEBIN=ON;
```
* DML (может быть настроено при создании таблицы)
```
ALTER TABLE <..> ENABLE ROW MOVEMENT;
```
EXTERNAL - Внешние метаданные
* Можно делать select
* Нельзя: index, constraint, update, delete
* Хранится в папке на сервере
* Создание (синтаксис схож с синтаксисом SQL Loader )
```
CREATE TABLE <..> (<cols>)
ORGANIZATION EXTERNAL
( ... LOCATION ('file.csv') );
```
Советы по оптимизации составных индексов:
* Самые часто используемые столбцы должны идти первыми
** Поиск по первым столбцам - range scan
** Поиск по другим - scip scan
Поиск по части индекса, для каждого уникального значения префикса индекса.
INTERSECT - убираем дубликаты строк.
Пересечение 2ух одинаковых строк с 2умя другими даст одну.
CUBE/ROLLUP по одному столбцу добавляет лишь 1 строку - общий итог.
* Системные представления хранятся в схеме SYS
* DICTIONARY - описание всех таблиц
* USER_<..> таблицы не имеют OWNER столбца
Иерархические запросы:
* Сортировка по полю внутри уровня.
```
ORDER BY sibling by <..>;
```
* Полный путь до уровня
```
SYS_COMMENT_BY_PATH(col, '/')
```
* Вывод данных в зависимости от уровня
```
CONNECT_BY_ROOT COL -- данные из верха иерархии
CONNECT_BY_LEAF COL -- данные из самого низа (листка)
CONNECT_BY_CYCLE COL -- данные, где начался цикл
```
* WHERE идет и выполняется перед START WITH
* Выдача прав с возможностью дальнейшей передачи (ADMIN OPTION)
```
GRANT CREATE ANY TABLE TO <..> WITH ADMIN OPTION;
```
* REVOKE права - отбирает не только сами права, но и возможность передачи (если были даны через ADMIN OPTION )
UNION с любым другими SET операторами аналогичен DISTINCT
```
SELECT 1 FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION
SELECT 2 FROM DUAL
```
Запрос даст 2 строки, не смотря что первый оператор UNION ALL.

Скахин Алексей / pihel

Страницы

вторник, 27 января 2015 г.

ORACLE 11-12: 1Z0-047 Oracle Database SQL Expert. Новые вопросы 15 сентября 2014.

воскресенье, 29 июня 2014 г.

Неочевидные вопросы сертификации 1Z0-047 Oracle Database SQL Expert

Разделы

Реклама

Архив блога

В сети