Áú»¢¶Ä²©

4 §±§â§Ú§á§â§Ö§Þ§Ñ auditlog §ä§Ñ§Ò§Ö§Ý§Ö §Ù§Ñ §á§Ñ§â§ä§Ú§è§Ú§à§ß§Ú§ã§Ñ?§Ö

§±§â§Ö§Ô§Ý§Ö§Õ

§¯§Ö§Ü§Ö §Ò§Ñ§Ù§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ (§ß§Ñ §á§â§Ú§Þ§Ö§â, MySQL) §Ù§Ñ§ç§ä§Ö§Ó§Ñ?§å §Õ§Ñ §Ü§à§Ý§à§ß§Ñ §Ù§Ñ §á§Ñ§â§ä§Ú§è§Ú§à§ß§Ú§ã§Ñ?§Ö §Ò§å§Õ§Ö §Õ§Ö§à ?§Ö§Õ§Ú§ß§ã§ä§Ó§Ö§ß§à§Ô §à§Ô§â§Ñ§ß§Ú§é§Ö?§Ñ §ä§Ñ§Ò§Ö§Ý§Ö. §³§ä§à§Ô§Ñ, §Õ§Ñ §Ò§Ú§ã§ä§Ö §á§à§Õ§Ö§Ý§Ú§Ý§Ú §ä§Ñ§Ò§Ö§Ý§å auditlog §á§à §Ó§â§Ö§Þ§Ö§ß§å, §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é §Þ§à§â§Ñ §Õ§Ñ §ã§Ö §á§â§à§Þ§Ö§ß§Ú §Ú§Ù auditid §å §Ü§à§Þ§á§à§Ù§Ú§ä§ß§Ú §Ü?§å§é auditid + clock.

§°§Ó§Ñ §ã§Ö§Ü§è§Ú?§Ñ §á§â§å§Ø§Ñ §å§á§å§ä§ã§ä§Ó§Ñ §Ù§Ñ §á§â§à§Þ§Ö§ß§å §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §ä§Ñ§Ò§Ö§Ý§Ö auditlog.

§±§Ñ§â§ä§Ú§è§Ú§à§ß§Ú§â§Ñ?§Ö §ä§Ñ§Ò§Ö§Ý§Ö auditlog §Þ§à§Ø§Ö §á§à§Ò§à?§ê§Ñ§ä§Ú, §ß§Ñ §á§â§Ú§Þ§Ö§â, §à§Õ§â§Ø§Ñ§Ó§Ñ?§Ö §å §Ó§Ö§Ý§Ú§Ü§Ú§Þ §à§Ü§â§å§Ø§Ö?§Ú§Þ§Ñ. §ª§Ñ§Ü§à Áú»¢¶Ä²© housekeeping §ä§â§Ö§ß§å§ä§ß§à §ß§Ö §Þ§à§Ø§Ö §Õ§Ñ §Ú§ã§Ü§à§â§Ú§ã§ä§Ú §á§â§Ö§Õ§ß§à§ã§ä§Ú §á§Ñ§â§ä§Ú§è§Ú§à§ß§Ú§â§Ñ§ß§Ú§ç §ä§Ñ§Ò§Ö§Ý§Ñ (§à§ã§Ú§Þ §Ù§Ñ TimescaleDB), §Þ§à§Ø§Ö§ä§Ö §à§ß§Ö§Þ§à§Ô§å?§Ú§ä§Ú Áú»¢¶Ä²© §à§Õ§â§Ø§Ñ§Ó§Ñ?§Ö §Ú §Ú§Ù§Ò§â§Ú§ã§Ñ§ä§Ú §á§Ñ§â§ä§Ú§è§Ú?§Ö §á§à§Þ§à?§å §ã§Ü§â§Ú§á§ä§Ú.

§°§Õ Áú»¢¶Ä²©-§Ñ 7.0, §ä§Ñ§Ò§Ö§Ý§Ñ auditlog §Ù§Ñ TimescaleDB ?§Ö §Ü§à§ß§Ó§Ö§â§ä§à§Ó§Ñ§ß§Ñ §å §ç§Ú§á§Ö§â§ä§Ñ§Ò§Ö§Ý§å, §ê§ä§à §à§Þ§à§Ô§å?§Ñ§Ó§Ñ §Õ§à§Þ§Ñ?§Ú§è§Ú §Õ§Ñ §Ú§ã§á§å§ã§ä§Ú §á§à§Õ§Ñ§ä§Ü§Ö §á§à §Õ§Ö§Ý§à§Ó§Ú§Þ§Ñ. §¥§Ñ §Ò§Ú§ã§ä§Ö §ß§Ñ§Õ§à§Ô§â§Ñ§Õ§Ú§Ý§Ú §á§à§ã§ä§à?§Ö?§å §ä§Ñ§Ò§Ö§Ý§å §Ñ§å§Õ§Ú§ä§Ý§à§Ô §å §ç§Ú§á§Ö§â§ä§Ñ§Ò§Ö§Ý§å, §á§à§Ô§Ý§Ö§Õ§Ñ?§ä§Ö §¯§Ñ§Õ§à§Ô§â§Ñ§Õ?§å TimescaleDB §ê§Ö§Þ§Ö.

MySQL

§£§Ñ§Ø§ß§Ö §ß§Ñ§á§à§Þ§Ö§ß§Ö §à §à§Ò§ß§Ñ§Ó?§Ñ?§å §Ú§ß§Õ§Ö§Ü§ã§Ñ

