{"id":14765,"date":"2020-10-01T13:09:06","date_gmt":"2020-10-01T11:09:06","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/"},"modified":"2020-10-01T13:09:06","modified_gmt":"2020-10-01T11:09:06","slug":"getting-started-with-exasol-some-words-about-indexes-and-transactions","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/","title":{"rendered":"Getting started with Exasol \u2013 Some words about indexes and transactions"},"content":{"rendered":"

If you followed Getting started with Exasol \u2013 Setting up an environment<\/a> and Getting started with Exasol \u2013 Loading data from PostgreSQL<\/a> you should have an Exasol test system up and running and a virtual schema<\/a> pointing to a PostgreSQL schema. What we’ll be doing in this post is to load the data from PostgreSQL into Exasol and then have a look at how transactions work in Exasol and how Exasol is handling indexes.<\/p>\n

<\/p>\n

As a quick reminder the virtual schema we’ve created in the previous post looks like this:<\/p>\n

\nSQL_EXA> select table_name from exa_dba_tables where TABLE_SCHEMA = 'POSTGRESQL_REMOTE';\nEXA: select table_name from exa_dba_tables where TABLE_SCHEMA = 'POSTGRESQL...\n\nTABLE_NAME                                                                                                                      \n--------------------------------------------------------------------------------------------------------------------------------\nPGBENCH_ACCOUNTS                                                                                                                \nPGBENCH_BRANCHES                                                                                                                \nPGBENCH_HISTORY                                                                                                                 \nPGBENCH_TELLERS                                                                                                                 \n\n4 rows in resultset.\n<\/pre>\n
As these table are actually in PostgreSQL we’re going to load them locally into an Exasol schema:<\/p>\n
\nSQL_EXA> create schema demo;\nEXA: create schema demo;\n\nRows affected: 0\n\nSQL_EXA> open schema demo;\nEXA: open schema demo;\n\nRows affected: 0\n\nSQL_EXA> create table PGBENCH_ACCOUNTS as select * from POSTGRESQL_REMOTE.PGBENCH_ACCOUNTS;\nEXA: create table PGBENCH_ACCOUNTS as select * from POSTGRESQL_REMOTE.PGBEN...\n\nRows affected: 10000000\n<\/pre>\n
An alternative method to do the same is to use SELECT INTO<\/a> statement in Exasol:<\/p>\n
\nSQL_EXA> select * into table PGBENCH_BRANCHES from POSTGRESQL_REMOTE.PGBENCH_BRANCHES;\nEXA: select * into table PGBENCH_BRANCHES from POSTGRESQL_REMOTE.PGBENCH_BR...\n\nRows affected: 100\n\nSQL_EXA> select * into table PGBENCH_HISTORY from POSTGRESQL_REMOTE.PGBENCH_HISTORY;\nEXA: select * into table PGBENCH_HISTORY from POSTGRESQL_REMOTE.PGBENCH_HIS...\n\nRows affected: 0\n\nSQL_EXA> select * into table PGBENCH_TELLERS from POSTGRESQL_REMOTE.PGBENCH_TELLERS;\nEXA: select * into table PGBENCH_TELLERS from POSTGRESQL_REMOTE.PGBENCH_TEL...\n\nRows affected: 1000\n<\/pre>\n
Of course we lost all primary keys, foreign keys and check constraints by doing this:<\/p>\n
\nSQL_EXA> select count(*) from EXA_DBA_CONSTRAINTS where CONSTRAINT_SCHEMA = 'DEMO';\nEXA: select count(*) from EXA_DBA_CONSTRAINTS where CONSTRAINT_SCHEMA = 'DE...\n\nCOUNT(*)             \n---------------------\n                    0\n\n1 row in resultset.\n<\/pre>\n
Let’s add the same constraints as we have them on the PostgreSQL side:<\/p>\n
\nSQL_EXA> alter table PGBENCH_ACCOUNTS add constraint PGBENCH_ACCOUNTS_PK primary key (AID);\nEXA: alter table PGBENCH_ACCOUNTS add constraint PGBENCH_ACCOUNTS_PK primar...\n\nRows affected: 0\n\nSQL_EXA> alter table PGBENCH_BRANCHES add constraint PGBENCH_BRANCHES_PK primary key (BID);\nEXA: alter table PGBENCH_BRANCHES add constraint PGBENCH_BRANCHES_PK primar...\n\nRows affected: 0\n\nSQL_EXA> alter table PGBENCH_TELLERS add constraint PGBENCH_TELLERS_PK primary key (TID);\nEXA: alter table PGBENCH_TELLERS add constraint PGBENCH_TELLERS_PK primary ...\n\nRows affected: 0\n\nSQL_EXA> select count(*) from EXA_DBA_CONSTRAINTS where CONSTRAINT_SCHEMA = 'DEMO';\nEXA: select count(*) from EXA_DBA_CONSTRAINTS where CONSTRAINT_SCHEMA = 'DE...\n\nCOUNT(*)             \n---------------------\n                    3\n\n1 row in resultset.\n<\/pre>\n
Now we have exactly the same setup as in PostgreSQL. Creating the primary keys should also have created some indexes in the background:<\/p>\n
\nSQL_EXA> col index_schema for a30;\nCOLUMN   index_schema ON\nFORMAT   a30\nSQL_EXA> col index_table for a30;\nCOLUMN   index_table ON\nFORMAT   a30\nSQL_EXA> col index_type for a15;\nCOLUMN   index_type ON\nFORMAT   a15\nSQL_EXA> select INDEX_SCHEMA,INDEX_TABLE,INDEX_TYPE from EXA_DBA_INDICES where INDEX_SCHEMA = 'DEMO';\nEXA: select INDEX_SCHEMA,INDEX_TABLE,INDEX_TYPE from EXA_DBA_INDICES where ...\n\nINDEX_SCHEMA                   INDEX_TABLE                    INDEX_TYPE     \n------------------------------ ------------------------------ ---------------\nDEMO                           PGBENCH_ACCOUNTS               GLOBAL         \nDEMO                           PGBENCH_BRANCHES               GLOBAL         \nDEMO                           PGBENCH_TELLERS                GLOBAL         \n\n3 rows in resultset.\n\nSQL_EXA> \n<\/pre>\n
If you’re looking for an index name, there is none:<\/p>\n
\nSQL_EXA> col column_name for a30;\nCOLUMN   column_name ON\nFORMAT   a30\nSQL_EXA> col column_comment for a50;\nCOLUMN   column_comment ON\nFORMAT   a50\nSQL_EXA> desc full EXA_DBA_INDICES;\nEXA: desc full EXA_DBA_INDICES;\n\nCOLUMN_NAME                    SQL_TYPE                                 NULLABLE DISTRIBUTION_KEY PARTITION_KEY    COLUMN_COMMENT                                    \n------------------------------ ---------------------------------------- -------- ---------------- ---------------- --------------------------------------------------\nINDEX_SCHEMA                   VARCHAR(128) UTF8                                                                   Schema of the index                               \nINDEX_TABLE                    VARCHAR(128) UTF8                                                                   Table of the index                                \nINDEX_OWNER                    VARCHAR(128) UTF8                                                                   Owner of the index                                \nINDEX_OBJECT_ID                DECIMAL(18,0)                                                                       ID of the index                                   \nINDEX_TYPE                     VARCHAR(20) UTF8                                                                    Index type                                        \nIS_GEOMETRY                    BOOLEAN                                                                             States whether this is a geospatial index         \nMEM_OBJECT_SIZE                DECIMAL(18,0)                                                                       Index size in bytes (at last COMMIT)              \nCREATED                        TIMESTAMP                                                                           Timestamp of when the index was created           \nLAST_COMMIT                    TIMESTAMP                                                                           Last time the object was changed in the DB        \nREMARKS                        VARCHAR(100000) UTF8                                                                Additional information about the index            \n\n10 rows in resultset.\nSQL_EXA> \ntree (bi\n<\/pre>\n
There isn’t even a CREATE INDEX<\/a> statement in Exasol. A “Global” index exists on all nodes of the cluster while a “Local” index only exits on the given node. Indexes are created and maintained automatically based on the queries the system is processing. We can easily see this when joining two simple tables:<\/p>\n
\nSQL_EXA> create table demo1 ( a int, b int, c int );\nEXA: create table demo1 ( a int, b int, c int );\n\nRows affected: 0\n\nSQL_EXA> insert into demo1 select 1,1,1 from pgbench_accounts where aid < 1000;\nEXA: insert into demo1 select 1,1,1 from pgbench_accounts where aid  create table demo2 ( a int, b int, c int );\n\nSQL_EXA> create table demo2 ( a int, b int, c int );\nEXA: create table demo2 ( a int, b int, c int );\n\nRows affected: 0\n\nSQL_EXA> insert into demo2 select * from demo1;\nEXA: insert into demo2 select * from demo1;\n\nRows affected: 999\n\nSQL_EXA> select count(*) from demo1 a, demo2 b where a.a = b.a;\nEXA: select count(*) from demo1 a, demo2 b where a.a = b.a;\n\nCOUNT(*)             \n---------------------\n               998001\n\n1 row in resultset.\n\nSQL_EXA> select INDEX_SCHEMA,INDEX_TABLE,INDEX_TYPE from EXA_DBA_INDICES where INDEX_SCHEMA = 'DEMO';\nEXA: select INDEX_SCHEMA,INDEX_TABLE,INDEX_TYPE from EXA_DBA_INDICES where ...\n\nINDEX_SCHEMA                                 INDEX_TABLE                    INDEX_TYPE          \n-------------------------------------------- ------------------------------ --------------------\nDEMO                                         PGBENCH_ACCOUNTS               GLOBAL              \nDEMO                                         PGBENCH_BRANCHES               GLOBAL              \nDEMO                                         PGBENCH_TELLERS                GLOBAL              \nDEMO                                         DEMO1                          LOCAL               \n<\/pre>\n
If an index is not used for 5 weeks it will be dropped automatically.<\/p>\n
Coming to transaction handling. First of all you need to know the auto commit is enabled by default (the same as in PostgreSQL) in the clients:<\/p>\n
\nSQL_EXA> show autocommit;\nAUTOCOMMIT = \"ON\"\n<\/pre>\n
Exasol supports the serializable<\/a> transaction isolation level. This is the strongest level but also comes with some downsides. It can happen that transaction need to wait or even abort when the order of the transactions can not be guaranteed. DDL is transactional as well:<\/p>\n
\n-- session 1\nSQL_EXA> set AUTOCOMMIT off;\nSQL_EXA> create table t1 ( a int );\nEXA: create table t1 ( a int );\n\n\n-- session 2\nSQL_EXA> set AUTOCOMMIT off;\nSQL_EXA> \nSQL_EXA> select * from t1;\nEXA: select * from t1;\nError: [42000] object T1 not found [line 1, column 15] (Session: 1679358615345561600)\n\n-- session 1\nSQL_EXA> commit;\nEXA: commit;\n\n-- session 2\nSQL_EXA> select * from t1;\nEXA: select * from t1;\n\nA                    \n---------------------\n\n0 rows in resultset.\n\nSQL_EXA> \n<\/pre>\n
The recommendation is to go with the default and let auto commit on to keep transactions as short as possible. Otherwise this can happen:<\/p>\n
\n-- session 1\nSQL_EXA> set AUTOCOMMIT off;\nSQL_EXA> insert into t1 values(1);\nEXA: insert into t1 values(1);\n\nRows affected: 1\n\nSQL_EXA> insert into t1 values(2);\nEXA: insert into t1 values(2);\n\nRows affected: 1\n\nSQL_EXA> insert into t1 values(3);\nEXA: insert into t1 values(3);\n\nRows affected: 1\n\nSQL_EXA> commit;\nEXA: commit;\n\nRows affected: 0\n\nSQL_EXA> update t1 set a = 1 where a = 2;\nEXA: update t1 set a = 1 where a = 2;\n\nRows affected: 1\n\nSQL_EXA> \n\n\n-- session 2\nSQL_EXA> set AUTOCOMMIT off;\nSQL_EXA> insert into t1 values (1);\nEXA: insert into t1 values (1);    --- must wait for session on to either commit or rollback\n<\/pre>\n
… or even this:<\/p>\n
\n-- session 2\nSQL_EXA> select * from t1;\nEXA: select * from t1;\n\nA                    \n---------------------\n                    1\n                    1\n                    3\n                    1\n\n4 rows in resultset.\n\n\n-- session 1\nSQL_EXA> update t1 set a = 1 where a = 2;\nEXA: update t1 set a = 1 where a = 2;\n\nRows affected: 0\n\nSQL_EXA> \n\n-- session 2\nSQL_EXA> insert into t1 values (5);\nEXA: insert into t1 values (5);\nError: [40001] GlobalTransactionRollback msg: Transaction collision: automatic transaction rollback. (Session: 1679360319468797952)\n<\/pre>\n
These kind of conflict are recorded in the catalog and you can check them:<\/p>\n
\nSQL_EXA> col conflict_objects for a30;\nCOLUMN   conflict_objects ON\nFORMAT   a30\nSQL_EXA> select * from EXA_DBA_TRANSACTION_CONFLICTS;\nEXA: select * from EXA_DBA_TRANSACTION_CONFLICTS;\n\nSESSION_ID            CONFLICT_SESSION_ID   START_TIME                 STOP_TIME                  CONFLICT_TYPE        CONFLICT_OBJECTS               CONFLICT_INFO                           \n--------------------- --------------------- -------------------------- -------------------------- -------------------- ------------------------------ ----------------------\n  1679358615345561600                       2020-10-01 14:13:12.871000 2020-10-01 14:13:12.871000 TRANSACTION ROLLBACK DEMO.T1                        intern merged sessions                  \n  1679358615345561600   1679357953088880640 2020-10-01 14:23:31.669000 2020-10-01 14:27:35.941000 WAIT FOR COMMIT      DEMO.T1                                                                \n  1679359718013206528   1679357953088880640 2020-10-01 14:24:05.772000 2020-10-01 14:27:35.941000 WAIT FOR COMMIT      DEMO.T1                                                                \n  1679359718013206528   1679357953088880640 2020-10-01 14:29:05.514000 2020-10-01 14:32:06.309000 WAIT FOR COMMIT      DEMO.T1                                                                \n  1679360160561037312   1679357953088880640 2020-10-01 14:31:11.577000 2020-10-01 14:32:06.309000 WAIT FOR COMMIT      DEMO.T1                                                                \n  1679360185071960064   1679357953088880640 2020-10-01 14:31:59.416000 2020-10-01 14:32:06.309000 WAIT FOR COMMIT      DEMO.T1                                                                \n  1679360185071960064   1679359718013206528 2020-10-01 14:32:06.352000 2020-10-01 14:32:12.255000 WAIT FOR COMMIT      DEMO.T1                                                                \n  1679359718013206528   1679357953088880640 2020-10-01 14:32:51.164000 2020-10-01 14:32:51.164000 TRANSACTION ROLLBACK DEMO.T1                                                                \n\n8 rows in resultset.\n<\/pre>\n
As soon as you write to an object you are working on your own (temporary copy) of the object, Exasol calls this Multi-Copy. When a transaction commits that version of the objects becomes the valid one. Transactions that started before will still use the previous version of the object.<\/p>\n
The recommendation therefore is to always have auto commit turned on in the client and for the cases where you need to turn it off: Keep the transactions as short as possible. That’s it for the introduction of indexes and transactions in Exasol. In the next post we’ll look at database sessions and auditing.<\/p>\n","protected":false},"excerpt":{"rendered":"
If you followed Getting started with Exasol \u2013 Setting up an environment and Getting started with Exasol \u2013 Loading data from PostgreSQL you should have an Exasol test system up and running and a virtual schema pointing to a PostgreSQL schema. What we’ll be doing in this post is to load the data from PostgreSQL […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[2127,2128,77],"type_dbi":[],"class_list":["post-14765","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-exasol","tag-mpp","tag-postgresql"],"acf":[],"yoast_head":"\nGetting started with Exasol \u2013 Some words about indexes and transactions - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Getting started with Exasol \u2013 Some words about indexes and transactions\" \/>\n<meta property=\"og:description\" content=\"If you followed Getting started with Exasol \u2013 Setting up an environment and Getting started with Exasol \u2013 Loading data from PostgreSQL you should have an Exasol test system up and running and a virtual schema pointing to a PostgreSQL schema. What we’ll be doing in this post is to load the data from PostgreSQL […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-01T11:09:06+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Getting started with Exasol \u2013 Some words about indexes and transactions\",\"datePublished\":\"2020-10-01T11:09:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/\"},\"wordCount\":479,\"commentCount\":0,\"keywords\":[\"Exasol\",\"MPP\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/\",\"name\":\"Getting started with Exasol \u2013 Some words about indexes and transactions - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-10-01T11:09:06+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/getting-started-with-exasol-some-words-about-indexes-and-transactions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Getting started with Exasol \u2013 Some words about indexes and transactions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Getting started with Exasol \u2013 Some words about indexes and transactions - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/","og_locale":"en_US","og_type":"article","og_title":"Getting started with Exasol \u2013 Some words about indexes and transactions","og_description":"If you followed Getting started with Exasol \u2013 Setting up an environment and Getting started with Exasol \u2013 Loading data from PostgreSQL you should have an Exasol test system up and running and a virtual schema pointing to a PostgreSQL schema. What we’ll be doing in this post is to load the data from PostgreSQL […]","og_url":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/","og_site_name":"dbi Blog","article_published_time":"2020-10-01T11:09:06+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Getting started with Exasol \u2013 Some words about indexes and transactions","datePublished":"2020-10-01T11:09:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/"},"wordCount":479,"commentCount":0,"keywords":["Exasol","MPP","PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/","url":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/","name":"Getting started with Exasol \u2013 Some words about indexes and transactions - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-10-01T11:09:06+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-exasol-some-words-about-indexes-and-transactions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Getting started with Exasol \u2013 Some words about indexes and transactions"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14765","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14765"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14765\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14765"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14765"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14765"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14765"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}