Wrong number of rows in tkprof?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 10 Aug 2001 16:30:57 GMT
Message-ID: <Xns90F9445F979EAgnuegischgnueg@130.133.1.4>

Hi

I decided to get to know tkprof better.
Therefore, I created two tables without any indexes, and filled these with 2 and 6 rows, respectively:

create table category (
  id int,
  description varchar(50)
);

create table thing (
  id int,
  description varchar(50)
);

insert into category values (1, 'fruits'); insert into category values (2, 'beers');

insert into thing values (1, 'apple');
insert into thing values (1, 'banana');
insert into thing values (1, 'apricot');
insert into thing values (1, 'pears');

insert into thing values (2, 'heineken'); insert into thing values (2, 'budwiser');

Then, I altered my session as follows:

alter session set sql_trace=true;
alter session set timed_statistics=true;

Then, the query:

select t.description from thing t, category c where t.id=c.id and c.description='beers';

Then, in order to close the cursor for that statement:

select 1 from dual;

tkprof output:

---

---

select t.description
from
 thing t, category c where t.id=c.id and c.description='beers'

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 2 0.00 0.00 0 2 8 2
------- ------ -------- ---------- ---------- ---------- ---------- -----

---

total 4 0.00 0.00 0 2 8 2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (RENE)

Rows Row Source Operation

-------  ---------------------------------------------------
      2  MERGE JOIN 
      2   SORT JOIN 
      1    TABLE ACCESS FULL CATEGORY 
      2   SORT JOIN 
      6    TABLE ACCESS FULL THING 


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      2   MERGE JOIN
      2    SORT (JOIN)
      1     TABLE ACCESS (FULL) OF 'CATEGORY'
      2    SORT (JOIN)
      6     TABLE ACCESS (FULL) OF 'THING'

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

---

Now, it tells me, that it retrieved 6 rows from thing (which is correct) but only one row from category (which is not, there are two!). Or, does that 1 mean soemthing different?

Another question: what does the 2 mean for the SORT (JOIN) line?

And am I right in assuming that the 2 of MERGE JOIN is how many rows are returned?

For any hint I want to thank in advance.

Rene

-- 
Recherchen im Schweizerischen Handelsamtsblatt:
http://www.adp-gmbh.ch/SwissCompanies/Search.php3

Received on Fri Aug 10 2001 - 11:30:57 CDT