The Right Column Order in Multi-Column Indexes
The execution time grows with the table size: if the table grows tenfold, the TABLE ACCESS FULL takes ten times as long. The danger of this operation is that it is often fast enough in a small development environment, but it causes serious performance problems in production.
The operation TABLE ACCESS FULL, also known as full table scan, can be the most efficient operation in some cases anyway, in particular when retrieving a large part of the table.
A concatenated index is one index across multiple columns.
The ordering of a two-column index is therefore like the ordering of a telephone directory: it is first sorted by surname, then by first name. That means that a two-column index does not support searching on the second column alone; that would be like searching a telephone directory by first name.
The most important consideration when defining a concatenated index is how to choose the column order so it can be used as often as possible.