Oracle Gotchas для опытного Newb

Что такое Oracle gotchas для кого-то нового для платформы, но не нового для реляционных баз данных (MySQL, MS SQL Server, Postgres и т.д.) в целом.

Два примера того, что я ищу

Многие продукты реляционной базы данных обрабатывают создание ключа auto_increment для вас. Oracle нет, вы должны вручную создать последовательность, а затем создать триггер
При вводе данных через интерфейс SQL Developer вам необходимо вручную зафиксировать данные

Бонусные очки для связанных с PHP getchas, так как платформа ~~Я буду~~ использовать этот гипотетический опытный newb.

Ответы

Ответ 1

Примечание: я объясняю только gotchas здесь, я. е. ситуации, когда `Oracle` ведет себя не так, как другие системы. `Oracle` имеет множество преимуществ по сравнению с другими `RDBMS`, но они не являются темой сообщения.

Вы не можете SELECT без FROM.
```
SELECT  1
```
не удастся, вам необходимо:
```
SELECT  1
FROM    dual
```
Пустая строка и NULL - это одно и то же.
```
SELECT  *
FROM    dual
WHERE   '' = ''
```
ничего не возвращает.
Нет TOP и LIMIT. Вы ограничиваете свои результаты в предложении WHERE:
```
SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        ORDER BY
                col
        )
WHERE   rownum < 10
```
именно таким образом, используя подзапрос, поскольку ROWNUM оценивается до ORDER BY.

Вы не можете вложить коррелированные подзапросы более одного уровня. Это не будет выполнено:

SELECT  (
        SELECT  *
        FROM    (
                SELECT  dummy
                FROM    dual di
                WHERE   di.dummy = do.dummy
                ORDER BY
                        dummy
                )
        WHERE   rownum = 1
        )
FROM    dual do

Это проблема.

Значения

NULL не индексируются. Этот запрос не будет использовать индекс для упорядочения:
```
SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        ORDER BY
                col
        )
WHERE   rownum < 10
```
если col не помечен как NOT NULL.

Обратите внимание на значения NULL, которые не индексируются, а не столбцы. Вы можете создать индекс в столбце с нулевым значением, а значения индекса NULL попадут в индекс.

Однако индекс не будет использоваться, когда условие запроса предполагает, что значения NULL могут удовлетворить его.

В приведенном выше примере вы хотите вернуть все значение (включая NULL s). Тогда индекс не знает значений не NULL, следовательно, не может их получить.
```
SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        ORDER BY
                col
        )
WHERE   rownum < 10
```
Но этот запрос будет использовать индекс:
```
SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   col IS NOT NULL
        ORDER BY
                col
        )
WHERE   rownum < 10
```
так как значения не NULL никогда не могут удовлетворять условию.
По умолчанию NULL сортируются последним, а не первым (например, в PostgreSQL, но в отличие от MySQL и SQL Server)

Этот запрос:
```
SELECT  *
FROM    (
        SELECT  1 AS id
        FROM    dual
        UNION ALL
        SELECT  NULL AS id
        FROM    dual
        ) q
ORDER BY
        id
```
вернет
```
id
---
1
NULL
```
Чтобы отсортировать, как в SQL Server и MySQL, используйте это:
```
SELECT  *
FROM    (
        SELECT  1 AS id
        FROM    dual
        UNION ALL
        SELECT  NULL AS id
        FROM    dual
        ) q
ORDER BY
        id NULLS FIRST
```
Обратите внимание, что он нарушает порядок ROWNUM, если последний не используется из подзапроса (как описано выше)
"MYTABLE" и "MYTABLE" (материал с двойными кавычками) - это разные объекты.
```
SELECT  *
FROM    mytable -- wihout quotes
```
будет выбирать из первого, а не последнего. Если первое не существует, запрос завершится с ошибкой.
```
CREATE TABLE mytable
```
создает "MYTABLE", а не "MYTABLE".
В Oracle все неявные блокировки (которые являются результатом операций DML) являются уровнями строк и никогда не эскалируются. Это не строка, на которую не влияет транзакция, может быть неявно заблокирована.

Писатели никогда не блокируют читателей (и наоборот).

Чтобы заблокировать всю таблицу, вы должны указать явный оператор LOCK TABLE.

Блокировки строк хранятся в данных.
В Oracle нет индексов "CLUSTERED", есть "индексированные таблицы". По умолчанию таблицы организованы в кучу (в отличие от SQL Server и MySQL с InnoDB).

В мире Oracle "кластерное хранилище" означает организацию нескольких таблиц, так что строки, которые совместно используют общий ключ (из нескольких таблиц), также разделяют данные.

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

Ответ 2

SELECT 1 не работает, тогда выберите 1 из двойного.

Если вы работаете с иерархическими данными, соединитесь с ними отлично.

Ответ 3

Один комментарий: вам не нужно создавать триггер, чтобы использовать последовательности, если вы не настаиваете на репликации поведения столбца IDENTITY в Sybase/SQL Server. Я считаю более полезным просто использовать последовательность непосредственно в действительных инструкциях вставки, например.

INSERT
  INTO MyTable
     ( KeyCol
     , Name
     , Value
     )
SELECT Seq_MyTable.NextVal
     , 'some name'
     , 123
  FROM dual;

