full table scans are not always deceptive, considering the query not being optimized properly and hitting large tables multiple times. The explain plan would go for 'direct path read' wait events, so the optimizer will not put the query in the SGA buffer cache instead forces the execution entirely pinning against PGA. This favors the quickest output fetch with a bit downside to the I/O during the query execution.
thanks a lot for a really informative video.. Can we have examples of queries which would result in different index access method and join methods ? That would really be helpful