Открыто о СУБД Oracle на русском : создаем связь, измерение, каталог и функцию

Выпуск 20

Синтаксис: создаем связь, измерение, каталог и функцию

Уважаемые подписчики! Этот юбилейный выпуск просто должен быть авторским. Мы продолжим изучение формального синтаксиса Oracle SQL. Продолжим разбирать операторы CREATE, по алфавиту (надо же знать, как создавать столь многочисленные объекты базы данных). Синтаксис представлен в виде расширенных формул Бэкуса-Наура для версии сервера 9.0.1.

Оператор CREATE DATABASE LINK

Создание связи базы данных - объекта схемы в локальной базе данных, позволяющего обращаться к объектам в удаленной базе данных (не обязательно Oracle! - вполне можно подключить, например, практически любой ODBC-источник данных, но об этом, - как-нибудь в другой раз...).

<оператор CREATE DATABASE LINK> ::=

CREATE [SHARED] [PUBLIC] DATABASE LINK <имя связи>
   [<идентификация>] [USING '<строка связи>'] ;

<идентификация> ::=

CONNECT TO <пользователь>
| <конструкция аутентификации>

<пользователь> ::=

CURRENT_USER
| <имя пользователя> IDENTIFIED BY <пароль>
   [<конструкция аутентификации>]

<конструкция аутентификации> ::=

AUTHENTICATED BY <имя пользователя> IDENTIFIED BY <пароль>

Примеры

Связь от имени текущего пользователя

CREATE DATABASE LINK a
    CONNECT TO CURRENT_USER USING 'training.profix.com';

Связь от имени заданного пользователя

CREATE DATABASE LINK training
    CONNECT TO scott IDENTIFIED BY tiger
    USING 'training.profix.com';

Общедоступная связь базы данных

CREATE SHARED PUBLIC DATABASE LINK sales.hq.acme.com
    CONNECT TO hr IDENTIFIED BY hr
    AUTHENTICATED BY anupam IDENTIFIED BY bhide
    USING 'sales';

Связь с локальным ODBC-источником данных

CREATE DATABASE LINK ho.world USING 'hsodbc.profix.com';

При наличии следующей записи в файле tnsnames.ora:

hsodbc.profix.com  = 
  (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    )
    (CONNECT_DATA =
      (SID = hsodbc)
    )
    (HS = OK)
  ) 

и сконфигурированном соответствующим образом listener, можно затем выполнять запросы вида:

SQL> select * from t2@ho.world;

        c1         c2
---------- ----------
         3          4
         5          6
         7          8

скажем, к таблице в базе данных Access.

Оператор CREATE DIMENSION

Создание измерения - объекта, задающего иерархическое отношение между парами наборов столбцов. Оптимизатор использует заданные таким образом отношения для переписывания запросов так, чтобы они обращались к материализованным представлениям.

<оператор CREATE DIMENSION> ::=

CREATE DIMENSION [<схема>.] <имя измерения>
   <конструкция уровня> {<конструкция уровня>}
   <конструкция иерархии или атрибута> {<конструкция иерархии или атрибута>};

<конструкция уровня> ::=

LEVEL <уровень> IS <таблица уровня>.<столбец уровня>
| LEVEL <уровень> IS (<таблица уровня>.<столбец уровня>
   {, <таблица уровня>.<столбец уровня>})

<конструкция иерархии или атрибута> ::=

<конструкция иерархии> | <конструкция атрибута>

<конструкция иерархии> ::=

