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

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

Метки: , , , , , ,
С 15 сентября 2014 в экзамен 1Z0-047 включены новые темы вопросов по Oracle 11-12


  • Limit the rows that are retrieved by a query
  • Use the SQL row limiting clause
Документация

<12 
* SELECT rownum FROM (SELECT … ORDER BY 1) WHERE rownum > 5
* SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY 1)  as rnm FROM … ) WHERE rnm > 5
>=12 
SELECT val
FROM   rownum_order_test
ORDER BY val DESC
[ OFFSET offset ROWS  ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    ROWS { ONLY | WITH TIES } ]
OFFSET – смещение сначала
FETCH — сколько отобрать
NEXT – следующие от смещения
"WITH TIES" - может вернуть больше, если следующее значение == последнему значению в окне.
Дополнительно:
* Запрос физически трансформируется к «ROW_NUMBER() OVER ( ORDER BY 1)»
* Если OFFSET не задан, то он = 0
* Если любое ключевое слово <0, то = 0
* Если любое ключевое слово = NULL, то ничего не вернется
* Доли откидываются
* Если OFFSET больше числа записей, то ничего не вернется
* Если rowcount или percent больше числа записей, то вернутся все
* row limiting не может быть использовано в FOR UPDATE запросах, CURRVAL и NEXTVAL псевдоколонках или в an fast refresh materialized view.


  • Create queries using the PIVOT and UNPIVOT clause
Документация

SELECT * FROM   (SELECT product_code, quantity FROM   tbl)
PIVOT  (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
           210             90            160
Запрос разворачивает строки в «product_code» в аналогичные колонки. Колонки прописаны жестко.
Запрос получается ничем не лучше DECODE Или CASE WHEN.

Колонки можно прописать запросом (или все = ANY), если возвращать XML:
SELECT * FROM   (SELECT product_code, quantity
        FROM   pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT  DISTINCT product_code                                                                  
FROM   pivot_test   WHERE  id < 10));

SELECT * FROM   (SELECT product_code, quantity FROM   pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (ANY));

<PivotSet>
  <item>
    <column name = "PRODUCT_CODE">A</column>
    <column name = "SUM_QUANTITY">210</column>
  </item>
  <item>
    <column name = "PRODUCT_CODE">B</column>
    <column name = "SUM_QUANTITY">90</column>
  </item>
...
</PivotSet>

Обратная операция — преобразования колонок в строки (аналог UNION)
SELECT * FROM   unpivot_test
UNPIVOT (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c AS 'C', product_code_d AS 'D'));
* Поумолчанию NULL в quantity не учитывается, чтобы учитывать нужно дописать: INCLUDE NULLS

  • Use pattern matching to recognize patterns across multiple rows in a table
Документация

SELECT * FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   LAST(UP.tstamp) AS peak_tstamp,
                   LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno
         ONE ROW PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
       ) MR
ORDER BY MR.product, MR.start_tstamp;

PRODUCT    START_TSTAM PEAK_TSTAMP END_TSTAMP         MNO
---------- ----------- ----------- ----------- ----------
TWINKIES   01-OCT-2014 03-OCT-2014 06-OCT-2014          1
TWINKIES   06-OCT-2014 08-OCT-2014 09-OCT-2014          2
TWINKIES   09-OCT-2014 13-OCT-2014 16-OCT-2014          3
TWINKIES   16-OCT-2014 18-OCT-2014 20-OCT-2014          4
Расширение аналитических функций — выявление тенденций (холмы, провалы, стабильность)
«MEASURES» - колонки, которые отбираем
«[ONE ROW | ALL ROWS] PER MATCH» - сколько строк на совпадение
«MATCH_NUMBER | CLASSIFIER() » - сколько подошло или какой патерн был найден
«PATTERN (STRT UP+ DOWN+)» - какие зависмости ищем — рост, падение или без изменения — FLAT
допустимы регулярные выражения : + - хотябы 1, {1} – точно 1, * - любое совпадение
"AFTER MATCH SKIP PAST LAST ROW " - искать пока есть совпадения паттерна
«LAST/NEXT/FIRST/PREV» - гуляние по выборке для сравнения
  • Using Single-Row Functions to Customize Output
  • Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD)
