Concatenated Index explained

I have very simple requirements of indexing Oracle GL base tables. So taking the opportunity to write this post.

Table: GL_JE_LINES

First of all, checking the exisiting indexes by running this query.
Select * From All_Ind_Columns Where Table_Name = 'GL_JE_LINES';

Attribute19 in the table is keeping the link to the journal import file. It is used heavily for queries. I considred few more comlumns and created indexes as follows.

EXECUTE IMMEDIATE
   'CREATE INDEX XXGL.XXGL_PERF_gl_je_lines_n1 '||
      'ON gl.gl_je_lines (attribute19, ledger_id) '||
      'TABLESPACE APPS_TS_TX_IDX';

Execute Immediate
   'CREATE INDEX xxgl.xxgl_perf_gl_je_lines_n2 '||
      'ON gl.gl_je_lines (ledger_id, period_name, effective_date, status, code_combination_id, je_header_id) '||
      'TABLESPACE APPS_TS_TX_IDX';

Execute Immediate
   'CREATE INDEX XXGL.XXGL_PERF_GL_JE_LINES_N3 '||
      'ON GL.GL_JE_LINES(attribute19) '||
      'TABLESPACE APPS_TS_TX_IDX ';

How to check which index is being used while running select statements?
Run the following commands in order to see the execution plan that shows what index is going to be used by the select.
Explain Plan For Select * From gl_je_lines Where ledger_id = 2021 and period_name = '2015-01';
select * from table(dbms_xplan.display);

In a concatenated index, the first column is the primary sort criterion and the second column determines the order only if two entries have the same value in the first column and so on.

So if a select query is written as follows, its not going to use xxgl_perf_gl_je_lines_n2 index, instead will do a full table scan:
select * from gl_je_lines Where effective_date = '15-05-2015';

More explanation is provided in the link below, a very good and simple article to read.

use-the-index-luke.com

Happy indexing!