[DS-2981] Create indexes for UUID fields for Oracle migration script

[dspace-bot]

Imported from JIRA [DS-2981] created by terrywbrady

A similar approach was taken for postgres instances and it has resulted in significant performance improvements.

[dspace-bot]

terrywbrady said:

This linked ticket describes the approach taken for postgres

[dspace-bot]

tdonohue said:

Fixing this ticket is just a matter of taking the added PostgreSQL migrations in PR#1140 (https://github.com/DSpace/DSpace/pull/1140/files) and creating the same indexes in the corresponding Oracle migration scripts.

[dspace-bot]

mwood said:

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:2570145300346198113

[dspace-bot]

tom.desair said:

Mark H. Wood Good link.

I think PostgreSQL has the same problem. PostgreSQL will automatically create a unique b-tree index on primary key fields (http://www.postgresql.org/docs/current/static/indexes-unique.html). But because b-trees store their index entries ascending (http://www.postgresql.org/docs/current/static/indexes-ordering.html), the primary key indexes will also suffer under the random UUID values. The indexes from PR#1140 have the same issue (because the default index implementation is b-tree: http://www.postgresql.org/docs/current/static/sql-createindex.html).

I don't know if there is a way to prevent this in PostgreSQL or Oracle.

[dspace-bot]

mwood said:

A fresh install using the related PR works.

[dspace-bot]

mwood said:

https://blog.starkandwayne.com/2015/05/23/uuid-primary-keys-in-postgresql/

We may want to consider generating UUIDs differently for PostgreSQL.

[dspace-bot]

Is duplicated by: DS-3038