Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 создание PK на основе другого индекса  [new]
Харьковчанин
Member

Откуда: Харьков, конечно.
Сообщений: 194
Что то я сегодня туплю, наверное, но всё же..
Как создать первичный ключ таблицы, не создавая отдельного индекса, а используя имеющийся составной уникальный индекс с нужным полем на первом месте.
В запросах идёт отбор в основном по 2-м полям, не хочется заводить лишние индексы.

create table aa (a number, b number);

create UNIQUE INDEX ind_aa on aa (a,b);

ALTER TABLE aa ADD CONSTRAINT pk_aa primary key (a);

так получается 2 индекса. Как сделать с одним ?
22 авг 06, 18:16    [3039425]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Mikst
Member

Откуда: Москва
Сообщений: 983
Харьковчанин

ALTER TABLE aa ADD CONSTRAINT pk_aa primary key (a);

так получается 2 индекса. Как сделать с одним ?


В доке. USING INDEX
22 авг 06, 18:18    [3039434]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
using
22 авг 06, 18:18    [3039439]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Харьковчанин
Member

Откуда: Харьков, конечно.
Сообщений: 194
Разобрался :)
Воспользоваться такой возможностью можно только при наличии неуникального индекса.
Странно.. А почему уникальный не проходит?
22 авг 06, 18:18    [3039442]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Харьковчанин
Member

Откуда: Харьков, конечно.
Сообщений: 194
using я пробовал. Это не помогало.
22 авг 06, 18:20    [3039447]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Харьковчанин
using я пробовал. Это не помогало.

Не помогало как? И что значит уникальный индекс не проходит? Продемонстрируйте
22 авг 06, 18:24    [3039475]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Харьковчанин
Member

Откуда: Харьков, конечно.
Сообщений: 194
При выполнении такого сценария

create table aa (a number, b number);

create INDEX ind_aa on aa (a,b);

ALTER TABLE aa ADD CONSTRAINT pk_aa primary key (a) using index ;


создаётся 1 индекс и ограничение ПК

При таком

create table aa (a number, b number);

create unique INDEX ind_aa on aa (a,b);

ALTER TABLE aa ADD CONSTRAINT pk_aa primary key (a) using index ;


создаётся 2 индекса и ограничение ПК

То есть дело не using index а в уникальности составного ключа.
Вот этого то я и не понимаю..
22 авг 06, 18:30    [3039520]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Denis Popov
Member

Откуда: Санкт-Петербург
Сообщений: 7862
После USING INDEX должно стоять либо имя индекса, либо команда на его создание.
22 авг 06, 18:57    [3039660]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
RA\/EN
Member

Откуда:
Сообщений: 3659
Denis Popov
После USING INDEX должно стоять либо имя индекса, либо команда на его создание.


Нифига... Спецификация USING INDEX допускает ветку <index_properties>, которая может быть пустой. В таком случае выбирается, похоже, первый созданный индекс с подходящими полями. Описание в доке не нашел, вообще, по части ошбики "ORA-14196" интернет и металинк скупы. Почему нельзя UNIQUE INDEX использовать - тоже не совсем понятно* (см.далее)
Насколько я знаю, обычный индекс I_NU(a,b) в потрохах своих на самом деле есть уникальный индекc I_U(a,b,rowid).

Хотя есть замечание - уникальный индекс с полями, полностью совпадающими с полями констрейнта, использовать можно (причем порядок не важен).

SQL> drop table aa
  2  /

Table dropped

SQL> create table aa (a number, b number)
  2  /

Table created

SQL> create unique index idx_aa on aa(b,a)
  2  /

Index created

SQL> ALTER TABLE aa ADD CONSTRAINT pk_aa primary key (a,b) using index
  2  /

Table altered

SQL> select count(*) from user_indexes where table_name='AA'
  2  /

  COUNT(*)
----------
         1
22 авг 06, 19:40    [3039805]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Ales Protiv
Member

Откуда: Прага
Сообщений: 1872
я вот буквально вчера пробовал подцепить к создаваемому первичному ключу уже созданный индекс на пару миллионов записей, используя синтаксис
... using index index_name
По логике должно было пройти быстро. А оно затормозило ой как надолго... времени разбираться не было, оставил на ночь - всё прошло ок, но непонятки остались... что он так долго делает в таком случае, если индекс уже есть?
23 авг 06, 10:30    [3041454]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
Проверяет на пустые значения
23 авг 06, 10:32    [3041465]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
А может все-таки кто-нибудь объяснить(а лучше ткнуть в доку, пока не нахожу):
1) почему нельзя использовать составной уникальный индекс для primary key? (может быть Оракл просто не допускает, что уникальный индекс может входить в состав уникального же бОльшего индекса - пусть это странновато, но почему это Ораклу просто не проверить?)
2) почему можно использовать в качестве индекса для primary обратный индекс(как в примере RA\/EN)??? Как это будет выглядеть при использовании этого индекса?
23 авг 06, 10:50    [3041650]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
2ой вопрос снимается - торможу. Но про 1ый - интересно.
23 авг 06, 10:55    [3041699]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
ИМХО ответ вот в этой теме
Если кому то недостаточно давайте копать дальше
23 авг 06, 11:05    [3041829]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
Jannny
А может все-таки кто-нибудь объяснить(а лучше ткнуть в доку, пока не нахожу):
1) почему нельзя использовать составной уникальный индекс для primary key? (может быть Оракл просто не допускает, что уникальный индекс может входить в состав уникального же бОльшего индекса - пусть это странновато, но почему это Ораклу просто не проверить?)


