| Partition Criteria [message #198924] |
Thu, 19 October 2006 03:33  |
piyushbhatt
Messages: 1
Registered: October 2006
|
Junior Member |
|
|
I have a table which will have 10 million records on an average.This table always contains last 5 days of data.
The business requirement is such that when I am loading an item(read a record) in a table first I need to lookup in this table to see if the item exists. It may return one or more match If yes, then i set a particuar status to the item. This needs to be done for each item which needs to be loaded.
The performance is painfully slow as of know. I use ADO.NET and c# using a windows service for doing the lookup.
The lookup columns are typically those which do not follow a range of value i.e. the range is unknown and lookup also happens on 4 columns of a table except for one column. This column has a discreet set of values which can be partitioned using list partitioning. But the discreet values are pretty less(around 4).
What is the partition and indexing strategy I should adopt for this scenario. The database is 10.1.0.2.0
Typically I would require around 50000 records to load in 1 hour using the lookup
|
|
|
|
| Re: Partition Criteria [message #198953 is a reply to message #198924] |
Thu, 19 October 2006 05:32  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Before looking into partitioning,
lets first look into the basics.
Are the statistics on this lookup table(and its index) updated every time you get new data?
Else, first do that. Use dbms_stats with cascade=> true.
>>Typically I would require around 50000 records to load in 1 hour using the lookup
PLease rephrase.
Are You having around 50,000 records to probed against this 10 million record lookup table?
Try using GTT (global Temp table).
Load these records into GTT.
Now just do a table join (where gtt.column_name=lookup.columnname).
You can even have stats on GTT.
May be i am completely wrong and talking about something else.
[Updated on: Thu, 19 October 2006 05:33] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
