Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / April 2006

Tip: Looking for answers? Try searching our database.

partioned table or partioned index

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Sumanth - 13 Apr 2006 21:35 GMT
Hi,

I have a table that I would like to partition. It has a column c1 which has
100 distinct values.
I was planning to partition the table on column c1 using a partioned index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour as
above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth
Sumanth - 13 Apr 2006 22:44 GMT
read thru some docs and I believe that going with a partioned table space
with a partition by clause on C1 would
work. I then can define a data partioned secondary index that would be valid
on each of the partitions.

What is an index that is created as a partioned cluster?

Thanks,
Sumanth

> Hi,
>
[quoted text clipped - 18 lines]
> Thanks,
> Sumanth
Serge Rielau - 13 Apr 2006 22:49 GMT
> Hi,
>
[quoted text clipped - 12 lines]
> tables I am dealing with
> are really huge?
Sumath, what platform are you on?

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Sumanth - 14 Apr 2006 14:26 GMT
So on AIX what are my options. If I have a table that is big (close to say
200 GB) what are my options in partitioning the table.

Thanks,
Sumanth

>> Hi,
>>
[quoted text clipped - 17 lines]
> Cheers
> Serge
Serge Rielau - 14 Apr 2006 17:14 GMT
> So on AIX what are my options. If I have a table that is big (close to say
> 200 GB) what are my options in partitioning the table.
On DB2 V8.2 for AIX you can use
* nothing special and a 16KB or 32KB pagesize.
* Multi-dimensional clustering (MDC) with at 16KB or 32KB pagesize
The advantage of MDC is that it's one table. You get benefits on rollout
(faster deletes of a "partition") and you get fast scans.
MDC is avlaible without extra cost on licence.
* Hash partition the table across multiple database partitions
The advantage of this is scale out across multiple physical machines (if
you choose so).
It requires the Database partitioning feature (significant extra $$/CPU)
and you need to think about the design of your database beyond just this
one table (fnding partitioning keys, etc...).
I would not recommend DPF in an OLTP system just because a single table
is big. DPF works, and works well in OLTP, but it requires careful
layout of your schema.

* UNION ALL views
The advantage can be fast roll-in/roll-out and partition elimination at
runtime as well as compile-ttime.
DB2 is reasonably good about keeping the optimizer plans in check.
Today DB2 supports in access of 100 branches of UNION ALL. I think (!)
you need to run with optimization level 7  to get all the goodies.

In DB2 Viper (ETA pre-announced for "this summer")
You have two more options:
1. DB2 supports more rows per page and more pages per tablespace.
This means you can have a single table of, uh, 16TB and 1.2 trillion
rows if you so desire.
I.e. a simple 4KB pagesize table will do just fine
2. range partitioning
This is the replacement for UNION ALL views. Thousands of partitions are
supported along with special ATTACH and DETACH DDL statements for fast
roll-in, roll-out.

In DB2 Viper we do not recommend UNION ALL views anymore for partitioning.

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Sumanth - 14 Apr 2006 18:20 GMT
Thanks Serge.
>> So on AIX what are my options. If I have a table that is big (close to
>> say 200 GB) what are my options in partitioning the table.
[quoted text clipped - 36 lines]
> Cheers
> Serge
Ian - 14 Apr 2006 18:32 GMT
> In DB2 Viper we do not recommend UNION ALL views anymore for partitioning.

That's funny - aren't there still people on the mainframe recommending
UNION ALL in certain situations?  (but that may be related to issues
with rebuild of global indexes).

And does this mean that the optimizer's "union all" smarts will be
removed?

Thanks -

Ian
Serge Rielau - 14 Apr 2006 22:23 GMT
>> In DB2 Viper we do not recommend UNION ALL views anymore for
>> partitioning.
>
> That's funny - aren't there still people on the mainframe recommending
> UNION ALL in certain situations?  (but that may be related to issues
> with rebuild of global indexes).
Nothing is ever black and white. You are correct, range partitioned
tables use global indexes. However some of the downsides of global
indexes as they may exist on DB2 for zOS have been overcome (e.g.
asynchronous index cleanup after a roll-out).
Also if you have existing tables which do not have identical signatures
then UNION ALL views will still be valuable.
Lastly I see UNION ALL views as a valuable asset for information
integration (although DB2 today does not support check constraints on
nicknames....)

> And does this mean that the optimizer's "union all" smarts will be
> removed?
Absolutely not! But I doubt we'll invest into further improvements.
E.g. if a customer requested an ALTER VIEW ADD BRANCH (I'm making up
syntax here) there would be a questioning whether they are looking at
the right tool for the job.

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mark A - 14 Apr 2006 00:46 GMT
> Hi,
>
[quoted text clipped - 18 lines]
> Thanks,
> Sumanth

Partitioned tablespaces are only for DB2 for z/OS and OS/390. If you are
using that platform, please state that in your response.
Sumanth - 14 Apr 2006 14:22 GMT
I am on AIX.

-Sumanth

>> Hi,
>>
[quoted text clipped - 21 lines]
> Partitioned tablespaces are only for DB2 for z/OS and OS/390. If you are
> using that platform, please state that in your response.
Sumanth - 14 Apr 2006 14:44 GMT
Can i create separate tables for each value of c1 and use a check constraint
on each of these tables.
And create a view that runs across all these tables (100 of them)
Will the access path of the queries be efficient.?

Any other options?

Thanks,
Sumanth

> Hi,
>
[quoted text clipped - 18 lines]
> Thanks,
> Sumanth
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.