Home » SQL & PL/SQL » SQL & PL/SQL » SQL taking more time when selecting column which has length of Varchar2(4000) (Oracle 9.2.1, Solaris)
SQL taking more time when selecting column which has length of Varchar2(4000) [message #354288] Fri, 17 October 2008 06:34 Go to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Hi All,

we are facing strange problem while selecting data from table.

when we run select statement with out one particular column which has length of Varchar2(4000) query getting completed in just second..

and whenever we inclucde those columns ( we have only two and those just using in select clause) same query taking more than 15 seconds.

can anyone give ideas on the same..

Thanking in advance. Smile
Re: SQL taking more time when selecting column which has length of Varchar2(4000) [message #354289 is a reply to message #354288] Fri, 17 October 2008 06:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It might take time to get a number of these columns over your network: for each row retrieved, you need to pull 8k over the line
Re: SQL taking more time when selecting column which has length of Varchar2(4000) [message #354293 is a reply to message #354289] Fri, 17 October 2008 07:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Possibly you have an index on the other two columns....

:Possibly you are running this query in SQL*Plus and are seeing the extra time it takes to print out all the data on screen....
Re: SQL taking more time when selecting column which has length of Varchar2(4000) [message #354443 is a reply to message #354293] Sun, 19 October 2008 00:48 Go to previous message
rleishman
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Run a SQL Trace and post the TKprof output here.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#i4640

Ross Leishman
Previous Topic: "ORA-12519: TNS:no appropriate service handler found"
Next Topic: Advanced partitioning question
Goto Forum:
  


Current Time: Wed Aug 23 12:44:57 CDT 2017

Total time taken to generate the page: 0.08948 seconds