Home » RDBMS Server » Performance Tuning » table Partition when values are unknown (11g)
table Partition when values are unknown [message #674139] Mon, 07 January 2019 03:39 Go to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
Dear forum members,

need a advise for the following scenario.

i have a table where data is stored with date field (start_date) and store_id, i need to partition the table in a way that start date is interval at day and storeid is added as subpartition. please note that i wanted to purge the data storeid wise for specific day only.

i tried the following but multiple store ids are getting in same subpartition which i dont want. Also note that store id is not fixed and can grow dynamically.
not sure if this is right approach , kindly suggest.

create table
pos_data (
start_date DATE not null,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY HASH(store_id)

(
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('31-12-2018', 'DD-MM-YYYY'))
);

thanks
Re: table Partition when values are unknown [message #674140 is a reply to message #674139] Mon, 07 January 2019 03:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have not specified how many hash partitions, so you'll get only one (which is a bit pointless). You need to provide a list subpartitioning template, with a default partition for all values that are not yet known.

(by the way, I wish you would not say "field" when you mean "column").
Re: table Partition when values are unknown [message #674141 is a reply to message #674140] Mon, 07 January 2019 04:07 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
thanks john for your reply, as numbers of storeid is not defined so i dont know how many subpartitions i need to create. though i tried with 32 subpartitions but that also store the multiple storeid in same subpartition. secondly the template also not created as the there is no specific range for store id as i need to store each storeid data separately in subpartitions.
Re: table Partition when values are unknown [message #674142 is a reply to message #674141] Mon, 07 January 2019 04:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So it would seem that this partitioning strategy (or possibly any partitioning strategy) is not a solution for whatever problem it is that you are addressing.

I hope you are not making the mistake of implementing partitioning because you like the idea, rather than implementing it as a solution to a problem. You need to define the problem and then prove mathematically that your chosen partitioning strategy will fix it. Partitioning is not an end in itself.
Re: table Partition when values are unknown [message #674144 is a reply to message #674142] Mon, 07 January 2019 05:14 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
my strategy might not be correct, thats why i need some expert opinion.
problem is , i wanted to purge the data based on StoreID on daily basis but store ID is not something which is predefined. not sure how to handle it.

[Updated on: Mon, 07 January 2019 05:14]

Report message to a moderator

Re: table Partition when values are unknown [message #674145 is a reply to message #674144] Mon, 07 January 2019 05:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
How much data are you expecting per store per day?
Re: table Partition when values are unknown [message #674146 is a reply to message #674144] Mon, 07 January 2019 05:25 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
i wanted to purge the data based on StoreID on daily basis
This is not a problem: it is a requirement. Is it causing some sort of problem?
Re: table Partition when values are unknown [message #674147 is a reply to message #674145] Mon, 07 January 2019 05:27 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
data is around 10GB per day per Store, these are logs which we need to purge on daily basis. otherwise database size increase every month.
Re: table Partition when values are unknown [message #674148 is a reply to message #674146] Mon, 07 January 2019 05:29 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
yes database size increasing due to this and we need to purge daily/weekly basis. however for some stores we need to keep the data for audit purpose.
Re: table Partition when values are unknown [message #674149 is a reply to message #674148] Mon, 07 January 2019 05:31 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So you are inserting and removing c. 10GB per store per day? How many stores? And how many days do you want to keep online?

[Updated on: Mon, 07 January 2019 05:31]

Report message to a moderator

Re: table Partition when values are unknown [message #674150 is a reply to message #674149] Mon, 07 January 2019 05:43 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
John Watson wrote on Mon, 07 January 2019 15:31
So you are inserting and removing c. 10GB per store per day? How many stores? And how many days do you want to keep online?

there are around 70 stores/agents and yes 10gb per stores/agent per day. we have a plan to keep one month of data and then purge the data beyond one month. but as i said earlier for some stores we might need data beyond 1 month too. for that we dont want to purge until audit is complete.
Re: table Partition when values are unknown [message #674151 is a reply to message #674150] Mon, 07 January 2019 05:54 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
one more thing that stores may increase in future, so dont want to limit the partitions.
Re: table Partition when values are unknown [message #674152 is a reply to message #674151] Mon, 07 January 2019 06:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Are you sure of your numbers? Your rows are only twenty or thirty bytes, so 10GB is about 350,000,000 rows per store per day. Seventy stores and a month of data means your table will be c. 20TB.

You will be inserting and deleting c. 3% of the table per day. That is probably too much to do efficiently with either an index driven delete or a scan driven delete over the whole table, so I can see that partitioning might be sensible, but why composite partitioning? Just daily range partitions might be fine without any need for further pruning. Or if not, use (perhaps) 128 hash subpartitions, that will prune the partition access for deletes right down. Or if for some reason scanning your partitions is still too slow, sure, use a list subpartition template and then you can drop entire partitions for individual stores per day. Seventy list partitions isn't very many, and I don't suppose you are building new stores that frequently. So whenever you do, let the rows for the new store go into the default subpartition and then split it.

Or wait a while! Automatic list partitioning was introduced in release 12, it won't be long before we have automatic list subpartitioning.
Re: table Partition when values are unknown [message #674153 is a reply to message #674152] Mon, 07 January 2019 06:22 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
John Watson wrote on Mon, 07 January 2019 16:02
Are you sure of your numbers? Your rows are only twenty or thirty bytes, so 10GB is about 350,000,000 rows per store per day. Seventy stores and a month of data means your table will be c. 20TB.

You will be inserting and deleting c. 3% of the table per day. That is probably too much to do efficiently with either an index driven delete or a scan driven delete over the whole table, so I can see that partitioning might be sensible, but why composite partitioning? Just daily range partitions might be fine without any need for further pruning. Or if not, use (perhaps) 128 hash subpartitions, that will prune the partition access for deletes right down. Or if for some reason scanning your partitions is still too slow, sure, use a list subpartition template and then you can drop entire partitions for individual stores per day. Seventy list partitions isn't very many, and I don't suppose you are building new stores that frequently. So whenever you do, let the rows for the new store go into the default subpartition and then split it.

Or wait a while! Automatic list partitioning was introduced in release 12, it won't be long before we have automatic list subpartitioning.

Pos_data is a table where i wanted to try this strategy first, actual table is where we have a clob also and data is mostly in 100kb per row.
for your 2nd question "why not only on daily range" , that is because daily range means we need to purge for all stores which we cant do due to audit purpose.
new store is getting added every other month thats why wanted to keep it dynamic.

as per the your suggestion, it seems i have to maintain the list of existing stores, and then whenever new stores comes we need to create new subpartition for that.

thanks you so much for your value able suggestions and time.
have a great day.

Re: table Partition when values are unknown [message #674155 is a reply to message #674153] Mon, 07 January 2019 06:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Why can't you delete based on start_date and store_id? That will work whether you partition or not. I suggested partitioning strategies which would make the delete much more efficient, if that is necessary.

I am beginning to suspect that when you say "purge" you are under the impression that this is an operation that can be accomplished only with a partition DDL. That is a perfect example of what I said before: partitioning is (or may be) a solution to a problem, and the need to remove rows is not a problem. It is something you need to do, and there are many ways of doing it. I have found over many years that partitioning often causes more problems than it solves, and you must be phenomenally careful with how you do it. In your case, you may have appalling problems with indexes. For example, your local indexes will be distributed across 200 or more partitions which will make them horrifically inefficient and your global indexes will need rebuilding after every partition drop.
Re: table Partition when values are unknown [message #674164 is a reply to message #674155] Tue, 08 January 2019 01:58 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
John Watson wrote on Mon, 07 January 2019 16:43
Why can't you delete based on start_date and store_id? That will work whether you partition or not. I suggested partitioning strategies which would make the delete much more efficient, if that is necessary.

I am beginning to suspect that when you say "purge" you are under the impression that this is an operation that can be accomplished only with a partition DDL. That is a perfect example of what I said before: partitioning is (or may be) a solution to a problem, and the need to remove rows is not a problem. It is something you need to do, and there are many ways of doing it. I have found over many years that partitioning often causes more problems than it solves, and you must be phenomenally careful with how you do it. In your case, you may have appalling problems with indexes. For example, your local indexes will be distributed across 200 or more partitions which will make them horrifically inefficient and your global indexes will need rebuilding after every partition drop.

if we delete we wont be able to recover the storage, isnt it? as i understand truncating partition is the way to reclaim and utilize the storage more efficiently?
Re: table Partition when values are unknown [message #674165 is a reply to message #674164] Tue, 08 January 2019 02:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why do you need to reclaim the storage?
When you delete the space is left allocated to the table and then new rows that are inserted go into the space that was occupied by the deleted rows.
If you're getting roughly the same amount of data per day then reclaiming the storage is pointless - the table is going to need it all by the end of the day and if it's deallocated then oracle is just going to have to reallocate it.

Reclaiming storage is only useful if the table held way more data than normal.
Re: table Partition when values are unknown [message #674166 is a reply to message #674164] Tue, 08 January 2019 04:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
if we delete we wont be able to recover the storage, isnt it? as i understand truncating partition is the way to reclaim and utilize the storage more efficiently?
Ah. Yes. But.... This is a problem caused by the partitioning. It would not be a problem if the table were not partitioned by date. Do you see what I mean when I say that you need to be careful?

What is the actual problem? Is it, for example, that removing a day of rows takes more than a day? Or that you are getting errors related to undo? Or that you are having performance problems caused by IO or CPU starvation?
Re: table Partition when values are unknown [message #674167 is a reply to message #674165] Tue, 08 January 2019 06:54 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
cookiemonster wrote on Tue, 08 January 2019 12:56
Why do you need to reclaim the storage?
When you delete the space is left allocated to the table and then new rows that are inserted go into the space that was occupied by the deleted rows.
If you're getting roughly the same amount of data per day then reclaiming the storage is pointless - the table is going to need it all by the end of the day and if it's deallocated then oracle is just going to have to reallocate it.

Reclaiming storage is only useful if the table held way more data than normal.
Since we are deleting huge data so truncate is more faster than delete, Delete will takes lot of time and wont set the hwm hence storage will grow more faster as i understand. isnt it?
Re: table Partition when values are unknown [message #674168 is a reply to message #674166] Tue, 08 January 2019 07:00 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
John Watson wrote on Tue, 08 January 2019 14:16
Quote:
if we delete we wont be able to recover the storage, isnt it? as i understand truncating partition is the way to reclaim and utilize the storage more efficiently?
Ah. Yes. But.... This is a problem caused by the partitioning. It would not be a problem if the table were not partitioned by date. Do you see what I mean when I say that you need to be careful?

What is the actual problem? Is it, for example, that removing a day of rows takes more than a day? Or that you are getting errors related to undo? Or that you are having performance problems caused by IO or CPU starvation?

honestly i have not tried delete due to the reason that it would take alot of time to delete 50-100gb of data daily. and it will put extra load on server and insert will also slow down which we dont want.
Re: table Partition when values are unknown [message #674169 is a reply to message #674168] Tue, 08 January 2019 07:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
To reiterate a point from earlier - are you sure about your numbers?

Either your table is a lot wider than your example (and I mean a lot wider - maybe with a clob or something) or you're assuming your going to get amazon levels of business. And if you're amazon like then your inventory_id better be a darn sight bigger than number(6).

Where did the 10G figure come from?
And how many rows does it translate to?
Re: table Partition when values are unknown [message #674176 is a reply to message #674169] Tue, 08 January 2019 22:47 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
cookiemonster wrote on Tue, 08 January 2019 17:50
To reiterate a point from earlier - are you sure about your numbers?

Either your table is a lot wider than your example (and I mean a lot wider - maybe with a clob or something) or you're assuming your going to get amazon levels of business. And if you're amazon like then your inventory_id better be a darn sight bigger than number(6).

Where did the 10G figure come from?
And how many rows does it translate to?
i already clarified that this table is for testing purpose, actual table is alot bigger than this and have a clob, which we are using to store the payload.
Re: table Partition when values are unknown [message #674177 is a reply to message #674167] Wed, 09 January 2019 02:52 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
honestly i have not tried delete due to the reason that it would take alot of time to delete 50-100gb of data daily. and it will put extra load on server and insert will also slow down which we dont want.
Ah. So you have no idea if you actually have a problem or not. Better try a DELETE, with parallel DML and make sure your LOBs are securefile LOBs, before investigating any possible need for partitions. Also decide on the requirement (how long can it take? One hour? 12 hours? Does it matter at all?)
Re: table Partition when values are unknown [message #674179 is a reply to message #674176] Wed, 09 January 2019 03:36 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
kashifchughtai wrote on Wed, 09 January 2019 04:47
cookiemonster wrote on Tue, 08 January 2019 17:50
To reiterate a point from earlier - are you sure about your numbers?

Either your table is a lot wider than your example (and I mean a lot wider - maybe with a clob or something) or you're assuming your going to get amazon levels of business. And if you're amazon like then your inventory_id better be a darn sight bigger than number(6).

Where did the 10G figure come from?
And how many rows does it translate to?
i already clarified that this table is for testing purpose, actual table is alot bigger than this and have a clob, which we are using to store the payload.
If you want to test performance improvements your test table is going to need a clob too.
Previous Topic: how to avoid window sort?
Next Topic: driving table and join order
Goto Forum:
  


Current Time: Thu Mar 28 09:32:55 CDT 2024