| Table Partitioned with Primary key [message #469417] |
Wed, 04 August 2010 10:12  |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
Hi All,
I have normal tables with hugh Data and would like to increase the performace by following means:
1) Add a new column in each table. Say this column Name is IS_LIVE. This new column have only two value 1 ( LIVE ) OR 0 ( NOT LIVE ).
2) Change the normal tables to Partitioned table. There would be only two partitioned in all the table. The partitioned key column would be IS_LIVE and both partitioend recrods would be in two different tablespace.
3) Added a POLICY function to these partitioned table to Always add a Query Predicate of '1' to all queuries.
I am interested to know that what kind of Indexes ( Global Or local ) would be suitable for these kind of Design.
2) Is there any use of having Local index on IS_LIVE.
Please note that Primary Key doesnot have this new column in it.
Thanks
Rawat
|
|
|
|
|
|
|
|
|
|
| Re: Table Partitioned with Primary key [message #469427 is a reply to message #469422] |
Wed, 04 August 2010 10:55  |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
The Values of IS_LIVE would be changed only once in a month from the Batch Job.
All the records which would matured would updated to 0 .
The SELECT Claues would never have exclusive Where condition with IS_LIVE.
The Statement would always:
Select * from tbl_a.
It would never be filter by IS_LIVE column.:
Select * from tbl_a where is_live = '0' would never execute.
Rather this would be applied by Oracle by using a POLICY function ( virtual Private Database ) Like.
Select * from ( Seelct * from tbl_a ) where IS_LIVE = '1'.
|
|
|
|
|
|
|
|
| Re: Table Partitioned with Primary key [message #469483 is a reply to message #469434] |
Wed, 04 August 2010 22:21 |
rawat_me
Messages: 45
Registered: September 2005
|
Member |
|
|
@cookiemonster:
There are around 40M records in the table at present.
I am expecting 20M would move to non_LIVE in first run and subsequent run of every month would move around 45K records to Non_LIVE data every month.
Logic is : All transation which are expired in the last month would move to Non_LIVE.
All new and old transaction which are still not expired would still remain as LIVE.
So around one point of time i am expecting 20M LIVE transaction
and 20M non-Live transaction.
@BlackSwan:
I am expecting around 30-40% of improvement as 50% of Data would be non-Live and applying sql:
Select * from ( Select * from tbl_a ) where IS_LIVE = '1'
should filter non_live data. I hope Oracle internally would do that.
[Updated on: Wed, 04 August 2010 22:52] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Table Partitioned with Primary key [message #470073 is a reply to message #469587] |
Sat, 07 August 2010 23:12 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You don't need to index the partition key. There is no point indexing it on its own because Oracle already knows where all the LIVE and NON-LIVE rows are, and there is not point adding it to Local Non-Unique indexes for the same reason.
If you want your Primary key enforced WITHIN EACH PARTITION (rather than across the whole table), you can add the column to your Primary Key index and keep the index LOCAL. If - as you say - all queries are via the VPD, then all queries will implicitly include a predicate on the LIVE/NON-LIVE column. That being the case, I would make the new partition key column the FIRST column of the Primary Key index, and make the Primary Key index PREFIXED and LOCALLY PARTITIONED. Search the doco for why Prefixed is important.
However, if you want to retain uniqueness across the entire table, you will need a Global index on your PK, which should not include the table partition key.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
