Home » RDBMS Server » Performance Tuning » Partition Criteria
Partition Criteria [message #198924] Thu, 19 October 2006 03:33 Go to next message
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

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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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

Re: Partition Criteria [message #201088 is a reply to message #198924] Thu, 02 November 2006 08:24 Go to previous message
Messages: 619
Registered: July 2006
Senior Member
Look at EXPLAIN of all your SQL statements.
Check if these statements are using correct indexes.
Previous Topic: Tuning the query - Outer join
Next Topic: how to change this query, runs forever
Goto Forum:

Current Time: Thu Feb 23 10:21:38 CST 2017

Total time taken to generate the page: 0.11044 seconds