[
https://issues.apache.org/jira/browse/HIVE-26144?focusedWorklogId=757624&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-757624
]
ASF GitHub Bot logged work on HIVE-26144:
-----------------------------------------
Author: ASF GitHub Bot
Created on: 16/Apr/22 21:04
Start Date: 16/Apr/22 21:04
Worklog Time Spent: 10m
Work Description: kovjanos commented on PR #3214:
URL: https://github.com/apache/hive/pull/3214#issuecomment-1100754582
@zabetak Tested with Derby, MySQL, PostgreSQL and MSSQL and worked - see
below.
Oracle doesn't work, I need to review again.
Versions tested:
```
az82/docker-derby
mysql:5.7
postgres:11.6
mcr.microsoft.com/mssql/server:2019-latest
```
Derby
```
CREATE TABLE TEST_TABLE (
ID bigint NOT NULL,
DB varchar(10) NOT NULL
);
CREATE INDEX TEST_TABLE_IDX ON TEST_TABLE (DB);
INSERT INTO TEST_TABLE VALUES (1,'1-db'),(2,'2-db'),(3,'3-db');
ALTER TABLE TEST_TABLE ADD COLUMN PKEY bigint PRIMARY KEY GENERATED BY
DEFAULT AS IDENTITY;
SELECT * FROM TEST_TABLE;
INSERT INTO TEST_TABLE (ID,DB) VALUES (4,'4-db'),(5,'5-db'),(6,'6-db');
SELECT * FROM TEST_TABLE;
```
PostgreSQL:
```
CREATE TABLE "TEST_TABLE" (
"ID" bigint NOT NULL,
"DB" varchar(10) NOT NULL);
CREATE INDEX TEST_TABLE_IDX ON "TEST_TABLE" USING btree ("DB");
INSERT INTO "TEST_TABLE" VALUES (1,'1-db'),(2,'2-db'),(3,'3-db');
ALTER TABLE "TEST_TABLE" ADD "PKEY" bigserial PRIMARY KEY;
SELECT * FROM "TEST_TABLE";
INSERT INTO "TEST_TABLE" VALUES (4,'4-db'),(5,'5-db'),(6,'6-db');
SELECT * FROM "TEST_TABLE";
```
MySQL:
```
CREATE TABLE TEST_TABLE (
ID bigint NOT NULL,
DB varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE INDEX TEST_TABLE_IDX ON TEST_TABLE (DB) USING BTREE;
INSERT INTO TEST_TABLE VALUES (1,'1-db'),(2,'2-db'),(3,'3-db');
ALTER TABLE TEST_TABLE ADD COLUMN PKEY BIGINT NOT NULL PRIMARY KEY
AUTO_INCREMENT FIRST;
SELECT * FROM TEST_TABLE;
INSERT INTO TEST_TABLE (ID,DB) VALUES (4,'4-db'),(5,'5-db'),(6,'6-db');
SELECT * FROM TEST_TABLE;
```
MSSQL:
```
CREATE TABLE TEST_TABLE (
ID bigint NOT NULL,
DB varchar(10) NOT NULL
);
CREATE INDEX TEST_TABLE_IDX ON TEST_TABLE (DB);
INSERT INTO TEST_TABLE VALUES (1,'1-db'),(2,'2-db'),(3,'3-db');
ALTER TABLE TEST_TABLE ADD PKEY bigint NOT NULL IDENTITY(1,1) PRIMARY KEY;
SELECT * FROM TEST_TABLE;
INSERT INTO TEST_TABLE VALUES (1,'1-db'),(2,'2-db'),(3,'3-db');
SELECT * FROM TEST_TABLE;
```
Issue Time Tracking
-------------------
Worklog Id: (was: 757624)
Time Spent: 0.5h (was: 20m)
> Add keys/indexes to support highly concurrent workload
> ------------------------------------------------------
>
> Key: HIVE-26144
> URL: https://issues.apache.org/jira/browse/HIVE-26144
> Project: Hive
> Issue Type: Sub-task
> Components: Database/Schema
> Reporter: Janos Kovacs
> Assignee: Janos Kovacs
> Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> The following indexes are added to avoid full table-scan in backend rdbms:
> - primary key for COMPLETED_TXN_COMPONENTS
> - primary key for TXN_COMPONENTS
> - index for TXN_WRITE_NOTIFICATION_LOG
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
