ExamGuru IT/.Net Guru : NOCOUNT?!? on ASPFriends.com 'ngfx-oracle' list

.NETGURU

NOCOUNT?!?

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-oracle' list.

Moore, Dot Net Guruhew

SQL Server has an option "NOCOUNT" that turns off the end results for queries on how many rows are returned. Does Oracle have this same option? It would add a lot of efficiency if the database doesn't have to count results as they are returned. Every little bit helps. :)

Thanks
Dot Net Guru

Reply to this message...

Tom Wells

Dot Net Guru,

If I understand your question correctly you are refering to the RowNum psuedocolumn. Is this correct?
It's not clear from the Oracle docs I have as to this specific question. But, from what I can determine,
the following info may help.

Example of how RowNum is used:

Given:
Table: CUSTOMER
(
ID NUMBER,
NAME VARCHAR2
)

Select RowNum, ID, NAME
From CUSTOMER
Where ID < 3000

You can also do this to limit the resultset to a certain number of records (similar to TOP in T-SQL) like this:

Select RowNum, ID, NAME
From CUSTOMER
Where RowNum < 100

or

Select ID, NAME
From CUSTOMER
Where RowNum < 100

A RowNum column is generated, assigned, and incremented for each row returned. RowNum is generated
and assigned to the row in the order fetched by the RDBMS, NOT in the order set by a ORDER clause. As
far as I can determine, if the RowNum psuedocolumn is not used in the query, the RDBMS optimizes this
step out of the query.

Another option is to check out some of the Oracle HINTS. If you need info on psuedocolumns or HINTs
let me know.

Tom.

----- Original Message -----
From: "Moore, Dot Net Guruhew" <Click here to reveal e-mail address>
To: "ngfx-oracle" <Click here to reveal e-mail address>
Sent: Tuesday, June 25, 2002 11:51 AM
Subject: [ngfx-oracle] NOCOUNT?!?

[Original message clipped]

Reply to this message...

Moore, Dot Net Guruhew

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------=_NextPartTM-000-e155cc77-1231-4f4c-934c-891eeb9c37f9
Content-Type: multipart/alternative;
    boundary="----_=_NextPart_001_01C22106.39E4B82A"

------_=_NextPart_001_01C22106.39E4B82A
Content-Type: text/plain

Well, you didn't understand the question correctly. At the end of every query, there is a line like the following:

65 rows selected.

I don't want to see this and I don't want this calculated because it is causing a small performance hit. In SQL Server all you have to do is at NOCOUNT and I was looking to a similar version for oracle.

Dot Net Guru

-----Original Message-----
From: Tom Wells [mailto:Click here to reveal e-mail address]
Sent: Wednesday, June 26, 2002 6:07 AM
To: ngfx-oracle
Subject: [ngfx-oracle] Re: NOCOUNT?!?

Dot Net Guru,

If I understand your question correctly you are refering to the RowNum psuedocolumn. Is this correct?

It's not clear from the Oracle docs I have as to this specific question. But, from what I can determine,

the following info may help.

Example of how RowNum is used:

Given:

Table: CUSTOMER

(

ID NUMBER,

NAME VARCHAR2

)

Select RowNum, ID, NAME

From CUSTOMER

Where ID < 3000

You can also do this to limit the resultset to a certain number of records (similar to TOP in T-SQL) like this:

Select RowNum, ID, NAME

From CUSTOMER

Where RowNum < 100

or

Select ID, NAME

From CUSTOMER

Where RowNum < 100

A RowNum column is generated, assigned, and incremented for each row returned. RowNum is generated

and assigned to the row in the order fetched by the RDBMS, NOT in the order set by a ORDER clause. As

far as I can determine, if the RowNum psuedocolumn is not used in the query, the RDBMS optimizes this

step out of the query.

Another option is to check out some of the Oracle HINTS. If you need info on psuedocolumns or HINTs

let me know.

Tom.

----- Original Message -----

From: "Moore, Dot Net Guruhew" < <mailto:Click here to reveal e-mail address> Click here to reveal e-mail address>

To: "ngfx-oracle" < <mailto:Click here to reveal e-mail address> Click here to reveal e-mail address>

Sent: Tuesday, June 25, 2002 11:51 AM

Subject: [ngfx-oracle] NOCOUNT?!?

[Original message clipped]

------_=_NextPart_001_01C22106.39E4B82A
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">

<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dblue>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>Well, you didn't understand the
question correctly. At the end of every query, there is a line =
like the
following:</span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>65 rows =
selected.</span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'>I don't want to see this and I =
don't
want this calculated because it is causing a small performance =
hit. In SQL
Server all you have to do is at NOCOUNT and I was looking to a similar =
version
for oracle.</span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>

<p class=3DMsoNormal><font size=3D2 color=3Dnavy face=3DArial><span =
style=3D'font-size:
10.0pt;font-family:Arial;color:navy'> </span></font></p>

<div>

<p class=3DMsoNormal><em><b><i><font size=3D3 color=3Dnavy =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt;color:navy;font-weight:bold'>Dot Net Guru</span></font=
></i></b></em><font
color=3Dnavy><span style=3D'color:navy'> </span></font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'> </span></font></p>

