Hi Rajeev, a Covering Index means that all of the columns needed to process a query are included in an existing index. The optimizer can use this index instead of scanning the base table, which usually results in less IOs -> faster query.
It might be a NUSI (simple case, only a single table is used in query) or a Join Index (complex case, multiple tables plus aggregation possible). Creating an index specifically for query covering is mainly useful if you got a set of predefined queries with a large number of executions.
I would like to extent my understanding with a simple scenario
Now for example i have a set of 5 columns which are frequently queried and a particular query utilises only these 5 if i were to define a compound SI using these 5 then the next time i use any of these 5 columns in either select or in the where clause then the optimizer would make a choice to read the subtable instead of accessing the larger base table
however if the query uses only say 2-3 columns then it may not choose to use the subtable
Also in case of SI is there a necessity that all the index columns be used in the where clause of the query