Store Schema Optimization (Views)

We had the problem that after applying all the previous mentioned optimizations the access doesn't speed up. We have a big Oracle store database with about 13 million entries in tw_methods and loading of more recently published items became really slow. The following redefinitions of views speeds up the access incredibly. (Feel free to send comments to kopp at collogia.de)

Analyzing the database accesses I found out that some views take too much time and could be defined to work more efficiently.

What's the problem?

The tw_methodsAndSourcesView is defined in the following way

CREATE VIEW tw_methodsAndSourcesView as
  SELECT bern.tw_method.primaryKey, bern.tw_method.name, bern.tw_method.timeStamp, bern.tw_method.version, bern.tw_method.userName, bern.tw_method.trace, bern.tw_method.className, bern.tw_methods.packageRef, bern.tw_method.sourceCodeID, bern.tw_method.protocolName, bern.TW_Blob.blobData, bern.TW_Blob.blobType
  FROM bern.tw_methods, bern.tw_method, bern.TW_Blob
  WHERE bern.tw_methods.methodRef = bern.tw_method.primaryKey
  AND bern.tw_method.sourceCodeID = bern.TW_Blob.primaryKey;


The call of the the view gets an additional where-clause which looks like

  packageRef = ..., primaryKey not in (...)

What does the database do with the query?
The first restriction is done by packageRef on tw_methods but the next is done by primaryKey on tw_method which results in a full table scan.
But the where clause of the view definition equates bern.tw_methods.methodRef and bern.tw_method.primaryKey. Therefore, the second restriction could also be applied on the same table as the first one (tw_methods).

So the view definition should use tw_methods.methodRef with alias primaryKey instead of tw_method.primaryKey.

After redefining the view as follows the view speeds up incredibly!

CREATE or Replace VIEW tw_methodsAndSourcesView as
  SELECT bern.tw_methods.methodRef primaryKey, bern.tw_method.name, bern.tw_method.timeStamp, bern.tw_method.version, bern.tw_method.userName, bern.tw_method.trace, bern.tw_method.className, bern.tw_methods.packageRef, bern.tw_method.sourceCodeID, bern.tw_method.protocolName, bern.TW_Blob.blobData, bern.TW_Blob.blobType
  FROM bern.tw_methods, bern.tw_method, bern.TW_Blob
  WHERE bern.tw_methods.methodRef = bern.tw_method.primaryKey
  AND bern.tw_method.sourceCodeID = bern.TW_Blob.primaryKey;

The following views can be redefined in the same way:

CREATE or Replace VIEW TW_DataAndSourcesView as
  SELECT bern.TW_Data.dataRef primaryKey, bern.TW_DataElement.name, bern.TW_DataElement.timeStamp, bern.TW_DataElement.userName, bern.TW_DataElement.trace, bern.TW_Data.packageRef, bern.TW_DataElement.definitionID, bern.TW_DataElement.commentID, bern.TW_DataElement.environmentString, bern.TW_DataElement.protocolName, bern.TW_DataElement.typeString, bern.TW_Blob.blobData, bern.TW_Blob.blobType
  FROM bern.TW_Data, bern.TW_DataElement, bern.TW_Blob
  WHERE bern.TW_Data.dataRef = bern.TW_DataElement.primaryKey
  AND bern.TW_DataElement.definitionID = bern.TW_Blob.primaryKey;

CREATE or Replace VIEW TW_PkgClassesAndSourcesView as
  SELECT bern.TW_PkgClasses.classRef primaryKey, bern.TW_ClassRecord.name, bern.TW_ClassRecord.timeStamp, bern.TW_ClassRecord.userName, bern.TW_ClassRecord.trace, bern.TW_ClassRecord.superclass, bern.TW_ClassRecord.definitionID, bern.TW_ClassRecord.environmentString, bern.TW_ClassRecord.commentID, bern.TW_PkgClasses.packageRef, bern.TW_PkgClasses.definitionOrder, bern.TW_Blob.blobData, bern.TW_Blob.blobType
  FROM bern.TW_PkgClasses, bern.TW_ClassRecord, bern.TW_Blob
  WHERE bern.TW_PkgClasses.classRef = bern.TW_ClassRecord.primaryKey
  AND bern.TW_ClassRecord.definitionID = bern.TW_Blob.primaryKey;

CREATE or Replace VIEW TW_PkgNameSpacesAndSourcesView as
  SELECT bern.TW_PkgNameSpaces.nameSpaceRef primaryKey, bern.TW_NameSpaceRecord.name, bern.TW_NameSpaceRecord.timeStamp, bern.TW_NameSpaceRecord.userName, bern.TW_NameSpaceRecord.trace, bern.TW_NameSpaceRecord.definitionID, bern.TW_NameSpaceRecord.commentID, bern.TW_NameSpaceRecord.environmentString, bern.TW_PkgNameSpaces.packageRef, bern.TW_PkgNameSpaces.definitionOrder, bern.TW_Blob.blobData, bern.TW_Blob.blobType
  FROM bern.TW_PkgNameSpaces, bern.TW_NameSpaceRecord, bern.TW_Blob
  WHERE bern.TW_PkgNameSpaces.nameSpaceRef = bern.TW_NameSpaceRecord.primaryKey
  AND bern.TW_NameSpaceRecord.definitionID = bern.TW_Blob.primaryKey;