Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2007
    Posts
    12

    Unanswered: querying partioned table

    hey guys,

    i created a partitioned table where i can name the partitions for each partition as well......

    but when i try to query that table is there any means that i can query that named partition explicitly.

    ex: i created a table like this

    CREATE TABLE orders(id INT, shipdate DATE, …) PARTITION BY RANGE(shipdate) (
    PARTITION p1 STARTING MINVALUE,
    PARTITION p2 STARTING '4/1/2006',
    PARTITION p3 STARTING '10/1/2006'
    ENDING ‘12/31/2006' )

    I know that when i query on the table, db2 itself will take care of which partition to choose, but i want to make that of my choice...

    So how can i query that table on partition p1 or p2 or p3.... ?
    if so how....?
    can u any one suggest me with the query...

    thx in advance
    goutam
    Last edited by gou007; 07-19-07 at 16:25.

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    how about "select c1,c2... from orders where shipdate between '4/1/2006' and '9/20/2006' " to select p2 etc.
    mota

  3. #3
    Join Date
    Jun 2007
    Posts
    12
    hi dbamota,

    u'r sql looks obvious to me...
    but u'r same sql can be run on non partitioned table as well....

    but what i was looking for is...
    can i query on a particular partition of which i specify...?

    like
    select c1,c2 from orders in partition p1.....

    thx,
    goutam

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why would you want to do that?

    The "IN PARTITION ..." as you suggest is not available, fortunately.

    You can have a look at the access plan to verify that DB2 excludes all other partitions. If you don't trust that, create separate base tables and query those directly. If you want to query all those base tables together, you can use a UNION ALL view over the base tables.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2003
    Posts
    237
    In Oracle, you can address the partitions; I don't think you can do that in db2.
    mota

  6. #6
    Join Date
    Jun 2007
    Posts
    12
    hey stolz and dbamota

    thx for u'r suggestions...

    And wel can any one let me know

    1. r there any other different ways that i can query the partitioned table
    2. r there any new clauses that were designed, to specify while querying on a partitioned tables.

    i cant find any material that discusess how to deal with queries for this specialized partition tables...
    if possible can any one provide me with the good links....


    thx guys,
    goutam

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I'm really curious: could you describe what you want to achieve and why you need to have access to the single partitions?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jul 2007
    Posts
    6
    Hey Goutam,

    I think we cant really run a select query on a single partition. the whole significance of the partition's is that it really improves te performance but i guess when you specify a range for a partition, what it does is it sorts the data accordingly in those ranges. For example say u are partitioning on ID which is unique and u have specified the ranges a sfollows.

    par1 --- 1 to 10
    par2 --- 11 to 40
    par3 --- 121 to 201
    par4 --- 41 to 120

    what it does when you do a select * is it sorts 1 to 10 and then 11 to 40 and 121 to 201 and then 41 to 120. So by this you can know the respective partitions and their ranges. But you cant really run a query for a single partition.

  9. #9
    Join Date
    Jul 2007
    Posts
    6
    ound this in a IBM material.

    db2 “select datapartitionnum(l_shipdate) as PartitionId, l_shipdate from lineitem
    where l_shipdate between ’01/06/1992’ and ‘31/07/1992’
    order by l_shipdate”


    source: https://www6.software.ibm.com/develo...dm/dm0612read/

    Hope this helps

  10. #10
    Join Date
    Jun 2007
    Posts
    12
    hey viper,

    thx dude.... this was what i was looking for......

    i guess sure this link wil help me and for others......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •