Re: Empty Table

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 25 Jan 2005 02:09:58 +0100
Message-ID: <023d01c5027a$97f727b0$3c02a8c0@JARAWIN>

Hi Jared,

yes, with hint suppressing parallelism everything is OK. The feature deploy only if you leverage both power features PX and partitioning:) See below.

Regards

Jaromir

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> set autotrace on
SQL> select count(*) from TEST where rownum = 1;

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21562 Card=1)    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       PX COORDINATOR
   4    3         PX SEND* (QC (RANDOM)) OF ':TQ10000' (Cost=21562 Car
:Q1000
          d=7639515)

   5    4           PX BLOCK* (ITERATOR) (Cost=21562 Card=7639515)
:Q1000
   6    5             TABLE ACCESS* (FULL) OF 'TEST' (TABLE) (Cost=215
:Q1000
          62 Card=7639515)



   4 PARALLEL_TO_SERIAL

   5 PARALLEL_COMBINED_WITH_CHILD
   6 PARALLEL_COMBINED_WITH_PARENT Statistics

---

         31  recursive calls
          3  db block gets
     100640  consistent gets
     100267  physical reads
        660  redo size
        393  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select /*+ parallel(t,1) */ count(*) from TEST t where rownum = 1;

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=40145 Card=1)    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       PARTITION RANGE (ALL) (Cost=40145 Card=7639515)
   4    3         TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=40145 Ca
          rd=7639515)

Statistics

---

          1  recursive calls
          0  db block gets
          4  consistent gets
          5  physical reads
          0  redo size
        393  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
----- Original Message -----

From: "Jared Still" <jkstill_at_gmail.com>
To: "jaromir nemec" <jaromir_at_db-nemec.com> Cc: <andert_at_gmail.com>; <jacintakean_at_yahoo.com>; <oracle-l_at_freelists.org> Sent: Tuesday, January 25, 2005 12:05 AM Subject: Re: Empty Table

Interesting. I hadn't considered that.

So, did you try your query with a NOPARALLEL hint?

Jared

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jan 24 2005 - 20:12:40 CST