Home » RDBMS Server » Performance Tuning » Querying a Partitioned table
Querying a Partitioned table [message #65066] Wed, 14 April 2004 05:36 Go to next message
Raj
Messages: 411
Registered: November 1998
Senior Member
Hi All,

I'm quite new to oracle partitions .I have a dump question. -> When I do select * from a partitioned table, does the query run in parallel mode by default or do I have to specify a hint.

Thanks.

 

 
Re: Querying a Partitioned table [message #65068 is a reply to message #65066] Wed, 14 April 2004 10:32 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
It will not run in parallel unless you have enabled parallel query servers and enabled parallel mode for the table.

SQL> drop table t;

Table dropped.

SQL> create table t partition by list(owner)
  2  (Partition SYS_P Values('SYS'),Partition System_P Values('SYSTEM'),
  3  Partition Thiru_P values('THIRU'),Partition Other_P values(Default))
  4  as select * from all_objects;

Table created.

SQL> analyze table t estimate statistics;

Table analyzed.

SQL> set autotrace on explain
SQL> select count(*) from t;

  COUNT(*)
----------
     30802

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     PARTITION LIST (ALL)
   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=45 Card=32561)

SQL> alter table t parallel;

Table altered.

SQL> select count(*) from t;

  COUNT(*)
----------
     30802

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                        :Q16000
   3    2       PARTITION LIST* (ALL)                                  :Q16000
   4    3         TABLE ACCESS* (FULL) OF 'T' (Cost=12 Card=32561)     :Q16000

   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
                                   ROM (SELECT /*+ NO_EXPAND ROWID(A2) */ 0 FRO
                                   M "T" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)  A
                                   2) A1

   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_COMBINED_WITH_PARENT



-Thiru
Re: Querying a Partitioned table [message #65073 is a reply to message #65068] Thu, 15 April 2004 00:45 Go to previous message
Ajendra
Messages: 165
Registered: February 2004
Senior Member
I am sorry if I am asking something silly. Could you please explain what exactly is the work of this stmt.

alter table t parallel;

Is it only applicable to Partitioned tables or to any table.

If its applicable to any table can we give optimizer hints as parallel and how does it affect the performance of qry.

Can we get more perforamnce
with the parallel option in a qry that uses subqry.

Under what cicrmstances shall we use the parallel qry option.

Thanks
Ajendra
Previous Topic: TEMP Space used by each user - HOWTO?
Next Topic: pctfree and pctused settings
Goto Forum:
  


Current Time: Fri Mar 29 10:48:06 CDT 2024