OraFAQ Forum: SQL & PL/SQL » Please help me out!!

Home » SQL & PL/SQL » SQL & PL/SQL » Please help me out!!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please help me out!! [message #249328]

Wed, 04 July 2007 01:57

fastfreeeasy
Messages: 25
Registered: June 2007

Junior Member

Oracle scans a table twice for a simple SQL statement like "SELECT emp_code INTO l_emp_code FROM emp" to check if there is a duplicate record and raise TOW_MANY_ROWS exception. What happens when there is an aggregate function in an SQL?

Report message to a moderator

Re: Please help me out!! [message #249333 is a reply to message #249328]

Wed, 04 July 2007 02:11

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Oracle scans a table twice for a simple SQL statement like "SELECT emp_code INTO l_emp_code FROM emp to check if there is a duplicate record"

This is wrong. Oracle scans only once.
Moreover your statement does not check duplicates.

Quote:

and raise TOW_MANY_ROWS exception.

Of course there is more than one row in emp!

Quote:

What happens when there is an aggregate function in an SQL?

I don't understand the question

Regards
Michel

Report message to a moderator

Re: Please help me out!! [message #249339 is a reply to message #249333]

Wed, 04 July 2007 02:19

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Thanks for confirming my suspicion, Michel. There isn't a second fetch. The query of the cursor gets executed once.

MHE

Report message to a moderator

Re: Please help me out!! [message #249345 is a reply to message #249333]

Wed, 04 July 2007 02:44

fastfreeeasy
Messages: 25
Registered: June 2007

Junior Member

I am changing my SQL statement like this "SELECT emp_code INTO l_emp_code FROM emp WHERE emp_code = 1234". If this statement is used in a PL/SQL block then will Oracle scans emp table twice? If not then how Oracle raise TWO_MANY_ROWS exception. Believe me I read this fact in Oracle book itself.

Report message to a moderator

Re: Please help me out!! [message #249348 is a reply to message #249328]

Wed, 04 July 2007 02:51

oracle_fly
Messages: 1
Registered: July 2007
Location: HangZhou in China

Junior Member

Really?! raise TOW_MANY_ROWS exception!
Mo,it's should raise too many rows exception!

Report message to a moderator

Re: Please help me out!! [message #249355 is a reply to message #249345]

Wed, 04 July 2007 03:10

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Post your execution
2/ Post the link or reference where you see that.

Regards
Michel

Report message to a moderator

Re: Please help me out!! [message #249356 is a reply to message #249345]

Wed, 04 July 2007 03:11

Maaher
Messages: 7065
Registered: December 2001

Senior Member

fastfreeeasy wrote on Wed, 04 July 2007 09:44

Believe me I read this fact in Oracle book itself.

I believe that you read it. What book? What Oracle version was it talking about?

MHE

Report message to a moderator

Re: Please help me out!! [message #249362 is a reply to message #249356]

Wed, 04 July 2007 03:24

Maaher
Messages: 7065
Registered: December 2001

Senior Member

A little follow up: I created a small test script:

ALTER SESSION SET SQL_TRACE = TRUE;
DECLARE
  v_thenum NUMBER;
BEGIN
  -- should, according to the 'Oracle book', fetch twice.
  SELECT employee_id
  INTO   v_thenum
  FROM   employees
  WHERE  rownum < 2;
END;
/

ALTER SESSION SET SQL_TRACE = FALSE;

In attach is my TKPROF output file. The interesting part is this:

DECLARE
  v_thenum NUMBER;
BEGIN
  -- should, according to the 'Oracle book', fetch twice.
  SELECT employee_id
  INTO   v_thenum
  FROM   employees
  WHERE  rownum < 2;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.03          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.03          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 33  
********************************************************************************

SELECT EMPLOYEE_ID 
FROM
 EMPLOYEES WHERE ROWNUM < 2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.03       0.04          0          1          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=1 pr=0 pw=0 time=65 us)
      1   INDEX FULL SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=40 us)(object id 12102)

Now, I'm not remotely a DBA but as far as I can see, there are nowhere signs of a double fetch.

MHE

Attachment: xe_cursors.txt
(Size: 6.25KB, Downloaded 409 times)

Report message to a moderator

Re: Please help me out!! [message #249364 is a reply to message #249355]

Wed, 04 July 2007 03:28

fastfreeeasy
Messages: 25
Registered: June 2007

Junior Member

DECLARE
l_emp_code emp.emp_code%TYPE ;
BEGIN
SELECT emp_code
INTO l_emp_code
FROM emp
WHERE emp_code = 1234 ;
END;

-- Now what will happen if more than 1 record exists in emp table --
-- for emp_code 1234 . Oracle will raise TOW_MANY_ROWS exception --
-- My question is how Oracle raise TOW_MANY_ROWS exception without--
-- scanning 2 twice. Is it possible in single scan? If so what is --
-- mechanism?

Report message to a moderator

Re: Please help me out!! [message #249371 is a reply to message #249364]

Wed, 04 July 2007 03:39

Maaher
Messages: 7065
Registered: December 2001

Senior Member

A little follow up: I created a small test script (again):

ALTER SESSION SET SQL_TRACE = TRUE;
DECLARE
  v_thenum NUMBER;
BEGIN
  -- should, according to the 'Oracle book', fetch twice.
  SELECT employee_id
  INTO   v_thenum
  FROM   employees;
END;
/

ALTER SESSION SET SQL_TRACE = FALSE;

And here is the TKPROF output:

TKPROF: Release 10.2.0.1.0 - Production on Wed Jul 4 10:35:47 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: xe_ora_2936.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

ALTER SESSION SET SQL_TRACE = TRUE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 33  
********************************************************************************

DECLARE
  v_thenum NUMBER;
BEGIN
  -- should, according to the 'Oracle book', fetch twice.
  SELECT employee_id
  INTO   v_thenum
  FROM   employees;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.07          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.07          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33  
********************************************************************************

SELECT EMPLOYEE_ID 
FROM
 EMPLOYEES


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          1          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  INDEX FULL SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=59 us)(object id 12102)

********************************************************************************

ALTER SESSION SET SQL_TRACE = FALSE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 33  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.01       0.07          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.01       0.07          0          0          0           0

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          1          0           1

Misses in library cache during parse: 1

    4  user  SQL statements in session.
    0  internal SQL statements in session.
    4  SQL statements in session.
********************************************************************************
Trace file: xe_ora_2936.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       4  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       4  SQL statements in trace file.
       4  unique SQL statements in trace file.
      56  lines in trace file.
       0  elapsed seconds in trace file.

MHE

Report message to a moderator

Re: Please help me out!! [message #249373 is a reply to message #249362]

Wed, 04 July 2007 03:53

fastfreeeasy
Messages: 25
Registered: June 2007

Junior Member

Very sorry... oracle scans only once. It searches further for next matching rows.

Report message to a moderator

Re: Please help me out!! [message #249387 is a reply to message #249364]

Wed, 04 July 2007 04:24

scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England

Senior Member

fastfreeeasy wrote on Wed, 04 July 2007 09:28

Forgive me for butting in Very Happy

but I thing fastfreeeasy is misunderstanding? In very simplistic terms (very simplistic Embarassed

) think of it this way - Oracle does the select statement (select emp_code from emp where emp_code = 1234; - a single 'scan' as so clearly demonstrated Laughing

) and then it tries to store that in your variable l_emp_code. However there is only one variable, so only room for one value. If the select returns more than one row they can't both fit in the single variable hence the TOO_MANY_ROWS error. Oracle doesn't get one record and then go and see if there are any more, the TOO_MANY_ROWS exception is returned on the SELECT INTO syntax.

Report message to a moderator

Re: Please help me out!! [message #249429 is a reply to message #249387]

Wed, 04 July 2007 08:08

ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India

Senior Member

I appreciate the doubt which fastfreeeasy posted. Because this is very basic thing and this should be cleared.

Ashu

Report message to a moderator

Re: Please help me out!! [message #249470 is a reply to message #249328]

Wed, 04 July 2007 10:56

flyboy
Messages: 1903
Registered: November 2006

Senior Member

ab_trivedi wrote on Wed, 04 July 2007 15:08

I appreciate the doubt which fastfreeeasy posted.

fastfreeeasy wrote on Wed, 04 July 2007 09:44

Believe me I read this fact in Oracle book itself.

I would appreaciate if fastfreeeasy would specify this 'Oracle book' (name, author or at least ISBN).
Maybe this was the behaviour in some historic release (although I do not believe it).

Report message to a moderator

Re: Please help me out!! [message #249638 is a reply to message #249470]

Thu, 05 July 2007 07:30

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

As I remember, back in the days of 7.3.4, it used to be the case that if you did a SELECT INTO, then Oracle would access the table to get the row specified, and then access it again to see if there were more rows, whereas if you opened an explicit cursor and did a fetch, it would only access the table once.

I believe this changed at release 8.0

Report message to a moderator

Re: Please help me out!! [message #249878 is a reply to message #249638]

Fri, 06 July 2007 06:56

fastfreeeasy
Messages: 25
Registered: June 2007

Junior Member

Yes, I agree with your comments.
"In very simplistic terms (very simplistic) think of it this way - Oracle does the select statement (select emp_code from emp where emp_code = 1234; - a single 'scan' as so clearly demonstrated) and then it tries to store that in your variable l_emp_code. However there is only one variable, so only room for one value. If the select returns more than one row they can't both fit in the single variable hence the TOO_MANY_ROWS error. Oracle doesn't get one record and then go and see if there are any more, the TOO_MANY_ROWS exception is returned on the SELECT INTO syntax." As per Scorpio Biker. Is this correct concept?

Report message to a moderator

Re: Please help me out!! [message #249879 is a reply to message #249878]

Fri, 06 July 2007 07:04

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As a concept, you can assume that.
In fact, this is not this, moreover it depends on version.
Basically, on the FIRST fetch Oracle knows if there is another row or not.

Regards
Michel

Report message to a moderator

Re: Please help me out!! [message #250221 is a reply to message #249879]

Mon, 09 July 2007 03:23

fastfreeeasy
Messages: 25
Registered: June 2007

Junior Member

Thanks a lot.

Report message to a moderator

Previous Topic:	Data between two dates in a 12-month period
Next Topic:	What is signature?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 08:40:09 CST 2024