Заменить все индексы группы таблиц на локальные или получить список колонок индекса / Oracle

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Заменить все индексы группы таблиц на локальные или получить список колонок индекса

25 сообщений из 28, страница 1 из 2

все

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128341

ARRay001

Гость

Доброго времени суток!

Никак не соображу как лучше сделать.
Исходная задача: заменить все глобальные обычные (не уникальные) индексы определенной группы таблиц (выбирается like по имени) на локальные. Если смогу получить список всех колонок индекса - остальное сделаю. Проблема в том, что некоторые колонки индексов содержат функциональные выражения, которые достаются списком типа long и что-то не получается сложить всё в одну текстовую переменную или коллекцию. Пока мыслю в этом направлении:
Прохожусь циклом по всем обычным глобальным индексам и пытаюсь достать их поля, чтобы создать потом такие же после drop

Код: plsql

1.
2.
3.

select a.column_name as col from user_ind_columns a where upper(a.index_name) = 'IDX_NAME' and upper(a.column_name) not like 'SYS_%' 
union all
select b.column_expression as col from all_ind_expressions b where upper(b.index_name) = 'IDX_NAME';

Но в запросе 2 ошибка, т.к. надо доставать список LONG и простой to_char не срабатывает. Может что-то не догоняю и всё делается проще?

...

Рейтинг:

0 / 0

21.01.2022, 11:26

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128373

PuM256

Гость

Код: plsql

select dbms_metadata.get_ddl('INDEX', 'INDEX_NAME', 'SCHEMA') from dual

...

Рейтинг:

0 / 0

21.01.2022, 12:48

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128378

Вячеслав Любомудров

Участник

Откуда: Владивосток

Сообщения: 18 655

Рейтинг: 0 / 0

Ты же все равно в PL/SQL это прикручивать будешь?
Я как-то так достаю:

Код: 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.

			expr_str := null;
			for r in (select e.column_expression, c.column_name
					from all_ind_expressions e, all_ind_columns c
					where c.index_name=e.index_name(+)
					and c.index_owner=e.index_owner(+)
					and c.column_position=e.column_position(+)
					and c.index_name=MY_INDEX_NAME
					and c.index_owner=MY_OWNER
					order by c.column_position) loop
				if r.column_expression is not null then
					expr_str := expr_str ||
						replace(
						 replace(
						  replace(
						   replace(
						    replace(
						     replace(r.column_expression,
						     chr(9), '''||CHR(9)||'''),
						    chr(10), '''||CHR(10)||'''),
						   chr(11), '''||CHR(11)||'''),
						  chr(12), '''||CHR(12)||'''),
						 chr(13), '''||CHR(13)||'''),
						'||''''||', '||') || ', ';
				else
					expr_str := expr_str || r.column_name  || ', ';
				end if;
			end loop;
			expr_str := rtrim(expr_str, ', ');

...

Рейтинг:

0 / 0

21.01.2022, 12:52

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128409

ARRay001

Гость

PuM256

Код: plsql

select dbms_metadata.get_ddl('INDEX', 'INDEX_NAME', 'SCHEMA') from dual

Интересный вариант, не знаю только, сработает ли в контексте скриптов по нашей системе обновлений...

...

Рейтинг:

0 / 0

21.01.2022, 14:07

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128410

ARRay001

Гость

Вячеслав Любомудров

Ты же все равно в PL/SQL это прикручивать будешь?
Я как-то так достаю:

Код: 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.

			expr_str := null;
			for r in (select e.column_expression, c.column_name
					from all_ind_expressions e, all_ind_columns c
					where c.index_name=e.index_name(+)
					and c.index_owner=e.index_owner(+)
					and c.column_position=e.column_position(+)
					and c.index_name=MY_INDEX_NAME
					and c.index_owner=MY_OWNER
					order by c.column_position) loop
				if r.column_expression is not null then
					expr_str := expr_str ||
						replace(
						 replace(
						  replace(
						   replace(
						    replace(
						     replace(r.column_expression,
						     chr(9), '''||CHR(9)||'''),
						    chr(10), '''||CHR(10)||'''),
						   chr(11), '''||CHR(11)||'''),
						  chr(12), '''||CHR(12)||'''),
						 chr(13), '''||CHR(13)||'''),
						'||''''||', '||') || ', ';
				else
					expr_str := expr_str || r.column_name  || ', ';
				end if;
			end loop;
			expr_str := rtrim(expr_str, ', ');

