Home » SQL & PL/SQL » SQL & PL/SQL » Please help me out!!
Please help me out!! [message #249328] Wed, 04 July 2007 01:57 Go to next message
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?
Re: Please help me out!! [message #249333 is a reply to message #249328] Wed, 04 July 2007 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Question

Regards
Michel
Re: Please help me out!! [message #249339 is a reply to message #249333] Wed, 04 July 2007 02:19 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Please help me out!! [message #249345 is a reply to message #249333] Wed, 04 July 2007 02:44 Go to previous messageGo to next message
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.
Re: Please help me out!! [message #249348 is a reply to message #249328] Wed, 04 July 2007 02:51 Go to previous messageGo to next message
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!
Re: Please help me out!! [message #249355 is a reply to message #249345] Wed, 04 July 2007 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Post your execution
2/ Post the link or reference where you see that.

Regards
Michel
Re: Please help me out!! [message #249356 is a reply to message #249345] Wed, 04 July 2007 03:11 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Please help me out!! [message #249362 is a reply to message #249356] Wed, 04 July 2007 03:24 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Please help me out!! [message #249364 is a reply to message #249355] Wed, 04 July 2007 03:28 Go to previous messageGo to next message
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?
Re: Please help me out!! [message #249371 is a reply to message #249364] Wed, 04 July 2007 03:39 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Please help me out!! [message #249373 is a reply to message #249362] Wed, 04 July 2007 03:53 Go to previous messageGo to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
Very sorry... oracle scans only once. It searches further for next matching rows.
Re: Please help me out!! [message #249387 is a reply to message #249364] Wed, 04 July 2007 04:24 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
fastfreeeasy wrote on Wed, 04 July 2007 09:28
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?



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.

Re: Please help me out!! [message #249429 is a reply to message #249387] Wed, 04 July 2007 08:08 Go to previous messageGo to next message
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
Re: Please help me out!! [message #249470 is a reply to message #249328] Wed, 04 July 2007 10:56 Go to previous messageGo to next message
flyboy
Messages: 1832
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).
Re: Please help me out!! [message #249638 is a reply to message #249470] Thu, 05 July 2007 07:30 Go to previous messageGo to next message
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
Re: Please help me out!! [message #249878 is a reply to message #249638] Fri, 06 July 2007 06:56 Go to previous messageGo to next message
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?
Re: Please help me out!! [message #249879 is a reply to message #249878] Fri, 06 July 2007 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Please help me out!! [message #250221 is a reply to message #249879] Mon, 09 July 2007 03:23 Go to previous message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
Thanks a lot.
Previous Topic: Armstrong numbers
Next Topic: What is signature?
Goto Forum:
  


Current Time: Fri Dec 09 02:14:50 CST 2016

Total time taken to generate the page: 0.10718 seconds