Вам не нужно беспокоиться о накладных расходах на запуск триггера, и у вас есть возможность справиться с вставкой строк в таблицу, не беспокоясь о назначенных значениях последовательности (например, при перемещении данных из схемы в другую), Вы также можете предварительно выбирать значения из последовательности для вставки диапазонов данных и других методов, которые функция IDENTITY либо делает трудным, либо невозможным.

Ответ 4

Кажется, что я столкнулся с большим количеством баз данных Oracle, чувствительных к регистру объектов и данных схемы, чем в SQL Server.

Ответ 5

Не забудьте использовать nvl (столбец) вокруг любого столбца в наборе строк, который может быть полностью заполнен нулевыми значениями. В противном случае столбец будет отсутствовать в наборе строк.

Это право, полное отсутствие!

Пример:

SELECT nvl(employeeName,'Archie'), nvl(employeeSpouse,'Edith') FROM Employee

Это гарантирует, что вы получите два столбца в наборе строк, даже если все значения равны нулю. Вы увидите кучу значений "Арчи" и "Эдит". Если вы не используете nvl(), вы можете получить только один столбец или ни один из них. Исходная часть этого состоит в том, что ваш код может отлично работать в вашей среде разработки и даже передавать QA, но когда он добирается до производства, значения в таблице могут изменять структуру результатов!

Итак, короче говоря, всякий раз, когда вы выбираете нулевой столбец, обязательно используйте nvl().

Ответ 6

В MySQL нет групповой конкатенации. Если вам нужна функция объединения совокупности групп, вы должны написать свой собственный. Вот моя реализация:

drop type T_GROUP_CONCAT;

create or replace type GROUP_CONCAT_PARAM as object
(
  val varchar2(255),
  separator varchar2(10),
  numToConcat NUMBER,
  MAP MEMBER FUNCTION GROUP_CONCAT_PARAM_ToInt  return VARCHAR2
);

--map function needed for disctinct in select clauses
CREATE OR REPLACE TYPE BODY GROUP_CONCAT_PARAM IS
    MAP MEMBER FUNCTION GROUP_CONCAT_PARAM_ToInt return VARCHAR2 is 
      begin 
        return val; 
      end; 

end;


/

CREATE OR REPLACE TYPE T_GROUP_CONCAT 
AS OBJECT (

runningConcat VARCHAR2(5000),
runningCount NUMBER,

STATIC FUNCTION ODCIAggregateInitialize
  ( actx IN OUT T_GROUP_CONCAT
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate
  ( self  IN OUT T_GROUP_CONCAT,
    val   IN       GROUP_CONCAT_PARAM
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate
  ( self             IN   T_GROUP_CONCAT,
    returnValue  OUT VARCHAR2,
    flags           IN   NUMBER
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge
  (self  IN OUT T_GROUP_CONCAT,
   ctx2 IN      T_GROUP_CONCAT
  ) RETURN NUMBER

);
/

CREATE OR REPLACE TYPE BODY T_GROUP_CONCAT AS

STATIC FUNCTION ODCIAggregateInitialize
  ( actx IN OUT T_GROUP_CONCAT
  ) RETURN NUMBER IS 
  BEGIN
    IF actx IS NULL THEN
      actx := T_GROUP_CONCAT ('', 0);
    ELSE
      actx.runningConcat := '';
      actx.runningCount := 0;
    END IF;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateIterate
  ( self  IN OUT T_GROUP_CONCAT,
    val   IN     GROUP_CONCAT_PARAM
  ) RETURN NUMBER IS
  BEGIN
    if self.runningCount = 0 then
        self.runningConcat := val.val;
    elsif self.runningCount < val.numToConcat then
        self.runningConcat := self.runningConcat || val.separator || val.val;
    end if;
    self.runningCount := self.runningCount + 1;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateTerminate
  ( self        IN  T_GROUP_CONCAT,
    ReturnValue OUT VARCHAR2,
    flags       IN  NUMBER
  ) RETURN NUMBER IS
  BEGIN
    returnValue := self.runningConcat;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateMerge
  (self IN OUT T_GROUP_CONCAT,
   ctx2 IN     T_GROUP_CONCAT
  ) RETURN NUMBER IS
  BEGIN
    self.runningConcat := self.runningConcat || ',' || ctx2.runningConcat;
    self.runningCount := self.runningCount + ctx2.runningCount;
    RETURN ODCIConst.Success;
  END;

END;
/

CREATE OR REPLACE FUNCTION GROUP_CONCAT
( x GROUP_CONCAT_PARAM
) RETURN VARCHAR2
--PARALLEL_ENABLE
AGGREGATE USING T_GROUP_CONCAT;
/

Чтобы использовать его:

select GROUP_CONCAT(GROUP_CONCAT_PARAM(tbl.someColumn, '|', 2)) from someTable tbl

Ответ 7

Я написал несколько отличий здесь: Подумайте, что стандарт SQL ANSI полностью переносится между базами данных? Подумайте еще раз.

Ответ 8

Временные таблицы

Вы создаете и индексируете их, как обычные таблицы, но каждый сеанс/транзакция видит только свои собственные данные. Это отличается от MS SQL.

Глобальные переменные

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

Триггеры

До самых последних версий не было способа определить способ срабатывания подобных триггеров. Если вы действительно позаботились о том, что "ПЕРЕД ОБНОВЛЕНИЕМ ДЛЯ КАЖДОЙ РЯДЫ" было первым, вы поставили все за один триггер.