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

Продолжение. Новые вопросы с сентября 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.

Вопросы по главам, которые не сложные, но все равно вызывали сложности у меня:
2)
* 3
CONSTRAINT в первую очреедь ассоциируется с TABLE (к INDEX не ассоциируется)

3)
* 3
Точка для ROLLBACK - SAVEPOINT (любой DDL в этой формулировке не подходит)
* 8
UPDATE может сменить любой столбец таблицы как на NULL, так и не на NULL (подразумевается что на таблице нет ограничений)
* 11
В DELETE обязателен только DELETE (FROM не обязательно)

6)
* 10
TO_YMINTERVAL('01-01') правильно писать (не TO_INTERVALYM)

* 12
Если не хотим видить эквиваленты таймзоны (LOCAL TZ), то нужно использовать TS WITH TZ

7)
* 3
Средние функции (AVG, MEDIAN) не применимы на CHAR данных

8)
* 5
USING(col) - inner, equijoin (natural join пишется без USING)
lack - отсутствие

9)
* 2
Подзапрос - запрос созданный из данных, найденных в базе (никаких новых данных)

11)
* 1
Слово Column не может использоваться в alter (modify, add)
Default значение должно идти после типа поля

* 2
Нельзя менять тип столбца, если в нем есть данные.

* 5
SET UNUSED должно идти сразу после таблицы

* 7
При добавлении CONSTRAINT делается modify (никаких ADD)

* 8
Create index ... ON ... (не FOR)

* 10
USING INDEX можно использовать только на PK и UNIQUE полях

* 13
FLASHBACK ... TO TS SYSTS - INTERVAl ...
Semijoin = exists

13)
* 4
ROLLUP по одному столбцу дает тольго общий итог

* 10
Для GROUPING функции требутеся только GROUP BY (не нужны rollup,cube)

* 13
NULL в GROUP BY GROUPING SETS(NULL, ... - означает добавить итоговую строку

14)
* 2, 6
ALL_ - вьюхи содержат данные на все данные к которым пользователь имеет доступ (DBA_ - все данные полностью)

* 8
COMMENT ON COLUMN ... - комментарий на столбце (нет TABLE)

* 11
Тип Constraint в USER_CONSTRAINTS - это R (не F)

* 12
Из системных вьюх можно вытащить даже состояние таблицы (чтобы это не значило)

15)
* 1
CTAS может названий колонок (колонки могут быть как алиасы у запроса, так и перечислены в CT )

* 11
Во WHERE MERGE можно фильтровать только данные по алиасу из USING (к самой merge таблице нельзя обратиться)

* 12 Для поддержки AS OF синтаксиса FLASHBACK таблицы, никаких изменении в нее вносить не надо

* 15
При выборке версий через VERSIONS BETWEEN выбираются только изменения идущие перед COMMIT (т.е. если сделано несколько UPD/ISN/DEL, то покажется только последний)

16)
* 9
Смена направления connect by делается через смену prior (не смотря на тотже start with)

* 14
с помощью CONNECT BY исключаются ветви из иерархических запросов (start with лишь устанавливает корень дерева)

* 15
ommited - опущен!!
WHERE идеть до START WITH

17)
* 4
+ внутри [] - это литерал (а не любой символ, как за)

* 5
класс в [] пишется в нижнем регистре

* 8
REGEXP_SUBSTR(source_char, pattern [, position [, occurrence
position - позиция в строке, occurrence - номер вхождения паттерна

REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrenсe [, match_param ] ] ] ])
source_char - текст, в котором выполняется поиск
pattern - регулярное выражение
replace_string - шаблон для замены каждого вхождения (NULL - если опущено)
position - позиция в строке, с которой необходимо начать поиск (по умолчанию 1)
occurrence - искомое вхождение (по умолчанию 1)

[:alnum:] -> [0-9a-z]
[:alpha:] -> [a-z]

* 9
правильно искать текст в скобочках так: '$[^)]$'

* 11
заменять 2 знака на 1 надо так: '( ){2,}' , '[[:blank:]]+'
+ и {2,} - не менее 1

