There is some solution
1. If you query on a column/columns you can think of creaing an index on the column/columns.
2. You can user the parallel option (if your server is set to do it) i.e. in the CREATE table statement mention parallel(DEGREE n) and in the select statement you can use
ALTER SESSION ENABLE PARALLEL DML
SELECT /*+ parallel(a,3) */
FROM table_1 a
This opens 3 DML sessions instead of 1 and the fetch may be faster.
3. You can partition the table data across various tablespaces (if you have the option)
These three solutions are general. You'll have to test them
to get the performance.
Don't be scared of full table scans sometimes its the best choice.
There are times where performance improved doing full table scans.
The new optimizer in oracle 8i prefers full table scan (cost based
optimizer) if the query is going to return more than 4-7% of the
table (of course you can use the index hint to override this
You might want to consider the selectivity of the column you are
going to use before you create an index. If its a low selectivity
column like gender, you might be better off doing a full table scan
rather than a index scan followed by a table scan.
Partitioning is a good option. But you may have to do some more
research before you commit yourself to it (you have to have a plan
with your existing data, updates to partitioning key (some versions
of oracle don't allow you to update the partitioned key) etc...