Yes, the benefit comes when you want to query on a part of the index. If you put the part-used predicates first the index can be used for queries that involve those predicates but not all of the columns in the index.
Also, unless you have other requirements it can help to put the most selective predicates first, as this can trim down index seek operations faster.
In your case
IDX_2 is not necessarily redundant depending on the nature of the queries on the table. However, it may not be necessary to include all of the columns. If, for example, you do a lot of queries by
IDX_2 may be useful to help resolve those queries as
IDX_1 is not in the right order to be useful for that. You might, however, find that
item is redundant on
From 9i, Oracle introduced a 'skip scan' operator where trailing index columns can be queried more efficiently, which can reduce the need for supplementary indexes of this sort.
In a more specific case, if you are querying
date and don't need any other columns then the query could be completely resolved though the index without having to read anything from the table. You can also build covering indexes that have non-indexed columns attached. If all of the needed columns can be resolved from the covering index the query doesn't need to touch the main table at all.
Finally, in answer to your last question: If you have a set of regularly used queries that are using up a lot of resources and could be tuned using an index then it's certainly worth considering. However, maintaining indexes comes with an overhead on inserts, so you will have to trade query performance off against the overhead that the indexes place on insert or update operations.