* 15
[abc] - любая буква из abc

18)
* 10
Ролям можно давать другие роли

* 13
Role можно создвать если есть права на Create Role

* 14
Гарантировано убрать права - убрать права у роли и пользователя

Различные источники в интернете:

62. * WHERE можно писать в merge WHEN MATCHED
* Можно писать DELETE
59. USER_OBJECTS - показывает статус VIEW
29. * Нельзя писать using по 2 полям
* нельзя писать join без on
3. TS WITH LOCAL TZ - можно использовать в PK
5. commit сбрасывает savepoint - он становится недоступен через rollback
15. NULL поля буду также отброшены, если написать NOT IN(число)
16. преобразование date в ts with local tz = cast
61. not null не создастся на PK в CTAS
65. сортировка по полю внутри уровня: order sibling by col
40. * если CYCLE не задано, то NOCYCLE - поумолчанию
* если CYCLE, то maxvalue может быть отрицательным
49. синоним не удаляется с таблицей
56. ROLLUP, CUBE (1,2)
итог по каждой группе (1столбец-2столбец), + итог общий, +подитог по каждой группе 1 столбца + подитог по каждой группе 2 столбца

http://exam.test4actual.com/1z0-047.pdf
1. При достижении MAXVALUE в CYCLE SEQ - отсчет пойдет с 1 (если не указано MINVALUE)
2. WITH CHECK OPTION - запрет на update/insert строки, которые не подходят под условие в VIEW
3. можно делать update view, даже если там join, но только столбцов, которые идут первые в from
4. insert into(select col1 from t where col2 > 100 WITH CHECK OPTION) VALUES(val1);
ошибка - col2 нет в списке и есть проверка по этому полю

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=303&p_certName=SQ1Z0_047
1. using пишется в скобочках (так что нельзя писать and за ними)
2. при уникальном столбце (emp_name VARCHAR2(25) UNIQUE,) также создается индекс!!
3. sequence CACHE - взаранее получить числа последовательности и держать в памяти.
Максимальное значние = (CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
если CACHE/NOCACHE упущено, то CACHE 20 поумолчанию
4. DROP TABLE и восстановления его с помощью FLASHBACK, восстанавливаются все констрейнты, кроме внешних ключей на другие таблицы 5. SESSION_PRIVS-текущие привелегии пользователя (1 столбец)

USER_SYNONYMS - содержит только PRIVATE syn пользователя
Если права на select через роль и напрямую через таблицу. Если отобрать одно из прав, право на select все равно останется.
GRANT что ON тип название TO кому. SYSTEM привилегии выполняются без "ON тип название". Можно перечислять "кому" через запятую (ON нельзя несколько).
Нельзя комбинировать system и object привилегии (роли и object - можно).
WITH GRANT OPTION только для OBJ, WITH ADMIN OPTION - для ролей и SYS

http://www.aiotestking.com/oracle/category/exam-1z0-047-oracle-database-sql-expert-november-6th-2014/
92 - только одно LONG поле может быть в таблице!
94 - в natural идет соединение по одинаковому имени и типу (если столбцов несколько, то по всем!!)
21 - ^ внутри [ - означает отрицание (за - начало строки)
136 - TS W LTZ - TS возвращается без TZ - просто TS
143 - CUBE дает 2n групп, ROLLUP - дает n+1 группу, и создает их справа на лево
144 - SESSION_PRIVS - SYS привилегии текущие
148 - ?????
175 - ?????
176 - ?????
179 - ALL = UPDATE,INSERT,SELECT (если было WITH GRANT OPTION - То отнимает все дочерние???)
192 - ???
201 - можно давать права на UPDATE(col1,col2) конкретных столбцов
223 - обновляемая view должна содержать PK
226 - USER_SYNONYMS - приватные синонимы, DICTIONARY - все названия объектов, к которым есть доступ
246 - DEFAULT DIRECTORY обязательно в External таблицах (указывает на папку с файлом, к которой есть права)

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

Страницы

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