</div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DTahoma><span
style=3D'font-size:10.0pt;font-family:Tahoma'>-----Original =
Message-----<br>
<b><span style=3D'font-weight:bold'>From:</span></b> Tom Wells
[mailto:Click here to reveal e-mail address] <br>
<b><span style=3D'font-weight:bold'>Sent:</span></b> =
</span></font><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;font-family:Tahoma'>Wednesday, June
26, 2002</span></font><font size=3D2 face=3DTahoma><span =
style=3D'font-size:10.0pt;
font-family:Tahoma'> </span></font><font size=3D2 face=3DTahoma><span
style=3D'font-size:10.0pt;font-family:Tahoma'>6:07 =
AM</span></font><font size=3D2
face=3DTahoma><span style=3D'font-size:10.0pt;font-family:Tahoma'><br>
<b><span style=3D'font-weight:bold'>To:</span></b> ngfx-oracle<br>
<b><span style=3D'font-weight:bold'>Subject:</span></b> [ngfx-oracle] =
Re:
NOCOUNT?!?</span></font></p>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>Dot Net Guru,</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>If I understand your question correctly you =
are
refering to the RowNum <em><i><font face=3D"Times New =
Roman">psuedocolumn</font></i></em>.
Is this correct?</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>It's not clear from the Oracle docs I have =
as to this
specific question. But, from what I can determine, </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>the following info may =
help.</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>Example of how RowNum is =
used:</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><strong><b><font =
size=3D2
face=3D"Times New Roman"><span =
style=3D'font-size:10.0pt'>Given:</span></font></b></strong><font
size=3D2><span style=3D'font-size:10.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>Table: =
CUSTOMER</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>    &nb=
sp;         (</span></font>=
</p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier =
New"'>       ID   &nbs=
p; NUMBER,
</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier =
New"'>       NAME   VA=
RCHAR2</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier =
New"'>       )</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>Select RowNum, ID, =
NAME</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>From =
CUSTOMER</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>Where ID < =
3000</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>You can also do this to limit the resultset =
to a
certain number of records (similar to TOP in T-SQL) like =
this:</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>Select RowNum, ID, =
NAME</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>From =
CUSTOMER</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>Where RowNum < =
100</span></font></p>

</div>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>    =
or</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>Select ID, =
NAME</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>From =
CUSTOMER</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Courier New"><span
style=3D'font-size:10.0pt;font-family:"Courier New"'>Where RowNum < =
100</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'> </span></font></p>

</div>

</div>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>A RowNum column is generated, =
assigned, and
incremented for each row returned. RowNum is generated =
</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>and assigned to the row in the order fetched =
by the
RDBMS, NOT in the order set by a ORDER clause. As </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>far as I can determine, if the RowNum =
psuedocolumn is
not used in the query, the RDBMS optimizes this </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>step out of the query.</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>Another option is to check out some of =
the Oracle
HINTS. If you need info on psuedocolumns =
or HINTs</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>let me know.</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3D"Times New Roman"><span
style=3D'font-size:10.0pt'>Tom.    </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>----- Original Message =
----- </span></font></p>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>From: "Moore, =
Dot Net Guruhew"
<</span></font><a href=3D"mailto:Click here to reveal e-mail address"><font =
size=3D2
face=3DArial><span =
style=3D'font-size:10.0pt;font-family:Arial'>Click here to reveal e-mail address</s=
pan></font></a><font
size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;font-family:Arial'>></span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>To: =
"ngfx-oracle" <</span></font><a
href=3D"mailto:Click here to reveal e-mail address"><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>Click here to reveal e-mail address<=
/span></font></a><font
size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;font-family:Arial'>></span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>Sent: Tuesday, June 25, =
2002 11:51
AM</span></font></p>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>Subject: [ngfx-oracle] =
NOCOUNT?!?</span></font></p>

</div>

</div>

<div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D3 =
face=3D"Times New Roman"><span
style=3D'font-size:12.0pt'> </span></font></p>

</div>

<p class=3DMsoNormal style=3D'margin-left:.5in'><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>> SQL Server has an =
option
"NOCOUNT" that turns off the end results for queries on how =
many rows
are returned. Does Oracle have this same option? It would =
add a lot
of efficiency if the database doesn't have to count results as they are
returned. Every little bit helps. :)<br>
> <br>
> Thanks<br>
> Dot Net Guru</span></font> </p>

</div>

</body>

</html>

------_=_NextPart_001_01C22106.39E4B82A--

------=_NextPartTM-000-e155cc77-1231-4f4c-934c-891eeb9c37f9--

Reply to this message...

Robert Strader

If I understand correctly - you want to turn off the following

SQL> select sysdate from dual;

SYSDATE
---------
01-JUL-02

1 row selected.

In SQL Plus you are looking for 'FEEDBACK"
SQL> show feedback
FEEDBACK ON for 6 or more rows

then just use the set command to turn it off
set feedback off

I believe this only applies to SQL Plus though.

Bob

At 09:50 AM 7/1/2002 -0400, you wrote:

[Original message clipped]

Reply to this message...

Moore, Dot Net Guruhew

That's exactly what I'm looking for, but for stored procedures and even datareaders. I couldn't remember the feedback word from my PL/SQL class (probably never thought I'd need it) and I will see if that works.

Still looking for definite ideas though.

Dot Net Guru

-----Original Message-----
From: Robert Strader [mailto:Click here to reveal e-mail address]
Sent: Monday, July 01, 2002 10:37 AM
To: ngfx-oracle
Subject: [ngfx-oracle] Re: NOCOUNT?!?

If I understand correctly - you want to turn off the following

SQL> select sysdate from dual;

SYSDATE
---------
01-JUL-02

1 row selected.

In SQL Plus you are looking for 'FEEDBACK"
SQL> show feedback
FEEDBACK ON for 6 or more rows

then just use the set command to turn it off
set feedback off

I believe this only applies to SQL Plus though.

Bob

Reply to this message...

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification