[Oracle] Вопрос про организацию индекса

Gasparfx

Вопрос:
Есть таблица, с таким вот DDL
CREATE TABLE ARCH_SIGNALS.TAG_000001 (

  DATETIME TIMESTAMP(6

  VALUE NUMBER(14, 6

  QUALITY NUMBER(3

TABLESPACE ARCH_SIGNALS;



CREATE INDEX ARCH_SIGNALS.IDX_TAG_000001 ON ARCH_SIGNALS.TAG_000001 (DATETIME)

TABLESPACE INDX

LOCAL;

таблица и индекс находятся в разных tablespace'ах и соотв. в разных датафайлах
при заполнении таблицы большим объёмом данных датафайл индекса имеет бОльший размер чем датафайл таблицы (больше примерно на 30%)
Почему так получается? По идее размер индекса должен быть меньше чем таблица, т.к. в него включено только одно поле из таблицы...

Dasar

primary key у таблицы какой?
ps
так в индексе обычно хранится поле из индекса + primary key.

Gasparfx

primary key у таблицы какой?

его там нету, в DDL прописана исчерпывающая информация о структуре таблицы

klyv

а вот сделай

Gasparfx

а вот не нужен, может быть несколько записей с одинаковым полем DATETIME (по которому сделан индекс)

pitrik2

так в индексе обычно хранится поле из индекса + primary key.
а разве не rowid?

pitrik2

По идее размер индекса должен быть меньше чем таблица, т.к. в него включено только одно поле из таблицы...
нууу
если остальные поля маленькие
и это одно поле хреново индексируется (например все значения разные и большие)
то ...

klyv

а, да, поля будут вноситься в индекс только если попросить (явно указать или сделать кластеризованный индекс)
а так - только rowid. но что-то мне подсказывает, что таки надо автору сделать primary key.

Gasparfx

Добавлю: характер данных - записи с монотонным увеличением поля DATETIME (архивация показаний с прибора с частотой записи примерно раз в секунду).
Мне эта база досталась в наследство, возникли сомнения в оптимальности её организации...
На данный момент размер таблицы 80Гб, индекса 120Гб.

klyv

если нужно только это, то зачем отделять индекс? сделай кластеризованный.

0000

Добавлю свои 5 копеек:
Присоединюсь к подозрению, что в индексе rowid хранится, что и увеличивает размер индекса. Так же стоит проверить селективность индекса - как много на одно DATETIME приходится значений в среднем.
Если хочется ускорить чтене данных, то можно почитать Кайта главы о таблицах и о индексах - возможно твою таблицу можно хранить как организованную по индексу (таблица = своему индексу). Правда Кайт только для 8-ой версии Оракла, так что еще лучше официальную документацию почитать.
Самый простой способ - спросить на sql.ru в форуме. Те зубры точно объяснят откуда растут ноги у таблицы и руки у ее создателя.
Еще мысля - рассмотреть возможно партицировать таблицу по полю DATETIME.

Gasparfx

индекс нужно отделять для ускорения записи на диск (датафайлы таблицы и индекса на разных дисках)
таких таблиц несколько тысяч, в каждую из них раз в секунду пишутся данные, нагрузка довольно большая получается

klyv

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

Gasparfx

Спасибо, попробуем :)

Vantucha

INDX
какое-то не очень информативное название таблспейса
там точно ничего кроме этого индекса не лежит?

Gasparfx

Да, специально созданный таблспейс для индексов
лежит на отдельном диске

Gasparfx

не совсем понял как к обычной таблице приделать кластеризованный индекс
ты имеешь ввиду создать кластерную таблицу со своим индексом?

klyv

ээ... нинай, как это называется в Oracle. видимо, так.

Gasparfx

Нет, таблспейс создавался вручную
Oracle 10g

Vantucha

Да, специально созданный таблспейс для индексов
я понял, что для индексов, как раз и имел ввиду, мб, там индексы на другие таблицы еще лежат?
вообще, по-моему такой индекс должен меньше весить, чем сама таблица, даже если одинаковых значений DATETIME практически нет
попробуй посмотреть, сколько реально занимают экстенты под индекс и таблицу
что-то типа:
select sum(bytes) from dba_extents where owner = 'ARCH_SIGNALS' and segment_name = 'TAG_000001'
select sum(bytes) from dba_extents where owner = 'ARCH_SIGNALS' and segment_name = 'IDX_TAG_000001'
бежать может долговато, учитывая размеры датафайлов
Мб датайфайл под таблицу "раздутый" просто.
И есчо: DDL, который ты привел - это тулза какая-то выдает или как?
смущает опция LOCAL в скрипте для индекса.
мне казалось, что локальные индексы можно только для партициированных таблиц создавать

Gasparfx

Индекс однако большего размера:
select sum(bytes) from dba_extents where owner = 'ARCH_SIGNALS' and segment_name = 'TAG_002937'



SUM(BYTES)

48300032  





select sum(bytes) from dba_extents where owner = 'ARCH_SIGNALS' and segment_name = 'IDX_TAG_002937'



SUM(BYTES)

70320128

Нащёт DDL реально косяк вышел
Вот правильный (сгенерил Enterprise Manager):
Таблица и индекс с секционированием
 CREATE TABLE "ARCH_SIGNALS"."TAG_002937" ("DATETIME" 

    "TIMESTAMP(6)", "VALUE" NUMBER(14, 6 "QUALITY" NUMBER(3  

    TABLESPACE "ARCH_SIGNALS" PCTFREE 10 INITRANS 1 MAXTRANS 255 

    NOLOGGING  

    PARTITION BY RANGE ("DATETIME") (PARTITION "P_11_2008"  

    VALUES LESS THAN  (TIMESTAMP' 2008-11-01 00:00:00') 

    TABLESPACE "ARCH_SIGNALS"  PCTFREE 10 PCTUSED 0 INITRANS 1 

    MAXTRANS 255 

    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 

    2147483645 PCTINCREASE 0) 

    LOGGING,

    PARTITION "P_12_2008"  

    VALUES LESS THAN  (TIMESTAMP' 2008-12-01 00:00:00') 

    TABLESPACE "ARCH_SIGNALS"  PCTFREE 10 PCTUSED 0 INITRANS 1 

    MAXTRANS 255 

    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 

    2147483645 PCTINCREASE 0) 

    LOGGING,

    PARTITION "P_1_2009"  

    VALUES LESS THAN  (TIMESTAMP' 2009-01-01 00:00:00') 

    TABLESPACE "ARCH_SIGNALS"  PCTFREE 10 PCTUSED 0 INITRANS 1 

    MAXTRANS 255 

    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 

    2147483645 PCTINCREASE 0) 

    LOGGING,

    PARTITION "P_2_2009"  

    VALUES LESS THAN  (TIMESTAMP' 2009-02-01 00:00:00') 

    TABLESPACE "ARCH_SIGNALS"  PCTFREE 10 PCTUSED 0 INITRANS 1 

    MAXTRANS 255 

    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 

    2147483645 PCTINCREASE 0) LOGGING)



CREATE INDEX "ARCH_SIGNALS"."IDX_TAG_002937" 

    ON "ARCH_SIGNALS"."TAG_002937"  ("DATETIME") 

    TABLESPACE "INDX" PCTFREE 10 INITRANS 2 MAXTRANS 255 

    STORAGE ( FREELISTS 1 FREELIST GROUPS 1) 

    NOLOGGING LOCAL (PARTITION "P_11_2008" 

    TABLESPACE "INDX"  PCTFREE 10 INITRANS 2 MAXTRANS 255 

    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 

    2147483645 PCTINCREASE 0) 

    LOGGING,

    PARTITION "P_12_2008" 

    TABLESPACE "INDX"  PCTFREE 10 INITRANS 2 MAXTRANS 255 

    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 

    2147483645 PCTINCREASE 0) 

    LOGGING,

    PARTITION "P_1_2009" 

    TABLESPACE "INDX"  PCTFREE 10 INITRANS 2 MAXTRANS 255 

    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 

    2147483645 PCTINCREASE 0) 

    LOGGING,

    PARTITION "P_2_2009" 

    TABLESPACE "INDX"  PCTFREE 10 INITRANS 2 MAXTRANS 255 

    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 

    2147483645 PCTINCREASE 0) LOGGING)

Примерный вид данных в таблице:
select * from ARCH_SIGNALS.TAG_002937 where datetime> systimestamp - interval '1' minute



DATETIME                     	VALUE    	QUALITY

26.12.2008 13:23:00,000000000	19,2     	0      

26.12.2008 13:23:12,263000000	16,800001	0      

26.12.2008 13:23:14,029000000	19,2     	0      

26.12.2008 13:23:22,763000000	16,800001	0      

26.12.2008 13:23:24,529000000	19,2     	0      

26.12.2008 13:23:26,154000000	16,800001	0      

26.12.2008 13:23:27,810000000	19,2     	0      

26.12.2008 13:23:29,763000000	16,800001	0      

26.12.2008 13:23:31,295000000	19,2     	0      

26.12.2008 13:23:32,826000000	19,2     	0      

26.12.2008 13:23:34,810000000	16,800001	0      

26.12.2008 13:23:36,435000000	19,2     	0      

26.12.2008 13:23:40,045000000	16,800001	0      

26.12.2008 13:23:43,341000000	19,2     	0

Gasparfx

Думаю пока остановиться на варианте с IOT, счас экспериментирую с ним, заполняя большими объёмами данных
Оставить комментарий
Имя или ник:
Комментарий: