SELECT Performance

• Retrieving specific field names is more efficient than retrieving all fields using the *.
  • The * character requires the added overhead of metadata interpretation lookups into the metadata dictionary to find the fields in the table.
• Reading field values directly from an index without reading a table at all is faster because the index occupies less physical space.

WHERE Performance

• The best filters utilize a primary key on a single table, preferably finding a single record only.
• Using the >, >=, <, and <= operators executes range searching.
  • Range searches are not as efficient as using equality with an = operator.
  • Range searching can still use indexing and is fairly efficient.
• Negative filters using NOT, !=, or <> try to find something that is not there.
  • Indexes are ignored and the entire table is read, so they have overhead and less efficient.
• The LIKE operator usually involves a full scan of a table and ignores indexing.
  • If searching for a small number of records, this could be extremely inefficient.
  • When searching for 10 records in 10 million, it is best to find those 10 records only using something like equality.
• Any type of functional expression used in a WHERE clause must be used carefully.
  • Functions are best not to be used where you expect a SQL statement to use an index.
• Utilize the index by not applying the function to a field in a table, but using the literal value on the opposite side of the expression:

SELECT * FROM [MyTable]
WHERE [CreateDate] = TO_DATE('01-JAN-2005');

• Very small tables are often more efficiently read by reading only the table, and not the index plus the table.
  • The same applies when large portions of a single table are read.
  • If enough of the table is read at once, the index may as well be ignored.
• Reading an index involves scanning an index and then passing pointers through to a table, scanning the table with index values found.
  • When enough of the table is read, index scanning activity to find table records can become more time-consuming than reading only the table (ignoring the index).
• In many databases, the sequence of fields in a WHERE clause can determine if an index is matched or missed.
• IN and EXISTS can be efficient, depending on how they are used.
  • Performing an IN check against a non-indexed field forces a full table scan.
• The EXISTS operator full scans two tables.
  • This can be made more efficient by using a WHERE clause in both queries and by using a correlation between the calling query and subquery.
• Matching indexes where AND and OR operators are used is important because anything missing in an index field use could result in a full table scan.

Joins

• Inner joins are the most efficient types of joins because they match records between two tables based on equality (an = sign).
  • A self-join is a special type of intersection where records on multiple hierarchical levels, stored in a single table, are returned as a hierarchical structure.
• Cross joins will be slow, depending on the number of records in both tables, relative to each other, and in total number of records.