ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Составной ключ для определенных записей
14 сообщений из 14, страница 1 из 1
Составной ключ для определенных записей
    #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:18
| Ответить | Цитировать | Написать  
Составной ключ для определенных записей
    #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, 22:59
| Ответить | Цитировать | Написать  
Составной ключ для определенных записей
    #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:08
| Ответить | Цитировать | Написать  
Составной ключ для определенных записей
    #39885094
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Понял, спасибо.
А как при этом должен быть сформирован запрос, чтобы этот индекс использовался?
where STATUS = 1 and TYPE_ID = ... and CODE = ... ?
...
Рейтинг: 0 / 0
04.11.2019, 23:10
| Ответить | Цитировать | Написать  
Составной ключ для определенных записей
    #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
04.11.2019, 23:13
| Ответить | Цитировать | Написать  
Составной ключ для определенных записей
    #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, 00:57
| Ответить | Цитировать | Написать  
Составной ключ для определенных записей
    #39885134
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Alibek B.
А как при этом должен быть сформирован запрос, чтобы этот индекс использовался?
where STATUS = 1 and TYPE_ID = ... and CODE = ... ?
Соломон немножко в прошлом веке со своим уникальным индексом и необходимостью знать, помнить и говнопастить выражения.
Выражения индекса, по-правильному, нужно описывать явными виртуальными колонками, а вместо индекса создавать нормальное ограничение уникальности.
Тогда проблем с "как писать запрос?" не возникнет.
...
Рейтинг: 0 / 0
05.11.2019, 07:45
| Ответить | Цитировать | Написать  
Составной ключ для определенных записей
    #39885137
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
А если использовать такой индекс: TYPE_ID, CODE, decode(STATUS,1,ID) ?

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


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