MySQL §Ñ§å§ä§à§Þ§Ñ§ä§ã§Ü§Ú §á§à§ß§à§Ó§à §Ô§â§Ñ§Õ§Ú §Ú§ß§Õ§Ö§Ü§ã§Ö §Ù§Ñ §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é §ä§à§Ü§à§Þ §à§á§Ö§â§Ñ§è§Ú?§Ö ALTER TABLE. §®§Ö?§å§ä§Ú§Þ, §Ó§Ö§à§Þ§Ñ ?§Ö §á§â§Ö§á§à§â§å§é?§Ú§Ó§à §Õ§Ñ §â§å§é§ß§à §á§à§ß§à§Ó§à §ß§Ñ§á§â§Ñ§Ó§Ú§ä§Ö §Ú§ß§Õ§Ö§Ü§ã§Ö §ã§Ñ §ß§Ñ§â§Ö§Õ§Ò§à§Þ OPTIMIZE TABLE §Õ§Ñ §Ò§Ú§ã§ä§Ö §à§ã§Ú§Ô§å§â§Ñ§Ý§Ú §à§á§ä§Ú§Þ§Ñ§Ý§ß§Ö §á§Ö§â§æ§à§â§Þ§Ñ§ß§ã§Ö §Ò§Ñ§Ù§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.

§°§Ò§ß§à§Ó§Ñ §Ú§ß§Õ§Ö§Ü§ã§Ñ §Þ§à§Ø§Ö §á§â§Ú§Ó§â§Ö§Þ§Ö§ß§à §Õ§Ñ §Ù§Ñ§ç§ä§Ö§Ó§Ñ §à§ß§à§Ý§Ú§Ü§à §Õ§à§Õ§Ñ§ä§ß§à§Ô §á§â§à§ã§ä§à§â§Ñ §ß§Ñ §Õ§Ú§ã§Ü§å §Ü§à§Ý§Ú§Ü§à §ã§Ñ§Þ§Ñ §ä§Ñ§Ò§Ö§Ý§Ñ §Ü§à§â§Ú§ã§ä§Ú. §¥§Ñ §Ò§Ú§ã§ä§Ö §Õ§à§Ò§Ú§Ý§Ú §ä§â§Ö§ß§å§ä§ß§å §Ó§Ö§Ý§Ú§é§Ú§ß§å §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ §Ú §Ú§ß§Õ§Ö§Ü§ã§Ñ, §Þ§à§Ø§Ö§ä§Ö §Ú§Ù§Ó§â§ê§Ú§ä§Ú §ã§Ý§Ö§Õ§Ö?§Ö §ß§Ñ§â§Ö§Õ§Ò§Ö:

ANALYZE TABLE auditlog;
       SHOW TABLE STATUS LIKE 'auditlog';

