Home » RDBMS Server » Server Administration » Partitioning Global Index
| Partitioning Global Index [message #154735] |
Tue, 10 January 2006 06:32  |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
Hi
I have to create global partition index on table which contains almost 600 millions rows and its a partitioned table having 33 partitions.
Now, global partition index should also contain partition on basis on columns (first one is number and other one is varchar).
How do i partition it ? I have to mention
PARTITION <partition-name> VALUES LESS THAN (somevalue,somevalue) 33 times in my create index statement. My second column(which is varchar) contains 87 distinct values(with repeatition of each value). how do i specify it here or how can i best distribute it.
If there is any other easy way thru which i can just create global index specifying that it has to create these many partitions
based on this column ?
I hope i am clear what i want to say.
Thanks & Regards
Chandan Singh
|
|
|
|
| Re: Partitioning Global Index [message #154793 is a reply to message #154735] |
Tue, 10 January 2006 11:15  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm not sure I am following, but yes you do have to specify the partitions you want to create when you create a partitioned object. BUT, just because you have 33 table partitions does not mean you have to create 33 index partitions when making a global partition index. Only local partition index need match the table partitions, global partition is independent.
As far as what type of partition and column and values, we don't have any info to go on as far as your data or your partitioning goal.
|
|
|
|
|
|
| Re: Partitioning Global Index [message #154933 is a reply to message #154735] |
Wed, 11 January 2006 07:30 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm sorry, but I'm still not following you, maybe someone else can read the question and understand it better than I? It may be something obvious that I'm just overlooking or not understanding.
You are trying to put the actual counts into partitions? Are you confusing the concepts of indexes and partitions, and perhaps materialized views? Or are you trying to globally partition the index, with list partition type, on the second column? But to what end, if you are trying to count them all? Should the table itself be list partitioned by column 2?
|
|
|
|
| Re: Partitioning Global Index [message #154935 is a reply to message #154735] |
Wed, 11 January 2006 07:37 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Below is what I do get about your situation, leaving out the details of partitions and indexes for the moment. Where do we go from here?
MYDBA >
MYDBA > create table t1
2 (
3 id not null,
4 value not null
5 )
6 as select rownum, mod(rownum,4) from dual connect by level <= 20;
Table created.
MYDBA >
MYDBA > desc t1;
Name Null? Type
----------------------------------------------- -------- --------------------------------
ID NOT NULL NUMBER
VALUE NOT NULL NUMBER
MYDBA >
MYDBA > select count(*) from t1;
COUNT(*)
----------
20
MYDBA >
MYDBA > select count(distinct id) from t1;
COUNT(DISTINCTID)
-----------------
20
MYDBA >
MYDBA > select count(distinct value) from t1;
COUNT(DISTINCTVALUE)
--------------------
4
MYDBA >
MYDBA > select id, count(*) from t1 group by id;
ID COUNT(*)
---------- ----------
1 1
6 1
11 1
13 1
2 1
14 1
20 1
4 1
5 1
8 1
17 1
3 1
7 1
18 1
9 1
10 1
12 1
15 1
16 1
19 1
20 rows selected.
MYDBA >
MYDBA > select value, count(*) from t1 group by value;
VALUE COUNT(*)
---------- ----------
1 5
2 5
3 5
0 5
MYDBA >
MYDBA > drop table t1;
Table dropped.
MYDBA >
MYDBA > spool off;
|
|
|
|
| Re: Partitioning Global Index [message #154948 is a reply to message #154933] |
Wed, 11 January 2006 08:22 |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
I am sorry, i think i am not able to explain it properly.
I have one table t1. This table t1 has 600 millions rows with 33 partitions.
Table :- T1
Columns :- c1,c2,c3,c4,c5,c6,c7,c8,c9,c10
Now i want to create one Global Partition Index on column c1(number) & c2(varchar).
To create this index i will use command
create index idx on t1(c1,c2)
global partition by range(c1,c2)
(partition p1 values less than (10000,'/account'),
partition p2 values less than (20000,'/event/account')
partition p3 values less than (30000,'/event/account/etc')
........)
First column is a no. and we can specify its limit like 10000,20000.........etc etc. so there is no problem with first column.
Till now, i hope i am clear.
Now my question was about second column, This second column c2 contain 87 distinct values (in development env) like ('/account','/event/account','/event/account/etc'.......so on)
So do i need to specify this line in create index statement "partition p3 values less than (30000,'/event/account/etc')
" 87 times upto "partition p87 values less than (100000000,'/event/account/abcd')" ( which i think is not a best way ) for each distinct value of c2 ??????
or
second way was to use query
select c2,count(*) from t1
group by c2;
suppose result is like this.
rownum c1 Count(*)
------ ----------- ------
1 '/event/account' 300
2 '/event/account/abcd' 50000
3 '/account' 144000
...
..
86 '/event/account/xyz' 100000
87 '/event/account/etc' 2000030
Now 87th is the row having maximum value of 2000030 and put this value '/event/account/etc' in 1st partition, second last value i.e 86th row into 2nd partition and so on....
like
partition p1 values less than (10000,'/event/account/etc'),
partition p2 values less than (20000,'/event/account/xyz'),
..
..
partition p28 values less than (20000,'/account'),
partition p29 values less than (30000,'/event/account/abcd'),
partition p30 values less than (maxvalue,maxvalue)
and place values having small no. of rows in last partition like
'/event/account' having 300 in last partition.
I just want to know what's the best way to range partition a varchar column. data will be like this only what i have given in the example above.
If you have got this above, then is it a right approach to range partition like this way on varchar columns ??
I hope i am clear this time.
Thanks a lot for going thru this.
Chandan Singh
|
|
|
|
| Re: Partitioning Global Index [message #154979 is a reply to message #154735] |
Wed, 11 January 2006 09:55 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Ok I am getting clearer, but I (think) that you don't want to do what you are trying to do. But of course if I don't understand then I could be off on that.
BUT, can you answer these questions:
1. What is the purpose/goal of your index? What types of questions are you attempting to answer and use this index to speed up? Why do you want a single index on both columns as opposed to multiple indexes each on single columns?
2. What is your purpose/goal of partitioning the index in the first place? Why do you want to partition it by these columns in their combination, as opposed to partitioning it by one or the other columns alone?
3. You did a query to get the counts of the number of times each of the 87 distinct values in c2 were present in the table: "select c2,count(*) from t1 group by c2;". But then you used those count values as the 'values less than' in a range partition. You took 2000030 to be a maximum value, but it was just a count. This doesn't make sense to me because a count is not a data value, it is just a count of the number of times something happened or existed. A 'values less than' is supposed to use data values, actual values or data points that exist in the table, to create ranges which will be used to place rows in a table (or index entries in an index partition).
Also keep in mind that with tables you can do list partitions, which is where you have one partition for each distinct value of a column. But you can't do list partitions with global partitioned indexes. You can do hash partitions, which is just an arbitrary way to break the index into smaller chunks for possible management and administrative benefits, or range partitions. Ranges make sense for numeric data, but not so much for character data, at least in your case, because you aren't looking for a range of letters, you are looking for specific character string values. With character data, 'values less than 'bbbb'' might mean 'aaaa'. But that isn't what you want, you have specific values, not an arbitrary set of letters.
SO, you may want to create the index on both columns, but then do a range partition of the index on only the first column, where you perhaps define some broad ranges to categorize your first column into. So say for example your first column is a number that is unique for all 600 million rows, you might define broad ranges of 'values less than 100 million', 'values less than 200 million', etc. Your character strings would still be in the index, and would go in whatever partition was dictated by column1, but you would just not partition on the character strings themselves.
OR, you could do a list table partition by your 87 distinct charcter strings, so that all '/account/etc' entries would go in a single table partition. Then if you asked a question looking for only rows with a certain character string, oracle would know that the answer is contained within a single table partition. You could then further add indexes, local ones, on c1 or on other columns. But this all gets back to your purpose.
|
|
|
|
| Re: Partitioning Global Index [message #155075 is a reply to message #154979] |
Thu, 12 January 2006 02:24 |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
First, thanks a lot for giving your valueable time you have spend on this. your last reply has helped a lot.
Actually i have this select statement to tune.
select distinct poid_DB, poid_ID0, poid_TYPE, poid_REV, created_t, descr, sys_descr, service_obj_DB, service_obj_ID0, service_obj_TYPE, service_obj_REV, session_obj_DB, session_obj_ID0, session_obj_TYPE, session_obj_REV
from event_t where event_t.account_obj_ID0 = :1 and ( event_t.poid_TYPE = :2 or event_t.poid_TYPE = :3 )
where in our examples column C1 is ACCOUNT_OBJ_ID0 and column c2 is POID_TYPE.
Now when i execute the above query using values
ACCOUNT_OBJ_ID0 = '1'
POID_TYPE = '/event/session' or POID_TYPE = '/account' then it takes up 273.163 secs but when i remove the distinct clause it takes 1.112 secs. I told them(developer) to remove the distinct clause(as it can be removed because poid_id0 is unique in select clause so having distinct doesnot make any sense) but they said query cannot be changed, we have to tune it using indexes or using hints then Sr. DBA suggested to create global partition index(composite) on these two columns. That's how we have to add this index. Now thing is that we have individual partition index on poid_type but not on account_obj_id0. They also created one local composite index on 3 columns(account_obj_id0,poid_type,end_t) but that too i think didn't work.
Now they are saying we have to create this Global Partition Index on these two columns.
I will create index on both columns and specify range on first column only and then will see the performance of query.
I want to know one thing how the values of column poid_type will get distributed among different partitions as we are not specifying its range in partition values. Is there any way i can know what values have been assigned to which partition ???
Thanks again for your timely help.
|
|
|
|
| Re: Partitioning Global Index [message #155185 is a reply to message #155075] |
Thu, 12 January 2006 14:31 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
| chandanbhamra wrote on Thu, 12 January 2006 03:24 |
I told them(developer) to remove the distinct clause(as it can be removed because poid_id0 is unique in select clause so having distinct doesnot make any sense) but they said query cannot be changed, we have to tune it using indexes or using hints
|
You are right, best way would be to remove distinct if it is not needed. But if you can't for political or other reasons, then can you indicate it is unique to the CBO (and I'm assuming you have current statistics on table and indexes, as specified in sticky of this forum) ?
Is poid_id0 a primary key with a PK constraint? Or if not can it be made into a unique constraint or unique composite index of some kind? Something to tell the CBO that it doesn't need to make the results distinct afterwards because they already are.
| Quote: |
Now they are saying we have to create this Global Partition Index on these two columns. I will create index on both columns and specify range on first column only and then will see the performance of query.
|
Post back with the results when you have them.
| Quote: |
I want to know one thing how the values of column poid_type will get distributed among different partitions as we are not specifying its range in partition values. Is there any way i can know what values have been assigned to which partition ???
|
They would be spread throughout all index partitions in more or less a random fashion. Spread out based on their value of the C1 field only. So one row may have an '/account/etc' which would go into partition 1, and another row may also have an '/account/etc', but it would go into partition 2. They would go into different partitions based on the C1 value.
One other thing that may help is that if this is read only data, or if this is non-transactional in the sense that you do periodic loads of data as in a warehouse or DSS but not OLTP style updates, then try your indexes as bitmaps instad of btrees.
Also if your big (and unique) criteria is C1 (id0), then you could table partition by that field, and not put it in an index at all. Then you would create local bitmap indexes on the C2 (the type field). When you did a query for an id0 of say a value of 1, oracle would know it only has to scan one table partition, and likewise only one index partition of the type index.
|
|
|
|
Goto Forum:
Current Time: Thu Feb 13 17:18:13 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
