Project

General

Profile

Revision 7455

additional db indexes for pathquery performance
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5696

View differences:

src/xmltables-oracle.sql
238 238
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
239 239
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner));
240 240
CREATE INDEX xml_documents_idx3 ON xml_documents (rootnodeid);
241
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);
241 242

  
243

  
242 244
/*
243 245
 * Revised Documents -- table to store XML documents saved after an UPDATE
244 246
 *                    or DELETE
......
300 302
CREATE INDEX xml_access_idx3 ON xml_access (perm_type);
301 303
CREATE INDEX xml_access_idx4 ON xml_access (perm_order);
302 304
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid);
305
CREATE INDEX xml_access_idx6 on xml_access(guid);
303 306

  
304 307

  
305 308
/*
......
364 367
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
365 368
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
366 369
CREATE INDEX xml_path_index_idx4 ON xml_path_index (nodedatadate);
370
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);
367 371

  
368 372

  
369

  
370

  
371 373
CREATE TABLE xml_relation (
372 374
	relationid    NUMBER(20) PRIMARY KEY, -- unique id
373 375
	docid         VARCHAR2(250),          -- the docid of the package file
......
451 453
   docid  VARCHAR2(250),  -- the local document id #
452 454
   rev    NUMBER(8)       -- the revision part of the local identifier
453 455
);
456
CREATE INDEX identifier_guid on identifier(guid);
457
CREATE INDEX identifier_docid on identifier(docid);
458
CREATE INDEX identifier_rev on identifier(rev);
459
CREATE INDEX identifier_docid_rev on identifier(docid, rev);
454 460

  
455 461
/*
456 462
 * accesssubtree -- table to store access subtree info
src/xmltables-postgres.sql
170 170
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
171 171
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner));
172 172
CREATE INDEX xml_documents_idx3 ON xml_documents (rootnodeid);
173
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);
173 174

  
174 175
/*
175 176
 * Revised Documents -- table to store XML documents saved after an UPDATE
......
225 226
CREATE INDEX xml_access_idx3 ON xml_access (perm_type);
226 227
CREATE INDEX xml_access_idx4 ON xml_access (perm_order);
227 228
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid);
229
CREATE INDEX xml_access_idx6 on xml_access(guid);
228 230
/*
229 231
 * ALTER TABLE xml_access ADD COLUMN guid text;
230 232
*/
......
283 285
   rev    INT8,          -- the revision part of the local identifier
284 286
   CONSTRAINT identifier_pk PRIMARY KEY (guid)
285 287
);
288
CREATE INDEX identifier_guid on identifier(guid);
289
CREATE INDEX identifier_docid on identifier(docid);
290
CREATE INDEX identifier_rev on identifier(rev);
291
CREATE INDEX identifier_docid_rev on identifier(docid, rev);
286 292

  
287 293
/*
288 294
 * Table used to store all document identifiers for system metadata objects
......
425 431
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
426 432
CREATE INDEX xml_path_index_idx4 ON xml_path_index (upper(nodedata));
427 433
CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate);
434
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);
428 435

  
429 436
/*
430 437
 * harvest_site_schedule -- table to store harvest sites and schedule info
src/upgrade-db-to-2.0.5-postgres.sql
5 5
ALTER TABLE systemMetadataReplicationStatus RENAME TO smReplicationStatus;
6 6

  
7 7
/*
8
 * Add some additional indexes for search
9
 */
10
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);
11

  
12
CREATE INDEX xml_access_idx6 on xml_access(guid);
13

  
14
CREATE INDEX identifier_guid on identifier(guid);
15
CREATE INDEX identifier_docid on identifier(docid);
16
CREATE INDEX identifier_rev on identifier(rev);
17
CREATE INDEX identifier_docid_rev on identifier(docid, rev);
18

  
19
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);
20

  
21
/*
8 22
 * update the database version
9 23
 */
10 24
UPDATE db_version SET status=0;
src/upgrade-db-to-2.0.5-oracle.sql
6 6
 * for use in 2.0.5. So, we do nothing here for Oracle deployments.
7 7
 */
8 8

  
9
/*
10
 * Add some additional indexes for search
11
 */
12
CREATE INDEX xml_documents_idx5 ON xml_documents (docid, rev);
9 13

  
14
CREATE INDEX xml_access_idx6 on xml_access(guid);
15

  
16
CREATE INDEX identifier_guid on identifier(guid);
17
CREATE INDEX identifier_docid on identifier(docid);
18
CREATE INDEX identifier_rev on identifier(rev);
19
CREATE INDEX identifier_docid_rev on identifier(docid, rev);
20

  
21
CREATE INDEX xml_path_index_idx6 ON xml_path_index (docid);
22

  
23

  
10 24
/*
11 25
 * update the database version
12 26
 */

Also available in: Unified diff