§¡§Ü§à ?§Ö §Õ§à§ã§ä§å§á§Ñ§ß §á§â§à§ã§ä§à§â §ß§Ñ §Õ§Ú§ã§Ü§å §Ù§Ñ§Ò§â§Ú?§Ñ§Ó§Ñ?§å?§Ú, §á§â§Ñ§ä§Ú§ä§Ö §å§á§å§ä§ã§ä§Ó§Ñ [§±§â§à§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §Ò§Ö§Ù §á§â§Ö§Ü§Ú§Õ§Ñ §â§Ñ§Õ§Ñ] (#altering-primary-key-without-downtime). §¥§à§ã§ä§å§á§ß§Ö §ã§å §Ú §Õ§â§å§Ô§Ö §à§á§è§Ú?§Ö:

• §±§à§Ó§Ö?§Ñ?§Ö MySQL §á§Ñ§â§Ñ§Þ§Ö§ä§â§Ñ §Þ§à§Ø§Ö §á§à§Þ§à?§Ú §Õ§Ñ §ã§Ö §ã§Þ§Ñ?§Ú §å§á§à§ä§â§Ö§Ò§Ñ §á§â§à§ã§ä§à§â§Ñ §ß§Ñ §Õ§Ú§ã§Ü§å §á§â§Ú§Ý§Ú§Ü§à§Þ §â§å§é§ß§à§Ô §à§Ò§ß§Ñ§Ó?§Ñ?§Ñ §Ú§ß§Õ§Ö§Ü§ã§Ñ. §®§Ö?§å§ä§Ú§Þ, §Ú§Ù§Þ§Ö§ß§Ñ §à§Ó§Ö §á§â§à§Þ§Ö§ß?§Ú§Ó§Ö §Þ§à§Ø§Ö §å§ä§Ú§è§Ñ§ä§Ú §ß§Ñ §å§Ü§å§á§ß§å §å§á§à§ä§â§Ö§Ò§å §Þ§Ö§Þ§à§â§Ú?§Ö §Ò§Ñ§Ù§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.
• §²§Ñ§Ù§Þ§Ú§ã§Ý§Ú§ä§Ö §à §à§ã§Ý§à§Ò§Ñ?§Ñ?§å §á§â§à§ã§ä§à§â§Ñ §Ò§â§Ú§ã§Ñ?§Ö§Þ §á§à§ä§Ö§ß§è§Ú?§Ñ§Ý§ß§à §ß§Ö§á§à§ä§â§Ö§Ò§ß§Ú§ç §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.
• §²§Ñ§Ù§Þ§Ú§ã§Ý§Ú§ä§Ö §à §ã§Þ§Ñ?§Ö?§å §á§Ñ§â§Ñ§Þ§Ö§ä§â§Ñ §±§Ö§â§Ú§à§Õ §ã§Ü§Ý§Ñ§Õ§Ú§ê§ä§Ö?§Ñ §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ §à§Õ§â§Ø§Ñ§Ó§Ñ?§Ö §á§â§Ö §ß§Ö§Ô§à §ê§ä§à §Ú§Ù§Ó§â§ê§Ú§ä§Ö §à§Õ§â§Ø§Ñ§Ó§Ñ?§Ö.

§ª§Ù§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §å§Ù §Ù§Ñ§ã§ä§à?§Ö

1. §°§Ò§â§Ú§ê§Ú§ä§Ö §ä§â§Ö§ß§å§ä§ß§Ú §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é §ä§Ñ§Ò§Ö§Ý§Ö auditlog §Ú §Õ§à§Õ§Ñ?§ä§Ö §ß§à§Ó§Ú §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é.

ALTER TABLE auditlog DROP PRIMARY KEY, ADD PRIMARY KEY (auditid, clock);

2. §±§à§ß§à§Ó§à §Ú§Ù§Ô§â§Ñ§Õ§Ú§ä§Ö §Ú§ß§Õ§Ö§Ü§ã§Ö (§à§á§è§Ú§à§ß§à, §Ñ§Ý§Ú §ã§Ö §Ó§Ö§à§Þ§Ñ §á§â§Ö§á§à§â§å§é§å?§Ö, §á§à§Ô§Ý§Ö§Õ§Ñ?§ä§Ö §£§Ñ§Ø§ß§Ö §ß§Ñ§á§à§Þ§Ö§ß§Ö §à §á§à§ß§à§Ó§ß§à? §Ú§Ù§Ô§â§Ñ§Õ?§Ú §Ú§ß§Õ§Ö§Ü§ã§Ñ).

OPTIMIZE TABLE auditlog;

§ª§Ù§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §Ò§Ö§Ù §Ù§Ñ§ã§ä§à?§Ñ

§²§å§é§ß§Ú §Þ§Ö§ä§à§Õ §Ú§Ù§Þ§Ö§ß§Ö §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ ?§Ö §à§á§Ú§ã§Ñ§ß §à§Ó§Õ§Ö. §¡§Ý§ä§Ö§â§ß§Ñ§ä§Ú§Ó§ß§à, §Þ§à§Ø§Ö§ä§Ö §Ü§à§â§Ú§ã§ä§Ú§ä§Ú Percona §Ñ§Ý§Ñ§ä . §°§Ó§Ñ? §Ñ§Ý§Ñ§ä §Ñ§å§ä§à§Þ§Ñ§ä§ã§Ü§Ú §Ú§Ù§Ó§â§ê§Ñ§Ó§Ñ §ã§Ý§Ö§Õ§Ö?§Ö §Ñ§Ü§è§Ú?§Ö, §Ú§ã§ä§à§Ó§â§Ö§Þ§Ö§ß§à §Þ§Ú§ß§Ú§Þ§Ú§Ù§å?§å?§Ú §á§â§à§ã§ä§à§â §Ü§à?§Ú §ã§Ö §Ü§à§â§Ú§ã§ä§Ú §Ù§Ñ §Ú§Ù§Þ§Ö§ß§å §ä§Ñ§Ò§Ö§Ý§Ö auditlog.

1. §¬§â§Ö§Ú§â§Ñ?§ä§Ö §ß§à§Ó§å §ä§Ñ§Ò§Ö§Ý§å §ã§Ñ §ß§à§Ó§Ú§Þ §á§â§Ú§Þ§Ñ§â§ß§Ú§Þ §Ü?§å§é§Ö§Þ §Ú §Ü§â§Ö§Ú§â§Ñ?§ä§Ö §Ú§ß§Õ§Ö§Ü§ã§Ö.

CREATE TABLE `auditlog_new` (
         `auditid`            varchar(25)                               NOT NULL,
         `userid`             bigint unsigned                           NULL,
         `username`           varchar(100)    DEFAULT ''                NOT NULL,
         `clock`              integer         DEFAULT '0'               NOT NULL,
         `ip`                 varchar(39)     DEFAULT ''                NOT NULL,
         `action`             integer         DEFAULT '0'               NOT NULL,
         `resourcetype`       integer         DEFAULT '0'               NOT NULL,
         `resourceid`         bigint unsigned                           NULL,
         `resource_cuid`      varchar(25)                               NULL,
         `resourcename`       varchar(255)    DEFAULT ''                NOT NULL,
         `recordsetid`        varchar(25)                               NOT NULL,
         `details`            longtext                                  NOT NULL,
         PRIMARY KEY (auditid,clock)
       ) ENGINE=InnoDB;
       CREATE INDEX `auditlog_1` ON `auditlog_new` (`userid`,`clock`);
       CREATE INDEX `auditlog_2` ON `auditlog_new` (`clock`);
       CREATE INDEX `auditlog_3` ON `auditlog_new` (`resourcetype`,`resourceid`);

2. §©§Ñ§Þ§Ö§ß§Ú§ä§Ö §ä§Ñ§Ò§Ö§Ý§Ö.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. §¬§à§á§Ú§â§Ñ?§ä§Ö §á§à§Õ§Ñ§ä§Ü§Ö §Ú§Ù §ã§ä§Ñ§â§Ö §ä§Ñ§Ò§Ö§Ý§Ö §å §ß§à§Ó§å §ä§Ñ§Ò§Ö§Ý§å.

INSERT INTO auditlog SELECT * FROM auditlog_old;

§°§Ó§à §Þ§à§Ø§Ö §Ò§Ú§ä§Ú §å§é§Ú?§Ö§ß§à §å §Õ§Ö§Ý§à§Ó§Ú§Þ§Ñ (§Ó§Ú§ê§Ö INSERT INTO §ß§Ñ§â§Ö§Õ§Ò§Ú §ã§Ñ WHERE clock §Ü§Ý§Ñ§å§Ù§å§Ý§Ñ§Þ§Ñ §á§à §á§à§ä§â§Ö§Ò§Ú) §Ü§Ñ§Ü§à §Ò§Ú §ã§Ö §Ú§Ù§Ò§Ö§Ô§Ý§à §á§â§Ö§Ü§à§Þ§Ö§â§ß§à §Ü§à§â§Ú§ê?§Ö?§Ö §â§Ö§ã§å§â§ã§Ñ.

4. §°§Ò§â§Ú§ê§Ú§ä§Ö §ã§ä§Ñ§â§å §ä§Ñ§Ò§Ö§Ý§å.

DROP TABLE auditlog_old;

PostgreSQL

§£§Ñ§Ø§ß§Ö §ß§Ñ§á§à§Þ§Ö§ß§Ö §à §à§Ò§ß§Ñ§Ó?§Ñ?§å §Ú§ß§Õ§Ö§Ü§ã§Ñ

PostgreSQL §Ñ§å§ä§à§Þ§Ñ§ä§ã§Ü§Ú §á§à§ß§à§Ó§à §Ô§â§Ñ§Õ§Ú §Ú§ß§Õ§Ö§Ü§ã§Ö §Ù§Ñ §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é §ä§à§Ü§à§Þ §à§á§Ö§â§Ñ§è§Ú?§Ö ALTER TABLE. §®§Ö?§å§ä§Ú§Þ, §Ó§Ö§à§Þ§Ñ §ã§Ö §á§â§Ö§á§à§â§å§é§å?§Ö §Õ§Ñ §ã§Ö §Ú§ß§Õ§Ö§Ü§ã§Ú §â§å§é§ß§à §â§Ö§Ü§à§ß§ã§ä§â§å§Ú§ê§å §á§à§Þ§à?§å §Ú§Ù§â§Ñ§Ù§Ñ REINDEX TABLE CONCURRENTLY §Õ§Ñ §Ò§Ú§ã§ä§Ö §à§Ò§Ö§Ù§Ò§Ö§Õ§Ú§Ý§Ú §à§á§ä§Ú§Þ§Ñ§Ý§ß§Ö §á§Ö§â§æ§à§â§Þ§Ñ§ß§ã§Ö §Ò§Ñ§Ù§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.

§°§Ò§ß§à§Ó§Ñ §Ú§ß§Õ§Ö§Ü§ã§Ñ §Þ§à§Ø§Ö §á§â§Ú§Ó§â§Ö§Þ§Ö§ß§à §Õ§Ñ §Ù§Ñ§ç§ä§Ö§Ó§Ñ §Õ§à §ä§â§Ú §á§å§ä§Ñ §Ó§Ú§ê§Ö §á§â§à§ã§ä§à§â§Ñ §ß§Ñ §Õ§Ú§ã§Ü§å §Ü§à?§Ú §Ú§ß§Õ§Ö§Ü§ã§Ú §ä§â§Ö§ß§å§ä§ß§à §Ü§à§â§Ú§ã§ä§Ö. §¥§Ñ §Ò§Ú§ã§ä§Ö §Õ§à§Ò§Ú§Ý§Ú §ä§â§Ö§ß§å§ä§ß§å §Ó§Ö§Ý§Ú§é§Ú§ß§å §Ú§ß§Õ§Ö§Ü§ã§Ñ, §Þ§à§Ø§Ö§ä§Ö §Ú§Ù§Ó§â§ê§Ú§ä§Ú §ã§Ý§Ö§Õ§Ö?§Ú §å§á§Ú§ä:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

§¡§Ü§à ?§Ö §Õ§à§ã§ä§å§á§Ñ§ß §á§â§à§ã§ä§à§â §ß§Ñ §Õ§Ú§ã§Ü§å §Ù§Ñ§Ò§â§Ú?§Ñ§Ó§Ñ?§å?§Ú, §á§â§Ñ§ä§Ú§ä§Ö §å§á§å§ä§ã§ä§Ó§Ñ §±§â§à§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §Ò§Ö§Ù §á§â§Ö§Ü§Ú§Õ§Ñ §â§Ñ§Õ§Ñ. §¥§à§ã§ä§å§á§ß§Ö §ã§å §Ú §Õ§â§å§Ô§Ö §à§á§è§Ú?§Ö:

• §±§à§Ó§Ö?§Ñ?§Ö PostgreSQL §á§Ñ§â§Ñ§Þ§Ö§ä§â§Ñ §Þ§à§Ø§Ö §á§à§Þ§à?§Ú §Õ§Ñ §ã§Ö §ã§Þ§Ñ?§Ú §å§á§à§ä§â§Ö§Ò§Ñ §á§â§à§ã§ä§à§â§Ñ §ß§Ñ §Õ§Ú§ã§Ü§å §Ü§Ñ§Õ§Ñ §ã§Ö §â§å§é§ß§à §Ú§ß§Õ§Ö§Ü§ã§Ú §â§Ö§Ü§à§ß§ã§ä§â§å§Ú§ê§å. §®§Ö?§å§ä§Ú§Þ, §Ú§Ù§Þ§Ö§ß§Ñ §à§Ó§Ö §á§â§à§Þ§Ö§ß?§Ú§Ó§Ö §Þ§à§Ø§Ö §å§ä§Ú§è§Ñ§ä§Ú §ß§Ñ §å§Ü§å§á§ß§å §å§á§à§ä§â§Ö§Ò§å §Þ§Ö§Þ§à§â§Ú?§Ö §Ò§Ñ§Ù§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.
• §¡§Ü§à §Ú§Þ§Ñ§ä§Ö §Õ§â§å§Ô§Ú §Õ§Ú§ã§Ü §Ú§Ý§Ú §ä§Ñ§Ò§Ö§Ý§Ñ§â§ß§Ú §á§â§à§ã§ä§à§â §ã§Ñ §Ó§Ú§ê§Ö §â§Ñ§ã§á§à§Ý§à§Ø§Ú§Ó§à§Ô §á§â§à§ã§ä§à§â§Ñ, §â§Ñ§Ù§Þ§Ú§ã§Ý§Ú§ä§Ö §à §á§â§à§Þ§Ö§ß§Ú §á§â§Ú§Ó§â§Ö§Þ§Ö§ß§Ö §Ý§à§Ü§Ñ§è§Ú?§Ö §Ù§Ñ §ã§Ü§Ý§Ñ§Õ§Ú§ê§ä§Ö?§Ö §Ù§Ñ §â§Ö§Ü§à§ß§ã§ä§â§å§Ü§è§Ú?§å §Ú§ß§Õ§Ö§Ü§ã§Ñ. §®§à§Ø§Ö§ä§Ö §Õ§Ñ §á§à§Õ§Ö§ã§Ú§ä§Ö PostgreSQL §á§Ñ§â§Ñ§Þ§Ö§ä§Ñ§â §Õ§Ñ §ß§Ñ§Ó§Ö§Õ§Ö§ä§Ö §Õ§â§å§Ô§Ñ§é§Ú?§Ú §á§â§à§ã§ä§à§â §ä§Ñ§Ò§Ö§Ý§Ö §Ù§Ñ §á§â§Ú§Ó§â§Ö§Þ§Ö§ß§Ö §à§Ò?§Ö§Ü§ä§Ö.
• §²§Ñ§Ù§Þ§Ú§ã§Ý§Ú§ä§Ö §à§ã§Ý§à§Ò§Ñ?§Ñ?§Ö §á§â§à§ã§ä§à§â§Ñ §Ò§â§Ú§ã§Ñ?§Ö§Þ §á§à§ä§Ö§ß§è§Ú?§Ñ§Ý§ß§à §ß§Ö§á§à§ä§â§Ö§Ò§ß§Ú§ç §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.
• §²§Ñ§Ù§Þ§Ú§ã§Ý§Ú§ä§Ö §ã§Þ§Ñ?§Ö?§Ö §á§Ñ§â§Ñ§Þ§Ö§ä§â§Ñ §±§Ö§â§Ú§à§Õ §é§å§Ó§Ñ?§Ñ §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ §à§Õ§â§Ø§Ñ§Ó§Ñ?§Ö §á§â§Ö §ß§Ö§Ô§à §ê§ä§à §Ú§Ù§Ó§â§ê§Ú§ä§Ö §à§Õ§â§Ø§Ñ§Ó§Ñ?§Ö.

§±§â§à§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §ã§Ñ §Ù§Ñ§ã§ä§à?§Ö§Þ

1. §µ§Ü§Ý§à§ß§Ú§ä§Ö §ä§â§Ö§ß§å§ä§ß§Ú §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é §ä§Ñ§Ò§Ö§Ý§Ö auditlog §Ú §Õ§à§Õ§Ñ?§ä§Ö §ß§à§Ó§Ú §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é.

ALTER TABLE auditlog DROP CONSTRAINT auditlog_pkey;
       ALTER TABLE auditlog ADD PRIMARY KEY (auditid,clock);

2. §±§à§ß§à§Ó§à §Ú§Ù§Ô§â§Ñ§Õ§Ú§ä§Ö §Ú§ß§Õ§Ö§Ü§ã§Ö (§à§á§è§Ú§à§ß§à, §Ñ§Ý§Ú §ã§Ö §Ó§Ö§à§Þ§Ñ§á§â§Ö§á§à§â§å§é§å?§Ö, §á§à§Ô§Ý§Ö§Õ§Ñ?§ä§Ö §£§Ñ§Ø§ß§Ö §ß§Ñ§á§à§Þ§Ö§ß§Ö §à §á§à§ß§à§Ó§ß§à? §Ú§Ù§Ô§â§Ñ§Õ?§Ú §Ú§ß§Õ§Ö§Ü§ã§Ñ).

REINDEX TABLE CONCURRENTLY auditlog;

§±§â§à§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §Ò§Ö§Ù §Ù§Ñ§ã§ä§à?§Ñ

§°§Ó§Õ§Ö ?§Ö §à§á§Ú§ã§Ñ§ß§Ñ §â§å§é§ß§Ñ §Þ§Ö§ä§à§Õ§Ñ §á§â§à§Þ§Ö§ß§Ö §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ. §¡§Ý§ä§Ö§â§ß§Ñ§ä§Ú§Ó§ß§à, §Ö§Ü§ã§ä§Ö§ß§Ù§Ú?§Ñ pg_repack §ã§Ö §Þ§à§Ø§Ö §â§Ñ§Ù§Þ§à§ä§â§Ú§ä§Ú §Ù§Ñ §Ü§â§Ö§Ú§â§Ñ?§Ö §ß§à§Ó§Ö §ä§Ñ§Ò§Ö§Ý§Ö, §Ü§à§á§Ú§â§Ñ?§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ §Ú §Ù§Ñ§Þ§Ö§ß§å §ä§Ñ§Ò§Ö§Ý§Ñ.

1. §¬§â§Ö§Ú§â§Ñ?§ä§Ö §ß§à§Ó§å §ä§Ñ§Ò§Ö§Ý§å §ã§Ñ §ß§à§Ó§Ú§Þ §á§â§Ú§Þ§Ñ§â§ß§Ú§Þ §Ü?§å§é§Ö§Þ §Ú §Ü§â§Ö§Ú§â§Ñ?§ä§Ö §Ú§ß§Õ§Ö§Ü§ã§Ö.

CREATE TABLE auditlog_new (
         auditid              varchar(25)                               NOT NULL,
         userid               bigint                                    NULL,
         username             varchar(100)    DEFAULT ''                NOT NULL,
         clock                integer         DEFAULT '0'               NOT NULL,
         ip                   varchar(39)     DEFAULT ''                NOT NULL,
         action               integer         DEFAULT '0'               NOT NULL,
         resourcetype         integer         DEFAULT '0'               NOT NULL,
         resourceid           bigint                                    NULL,
         resource_cuid        varchar(25)                               NULL,
         resourcename         varchar(255)    DEFAULT ''                NOT NULL,
         recordsetid          varchar(25)                               NOT NULL,
         details              text            DEFAULT ''                NOT NULL,
         PRIMARY KEY (auditid,clock)
       );
       CREATE INDEX auditlog_new_1 ON auditlog_new (userid,clock);
       CREATE INDEX auditlog_new_2 ON auditlog_new (clock);
       CREATE INDEX auditlog_new_3 ON auditlog_new (resourcetype,resourceid);

2. §©§Ñ§Þ§Ö§ß§Ú§ä§Ö §ä§Ñ§Ò§Ö§Ý§Ö.

ALTER TABLE auditlog RENAME TO auditlog_old;
       ALTER TABLE auditlog_new RENAME TO auditlog;

3. §¬§à§á§Ú§â§Ñ?§ä§Ö §á§à§Õ§Ñ§ä§Ü§Ö §Ú§Ù §ã§ä§Ñ§â§Ö §ä§Ñ§Ò§Ö§Ý§Ö §å §ß§à§Ó§å §ä§Ñ§Ò§Ö§Ý§å.

INSERT INTO auditlog SELECT * FROM auditlog_old;

§°§Ó§à §ã§Ö §Þ§à§Ø§Ö §å§â§Ñ§Õ§Ú§ä§Ú §å §Õ§Ö§Ý§à§Ó§Ú§Þ§Ñ (§Ó§Ú§ê§Ö §ß§Ñ§â§Ö§Õ§Ò§Ú INSERT INTO §ã§Ñ §Ü§Ý§Ñ§å§Ù§å§Ý§Ñ§Þ§Ñ WHERE clock §á§à §á§à§ä§â§Ö§Ò§Ú) §Õ§Ñ §Ò§Ú §ã§Ö §Ú§Ù§Ò§Ö§Ô§Ý§à §á§â§Ö§Ü§à§Þ§Ö§â§ß§à §Ü§à§â§Ú§ê?§Ö?§Ö §â§Ö§ã§å§â§ã§Ñ.

4. §ª§Ù§Ò§â§Ú§ê§Ú§ä§Ö §ã§ä§Ñ§â§å §ä§Ñ§Ò§Ö§Ý§å.

DROP TABLE auditlog_old;

Oracle

§£§Ñ§Ø§ß§Ö §ß§Ñ§á§à§Þ§Ö§ß§Ö §à §à§Ò§ß§Ñ§Ó?§Ñ?§å §Ú§ß§Õ§Ö§Ü§ã§Ñ

Oracle §Ñ§å§ä§à§Þ§Ñ§ä§ã§Ü§Ú §á§à§ß§à§Ó§à §Ô§â§Ñ§Õ§Ú §Ú§ß§Õ§Ö§Ü§ã§Ö §Ù§Ñ §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é §ä§à§Ü§à§Þ §à§á§Ö§â§Ñ§è§Ú?§Ö ALTER TABLE. §®§Ö?§å§ä§Ú§Þ, §Ó§Ö§à§Þ§Ñ ?§Ö §á§â§Ö§á§à§â§å§é?§Ú§Ó§à §Õ§Ñ §ã§Ö §Ú§ß§Õ§Ö§Ü§ã§Ú §â§å§é§ß§à §â§Ö§Ü§à§ß§ã§ä§â§å§Ú§ê§å §ã§Ñ §ß§Ñ§â§Ö§Õ§Ò§Ñ§Þ§Ñ ALTER INDEX <index> REBUILD PARALLEL §Ü§Ñ§Ü§à §Ò§Ú §ã§Ö §à§Ò§Ö§Ù§Ò§Ö§Õ§Ú§Ý§Ö §à§á§ä§Ú§Þ§Ñ§Ý§ß§Ö §á§Ö§â§æ§à§â§Þ§Ñ§ß§ã§Ö §Ò§Ñ§Ù§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.

§°§Ò§ß§à§Ó§Ñ §Ú§ß§Õ§Ö§Ü§ã§Ñ §Þ§à§Ø§Ö §á§â§Ú§Ó§â§Ö§Þ§Ö§ß§à §Ù§Ñ§ç§ä§Ö§Ó§Ñ§ä§Ú §Ù§ß§Ñ§é§Ñ?§Ñ§ß §á§â§à§ã§ä§à§â §ß§Ñ §Õ§Ú§ã§Ü§å.

§¡§Ü§à ?§Ö §Õ§à§ã§ä§å§á§Ñ§ß §á§â§à§ã§ä§à§â §ß§Ñ §Õ§Ú§ã§Ü§å §Ù§Ñ§Ò§â§Ú?§Ñ§Ó§Ñ?§å?§Ú, §á§â§Ñ§ä§Ú§ä§Ö §å§á§å§ä§ã§ä§Ó§Ñ §±§â§à§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §Ò§Ö§Ù §á§â§Ö§Ü§Ú§Õ§Ñ §â§Ñ§Õ§Ñ. §¥§à§ã§ä§å§á§ß§Ö §ã§å §Ú §Õ§â§å§Ô§Ö §à§á§è§Ú?§Ö:

• §±§à§Ó§Ö?§Ñ?§Ö Oracle §á§Ñ§â§Ñ§Þ§Ö§ä§â§Ñ §Þ§à§Ø§Ö §á§à§Þ§à?§Ú §Õ§Ñ §ã§Ö §ã§Þ§Ñ?§Ú §å§á§à§ä§â§Ö§Ò§Ñ §á§â§à§ã§ä§à§â§Ñ §ß§Ñ §Õ§Ú§ã§Ü§å §á§â§Ú§Ý§Ú§Ü§à§Þ §â§å§é§ß§à§Ô §à§Ò§ß§Ñ§Ó?§Ñ?§Ñ §Ú§ß§Õ§Ö§Ü§ã§Ñ . §®§Ö?§å§ä§Ú§Þ, §Ú§Ù§Þ§Ö§ß§Ñ §à§Ó§Ö §á§â§à§Þ§Ö§ß?§Ú§Ó§Ö ?§Ö §å§ä§Ú§è§Ñ§ä§Ú §ß§Ñ §å§Ü§å§á§ß§å §å§á§à§ä§â§Ö§Ò§å §Þ§Ö§Þ§à§â§Ú?§Ö §Ò§Ñ§Ù§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.
• §®§à§Ø§Ö§ä§Ö §á§à§ã§ä§Ñ§Ó§Ú§ä§Ú §á§Ñ§â§Ñ§Ý§Ö§Ý§ß§Ú §ã§ä§Ö§á§Ö§ß §Ü§à§â§Ú§ã§ä§Ö?§Ú PARALLEL §Ü§Ý§Ñ§å§Ù§å§Ý§å, §ß§Ñ §á§â§Ú§Þ§Ö§â: ALTER INDEX auditlog_1 REBUILD PARALLEL 4
• §²§Ñ§Ù§Þ§Ú§ã§Ý§Ú§ä§Ö §à §à§ã§Ý§à§Ò§Ñ?§Ñ?§å §á§â§à§ã§ä§à§â§Ñ §Ò§â§Ú§ã§Ñ?§Ö§Þ §á§à§ä§Ö§ß§è§Ú?§Ñ§Ý§ß§à §ß§Ö§á§à§ä§â§Ö§Ò§ß§Ú§ç §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ.
• §²§Ñ§Ù§Þ§Ú§ã§Ý§Ú§ä§Ö §à §ã§Þ§Ñ?§Ö?§å §á§Ñ§â§Ñ§Þ§Ö§ä§â§Ñ §±§Ö§â§Ú§à§Õ §ã§Ü§Ý§Ñ§Õ§Ú§ê§ä§Ö?§Ñ §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ §à§Õ§â§Ø§Ñ§Ó§Ñ?§Ö §á§â§Ö §ß§Ö§Ô§à §ê§ä§à §Ú§Ù§Ó§â§ê§Ú§ä§Ö §à§Õ§â§Ø§Ñ§Ó§Ñ?§Ö.

§±§â§à§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §ã§Ñ §Ù§Ñ§ã§ä§à?§Ö§Þ

1. §±§â§Ö§å§Ù§Þ§Ú§ä§Ö §Ú§Þ§Ö §à§Ô§â§Ñ§ß§Ú§é§Ö?§Ñ.

SELECT CONSTRAINT_NAME FROM all_constraints WHERE TABLE_NAME = 'AUDITLOG' AND CONSTRAINT_TYPE = 'P';

2. §ª§Ù§Ò§â§Ú§ê§Ú§ä§Ö §ä§â§Ö§ß§å§ä§ß§Ú §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é §ä§Ñ§Ò§Ö§Ý§Ö auditlog §Ú §Õ§à§Õ§Ñ?§ä§Ö §ß§à§Ó§Ú §á§â§Ú§Þ§Ñ§â§ß§Ú §Ü?§å§é.

ALTER TABLE auditlog DROP CONSTRAINT <constraint_name>;
       ALTER TABLE auditlog ADD CONSTRAINT auditlog_pk PRIMARY KEY (auditid, clock);

3. §±§à§ß§à§Ó§à §Ú§Ù§Ô§â§Ñ§Õ§Ú§ä§Ö §Ú§ß§Õ§Ö§Ü§ã§Ö (§à§á§è§Ú§à§ß§à, §Ñ§Ý§Ú §ã§Ö §á§â§Ö§á§à§â§å§é§å?§Ö, §á§à§Ô§Ý§Ö§Õ§Ñ?§ä§Ö §£§Ñ§Ø§ß§Ö §ß§Ñ§á§à§Þ§Ö§ß§Ö §à §á§à§ß§à§Ó§ß§à? §Ú§Ù§Ô§â§Ñ§Õ?§Ú §Ú§ß§Õ§Ö§Ü§ã§Ñ).

3.1. §¥§à§Ò§Ñ§Ó§Ú§ä§Ö §Ú§Þ§Ö§ß§Ñ §Ú§ß§Õ§Ö§Ü§ã§Ñ.

SELECT index_name FROM user_indexes WHERE table_name='AUDITLOG';

3.2. §±§à§ß§à§Ó§à §Ú§Ù§Ô§â§Ñ§Õ§Ú§ä§Ö §ã§Ó§Ñ§Ü§Ú §Ú§ß§Õ§Ö§Ü§ã.

ALTER INDEX auditlog_pk REBUILD PARALLEL;
       ALTER INDEX auditlog_1 REBUILD PARALLEL;
       ALTER INDEX auditlog_2 REBUILD PARALLEL;
       ALTER INDEX auditlog_3 REBUILD PARALLEL;

§±§â§à§Þ§Ö§ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§à§Ô §Ü?§å§é§Ñ §Ò§Ö§Ù §Ù§Ñ§ã§ä§à?§Ñ

1. §¬§â§Ö§Ú§â§Ñ?§ä§Ö §ß§à§Ó§å §ä§Ñ§Ò§Ö§Ý§å §ã§Ñ §ß§à§Ó§Ú§Þ §á§â§Ú§Þ§Ñ§â§ß§Ú§Þ §Ü?§å§é§Ö§Þ §Ú §Ü§â§Ö§Ú§â§Ñ?§ä§Ö §Ú§ß§Õ§Ö§Ü§ã§Ö.

CREATE TABLE auditlog_new (
         auditid              nvarchar2(25)                             ,
         userid               number(20)                                NULL,
         username             nvarchar2(100)  DEFAULT ''                ,
         clock                number(10)      DEFAULT '0'               NOT NULL,
         ip                   nvarchar2(39)   DEFAULT ''                ,
         action               number(10)      DEFAULT '0'               NOT NULL,
         resourcetype         number(10)      DEFAULT '0'               NOT NULL,
         resourceid           number(20)                                NULL,
         resource_cuid        nvarchar2(25)                             ,
         resourcename         nvarchar2(255)  DEFAULT ''                ,
         recordsetid          nvarchar2(25)                             ,
         details              nclob           DEFAULT ''                ,
         PRIMARY KEY (auditid,clock)
       );
       CREATE INDEX auditlog_new_1 ON auditlog_new (userid,clock);
       CREATE INDEX auditlog_new_2 ON auditlog_new (clock);
       CREATE INDEX auditlog_new_3 ON auditlog_new (resourcetype,resourceid);

2. §²§Ñ§Ù§Þ§Ö§ß§Ú§ä§Ö §ä§Ñ§Ò§Ö§Ý§Ö.

ALTER TABLE auditlog RENAME TO auditlog_old;
       ALTER TABLE auditlog_new RENAME TO auditlog;

3. §¬§à§á§Ú§â§Ñ?§ä§Ö §á§à§Õ§Ñ§ä§Ü§Ö §Ú§Ù §ã§ä§Ñ§â§Ö §ä§Ñ§Ò§Ö§Ý§Ö §å §ß§à§Ó§å §ä§Ñ§Ò§Ö§Ý§å.

INSERT INTO auditlog SELECT * FROM auditlog_old;

§°§Ó§à §ã§Ö §Þ§à§Ø§Ö §å§â§Ñ§Õ§Ú§ä§Ú §å §Õ§Ö§Ý§à§Ó§Ú§Þ§Ñ (§Ó§Ú§ê§Ö §ß§Ñ§â§Ö§Õ§Ò§Ú INSERT INTO §ã§Ñ §Ü§Ý§Ñ§å§Ù§å§Ý§Ñ§Þ§Ñ WHERE clock §á§à §á§à§ä§â§Ö§Ò§Ú) §Õ§Ñ §Ò§Ú §ã§Ö §Ú§Ù§Ò§Ö§Ô§Ý§à §á§â§Ö§Ü§à§Þ§Ö§â§ß§à §Ü§à§â§Ú§ê?§Ö?§Ö §â§Ö§ã§å§â§ã§Ñ.

4. §ª§Ù§Ò§â§Ú§ê§Ú§ä§Ö §ã§ä§Ñ§â§å §ä§Ñ§Ò§Ö§Ý§å.

DROP TABLE auditlog_old;

§±§à§Ô§Ý§Ö§Õ§Ñ?§ä§Ö §ä§Ñ§Ü§à?§Ö

¨C §¯§Ñ§Õ§à§Ô§â§Ñ§Õ?§Ñ §Ò§Ñ§Ù§Ö §á§à§Õ§Ñ§ä§Ñ§Ü§Ñ §ß§Ñ §á§â§Ú§Þ§Ñ§â§ß§Ö §Ü?§å§é§Ö§Ó§Ö