Почему нельзя очень даже можно:
1. Создадим представление от имени SYS, которое даёт нам информацию о том, какой индекс реально исползуется для первичного ключа:
create or replace view v$user_pk_indexes as
 select
  o1.object_name table_name,
  c.name constraint_name,
  o.object_name index_name
 from
  con$ c,
  cdef$ d,
  dba_objects o,
  dba_objects o1
 where
  c.con# = d.con# and
  o.object_id = d.enabled and
  d.obj# = o1.object_id and
  o1.owner = user
;
 drop public synonym user_pk_indexes;
 create public synonym user_pk_indexes for v$user_pk_indexes;

 grant select on user_pk_indexes to public;

2. Создадим таблицу, отдельно индекс, и потом добавим ограничение:
create table test2
(
test_key NUMBER(16),
test_id NUMBER(10)
);

create unique index pk_test_2 on test2(test_key, test_id);

alter table test2 add
(constraint pk_test2 primary key (test_id,test_key));

3. Проверим какой индекс используется
col table_name format a20;
col constraint_name format a20;
col index_name format a20;

select * from user_pk_indexes where table_name = 'TEST2';


SQL> create table test2
  2  (
  3  test_key NUMBER(16),
  4  test_id NUMBER(10)
  5  );

Таблица создана.

SQL> create unique index pk_test_2 on test2(test_key, test_id);

Индекс создан.

SQL> alter table test2 add
  2  (constraint pk_test2 primary key (test_id,test_key));

Таблица изменена.


SQL> select * from user_pk_indexes where table_name = 'TEST2';

TABLE_NAME           CONSTRAINT_NAME      INDEX_NAME
-------------------- -------------------- --------------------
TEST2                PK_TEST2             PK_TEST_2

Прекрасно видно, что для первичного ключа используется составной индекс, который создавался.
23 авг 06, 12:23    [3042441]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
RA\/EN
Member

Откуда:
Сообщений: 3659
softbuilder@inbox.ru


Прекрасно видно, что для первичного ключа используется составной индекс, который создавался.


Не совсем верный тесткейс - добавь в иднекс поля (в конец), которых нет в PK.
23 авг 06, 12:28    [3042472]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
to
softbuilder@inbox.ru
все хорошо до тех пор пока количество и наименование полей в индексе и констрейнсе совпадает, причем порядок не важен, но вот если в уникальном индексе на 1 поле больше будет?
23 авг 06, 12:31    [3042495]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
RA\/EN
softbuilder@inbox.ru


Прекрасно видно, что для первичного ключа используется составной индекс, который создавался.


Не совсем верный тесткейс - добавь в иднекс поля (в конец), которых нет в PK.


Зачем?
23 авг 06, 12:51    [3042654]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
Vint
to
softbuilder@inbox.ru
все хорошо до тех пор пока количество и наименование полей в индексе и констрейнсе совпадает, причем порядок не важен, но вот если в уникальном индексе на 1 поле больше будет?


Не понимаю зачем это делать.
23 авг 06, 12:53    [3042665]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
RA\/EN
Member

Откуда:
Сообщений: 3659
softbuilder@inbox.ru
Vint
to
softbuilder@inbox.ru
все хорошо до тех пор пока количество и наименование полей в индексе и констрейнсе совпадает, причем порядок не важен, но вот если в уникальном индексе на 1 поле больше будет?


Не понимаю зачем это делать.


Если так сделать, индекс под констрейнт не подцепится.
Вопрос не в логической несуразности подобного индекса, а в различии таки в хранении UNIQUE и NON-UNIQUE индексов.
По приведенной выше ссылке в принципе понятно, почему...
23 авг 06, 13:30    [3042908]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
RA\/EN

Вопрос не в логической несуразности подобного индекса
, а в различии таки в хранении UNIQUE и NON-UNIQUE индексов.
По приведенной выше ссылке в принципе понятно, почему...


Ну это вы уже потом развили эту тему, насчёт различия. А проблема автора как раз упирается в нецелесобразность создания дополнительного индекса по двум столбцам.

Почему бы просто не создать уникальный индекс по столбцу "а"? В случае where a=? and b=? - будет использоваться первичный ключ и доступ к таблице будет по ROWID. Этого автору недостаточно?
23 авг 06, 13:59    [3043112]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
Харьковчанин
не хочется заводить лишние индексы.
Ну видимо автору недостаточно)))
23 авг 06, 14:12    [3043213]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Кудрявцев Леонид
Guest
RA\/EN
.......Вопрос не в логической несуразности подобного индекса, а в различии таки в хранении UNIQUE и NON-UNIQUE индексов.
По приведенной выше ссылке в принципе понятно, почему...


Мне не понятно. Честно попытался прочитать приведенную выше ссылку, но так и не понял. Почему unique индекс нельзя использовать под constraint?
23 авг 06, 14:47    [3043461]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
Потому что при создании уникального составного индекса оракл не уверен в том что он однозначно получит одну запись при запросе 1 поля
23 авг 06, 14:49    [3043488]     Ответить | Цитировать Сообщить модератору
 Re: создание PK на основе другого индекса  [new]
Mikst
Member

Откуда: Москва
Сообщений: 983
Vint
Потому что при создании уникального составного индекса оракл не уверен в том что он однозначно получит одну запись при запросе 1 поля


А при создании неуникального он уверен в этом?
23 авг 06, 15:09    [3043681]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить