| partitioning a big table [message #62198] |
Mon, 05 July 2004 03:16  |
mb
Messages: 51
Registered: March 2001
|
Member |
|
|
I have a big table more than 10,000,000 records,I create a table range partition that each partition is for each year.
when I compare the previuos table (without partition) with new table (with partition) ,the previous one is faster,why?
for my table what must i do for improving performance (my table saves some documents in each year)
thanks a lot
|
|
|
|
| Re: partitioning a big table [message #62202 is a reply to message #62198] |
Mon, 05 July 2004 07:19  |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
It depends on your query.
Your partition key must be related to the columns you have in your query predicate.
What is your query like?, how did you partition your table?, what is your table like?....
|
|
|
|
| Re: partitioning a big table [message #62209 is a reply to message #62202] |
Mon, 05 July 2004 20:26 |
mb
Messages: 51
Registered: March 2001
|
Member |
|
|
my table is:
create table tab (
doc_id number(8),
item number(6),
genacct_id number(8),
AUXACCT_ID NUMBER(8),
DETACCT_ID VARCHAR2(9),
AMOUNT NUMBER(18),
ITEM_DESCP NUMBER(3),
ITEM_DATE date)
partition by range (item_date) (
partition yr80 values less than (DATE1),
partition yr81 values less than (DATE2),
partition yr82 values less than (DATE3),
partition yr83 values less than (DATE4))
and where clause of my query has ITEM_DATE
I want to save the data of this table each year in each partition ,is this method , the best one?
|
|
|
|
| Re: partitioning a big table [message #62211 is a reply to message #62209] |
Mon, 05 July 2004 21:17 |
Satheesh Babu.S
Messages: 35
Registered: July 2004
|
Member |
|
|
Partition pruning is not happening, will it be possible to post the explain plan of both the previous and new one. Also where condition used could be of more help.
Thanks and Regards,
Satheesh Babu.S
Bangalore.
|
|
|
|
| Re: partitioning a big table [message #62228 is a reply to message #62209] |
Tue, 06 July 2004 05:20 |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
Yes, it is commonly used to store data yearly within each partition.
But i can't see the exact expression used in the previous example.
I think it would be helpful you provide us with the exact statement when creating the partitioned table and the exact query you use to fetch the table data.
Regards.
|
|
|
|
| Re: partitioning a big table [message #62282 is a reply to message #62228] |
Sun, 11 July 2004 20:39 |
mb
Messages: 51
Registered: March 2001
|
Member |
|
|
my table is :
create table tab (
doc_id number(8),
item number(6),
genacct_id number(8),
AUXACCT_ID NUMBER(8),
DETACCT_ID VARCHAR2(9),
AMOUNT NUMBER(18),
ITEM_DESCP NUMBER(3),
YR char(2))
partition by range (yr) (
partition yr1 values less than ('01'),
partition yr2 values less than ('02'),
partition yr3 values less than ('03'),
partition yr4 values less than ('04'))
that yr is the year and my old table is like this with index on yr without partition.
and my query that I run for both of them is :
select * from tab where yr='02'
the old one is faster than the new.
I have anather question ,too : if I want to put the data more than 2004 (yr>'04') in this table in new partition ,how I must add new partition to this structure?
|
|
|
|
| Re: partitioning a big table [message #62301 is a reply to message #62282] |
Tue, 13 July 2004 09:31 |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
Hello, instead of range partition i would use list partition, why don't you try this:
create table tab (
doc_id number(8),
item number(6),
genacct_id number(8),
AUXACCT_ID NUMBER(8),
DETACCT_ID VARCHAR2(9),
AMOUNT NUMBER(18),
ITEM_DESCP NUMBER(3),
YR char(2))
partition by list (yr) (
partition yr1 values ('01'),
partition yr2 values ('02'),
partition yr3 values ('03'),
partition yr4 values ('04'),
partition unknown values (default))
and i also add a local index on YR column.
All data where YR not in ('01','02','03','04') will fall into UNKNOWN partition.
to add partition you must do
alter table TB add partition NAME values('05')
But i'm not sure this works if you have a DEFAULT PARTITION. Check documentation regarding this matter.
Best luck.
www.otools.com.ar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
