($$) Views
Optimizing the performance of views involves optimizing the SQL statement upon which the view is based and also the SQL that is likely to result when selection criteria are pushed up into the view.
Creating views containing hints can be a useful technique for optimizing queries and SQL generated by query tools.
Partition views can be created which reduces the overhead of scanning a substantive range of large tables.
Snapshots can be used to store the results of complex queries and allow the rapid retrieval of results, which may somewhat be out of range.
Performance of a simple snapshot refresh is improved by the creation of a snapshot log, providing that only a small proportion of the source table has been changed.
Snapshots can reduce the overhead of SQL dramatically for static tables.
Oracle sequences are an efficient mechanism of generating primary key values and should be used in preference to sequence tables or other mechanisms.
The DECODE operator can be used to perform complex aggregations, which might otherwise need to be performed via multiple queries.
($$) Indexes
Indexes on small tables (less than 100 rows) are totally useless, unless of course they are here to implement unique constraints (primary or unique keys).
Indexes for which there are less than 8 different key values should come under very close scrutiny (this of course refers to 'traditional' tree-type indexes, not bitmap ones). Here, the distribution of values is of course extremely important: -
If the values are roughly equally distributed, the index can be scrapped. If, however, searches are for keys, which belong to a small minority, then the index can be kept. It can make sense to have an index on a personnel table on the 'SEX' column if you are frequently searching for females in a predominantly male environment or vice versa.
Concatenated indexes should be concatenated from the most selective to the least selective column.
Indexes on single columns, which also appear as the first column in a concatenated index, are redundant. Database engines can use the concatenated index only when the first columns in the index appear in the search condition.
Creating views containing hints can be a useful technique for optimizing queries and SQL generated by query tools.
Partition views can be created which reduces the overhead of scanning a substantive range of large tables.
Snapshots can be used to store the results of complex queries and allow the rapid retrieval of results, which may somewhat be out of range.
Performance of a simple snapshot refresh is improved by the creation of a snapshot log, providing that only a small proportion of the source table has been changed.
Snapshots can reduce the overhead of SQL dramatically for static tables.
Oracle sequences are an efficient mechanism of generating primary key values and should be used in preference to sequence tables or other mechanisms.
The DECODE operator can be used to perform complex aggregations, which might otherwise need to be performed via multiple queries.
($$) Indexes
Indexes on small tables (less than 100 rows) are totally useless, unless of course they are here to implement unique constraints (primary or unique keys).
Indexes for which there are less than 8 different key values should come under very close scrutiny (this of course refers to 'traditional' tree-type indexes, not bitmap ones). Here, the distribution of values is of course extremely important: -
If the values are roughly equally distributed, the index can be scrapped. If, however, searches are for keys, which belong to a small minority, then the index can be kept. It can make sense to have an index on a personnel table on the 'SEX' column if you are frequently searching for females in a predominantly male environment or vice versa.
Concatenated indexes should be concatenated from the most selective to the least selective column.
Indexes on single columns, which also appear as the first column in a concatenated index, are redundant. Database engines can use the concatenated index only when the first columns in the index appear in the search condition.
Posts
