Posts Tagged ‘database indexes’

Index Skip Scan in Oracle

Index Skip Scan in Oracle
With Oracle 9i, the Cost-Based Optimizer (CBO) is equipped with many useful features, one of them is "Index skip scan". In previous releases a composite index could only be used if the first column, the leading edge, of the index was referenced in the WHERE clause of a statement. In Oracle 9i this restriction is removed ...

Index usage with LIKE operator in Oracle & Domain Indexes

Index usage with LIKE operator in Oracle & Domain Indexes
A lot of developers might be confused about index selectivity while using %LIKE% operator. So please find below how index worked when you use LIKE operator.Problem StatementWhile optimizing high CPU consuming queries on 3rd party application, verified that most of the queries are using '%LIKE%' operator. Interestingly enough, while some of these queries are going for "INDEX RANGE" while others are going for "FULL TABLE ...

How To Reset MySQL Autoincrement Column

MySQL database provides a wonderful feature of Autoincrement Column index. Your database table can define its primary key as Autoincrement number and MySQL will take care of its unique value while inserting new rows.Each time you add a new row, MySQL increments the value automatically and persist it to table. But sometime you may want ...

Invisible Indexes in Oracle 11g

Invisible Indexes in Oracle 11g
The effective usage of an index is always a big question. There are instances in production environments, where an index may help the performance of just a single query, while it can degrade the performance of a number of queries. Always the Optimizer can’t guarantee the best suitable index required for the query in an environment ...

Understanding Primary Key(PK) Constraint in Oracle

Understanding Primary Key(PK) Constraint in Oracle
The Primary Key(PK) constraint is the most basic concept of any RDBMS (I am particularly interested in Oracle). Yet, I have noticed people getting confused when it comes to the practical usage and asking questions like:- I have disabled PK and now oracle is doing full table scan. - How PK constraints and indexes are related/different? - How ...

Full-Text Search using MySQL: Full-Text Search Capabilities

Full-Text Search using MySQL: Full-Text Search Capabilities
Have you ever thought how does the search functionality is implemented in all the websites! Most of the internet blogs and websites are powered by MySQL database. MySQL provides a wonderful way (Full-text Search) of implementing a little search engine in your website. All you have to do is to have MySQL 4.x and above. MySQL ...