Q:

Avoiding full table scan

I have the following query which obtains transactions from the transactions table and transaction detail.  Both tables have a big amount of entries, so this query takes a while to return results.  
SELECT * FROM transactions t LEFT JOIN transac_detail tidts ON (tidts.id_transac = t.id);

However, I'm more worried because of the fact that Oracle does a full table scan on both tables, according to explain plan, even though t.id and tidts.id_transac have indexes. 
Is there any way to optimize this without touching table structure? 

A:

I don't think it's true that the SQL neccessarily would be best served with full table scans.  This would really be most obviously true where there is a foreign key between the two, but even then there could be exceptions. 
I think the key question is this: "what proportion of the rows from each table are expected to be included in the result set?".  If the answer is "100% from each" then you have a clear case for full table scans (and a hash join). 
However consider the case where tables A and B are joined, with table A containing 5 rows with a foreign key to table B (the parent) containing a million rows.  Obviously here you'd look for a full scan of A with a nested loop join to B (the join column on table B would be indexed because it would have to be a primary or unique key). 
In the OP's case though it looks like you'd expect 100% rows to be returned from each table.  I'd expect to see a full scan of both tables, and a hash join with TRANSACTIONS(probably the smaller table) being accessed first and built into the hash table.  This would be the optimum join method, and I'd just be looking out for a situation where TRANSACTIONS is too big for a single-pass hash join.  If the join spills to disk then that could be a performance worry and you'd have to look at increasing the memory allocation or equi-partitioning the two tables to reduce the memory requirement.