Basically, Oracle has no ability to create a case-insensitive instance, as amazing as that seems! So, before 8i, if you wanted case-insensitive searches, you were guaranteed table scans. With 8i, this has been alleviated, but IMHO, it is still *no excuse* for not providing a case-insensitive option in the database!!
KMesser is *almost* right. If you want to guarantee that 'aaa' and 'AAA' cannot be inserted into the same table, create a *unique* function-based index on UPPER(colname);
This same index will also allow your case-insensitive searches to be indexed look-ups, providing that the WHERE clause matches the function-based index. ie WHERE UPPER(colname) [=|LIKE|...]...