Creating an Index in Oracle, and the Best Way to Make Use of It

Create index oracle

Indexes are among the most useful and underutilized components of SQL. The user can create an Oracle index and store values along with their location in it.

Similar to the index at the end of a book, an index enables the user to go straight to the data they are interested in. Indexes are most useful when a user has to find a few rows. Therefore, they can use an index in statements that return a handful of rows – after creating one, of course!

Simple Techniques to Create an Index in Oracle Database

Creating an index is a simple task in MySQL query optimization as you only need to know two things:

  • The columns that require indexing
  • The name you will give the index

Here’s how to create one:

create index <indexname> on <tablename> ( <col1>, <col2>, <col3>, … <coln> );

Eg. create index cars_colour_metallic on cars (colour);

However, there are a few things to know about indexes before you begin:

  • You can place several columns in a single index, which then becomes a composite or compound index.

For instance, in the above example, you could also add the types of cars in the index like this: create index cars_colour_metallic on cars (colour, type);

  • The order in which you set columns in the index affects its use by the optimizer.

Next, let’s take a look at two of the most important index types users create in Oracle.

Two Major Index Types – and When to Pick Each

There are several kinds of indexes in the Oracle database that can improve your SQL. However, one of the most significant decisions you’ll have to make is likely to involve choosing between B-trees and bitmaps.

Create Index Oracle: B-tree Versus Bitmap Indexes

B-trees:– Indexes are in balanced B-tree format by default, which means all the leaf nodes are located at the same depth. It takes equal effort (O(log n)) to access any value, and one leaf index entry contains one row of data.

Bitmap:- Bitmaps also store indexed values, but in a completely different manner as compared to B-trees. In it, one value entry is associated with a range of row values. A bitmap has a series of 1s (yes) and 0s (no) to indicate whether any of the range rows contains the value or not.

One major difference between these two index types is that a B-tree doesn’t include null indexed values; a bitmap does. A bitmap can, therefore, answer some statements during MySQL query optimization, such as targeted index searches in which the column has a null value.

Although this won’t work for a B-tree, the user can add a constant at the end of an index to turn it into a composite index.

Bitmaps are also helpful because compressing the bits is simpler, which is why a bitmap index is generally smaller as compared to a B-tree index with identical data.

Why You Need to Keep a Check on the Indexes You Create

With all the benefits an index provides, it is important to create as few of them as possible. This is because you may end up creating one for every specific requirement and forget about them over time. The same goes for other users who may come and go on your team. And no one will have a clue why Brad needed to create that six-column function-based nightmare.

Since you don’t know if the index in question is only used for year-end reporting or never used, you cannot drop an index whenever you want. This can result in awkward situations where a table contains more indexes than columns!

So, if you’re unsure between two excellent indexes and one “good enough” index, it is better to choose the latter. And don’t forget to test!

The Importance of Query Optimization in SQL Server Plus Tips

query optimization in SQL Server

The database is a vital part of a majority of IT systems as it contains that data that needs to be processed for utilization. SQL is the standard language for fetching data from the database using queries and query optimization in SQL Server helps maintain database performance.

The queries used to communicate with the database are almost always simple and quick. The most common operations include the four major data manipulation techniques (create, select, update, and delete) that execute at quick rates in most cases. However, as you already know, they aren’t the only tasks an administrator has to do in a database.

Why Experts Recommend Query Optimization in SQL

Operational databases may have a requirement to execute statements that take a long time. For instance, queries that need access to several tables or include tasks such as aggregation.

Although statements that operate on data warehouses aren’t time-critical, the ones running on operational databases are. They are necessary to fetch the requested information as quickly as possible – which makes SQL Server performance tuning important. One of the best examples of systems that need optimal SQL is one that has access to a geo server. A geo server contains millions of geographical information spread across countless tables.

Moreover, the database operations taking place on this data is often performance heavy. Such tasks may include the intersection of lands and calculations of area, the distance between two geographical locations, etc.

These involve complex geo operations that need a large amount of data present in multiple tables. If the SQL statement used for these operations isn’t efficient, it could take days or months to fetch the results. This is where the DBA will need query optimization in SQL Server to ensure the users don’t have to wait too long to get a result.

Some Quick Tips for Maintaining Optimal Database Query Performance

Take a look at some time-tested techniques of optimizing queries in SQL that not only save a lot of time and resources but are also useful for a wide range of databases:

  • Check efficiency using LIMIT – Many times, the queries have to run on a huge scale of data. You would not want to wait for your statement to execute completely before finding out that you have used the wrong statement or it was inefficient. Therefore, limiting your statement to a smaller amount of information can help you check its validity and with SQL Server performance tuning. Once you’ve run the query and are satisfied with its efficiency, you can carry it out on the scale you want.
  • Long statements can help – At times, you may have written statements that may seem simple to you as they didn’t take long to understand. However, they took too long to actually execute. In databases involving time-critical operations, the opposite would be preferable. Even though the queries will become complex and take a bit longer to understand, they will save a lot of time.
  • Give preference to quicker data structures – This depends on the programming-based knowledge of the DBA. For example, integer comparisons are far quicker than string comparisons.
  • Don’t be tempted to use IN operator – Checking the existence of certain data in a table may be important. But try not to use the IN operator as it slows down the result.

In Conclusion

As mentioned before, these tips are useful for a majority of databases out there. Since SQL is a declarative language, certain databases are likely to optimize for the cases we have talked about above.

However, test the tips before you try to apply them for query optimization in your database. In case some of these don’t work, you may want to consider using a tuning tool to make things easier. Get in touch with us to know how our tuning tool can simplify database query optimization for you.