* SELECT CONVERT('строка', 'нужная кодировка', 'исходная кодировка') FROM DUAL; 
* "FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno ORDER BY sal ASC NULLS LAST)"
Первое значение в окне, сгруппированного по deptno и отсортированного по sal.
* "PERCENTILE_CONT" - берет запись которая находится на позиции 0...1, если попадается несколько записей, то берется среднее.
«PERCENTILE_DISC» - тоже самое, но берется не среднее, а первое.
При 0,5 — тоже самое что MEDIAN()
   SELECT department_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) 
      "Median cont",
   PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) 
      "Median disc"
   FROM employees GROUP BY department_id
   ORDER BY department_id, "Median cont", "Median disc;
* "STDDEV" - стандартное отклонение от среднего, отсортированного по salary.
Вернет = 0, если 1 строка (STDDEV_SAMP = NULL)
select employee_name, bonus, STDDEV(bonus) OVER (ORDER BY salary)
from employees
where department = 'Marketing';
* LAG – вверх выборки гуляние, LEAD - вниз
LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

  • Use the cross_outer_apply_clause
"CROSS APPLY " - тоже самое что CROSS JOIN
"OUTER APPLY " - тоже самое что LEFT OUTER JOIN
ОТЛИЧИЕ: соединение только с подзапросом и условие соединения пишется в подзапросе.
  SELECT d.department_name, v.employee_id, v.last_name
FROM departments d OUTER APPLY (SELECT * FROM employees e
                                  WHERE e.department_id = d.department_id) v

  • Create a lateral inline view in a query
Так же писать INNER JOIN можно, если подзапрос обрамить в LATERAL
SELECT * FROM employees e, LATERAL(SELECT * FROM departments d
                                   WHERE e.department_id = d.department_id);

  • Use subqueries
INSERT INTO TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280)
   VALUES ('Smith', 280, 1750);
UPDATE TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280) p
   SET p.salary = p.salary + 100;
DELETE TABLE(SELECT h.people FROM hr_info h
   WHERE h.department_id = 280) p
   WHERE p.salary > 1700;
"WITH CHECK OPTION " - нельзя изменять данные, которых нет в подзапросе. (колонки в SELECT или условие WHERE)

  • Truncate tables, and recursively truncate child tables
TRUNCATE TABLE:
* Нельзя сделать rollback
* Нельзя сделать flashback
* Для сессионной таблицы удалятся только данные текущей сессии.
* Если есть FK, то нужно сначала почистить все зависимости
** TRUNCATE TABLE .. CASCADE – сделает truncate по всем FK on delete cascade.

  • Use 12c enhancements to the DEFAULT clause, invisible columns, virtual columns and identity columns in table creation/alteration
* DEFAULT
Возможность использовать sequence, как значение default
CREATE TABLE detail (
  id          NUMBER DEFAULT detail_seq.NEXTVAL,
  master_id   NUMBER DEFAULT master_seq.CURRVAL,
  description VARCHAR2(30)
);
* Нужно иметь права на seq, как при создании таблицы, так и при вставке.
CREATE TABLE t2 (
  col1        NUMBER DEFAULT default_seq.NEXTVAL,
  col2        NUMBER DEFAULT ON NULL default_on_null_seq.NEXTVAL,
  description VARCHAR2(30)
);
* Вставляется seq, только если происходит вставка NULL.
* с 11 версии делая add column с DEFAULT .. NOT NULL поумолчанию вставится DEFAULT

  • Identity Columns
CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(30)
);
* Нельзя вставлять данные в «IDENTITY» колонки (не число, ни NULL)
* Если «DEFAULT», вместо «ALWAYS», то можно вставлять числа
* Если «BY DEFAULT ON NULL», то можно и NULL и числа
* Такой способ значительно быстрей, чем триггеры

  • Invisible columns
CREATE TABLE tab1 (
  id          NUMBER,
  description VARCHAR2(50) INVISIBLE
);
* Не отображается в «SELECT *»
* Невидимыми могут быть любые колонки (виртуальные, ключ партиции), кроме external таблиц
* Невидимые столбцы не могут использоваться в пользовательских типах
* В insert нужно явно указывать этот столбец, чтобы вставить данные (на него обычно распространяется not null)
*Столбцы не нумеруются в общем списке, (нет сортировки)

  • Virtual columns
