Why do we use dense indexes for unique columns?

user_8147103 0
Viewed 313

I'm reading about Oracle database indexes, and there is some point that I couldn't get it on my head, why do we need dense indexes especially in case of dense indexes for unique columns ? the fact that having an entry on dense indexes for each entry on our database table, makes the I/O cost the same for searching over the index table or over the database table, which on my point of view doesn't optimize the performance of our DBMS, I'm sure that I'm missing a point on how dense indexes work, but I've really searched over the Oracle doc without finding an appropriate response to my question.
Ok I get the point, of using a dense index in case of non unique key, but my real question is how would using it for a unique key gain performance ? what is the difference between accessing entries on dense indexes and accessing database entries in case of unique key dense indexes as they have both the same number of entries ?

EDIT :

In a dense index, a record is created for every search key valued in the database. This helps you to search faster but needs more space to store index records. In this Indexing, method records contain search key value and points to the real record on the disk. enter image description here

Dense indexes definition

indexing oracle sql database
2 Answers

the fact that having an entry on dense indexes for each entry on our database table, makes the I/O cost the same for searching over the index table or over the database table,

Because indexes that I'm familiar with are all "dense indexes", I'll use "index" to mean that and "sparse index" for other varieties.

This is simply misguided.

The purpose of an index is to significantly reduce I/O costs of reading tables. An index conceptually does so by storing an ordered list of the keys. The index structure can then be quickly traversed to find a particular value or in many cases a range of values.

Conceptually you can think of an index as an ordered list of the keys and binary search. However, the actual implementation could be quite different. The most common use balanced trees. More esoteric types use hash codes. And then some types are specific to particular data types, such as GIS indexes and full text indexes.

A very important part of an index is that it can locate the specific rows with a particular value. A second important part is that SQL tables represent unordered multisets (multisets are just sets that allow duplicate values).

If you put these together, you will realize that a sparse index really doesn't make sense, because there is no way to find values that are not in the index.

What this is probably referring to are clustered indexes on a table. A clustered index is a particular type of index where the underlying data is actually sorted by the index key (only one clustered index per table). As a space optimization on clustered indexes, sparse storage makes some sense. Scanning the records on a single page is often comparable in cost to descending a few levels of index depth.

I also want to point out that "sparse" has a more common usage in database-talk -- and that would be sparse data. Column-oriented databases are optimized for -- among other things -- columns that often have NULL values. However, I don't think this use of "sparse index" is related to sparse data.

user_11440350

The key point is that indexes are not the same as table.

Indexes and Index-Organized Tables

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated Thus, an index can be dropped or created without physically affecting the table for the index.

So in context of unqiue index it is much faster to read from single column B-Tree index than table than contain multiple columns.

user_50708790
Related