10 Oracle Hints to the Database


Hints

A hint is an instruction passed to the optimizer through comments in an SQL statement. Hints enable you to make decisions normally made automatically by the optimizer.

Continue reading

Advertisements

06 Index Suppression


Reasons for Index Suppression (Accidental)

There are many reasons why an index might not be used even though you have an index on the column you are searching for.

  • Use of <> operator on an indexed column
  • Use o a SUBSTR function on an indexed column
  • use of arithmetic operators (+,_,*,/) on an indexed column
  • Use of TRUNC function on an indexed column
  • Use of || operator on an indexed column
  • Data type conversion on an indexed column
  • Use of IS NULL/ IS NOT NULL on an indexed column

Continue reading

02 Oracle Optimizer and its execution plan


Cost based optimization

What is cost based optimization?
Optimization is the selection of the best plan to un a SQL.

Oracle cost based optimizer is designed to determine the most efficient way to carry out an
SQL statement. but it can’t do this without good, up to date statistical information on
the data being accessed.

With the cost based approach, the optimizer factors in statistical information about the contents of the particular schema objets (tables, clusters, or indexes) being accessed. statistical information includes data on some of the items listed below

  • Columns, datatypes
  • Row count, Row length, Bytes, Percentage free
  • DB_BLOCK_SIZE
  • Statistics, Last Analyzed date
  • Pecent of Dsitinct Values on coulmn
  • All indexes on the table (including statistics)
  • All constraints on the table.

Continue reading

MongoDB Indexing Part 3


Sort Direction for index

When we created the index, we specified the sort direction for the index, in this case both ascending. Sorting on this indexes would work only is they are both indexed ascending or descending. But if you ix the direction of either fields, mongo will skip indexing all together.

Continue reading

MongoDB Indexing Part 2


Multi Term Query

db.animals.find({name:’cat’, tags:’land’}).explain()

— Looking for the name ‘cat and the tag ‘land’. the result

vlcsnap-2016-10-06-05h11m34s177

As you can see there is an index and the same index is being used.

Continue reading