HIERARCHY <имя иерархии> (<уровень потомка>
   CHILD OF <уровень родителя> {CHILD OF <уровень родителя>}
   [<конструкция соединения>]

<конструкция соединения> ::=

JOIN KEY <ключевые столбцы потомка> REFERENCES <уровень родителя>

<ключевые столбцы потомка> ::=

<имя ключевого столбца потомка>
| (<имя ключевого столбца потомка> {, <имя ключевого столбца потомка>})

<конструкция атрибута> ::=

ATTRIBUTE <уровень> DETERMINES <зависимые столбцы>
   {ATTRIBUTE <уровень> DETERMINES <зависимые столбцы>}

<зависимые столбцы> ::=

<имя зависимого столбца>
| (<имя зависимого столбца> {, <имя зависимого столбца>})

Примеры

Измерение customers_dim из документации Oracle

CREATE DIMENSION customers_dim
  LEVEL customer IS (customers.cust_id)
  LEVEL city IS (customers.cust_city)
  LEVEL state IS (customers.cust_state_province)
  LEVEL country IS (countries.country_id)
  LEVEL subregion IS (countries.country_subregion)
  LEVEL region IS (countries.country_region)
  HIERARCHY geog_rollup (
    customer CHILD OF
    city CHILD OF
    state CHILD OF
    country CHILD OF
    subregion CHILD OF
    region
  JOIN KEY (customers.country_id) REFERENCES country)
  ATTRIBUTE customer DETERMINES
    (cust_first_name, cust_last_name, cust_gender,
     cust_marital_status, cust_year_of_birth,
     cust_income_level, cust_credit_limit)
  ATTRIBUTE country DETERMINES (countries.country_name);

Простой пример из книги Тома Кайта (зачем вообще нужны измерения...)

tkyte@TKYTE816> create table sales
  2  (trans_date date, cust_id int, sales_amount number);

Table created.

tkyte@TKYTE816> insert /*+ APPEND */ into sales
  2  select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,
  3          mod(rownum,100) CUST_ID,
  4          abs(dbms_random.random)/100 SALES_AMOUNT
  5    from all_objects
  6  /

21921 rows created.

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816> begin
  2      for i in 1 .. 4
  3      loop
  4          insert /*+ APPEND */ into sales
  5          select trans_date, cust_id, abs(dbms_random.random)/100 
  6            from sales;
  7          commit;
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.
 
tkyte@TKYTE816> select count(*) from sales;

  COUNT(*)
----------
    350736

tkyte@TKYTE816> create table time_hierarchy
  2  (day primary key, mmyyyy, mon_yyyy, qtr_yyyy, yyyy)
  3  organization index
  4  as
  5  select distinct
  6     trans_date    DAY,
  7     cast (to_char(trans_date,'mmyyyy') as number) MMYYYY,
  8     to_char(trans_date,'mon-yyyy') MON_YYYY,
  9     'Q' || ceil( to_char(trans_date,'mm')/3) || ' FY'
 10         || to_char(trans_date,'yyyy') QTR_YYYY,
 11     cast( to_char( trans_date, 'yyyy' ) as number ) YYYY
 12    from sales
 13  /

Table created.

tkyte@TKYTE816> analyze table sales compute statistics;

Table analyzed.

tkyte@TKYTE816> analyze table time_hierarchy compute statistics;

Table analyzed.

tkyte@TKYTE816> create materialized view sales_mv
  2  build immediate
  3  refresh on demand
  4  enable query rewrite
  5  as
  6  select sales.cust_id, sum(sales.sales_amount) sales_amount,
  7         time_hierarchy.mmyyyy
  8    from sales, time_hierarchy
  9   where sales.trans_date = time_hierarchy.day
 10   group by sales.cust_id, time_hierarchy.mmyyyy
 11  /

Materialized view created.

tkyte@TKYTE816> set autotrace on
tkyte@TKYTE816> select time_hierarchy.mmyyyy, sum(sales_amount)
  2    from sales, time_hierarchy
  3   where sales.trans_date = time_hierarchy.day
  4   group by time_hierarchy.mmyyyy
  5  /

    MMYYYY SUM(SALES_AMOUNT)
---------- -----------------
     12001        3.2177E+11
     12002        1.0200E+10
     22001        2.8848E+11
     32001        3.1944E+11
     42001        3.1012E+11
     52001        3.2066E+11
     62001        3.0794E+11
     72001        3.1796E+11
     82001        3.2176E+11
     92001        3.0859E+11
    102001        3.1868E+11
    112001        3.0763E+11
    122001        3.1305E+11

13 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=327 Bytes=850VALVE)
   1    0   SORT (GROUP BY) (Cost=4 Card=327 Bytes=8502)
   2    1     TABLE ACCESS (FULL) OF 'SALES_MV' (Cost=1 Card=327 Bytes

Этот код показывает, как сервер переписывает запрос при наличии соответствующего материализованного представления. Однако, оптимизатор ничего не знает об отношении столбцов DAY, MMYYYY, QTR_YYYY и YYYY:

tkyte@TKYTE816> set timing on
tkyte@TKYTE816> set autotrace on
tkyte@TKYTE816> select time_hierarchy.qtr_yyyy, sum(sales_amount)
  2   from sales, time_hierarchy
  3  where sales.trans_date = time_hierarchy.day
  4  group by time_hierarchy.qtr_yyyy
  5  /

QTR_YYYY                                         SUM(SALES_AMOUNT)
------------------------------------------------ -----------------
Q1 FY2001                                               9.2969E+11
Q1 FY2002                                               1.0200E+10
Q2 FY2001                                               9.3872E+11
Q3 FY2001                                               9.4832E+11
Q4 FY2001                                               9.3936E+11

Elapsed: 00:00:05.58

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8289 Card=5 Bytes=14)
   1    0   SORT (GROUP BY) (Cost=8289 Card=5 Bytes=145)
   2    1     NESTED LOOPS (Cost=169 Card=350736 Bytes=10171344)
   3    2       TABLE ACCESS (FULL) OF 'SALES' (Cost=169 Card=350736 B
   4    2       INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_30180' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
         15  db block gets
     351853  consistent gets
 ...

Поэтому и не использует материализованное представление для данного запроса. Но, если создать следующее измерение

tkyte@TKYTE816> create dimension time_hierarchy_dim
  2          level day      is time_hierarchy.day
  3          level mmyyyy   is time_hierarchy.mmyyyy
  4          level qtr_yyyy is time_hierarchy.qtr_yyyy
  5          level yyyy     is time_hierarchy.yyyy
  6  hierarchy time_rollup
  7  (
  8   day child of
  9   mmyyyy child of
 10   qtr_yyyy child of
 11   yyyy
 12  )
 13  attribute mmyyyy
 14  determines mon_yyyy;

Dimension created.

Получим совсем другой результат:

tkyte@TKYTE816> set autotrace on
tkyte@TKYTE816> select time_hierarchy.qtr_yyyy, sum(sales_amount)
  2   from sales, time_hierarchy
  3  where sales.trans_date = time_hierarchy.day
  4  group by time_hierarchy.qtr_yyyy
  5  /

QTR_YYYY                                         SUM(SALES_AMOUNT)
----------------------------------------------- -----------------
Q1 FY2001                                               9.2969E+11
Q1 FY2002                                               1.0200E+10
Q2 FY2001                                               9.3872E+11
Q3 FY2001                                               9.4832E+11
Q4 FY2001                                               9.3936E+11

Elapsed: 00:00:00.20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=195)
   1    0   SORT (GROUP BY) (Cost=7 Card=5 Bytes=195)
   2    1     HASH JOIN (Cost=6 Card=150 Bytes=5850)
   3    2       VIEW (Cost=4 Card=46 Bytes=598)
   4    3         SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)
   5    4           INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_30180' (UNI
   6    2       TABLE ACCESS (FULL) OF 'SALES_MV' (Cost=1 Card=327 Byt
 
Statistics
----------------------------------------------------------
          0  recursive calls
         16  db block gets
         12  consistent gets
 ...

Вот для этого и нужны измерения...

Оператор CREATE DIRECTORY

Создает объект - каталог. Каталог задает псевдоним для каталога в файловой системе сервера, в котором находятся внешние файлы (большие объекты типа BFILE) и данные внешних таблиц. На эти псевдонимы можно ссылаться в дальнейшем, чтобы не привязываться к конкретным каталогам в файловой системе.

<оператор CREATE DIRECTORY> ::=

CREATE [OR REPLACE] DIRECTORY <псевдоним каталога> AS '<путь к каталогу>';

Примеры

Из документации Oracle:

CREATE DIRECTORY admin AS 'oracle/admin';

CREATE OR REPLACE DIRECTORY bfile_dir AS '/private1/LOB/files';

Оператор CREATE FUNCTION

Создает хранимую функцию или спецификацию вызова.

<оператор CREATE FUNCTION> ::=

[CREATE [OR REPLACE]] FUNCTION [<схема>.]<имя функции>
   [(<объявление параметра>{, <объявление параметра>})]
   RETURN <тип данных>
   {<авторизация, определенность и расспараллеливание>} <реализация функции>;

<объявление параметра> ::=

<имя параметра> [<режим передачи параметра>] <тип данных>
   [<по умолчанию равно> <выражение>]

<режим передачи параметра> ::=

IN | OUT [NOCOPY] | IN OUT [NOCOPY]

<по умолчанию равно> ::=

:= | DEFAULT

<авторизация, определенность и расспараллеливание> ::=

<авторизация> | DETERMINISTIC | <распараллеливание>

<авторизация> ::=

AUTHID CURRENT USER | AUTHID DEFINER

<распараллеливание> ::=

PARALLEL_ENABLE [(PARTITION <аргумент> BY <способ фрагментации>)
   [<организация потока>]]

<способ фрагментации> ::=

ANY
| HASH (<столбец>{, <столбец>})
| RANGE (<столбец>{, <столбец>})

<организация потока> ::=

ORDER BY (<столбец>{, <столбец>})
| CLUSTER (<столбец>{, <столбец>})

<реализация функции> ::=

AGGREGATE USING [<схема>.] <тип реализации>
| PIPELINED USING [<схема>.] <тип реализации>
| [PIPELINED] <as или is> <тело PL/SQL-подпрограммы>
| [PIPELINED] <as или is> <спецификация вызова>

<тип реализации> ::=

-- объектный тип, содержащий реализации подпрограмм ODCIAggregate.

<as или is> ::=

AS | IS

<тело PL/SQL-подпрограммы> ::=

-- это тема отдельного обсуждения - мы пока языком PL/SQL вообще занимались мало...

<спецификация вызова> ::=

LANGUAGE JAVA NAME '<строка>'
| LANGUAGE C [NAME <имя внешней С-функции>] LIBRARY <имя библиотеки>
   [AGENT IN (<аргумент>)] [WITH CONTEXT]
   [PARAMETERS (<параметры внешней C-функции>)]

Примеры

Создание обычной хранимой функции

create or replace 
function AverageSalary(dept_id emp.deptno%TYPE) return number
is
  total_sal number;
  emp_count integer := 0;
begin
  select count(*), sum(sal) into emp_count, total_sal from emp
    where deptno = dept_id;

  if emp_count = 0 then
    raise ZERO_DIVIDE;
  end if;

  return total_sal/emp_count;
exception
  when ZERO_DIVIDE then
    dbms_output.put_line('No employees for ' || dept_id || ' in AverageSalary!');
    raise;
  -- ...
end;

Подключение внешней C-функции

CREATE FUNCTION get_val (
    x_val IN NUMBER,
    y_val IN NUMBER,
    image IN LONG RAW)
RETURN BINARY_INTEGER AS LANGUAGE C
        NAME "c_get_val"
        LIBRARY c_utils
        PARAMETERS (...);

Создание новой функции агрегирования

CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER
 PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
-- Про тип SecondMaxImpl - разговор отдельный...

Создание и вызов функции на Java

create or replace and compile java source named "MyTimestamp"
as
  import java.lang.String;
  import java.sql.Timestamp;

public class MyTimestamp {
  public static String getTimestamp() {
    return (new Timestamp(System.currentTimeMillis())).toString();
  }
}
/
create or replace function my_timestamp return varchar2
as language java
name 'MyTimestamp.getTimestamp() return java.lang.String';
/

select my_timestamp,
  to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') 
from dual;

В следующем выпуске

Получение значений функций агрегирования из нескольких таблиц. Очередной шедевр Тома Кайта. Выпуск выйдет в конце недели. А еще, возможно, про пулы KEEP и RECYCLE буферного кэша, раз уж спрашивают, как объекты в памяти закрепить... Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.