Re: Wrong number of rows in tkprof?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 10 Aug 2001 19:49:43 +0200
Message-ID: <997465803.25047.0.pluto.d4ee154e@news.demon.nl>

"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message news:Xns90F9445F979EAgnuegischgnueg_at_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

The number you are seeing is the cardinality as computed by the optimizer. How did you analyse those two tables, if at all?

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Fri Aug 10 2001 - 12:49:43 CDT