We have a database table with a varchar2(4000) text column. All of the rows have less than 200 chars in the text column. We observed that
- SELECT text FROM tablename
is MUCH slower than
- SELECT SUBSTR(text, 1, 200) FROM tablename
(although the result is the same).
We have tested it with different ODBC browsers.
When we examine both versions of the SELECT with a network sniffer, we observe, that version 1 of the SELECT produces more TNS packets than version 2. Also, in version 1 of the SELECT you have much more requests at the network layer than in version 2. I think this is the reason, why a SELECT of 2.500 rows takes 13 seconds with version 1, and 1 second with version 2. Our customer has a slow network with a big latency time.
The Costomer uses Oracle 10.2.0.2.