This forum is currently locked. You can't register or post questions at this time. (read more)

Using multiple partitions in aggregate select statement

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Anand
Member
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Using multiple partitions in aggregate select statement

Postby Anand » Wed Jun 05, 2013 6:38 am

Hi Tim,

Please guide me on my following requirement:-

How can I use multiple partitions in an aggregate select statement from multiple tables?

I have a table with 50 million + rows which I am joining to some temp tables that I created.

Let's call the big table which is range partitioned on column "the_date" as follows:

Code: Select all

partition by range (the_date)
(
 partition big_tbl_P200402 values less than (TO_DATE(' 2004-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace my_space_big_tbl
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
 partition big_tbl_P200403 values less than (TO_DATE(' 2004-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace my_space_big_tbl
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
 partition big_tbl_P200404 values less than (TO_DATE(' 2004-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace my_space_big_tbl
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition big_tbl_P200405 values less than (TO_DATE(' 2004-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace my_space_big_tbl
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
etc, etc to
  partition big_tbl_P200512 values less than (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace my_space_big_tbl
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
)

I want to do the following:-

Code: Select all

select sum(user_cnt)
from big_tbl PARTITION(??????) -- HOW DO I use multiple partitions? Do I need to use multiple partitions?
where the_date between '1-May-2004' and '30-APR-2005'
group_by the_date;


Please help me out.
Thanks in advance.

With Regards,
Anand Kumar Ojha

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Using multiple partitions in aggregate select statement

Postby Tim... » Wed Jun 05, 2013 7:53 am

Hi.

No. You just select from the main table and the partitions relevant for your query will be queried. If you include the partition key as a predicate in your WHERE clause, Oracle can use partition pruning to omit the unused partitions from any scans.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests