Here we are on the topic of indexes and really what they are. When you really think about it, tables can get very, very large. What I mean by very, very large is that they can reach millions or hundreds of millions, or billions of rows of data, it's very difficult to go row by row to find something you're looking for. Imagine if you have hundreds of millions of rows of data, how is a database supposed to find anything in that stack? It, Oracle, does utilize some very complex and intelligent algorithms to locate your data very quickly. However, indexes make it possible to find that data in the fastest way possible. In short, you use an index on any column or columns that you want to locate very, very quickly. Things such as names that you look for very frequently, perhaps product IDs, or SKUs that you look for, those things that you look for very frequently are ideal candidates for the construction of and building of indexes on a given table. When you do construct an index, or you decide to construct an index on a given table, you want to think very carefully on why you're building that table on that given column. For example, you really don't want to build an index on a column that is not very search-friendly. Sounds funny, but it is true. Think about it for a minute. What if you have a column like notes column or a comments column? Those kinds of columns that are free flowing texts are not good reasons for creation of an index. They're just not good candidates because someone is typing in data that is in free-form. Free-form data, generally speaking, is not a good candidate for data that should be indexed. One of the easiest things you're going to do is to know how to create an index. Once you've made a decision about why you want one and you understand what it is and what purpose it serves, the way with which you create an index is quite simple. You simply issue the DDL command, data definition language of create an index. You do a create index command to construct one. Very, very simple. The create index command, simply create an index on a column or columns that you specify, allowing you to name the index as well. After that, for the columns you're creating the index on can begin to be used immediately. Your SQL statements. Once you create the index, your DML statements, which are your select, insert, update, delete statements do not need to be changed. The creation of an index on a given table does not alter your existing DML statements at all. It doesn't alter your views, your store procedures, your development environment, or any of that. They simply add value to your table and your environment immediately. What I mean by add value is, they add performance to your environment immediately when done right. There are many different options to choose from that you can use to create an index with. By default, indexes that are created in Oracle are what are known as B-tree index or balanced-tree indexes. There are many other ways or formats, or options you can use to create indexes with, but B-tree indexes are the most common ones. There are some best practices, there are lots of them actually. The simplest one I'm going to tell you about now is that you don't want to create indexes on the columns or data that are really not worthy. You know what I mean by not worthy? Such as if you were creating an index on US states, for example, well, there are just 50 states. That's not a reason to create an index. That column, that table is too small. If you were going to construct an index on a given column, you want to construct it on columns and tables that contain lots of data. What is lots of data? Well, it's hard to say what lots of data is? I usually like to say more than 20,000 rows. Let's just start with that. More than 20,000 rows or as I would like to say, north of 20,000 rows. Twenty thousand rows really isn't very much. Twenty thousand rows to construct an index on, you really have to take a look at what benefit it provides you with. Creating an index on tables and on columns that don't have a lot of data can work against you, because it begins to eat up space that perhaps doesn't need to be eaten up. That's indexes. I'm going to be showing you how to implement them and how to use them.
