Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Составной ключ для определенных записей  [new]
Alibek B.
Member

Откуда:
Сообщений: 3315
Есть таблица:
CREATE TABLE PINS
(
  TYPE_ID  NUMBER,
  CODE  NUMBER,
  STATUS  NUMBER,
  COPY NUMBER
)

Для STATUS=1 комбинация (TYPE_ID,CODE) должна быть уникальной.
Для STATUS=0 уникальность не требуется (точнее ее не будет, будут повторы CODE для одинакового TYPE_ID).
Как тут лучше сделать?
Неуникальный индекс по TYPE_ID,CODE и ручная проверка STATUS?
Вычисляемый индекс по TYPE_ID,CODE,nullif(STATUS,0)?
Уникальный индекс по TYPE_ID,CODE и обновление статуса таким образом: update PINS set STATUS=0, COPY=CODE, CODE=null where ... ?
(в последних двух вариантах я исхожу из того, что null не индексируется)
4 ноя 19, 22:18    [22009556]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9636
SQL> CREATE TABLE PINS
  2  (
  3    TYPE_ID  NUMBER,
  4    CODE  NUMBER,
  5    STATUS  NUMBER,
  6    COPY NUMBER
  7  )
  8  /

Table created.

SQL> CREATE UNIQUE INDEX PINS_UIDX1
  2  ON PINS(
  3          CASE STATUS
  4            WHEN 1 THEN TYPE_ID
  5          END,
  6          CASE STATUS
  7            WHEN 1 THEN CODE
  8          END
  9         )
 10  /

Index created.

SQL> INSERT
  2    INTO PINS(STATUS,TYPE_ID,CODE)
  3    VALUES(1,1,1)
  4  /

1 row created.

SQL> INSERT
  2    INTO PINS(STATUS,TYPE_ID,CODE)
  3    VALUES(1,1,1)
  4  /
INSERT
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PINS_UIDX1) violated


SQL> INSERT
  2    INTO PINS(STATUS,TYPE_ID,CODE)
  3    VALUES(0,1,1)
  4  /

1 row created.

SQL> INSERT
  2    INTO PINS(STATUS,TYPE_ID,CODE)
  3    VALUES(0,1,1)
  4  /

1 row created.

SQL> 


SY.
4 ноя 19, 22:59    [22009577]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9636
Alibek B.

Вычисляемый индекс по TYPE_ID,CODE,nullif(STATUS,0)?
Уникальный индекс по TYPE_ID,CODE и обновление статуса таким образом: update PINS set STATUS=0, COPY=CODE, CODE=null where ... ?
(в последних двух вариантах я исхожу из того, что null не индексируется)


У тебя неправильное понятие null не индексируется. Не индексируются записи у которых значения всех индeксных выражений NULL.

SY.
4 ноя 19, 23:08    [22009582]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Alibek B.
Member

Откуда:
Сообщений: 3315
Понял, спасибо.
А как при этом должен быть сформирован запрос, чтобы этот индекс использовался?
where STATUS = 1 and TYPE_ID = ... and CODE = ... ?
4 ноя 19, 23:10    [22009584]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Alibek B.
Member

Откуда:
Сообщений: 3315
SY
У тебя неправильное понятие null не индексируется. Не индексируются записи у которых значения всех индeксных выражений NULL.

Да, я уже это понял на экспериментах и примеры нашел, у меня сейчас индекс такой:
CREATE UNIQUE INDEX PIN_CODES ON PINS
(DECODE(STATUS,1,TYPE_ID), DECODE(STATUS,1,CODE))

Видимо немного перепутал с MSSQL.
4 ноя 19, 23:13    [22009588]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9636
WHERE CASE STATUS
        WHEN 1 THEN TYPE_ID
      END,
      CASE STATUS
          WHEN 1 THEN CODE
      END


или первый CASE - range scan, или второй CASE - skip scan.

SY.
5 ноя 19, 00:57    [22009635]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29406
Alibek B.
А как при этом должен быть сформирован запрос, чтобы этот индекс использовался?
where STATUS = 1 and TYPE_ID = ... and CODE = ... ?
Соломон немножко в прошлом веке со своим уникальным индексом и необходимостью знать, помнить и говнопастить выражения.
Выражения индекса, по-правильному, нужно описывать явными виртуальными колонками, а вместо индекса создавать нормальное ограничение уникальности.
Тогда проблем с "как писать запрос?" не возникнет.
5 ноя 19, 07:45    [22009683]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Alibek B.
Member

Откуда:
Сообщений: 3315
А если использовать такой индекс: TYPE_ID, CODE, decode(STATUS,1,ID) ?

ID всегда уникален (это sequence).
На мой взгляд использовать такой индекс проще и функции ограничения он так же будет выполнять.
5 ноя 19, 08:08    [22009688]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Alibek B.
Member

Откуда:
Сообщений: 3315
Сделал так:
CREATE TABLE PINS
(
  PIN_ID    NUMBER,
  TYPE_ID   NUMBER,
  CODE      VARCHAR2(20),
  STATUS    NUMBER,
  ...
);

CREATE UNIQUE INDEX PIN_CODES ON PINS
(TYPE_ID, CODE, DECODE(STATUS,1,0,PIN_ID));


На тестовых данных ведет себя, как мне и надо — разрешает дубли при STATUS!=1.
Но судя по explain, индекс не используется, пока в where я не укажу все три выражения:
select *
from pins
where type_id = 0 and code = '123' and decode(status,1,0,pin_id) = 0

Если последнее (с decode) не указывать (или указать просто status=1), то в плане TABLE ACCESS FULL.
Это из-за маленького размера таблицы или для уникального индекса Oracle не будет использовать часть индекса?

Сообщение было отредактировано: 5 ноя 19, 09:18
5 ноя 19, 09:13    [22009716]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29406
Alibek B.
А если использовать такой индекс: TYPE_ID, CODE, decode(STATUS,1,ID) ?
Ну а сам как думаешь?
Alibek B.
ID всегда уникален (это sequence).
А его не было в исходном вопросе.
Alibek B.
На мой взгляд использовать такой индекс проще
Для каких запросов?
5 ноя 19, 09:20    [22009721]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Alibek B.
Member

Откуда:
Сообщений: 3315
Мне нужно часто проверять записи по критерию: type_id=<type> and code=<code> and status=1.
Было бы удобнее всего, если бы и в SQL-запросе указывался аналогичный фильтр.
Но и так это удобнее, чем использовать case/decode для type_id и code.
5 ноя 19, 09:32    [22009732]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29406
Alibek B.
Но и так это удобнее, чем использовать case/decode для type_id и code.
Про виртуальные колонки не доходит?
5 ноя 19, 09:39    [22009736]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Alibek B.
Member

Откуда:
Сообщений: 3315
У меня Oracle 10g, в нем их нет.
5 ноя 19, 09:58    [22009747]     Ответить | Цитировать Сообщить модератору
 Re: Составной ключ для определенных записей  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29406
Alibek B.
У меня Oracle 10g
RTFM, застрявший в прошлом веке.
5 ноя 19, 10:06    [22009749]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить