Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Составной ключ для определенных записей / 14 сообщений из 14, страница 1 из 1
04.11.2019, 22:18
    #39885075
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Есть таблица:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
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 не индексируется)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2019, 22:59
    #39885091
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
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.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2019, 23:08
    #39885093
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
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.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2019, 23:10
    #39885094
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Понял, спасибо.
А как при этом должен быть сформирован запрос, чтобы этот индекс использовался?
where STATUS = 1 and TYPE_ID = ... and CODE = ... ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2019, 23:13
    #39885097
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
SY
У тебя неправильное понятие null не индексируется. Не индексируются записи у которых значения всех индeксных выражений NULL.

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


Видимо немного перепутал с MSSQL.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 00:57
    #39885114
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Код: plsql
1.
2.
3.
4.
5.
6.
WHERE CASE STATUS
        WHEN 1 THEN TYPE_ID
      END,
      CASE STATUS
          WHEN 1 THEN CODE
      END



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

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 07:45
    #39885134
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Alibek B.
А как при этом должен быть сформирован запрос, чтобы этот индекс использовался?
where STATUS = 1 and TYPE_ID = ... and CODE = ... ?
Соломон немножко в прошлом веке со своим уникальным индексом и необходимостью знать, помнить и говнопастить выражения.
Выражения индекса, по-правильному, нужно описывать явными виртуальными колонками, а вместо индекса создавать нормальное ограничение уникальности.
Тогда проблем с "как писать запрос?" не возникнет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 08:08
    #39885137
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
А если использовать такой индекс: TYPE_ID, CODE, decode(STATUS,1,ID) ?

ID всегда уникален (это sequence).
На мой взгляд использовать такой индекс проще и функции ограничения он так же будет выполнять.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 09:13
    #39885148
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Сделал так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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 я не укажу все три выражения:
Код: plsql
1.
2.
3.
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 не будет использовать часть индекса?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 09:20
    #39885150
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Alibek B.А если использовать такой индекс: TYPE_ID, CODE, decode(STATUS,1,ID) ?Ну а сам как думаешь?
Alibek B.ID всегда уникален (это sequence).А его не было в исходном вопросе.
Alibek B.
На мой взгляд использовать такой индекс проще
Для каких запросов?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 09:32
    #39885154
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Мне нужно часто проверять записи по критерию: type_id=<type> and code=<code> and status=1.
Было бы удобнее всего, если бы и в SQL-запросе указывался аналогичный фильтр.
Но и так это удобнее, чем использовать case/decode для type_id и code.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 09:39
    #39885156
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Alibek B.
Но и так это удобнее, чем использовать case/decode для type_id и code.
Про виртуальные колонки не доходит?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 09:58
    #39885165
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
У меня Oracle 10g, в нем их нет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.11.2019, 10:06
    #39885166
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Составной ключ для определенных записей
Alibek B.
У меня Oracle 10g
http://www.bugtraq.ru/forum/faq/general/smart-questions.html] RTFM , застрявший в прошлом веке.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Составной ключ для определенных записей / 14 сообщений из 14, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 163ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1881908]:
 0ms
get settings:
 12ms
get forum list:
 15ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 38ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 57ms
get tp. blocked users:
 2ms
others: 14ms
total:  163ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]