CREATE TABLE employees (
  id          NUMBER,
  salary1     AS (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL
);
* Столбец только для чтения
* Может использоваться в INDEX (тогда он будет функциональным)
* Нельзя использовать в: временных, index организованных, внешних таблицах, пользовательских типах-объектах.
* Не может быть ключом партиционирования
* Может использовать в запросе только данные этой таблицы (кроме других виртуальных колонок) и детерминистические функции (где результат зависит только от параметров)
* Результатом может быть только скаляр. Нельзя LOB, Long, User type.

  • Create simple and complex views with visible/invisible columns 
create or replace view TEST_VIEW (ID, TEXT INVISIBLE) as select ID, TEXT from TEST_TABLE;
Можно создавать view с невидимыми колонками.
  • Grant privileges on tables and on a user
В 12 версии добавилось «WITH DELEGATE OPTION », в отличии от ADMIN OPTION, вы можете передавать права не всем, а только права на pl/sql модули.

  • Create and maintain indexes including invisible indexes and multiple indexes on the same columns
Можно создавать несколько индексов на одном наборе колонок, если они имеют разный тип (BITMAP , TREE) или разную видимость
  • Use the following types of multitable INSERTs (Unconditional, Conditional and Pivot) 
PIVOT в данном случае имеется в виду, что длинная таблица из select вставляется в несколько других коротких.

  • Use explicit default values in INSERT and UPDATE statements 
Возможно отсылка к «Use 12c enhancements to the DEFAULT clause»
Так же можно явно писать в значении колонки «default», тогда вставится значение поумолчанию.
Также можно исключить колонку из insert, тогда она также вставится default.

  • Managing Data in Different Time Zones
  • Use Various datetime functions
TZ_OFFSET, ADD_MONTHS , LAST_DAY , MONTHS_BETWEEN , NEW_TIME 
SELECT NEW_TIME(TO_DATE('11-10-09 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST')     "New Date and Time"
  FROM DUAL;

New Date and Time
--------------------
09-NOV-2009 21:23:45
NEXT_DAY 
 
 SELECT NEXT_DAY('15-OCT-2009','TUESDAY') "NEXT DAY" FROM DUAL;

NEXT DAY
--------------------
20-OCT-2009 00:00:00
ROUND(date), TRUNC(date) 
 
 SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR')  "New Year" FROM DUAL;

 
 New Year
---------
01-JAN-01
FROM_TZ
Конвертирует TIMETAMP в TS WITH TZ 
 
FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '3:00')
TO_TIMESTAMP
Конвертирует char в TS 
 
TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
TO_TIMESTAMP_TZ
Конвертирует char в TS WITH TS 
 
TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')
TO_YMINTERVAL
char в интервал Y-m
TO_DSINTERVAL
char в интервал D h24:mi:ss "TO_DSINTERVAL('100 00:00:00')"
CURRENT_DATE
Время текущей сессии
CURRENT_TIMESTAMP
TS WITH TS текущей сессии
LOCALTIMESTAMP
TS текущей сессии (время такоеже, как у CURRENT_TIMESTAMP )
DBTIMEZONE
Временная зона БД
SESSIONTIMEZONE
Временная зона сессии
EXTRACT
На вход: date, interval, TS. Размерность входного параметра должна совпадать с извлекаемым параметром:
* HOUR, MINUTE, or SECOND не применимо для DATE, т. к. хранится в ANSI варианте без времени.
* TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET - только для времени с Time zone
* Нельзя извлечь секунды, если TO_YMINTERVAL

  • WITH Clause Enhancements in Oracle Database 12c
Возможность использовать процедуры и функции в WITH 
WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1
/

WITH
  PROCEDURE with_procedure(p_id IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('p_id=' || p_id);
  END;

  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    with_procedure(p_id);
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1
/
* Функции в WITH нельзя использовать в запросах в PLSQL
* Функции в WITH можно использовать в запросах в PLSQL, если делать их execute immedaite
* Функция в WITH значительно быстрей вызова обычной plsql функции (http://oracle-base.com/articles/12c/with-clause-enhancements-12cr1.php)
* PRAGMA UDF - решает проблему производительности обычных функций. Но такие функции нельзя будет использовать в PLSQL вызовах.
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
  PRAGMA UDF;
BEGIN
  RETURN p_id;
END;
/
* WITH с функциями нельзя поумолчнанию использовать в DML
* Такую возможность можно активировать хинтом /*+ WITH_PLSQL */
UPDATE /*+ WITH_PLSQL */ t1 a
SET a.id = (WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(a.id)
            FROM   dual);
/
Первая часть

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)