If I use global non-partitioned indexes, then I will only have one index to scan either using bol or carton. When using local index, access will have to scan 8 partition indexes and same as access using carton. I am thinking of composite partition as cutting the table in 2 dimensions so there are 8 x 8 cubes. (You don't really mean there is a hash-hash composite syntax ?) May be you can show me. I like the hash+hash approach but cannot figure how it can apply to my situation. Both indexes are heavily used but in most case only bol_no+location in the PK is used to join with another tables of similar keys. The carton is actual a 20 digits carton number and is the other index on the table. Let me explain a bit more on my case: The PK is bol_no(8)+location(4)+carton(20).
So, ignore that it is composite - look at the key itself and ask yourself - would we be able to efficiently answer these questions, or would a global index be called for. If not, if you ask questions "show me the last 55 products we sold" - something by date - then you most likely need a global index on date in order to do that fast. You can use local indexes (we can index partition eliminate) Say you partitioned on BOL_NO,CARTON (carton by the way doesn't sound like a good hash - is it almost unique? does it have TONS of values - a good hash key needs that attribute). Look at what you've partitioned on (the entire set). Your partitioning scheme either allows you to have local indexes - or not. There is no difference between "composite" and "non-composite" as far as index choice is concerned. You have a hash hash - if you wanted 8 ranges with 8 hashes and wanted a "hash hash", just use 64 hashed partitions! So is partioning going to help here or cause more problems? What should be correct approach? Settle_d = date trade was settled which is greater then or equal to trade_dt since you cannot settle a trade which is not traded. But they could go back and adjust this date. enter_d = date it was entered in the system. Useless PK.ĥ) Relationship between this dates. Thus it makes it difficult to figure out what to partiton on.ģ) Data will be loaded daily ( 200-300K) All inserts but accumulate up to 2 years.Ĥ)Data is never accessed by julian date+ seq_num.
Or users will say give me all data where trade_d = sysdate. So users query will say give me data for a given acct where enter_d = sysdate or settle_d = sysdate+1. Users will always access data from this table either by enter_d, trade_d or settle_D along with acct_id. Older data to be dropped based on as_of_date but data is never accessed by user based on this date.Ģ) Performance and Performance. REORGANIZE isn't required in order to move CLOSED delta rowgroups into compressed rowgroups.1) Make data available for 2 years online.For more information, see Reorganize and Rebuild Indexes. This means long-term blocking table locks aren't held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.
The REORGANIZE operation is always performed online.
0 kommentar(er)
