0

When I open up TOAD and do a select * from table,the results (first 500 rows) come back almost instantly. But the explain plan shows full table scan and the table is very huge. How come the results are so quick?

Victor
  • 16,609
  • 71
  • 229
  • 409

2 Answers2

4

In general, Oracle does not need to materialize the entire result set before it starts returning the data (there are, of course, cases where Oracle has to materialize the result set in order to sort it before it can start returning data). Assuming that your query doesn't require the entire result set to be materialized, Oracle will start returning the data to the client process whether that client process is TOAD or SQL*Plus or a JDBC application you wrote. When the client requests more data, Oracle will continue executing the query and return the next page of results. This allows TOAD to return the first 500 rows relatively quickly even if it would ultimately take many hours for Oracle to execute the entire query and to return the last row to the client.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks. But I see that if I create a view by doing a select * on the table and then do :select * from view then it takes much longer time. Why is this? – Victor Oct 15 '12 at 14:38
  • @Kaushik - There shouldn't be a difference. Are you seeing a different query plan what the view is involved? Is there a predicate (a `WHERE` clause) on either query? – Justin Cave Oct 15 '12 at 14:39
  • Sorry I was wrong.the view has got some complex predicates and that is the problem. – Victor Oct 15 '12 at 14:46
0

Toad only returns the first 500 rows for performance, but if you were to run that query through an Oracle interface, JDBC for example, it would return the entire result. My best guess is that the explain plan shows you the results in the case it doesn't get a subset of the records; that's how i use it. I don't have a source for this other than my own experience with it.

Christopher Gillis
  • 197
  • 9
  • Thank you. But my question is how the results are so quick even if it returns only 500 rows? will it not fetch the entire record set and then get 500 rows from it? – Victor Oct 15 '12 at 14:35
  • Good distinction, sorry that I misunderstood. The above answer explains it. – Christopher Gillis Oct 15 '12 at 19:21