Home » RDBMS Server » Server Administration » Maximum Query Length
Maximum Query Length [message #125023] Wed, 22 June 2005 16:18 Go to next message
kapil2005
Messages: 44
Registered: March 2005
Member
What is the Maximum Query length that oracle can run and what is my Maximum query length that sqlplus can handle. i have a query that is just a select statement 2.10 MB, can oracle run that query in oracle 9205 hp unix.
thanks
Re: Maximum Query Length [message #125025 is a reply to message #125023] Wed, 22 June 2005 16:38 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Out of curiosity, what are you doing with a 2MB query?
Re: Maximum Query Length [message #125030 is a reply to message #125023] Wed, 22 June 2005 17:21 Go to previous messageGo to next message
kapil2005
Messages: 44
Registered: March 2005
Member
I Know it makes curious, but I don’t know the query is built dynamically, it just have some sub queries and lot of logical “and” & “or” in where clause, so i want to know what is the Maximum query length or size.
Thanks for looking into
Re: Maximum Query Length [message #125110 is a reply to message #125023] Thu, 23 June 2005 06:15 Go to previous messageGo to next message
munishmsh
Messages: 15
Registered: May 2005
Location: Bangalore
Junior Member
I am not too sure, but I think the maximum lenght is somewhere around 64K of the query. Correct me if I am wrong.

[Updated on: Thu, 23 June 2005 06:22]

Report message to a moderator

Re: Maximum Query Length [message #125141 is a reply to message #125023] Thu, 23 June 2005 09:26 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I did a quick scan through the reference guide and didn't see a limit, although I haven't done a more extensive internet search. I'm pretty sure that there is a limit on the length of a single line in a query, but I don't know whether or not there is a limit on the query itself provided the lines were short enough. I don't think there is, but what happens when you run it, and save me the trouble of coming up with a test case?
Re: Maximum Query Length [message #125152 is a reply to message #125023] Thu, 23 June 2005 10:52 Go to previous messageGo to next message
kapil2005
Messages: 44
Registered: March 2005
Member
Ok guys, i found this one site, but i am trying to find the Evidence in oracle web site so that i am 100% sure.

http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm

please validate the maximum query length/size in the above web site with oracle docs
thanks
Re: Maximum Query Length [message #125153 is a reply to message #125141] Thu, 23 June 2005 10:53 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
It's 64K, according to the Oracle9i Database Reference. Look at the chapter "Logical Database Limits".

MHE
Re: Maximum Query Length [message #125155 is a reply to message #125023] Thu, 23 June 2005 10:55 Go to previous messageGo to next message
kapil2005
Messages: 44
Registered: March 2005
Member
please send me the link
Thanks a lot
Re: Maximum Query Length [message #125157 is a reply to message #125023] Thu, 23 June 2005 10:57 Go to previous messageGo to next message
kapil2005
Messages: 44
Registered: March 2005
Member
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch44.htm#288033
above is the link
Thanks a lot again
Re: Maximum Query Length [message #125172 is a reply to message #125023] Thu, 23 June 2005 11:49 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Thanks MHE for the pointer, caused me to notice something.

In 9iR2 docs it mentions the 64KB limit. But check out the 10gR1 page:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10755/limits003.htm#sthref3594

I'm not seeing a mention of the limit...

Although I'm not seeing any mention of a new feature here:

file:///E:/Scot/Oracle%2010g%20Doc%20Library/B14117_01/B14117_01/server.101/b10759/wnsql.htm#sthref11
Re: Maximum Query Length [message #125174 is a reply to message #125023] Thu, 23 June 2005 12:03 Go to previous message
munishmsh
Messages: 15
Registered: May 2005
Location: Bangalore
Junior Member
http://www-rohan.sdsu.edu/doc/oracle/server803/A54645_01/ch5.htm

this link shows the exact limit is 64K only for the 8i DB.
Previous Topic: Oracle Job did not start after promoting to PDC
Next Topic: user privs
Goto Forum:
  


Current Time: Thu Dec 05 10:07:01 CST 2024