Что-то многовато всего, но может тоже пойти - надо проверить всё...

...

Рейтинг:

0 / 0

21.01.2022, 14:09

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128433

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 744

Рейтинг: 2 / 0

ARRay001

Код: plsql

1.
2.
3.

select a.column_name as col from user_ind_columns a where upper(a.index_name) = 'IDX_NAME' and upper(a.column_name) not like 'SYS_%' 
union all
select b.column_expression as col from all_ind_expressions b where upper(b.index_name) = 'IDX_NAME';

Через DBMS_METADATA.

Код: plsql

SQL> create table t(x int, y int)
  2    partition by list(x)
  3    (
  4     partition values (0),
  5     partition values (1)
  6    );

Table created.

SQL>
SQL> create index t_i on t(x, mod(y, 16), x desc, x+y);

Index created.

SQL>
SQL> set serverout on
SQL>
SQL> declare
  2    handle    number;
  3    tx_handle handle%type;
  4    xmldoc    xmltype;
  5    ddl       clob;
  6  begin
  7    handle := dbms_metadata.open('INDEX');
  8
  9    dbms_metadata.set_filter(handle, 'SCHEMA', user);
 10    dbms_metadata.set_filter(handle, 'NAME', 'T_I');
 11
 12    tx_handle := dbms_metadata.add_transform(handle, 'SXML');
 13    xmldoc := dbms_metadata.fetch_xml(handle);
 14    dbms_metadata.close(handle);
 15
 16    select xmlquery('declare default element namespace "http://xmlns.oracle.com/ku";
 17                     copy $tmp := $src modify (
 18                       insert node <LOCAL_PARTITIONING/> as last into $tmp//INDEX/TABLE_INDEX)
 19                     return $tmp'
 20                       passing xmldoc as "src"
 21                       returning content)
 22      into xmldoc
 23      from dual;
 24
 25    dbms_lob.createtemporary(ddl, false);
 26    handle := dbms_metadata.openw('INDEX');
 27    tx_handle := dbms_metadata.add_transform(handle, 'SXMLDDL');
 28    dbms_metadata.set_transform_param(tx_handle, 'PARTITIONING', false);
 29    dbms_metadata.set_transform_param(tx_handle, 'PRESERVE_LOCAL', true);
 30    dbms_metadata.convert(handle, xmldoc, ddl);
 31    dbms_metadata.close(handle);
 32    dbms_output.put_line(ddl);
 33    dbms_lob.freetemporary(ddl);
 34  end;
 35  /
CREATE INDEX "TC"."T_I" ON "TC"."T" ("X",MOD("Y",16),"X" DESC,"X"+"Y")
  PCTFREE
10 INITRANS 2 MAXTRANS 255 LOGGING
  TABLESPACE "USERS"  LOCAL

PL/SQL procedure successfully completed.

...

Рейтинг:

0 / 0

21.01.2022, 14:52

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128436

ARRay001

Гость

SeaGate,

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

Код: plsql

select replace(replace(dbms_metadata.get_ddl('INDEX', 'IDX_1', 'SCHEMA1'),CHR(34),''),'TABLESPACE SCHEMA1', 'TABLESPACE SCHEMA1 LOCAL ')  from dual ;

Далее загоняем в переменную clob и запускаем EXECUTE IMMEDIATE

...

Рейтинг:

0 / 0

21.01.2022, 14:59

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128443

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 744

Рейтинг: 2 / 0

ARRay001

SeaGate,

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

Код: plsql

select replace(replace(dbms_metadata.get_ddl('INDEX', 'IDX_1', 'SCHEMA1'),CHR(34),''),'TABLESPACE SCHEMA1', 'TABLESPACE SCHEMA1 LOCAL ')  from dual ;

Далее загоняем в переменную clob и запускаем EXECUTE IMMEDIATE

DBMS_METADATA предоставляет конкретный API для манипуляции объектов и поддерживается вендором СУБД.
Нет смысла использовать строковые манипуляции, работающие в ограниченном числе случаев при определенных условиях.

Код: 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.

SQL> create table t(x int, y int, "TABLESPACE TRNNDEX" int, "x y" int)
  2    partition by list(x)
  3    (
  4     partition values (0),
  5     partition values (1)
  6    );

Table created.

SQL>
SQL> create index t_i on t(x, mod(y, 16), x desc, x+y, "TABLESPACE TRNNDEX", "x y");

Index created.

SQL>
SQL> select replace(replace(dbms_metadata.get_ddl('INDEX', 'T_I'),CHR(34),''),'TABLESPACE TRNNDEX', 'TABLESPACE TSPACE1 LOCAL ')  from dual ;

REPLACE(REPLACE(DBMS_METADATA.GET_DDL('INDEX','T_I'),CHR(34),''),'TABLESPACETRNN
--------------------------------------------------------------------------------

  CREATE INDEX TC.T_I ON TC.T (X, MOD(Y,16), X DESC, X+Y, TABLESPACE TSPACE1 LOC
AL , x y)
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE USERS

Приведенная наивная строковая манипуляция не будет сохранять валидный DDL в общем случае, что и демонстрирует пример выше.

...

Рейтинг:

0 / 0

21.01.2022, 15:15

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128447

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

Код: 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.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.

SQL> DROP TABLE INVOICES PURGE
  2  /

Table dropped.

SQL> CREATE TABLE INVOICES
  2  (INVOICE_NO    NUMBER NOT NULL,
  3   INVOICE_DATE  DATE   NOT NULL)
  4  PARTITION BY RANGE (INVOICE_DATE)
  5  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  6   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  7   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  8   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')))
  9  /

Table created.

SQL> INSERT
  2    INTO INVOICES
  3    SELECT  EMPNO,
  4            HIREDATE
  5      FROM  EMP
  6  /

14 rows created.

SQL> CREATE INDEX INVOICES_IDX ON INVOICES(INVOICE_NO)
  2  /

Index created.

SQL> SELECT  PARTITIONED
  2    FROM  USER_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

PARTITIONED
-----------
NO

SQL> SELECT  LOCALITY
  2    FROM  USER_PART_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

no rows selected

SQL> ALTER TABLE INVOICES MODIFY PARTITION BY RANGE (INVOICE_DATE)
  2  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  3   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  4   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  5   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')))
  6  ONLINE
  7  UPDATE INDEXES(INVOICES_IDX LOCAL)
  8  /

Table altered.

SQL> SELECT  PARTITIONED
  2    FROM  USER_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

PARTITIONED
-----------
YES

SQL> SELECT  LOCALITY
  2    FROM  USER_PART_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

LOCALITY
--------
LOCAL

SQL>

SY.

...

Рейтинг:

0 / 0

21.01.2022, 15:18

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128456

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 744

Рейтинг: 2 / 0

Код: plsql

SQL> DROP TABLE INVOICES PURGE
  2  /

Table dropped.

SQL> CREATE TABLE INVOICES
  2  (INVOICE_NO    NUMBER NOT NULL,
  3   INVOICE_DATE  DATE   NOT NULL)
  4  PARTITION BY RANGE (INVOICE_DATE)
  5  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  6   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  7   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  8   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')))
  9  /

Table created.

SQL> INSERT
  2    INTO INVOICES
  3    SELECT  EMPNO,
  4            HIREDATE
  5      FROM  EMP
  6  /

14 rows created.

SQL> CREATE INDEX INVOICES_IDX ON INVOICES(INVOICE_NO)
  2  /

Index created.

SQL> SELECT  PARTITIONED
  2    FROM  USER_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

PARTITIONED
-----------
NO

SQL> SELECT  LOCALITY
  2    FROM  USER_PART_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

no rows selected

SQL> ALTER TABLE INVOICES MODIFY PARTITION BY RANGE (INVOICE_DATE)
  2  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  3   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  4   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  5   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')))
  6  ONLINE
  7  UPDATE INDEXES(INVOICES_IDX LOCAL)
  8  /

Table altered.

SQL> SELECT  PARTITIONED
  2    FROM  USER_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

PARTITIONED
-----------
YES

SQL> SELECT  LOCALITY
  2    FROM  USER_PART_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

LOCALITY
--------
LOCAL

SQL>

SY.

Если допустимо то, что делает ALTER TABLE MODIFY PARTITIONING под капотом (аля DBMS_REDEFINITION), то да.
Если большие таблицы, где нужно поменять пару индексов, то накладные расходы могут оказаться несопоставимы.

Код: 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.

SQL> col subobject_name for a30
SQL> select object_type, subobject_name, data_object_id from obj where object_name='INVOICES';

OBJECT_TYPE             SUBOBJECT_NAME                 DATA_OBJECT_ID
----------------------- ------------------------------ --------------
TABLE
TABLE PARTITION         INVOICES_Q1                             78735
TABLE PARTITION         INVOICES_Q2                             78736
TABLE PARTITION         INVOICES_Q3                             78737
TABLE PARTITION         INVOICES_Q4                             78738

SQL>
SQL> alter session set events 'sql_trace';

Session altered.

SQL>
SQL> ALTER TABLE INVOICES MODIFY PARTITION BY RANGE (INVOICE_DATE)
  2  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  3   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  4   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  5   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')))
  6  ONLINE
  7  UPDATE INDEXES(INVOICES_IDX LOCAL)
  8  /

Table altered.

SQL>
SQL> alter session set events 'sql_trace off';

Session altered.

SQL>
SQL> select object_type, subobject_name, data_object_id from obj where object_name='INVOICES';

OBJECT_TYPE             SUBOBJECT_NAME                 DATA_OBJECT_ID
----------------------- ------------------------------ --------------
TABLE
TABLE PARTITION         INVOICES_Q1                             78744
TABLE PARTITION         INVOICES_Q2                             78745
TABLE PARTITION         INVOICES_Q3                             78746
TABLE PARTITION         INVOICES_Q4                             78747

...

Рейтинг:

0 / 0

21.01.2022, 15:26

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128457

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

SeaGate

Если большие таблицы, где нужно поменять пару индексов, то накладные расходы могут оказаться несопоставимы.

Согласен, просто решил показать способ как таковой. Жаль не поддерживается что-то типа:

Код: plsql

1.
2.

ALTER INDEX XXX MODIFY LOCAL
/

SY.

...

Рейтинг:

0 / 0

21.01.2022, 15:30

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128458

ARRay001

Гость

SeaGate,

это кому так придёт в голову назвать колонку? По сути, конечно, верно, но если точно знаешь, что такого безобразия не будет, то проще так сделать. Но поюзаю ещё полную правильную версию...

...

Рейтинг:

0 / 0

21.01.2022, 15:32

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128467

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

DBMS_METADATA как предлагал PuM256 есть самый простой способ:

Код: plsql

SQL> DROP TABLE INVOICES PURGE
  2  /

Table dropped.

SQL> CREATE TABLE INVOICES
  2  (INVOICE_NO    NUMBER NOT NULL,
  3   INVOICE_DATE  DATE   NOT NULL)
  4  PARTITION BY RANGE (INVOICE_DATE)
  5  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  6   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  7   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  8   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')))
  9  /

Table created.

SQL> INSERT
  2    INTO INVOICES
  3    SELECT  EMPNO,
  4            HIREDATE
  5      FROM  EMP
  6  /

14 rows created.

SQL> CREATE INDEX INVOICES_IDX ON INVOICES(INVOICE_NO)
  2  /

Index created.

SQL> SELECT  PARTITIONED
  2    FROM  USER_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

PARTITIONED
-----------
NO

SQL> SELECT  LOCALITY
  2    FROM  USER_PART_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

no rows selected

SQL> DECLARE
  2      V_DDL CLOB := DBMS_METADATA.GET_DDL('INDEX','INVOICES_IDX');
  3  BEGIN
  4      EXECUTE IMMEDIATE 'DROP INDEX INVOICES_IDX';
  5      EXECUTE IMMEDIATE V_DDL || ' LOCAL';
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT  PARTITIONED
  2    FROM  USER_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

PARTITIONED
-----------
YES

SQL> SELECT  LOCALITY
  2    FROM  USER_PART_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

LOCALITY
--------
LOCAL

SQL>

SY.

...

Рейтинг:

0 / 0

21.01.2022, 15:41

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128485

ARRay001

Гость

SY,
проблема в том, что LOCAL иногда надо вставлять в середину метадаты (когда есть хвост и партиции). Поэтому-то я и делал replace, а не прибавлял в конец. Но то, что она идёт после TABLESPACE Имя_Индекса LOCAL - это чаще всего (но не универсально). А что колонку назовут служебным словом - это эксклюзив

...

Рейтинг:

0 / 0

21.01.2022, 16:06

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128491

ARRay001

Гость

ARRay001,
А хотя, возможно, и так сработает. Надо тестить...

...

Рейтинг:

0 / 0

21.01.2022, 16:19

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128502

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 744

Рейтинг: 2 / 0

SYDBMS_METADATA как предлагал PuM256 есть самый простой способ:
Для частного случая из этой задачи, должно работать. Для более общего случая, когда, например, нужно пересоздать GLOBAL PARTITIONED индексы как LOCAL, уже не сработает.

Код: 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.

SQL> create table t(x int, y int, "TABLESPACE TRNNDEX" int, "x y" int)
  2    partition by list(x)
  3    (
  4     partition values (0),
  5     partition values (1)
  6    );

Table created.

SQL>
SQL> create index t_i on t(x, mod(y, 16), x desc, x+y, "TABLESPACE TRNNDEX", "x y") global partition by hash(x) partitions 1;

Index created.

SQL>
SQL> set serverout on
SQL>
SQL> declare
  2    ddl clob;
  3    new_ddl clob;
  4  begin
  5    ddl := dbms_metadata.get_ddl('INDEX', 'T_I');
  6    new_ddl := ddl||' LOCAL';
  7    dbms_output.put_line(new_ddl);
  8    execute immediate 'drop index t_i';
  9    execute immediate new_ddl;
 10  end;
 11  /

  CREATE INDEX "TC"."T_I" ON "TC"."T" ("X", MOD("Y",16), "X" DESC, "X"+"Y",
"TABLESPACE TRNNDEX", "x y")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE
"USERS"  GLOBAL PARTITION BY HASH ("X")
 (PARTITION "SYS_P3243" NOCOMPRESS

TABLESPACE "USERS" )  LOCAL
declare
*
ERROR at line 1:
ORA-14001: LOCAL clause contradicts previosly specified GLOBAL clause
ORA-06512: at line 9

Работа с API DBMS_METADATA дает корректный результат:

Код: 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.

SQL> create index t_i on t(x, mod(y, 16), x desc, x+y, "TABLESPACE TRNNDEX", "x y") global partition by hash(x) partitions 1;

Index created.

SQL>
SQL> declare
  2    handle    number;
  3    tx_handle handle%type;
  4    xmldoc    xmltype;
  5    ddl       clob;
  6  begin
  7    handle := dbms_metadata.open('INDEX');
  8
  9    dbms_metadata.set_filter(handle, 'SCHEMA', user);
 10    dbms_metadata.set_filter(handle, 'NAME', 'T_I');
 11
 12    tx_handle := dbms_metadata.add_transform(handle, 'SXML');
 13    xmldoc := dbms_metadata.fetch_xml(handle);
 14    dbms_metadata.close(handle);
 15
 16    select xmlquery('declare default element namespace "http://xmlns.oracle.com/ku";
 17                     copy $tmp := $src modify (
 18                       insert node <LOCAL_PARTITIONING/> as last into $tmp//INDEX/TABLE_INDEX)
 19                     return $tmp'
 20                       passing xmldoc as "src"
 21                       returning content)
 22      into xmldoc
 23      from dual;
 24
 25    dbms_lob.createtemporary(ddl, false);
 26    handle := dbms_metadata.openw('INDEX');
 27    tx_handle := dbms_metadata.add_transform(handle, 'SXMLDDL');
 28    dbms_metadata.set_transform_param(tx_handle, 'PARTITIONING', false);
 29    dbms_metadata.set_transform_param(tx_handle, 'PRESERVE_LOCAL', true);
 30    dbms_metadata.convert(handle, xmldoc, ddl);
 31    dbms_metadata.close(handle);
 32    dbms_output.put_line(ddl);
 33    dbms_lob.freetemporary(ddl);
 34  end;
 35  /
CREATE INDEX "TC"."T_I" ON "TC"."T" ("X",MOD("Y",16),"X"
DESC,"X"+"Y","TABLESPACE TRNNDEX","x y")
  PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"  LOCAL

PL/SQL procedure successfully completed.

...

Рейтинг:

0 / 0

21.01.2022, 16:31

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128504

ARRay001

Гость

SeaGate,
да, спасибо, попробую этот спрособ. Хотя с xmlquery особенно не работал и придётся разбираться...

...

Рейтинг:

0 / 0

21.01.2022, 16:34

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128528

ARRay001

Гость

На первом же индексе вылетела ошибка (см. вложение).

Текст DLL такой (имена заменены):

Код: plsql

1.
2.
3.
4.
5.

CREATE INDEX "SX1"."IDX_1" ON "SX1"."TBL1" ("FLD1")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS1"  LOCAL

...

Рейтинг:

0 / 0

21.01.2022, 17:05

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128537

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

ARRay001

SY,
проблема в том, что LOCAL иногда надо вставлять в середину метадаты (когда есть хвост и партиции).

Это про GLOBAL PARTITIONED INDEX? Тогда:

Код: plsql

SQL> DROP TABLE INVOICES PURGE
  2  /

Table dropped.

SQL> CREATE TABLE INVOICES
  2  (INVOICE_NO    NUMBER NOT NULL,
  3   INVOICE_DATE  DATE   NOT NULL)
  4  PARTITION BY RANGE (INVOICE_DATE)
  5  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  6   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  7   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  8   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')),
  9   PARTITION INVOICES_MAX VALUES LESS THAN (MAXVALUE))
 10  /

Table created.

SQL> INSERT
  2    INTO INVOICES
  3    SELECT  EMPNO,
  4            HIREDATE
  5      FROM  EMP
  6  /

14 rows created.

SQL> CREATE INDEX INVOICES_IDX ON INVOICES(INVOICE_DATE,INVOICE_NO) GLOBAL
  2  PARTITION BY RANGE (INVOICE_DATE,INVOICE_NO)
  3  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY'),MAXVALUE),
  4   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY'),MAXVALUE),
  5   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY'),MAXVALUE),
  6   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'),MAXVALUE),
  7   PARTITION INVOICES_MAX VALUES LESS THAN (MAXVALUE,MAXVALUE))
  8  /

Index created.

SQL> SELECT  PARTITIONED
  2    FROM  USER_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

PARTITIONED
-----------
YES

SQL> SELECT  LOCALITY
  2    FROM  USER_PART_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

LOCALITY
--------
GLOBAL

SQL> DECLARE
  2      V_DDL CLOB;
  3  BEGIN
  4      DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',FALSE);
  5      V_DDL := DBMS_METADATA.GET_DDL('INDEX','INVOICES_IDX');
  6      DBMS_OUTPUT.PUT_LINE(V_DDL);
  7      EXECUTE IMMEDIATE 'DROP INDEX INVOICES_IDX';
  8      EXECUTE IMMEDIATE V_DDL || ' LOCAL';
  9  END;
 10  /

  CREATE INDEX "SY47755"."INVOICES_IDX" ON "SY47755"."INVOICES"
("INVOICE_DATE", "INVOICE_NO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

PL/SQL procedure successfully completed.

SQL> SELECT  PARTITIONED
  2    FROM  USER_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

PARTITIONED
-----------
YES

SQL> SELECT  LOCALITY
  2    FROM  USER_PART_INDEXES
  3    WHERE INDEX_NAME = 'INVOICES_IDX'
  4  /

LOCALITY
--------
LOCAL

SQL>

SY.

...

Рейтинг:

0 / 0

21.01.2022, 17:27

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128538

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

ARRay001

На первом же индексе вылетела ошибка (см. вложение).

??? Ты бы RTFM что есть LOCAL INDEX.

SY.

...

Рейтинг:

0 / 0

21.01.2022, 17:28

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128543

ARRay001

Гость

SY,
Мы преобразуем глобальные (global_stats = 'YES') простые (NONUNIQUE) индексы. А вот какой тип индекса PARTITIONED или нет - это уже как попадет... Итог global_stats = 'No' (то, что делает local)

...

Рейтинг:

0 / 0

21.01.2022, 17:32

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128549

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

ARRay001

SY,
Мы преобразуем глобальные (global_stats = 'YES') простые (NONUNIQUE) индексы. А вот какой тип индекса PARTITION или нет - это уже как попадет...

Ты вообще-то понимаешь что такое global_stats?

GLOBAL_STATS VARCHAR2(3) Indicates whether statistics were calculated without merging underlying partitions (YES) or not (NO)

И она собирается/не собирается/как собирается исходя из DBMS_STATS.GATHER...STATS параметра granularity. A LOCAL/GLOBAL индекс это тип партицирования ничего общего с методом сбора статистики не имеющий.

SY.

...

Рейтинг:

0 / 0

21.01.2022, 17:48

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128550

ARRay001

Гость

SY,
значит я ошибся в параметре (не всё ещё изведал). Правильно - это то, что индекс из глобального должен стать локальным, если он не уникальный и уже не локальный.

...

Рейтинг:

0 / 0

21.01.2022, 17:52

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128555

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

ARRay001

Опять 25. Global partitioned index независим от партицирования/не партицирования таблицы и имеет свое партицирование

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

SQL> DROP TABLE INVOICES PURGE
  2  /

Table dropped.

SQL> CREATE TABLE INVOICES
  2  (INVOICE_NO    NUMBER NOT NULL,
  3   INVOICE_DATE  DATE   NOT NULL);

Table created.

SQL> CREATE INDEX INVOICES_IDX ON INVOICES(INVOICE_DATE,INVOICE_NO) GLOBAL
  2  PARTITION BY RANGE (INVOICE_DATE,INVOICE_NO)
  3  (PARTITION INVOICES_Q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY'),MAXVALUE),
  4   PARTITION INVOICES_Q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY'),MAXVALUE),
  5   PARTITION INVOICES_Q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY'),MAXVALUE),
  6   PARTITION INVOICES_Q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'),MAXVALUE),
  7   PARTITION INVOICES_MAX VALUES LESS THAN (MAXVALUE,MAXVALUE))
  8  /

Index created.

SQL>

LOCAL PARTITIONED INDEX наследует партицирование таблицы и посему просто не может быть создан не непартицированой таблице в чем ты и убедился. Так-что исключи GLOBAL PARTITIONED INDEXES на непартицированые таблицы.

SY.

...

Рейтинг:

0 / 0

21.01.2022, 18:12

| Ответить | Цитировать | Написать

Заменить все индексы группы таблиц на локальные или получить список колонок индекса

#40128558

ARRay001

Гость

SY,
Спасибо за подробный ответ! Не каждый день работаю с индексами и ещё много белых пятен...

...

Рейтинг:

0 / 0

21.01.2022, 18:32

| Ответить | Цитировать | Написать

25 сообщений из 28, страница 1 из 2

все

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=40128537&tid=1879588]:	0ms
get settings:	17ms
get forum list:	5ms
check forum access:	1ms
check topic access:	1ms
track hit:	32ms
get topic data:	2ms
get forum data:	1ms
get page messages:	386ms
get tp. blocked users:	0ms
others:	372ms

total:	817ms