| Compressing the partitions [message #638705] |
Thu, 18 June 2015 17:07  |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
Hi Experts,
I am trying to compress the existing partitions in my table,When I try to generate the command using the following command,it gives me errors:-
ORA-00923: FROM keyword not found where expected
select 'ALTER TABLE 'table_name' MOVE PARTITION 'partition_name' TABLESPACE tablespace_name COMPRESS NOLOGGING PARALLEL;' from dba_tab_partitions where table_name ='tab_name'
Could you please tell me the above syntax is not correct or is it there any efficient method to compress all
the partitions with one command.
Thanks,
Varun
|
|
|
|
|
|
|
|
| Re: Compressing the partitions [message #638769 is a reply to message #638705] |
Sat, 20 June 2015 00:52  |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
Thank John.I generated the query by putting || .
Blackswan,
I have implemented the compression of existing partitions and it has saved 40% space for each partition.
Thanks,
Varun
|
|
|
|
|
|
| Re: Compressing the partitions [message #638859 is a reply to message #638772] |
Tue, 23 June 2015 12:40 |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
Hey Michel,
I have implemented basic compression and then added append hint to the insert statements that involved
the tables I compressed. Performance is enhanced a lot of doing that. I don't see my database taking
the same amount of cpu that it used to take before on server side.
Thanks,
Varun
|
|
|
|
|
|
| Re: Compressing the partitions [message #638880 is a reply to message #638769] |
Wed, 24 June 2015 06:33 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
varunvir wrote on Sat, 20 June 2015 06:52Thank John.I generated the query by putting || .
Blackswan,
I have implemented the compression of existing partitions and it has saved 40% space for each partition.
Thanks,
Varun 40% is not a very good compression ratio. However, theoretically it should improve your SELECT performance significantly. But remember that if you are using indexed access paths, you won't get any performance improvements. Furthmore, and conventional DML will (in my experience) always be degraded, and will also reduce the compression ratio hugely.
|
|
|
|
| Re: Compressing the partitions [message #638881 is a reply to message #638880] |
Wed, 24 June 2015 06:44 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
It'll be inconsistent as initial blocks and data are not compressed. This occurs when the thing fills up.
I'm not a big fan for anything except old archives for regulatory reasons.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
