Hello,
Its been a long time since I posted something here...
I am into a deep problem at the moment.
Looking forward for best solutions at progresstalk.
Scenario:
I have a table which contains 22 Million Records in it.
Which contains data that can't be archived or deleted but to be kept in there and unfortunately the table needs to be searched and its a Online service(Hmmm....that makes it a uphill task).
The table structure looks like this.
Tablename: searchtable
Field & DataType:
st1 integer
st2 character
st3 character
st4 character
st5 character
st6 decimal
st7 character
st8 character
st9 character
st10 character
Please note that there is no primary unique index on this table.
st6 stores both date and time in the form of decimal.
Index details
idxindex1 components(all in ascending order)
(st1,st2,st3,st4,st5,st6)
idxindex2 components(all in ascending order)
(st1,st3,st4,st2,st5,st6)
The Index is built keeping in mind the sorted order required
when the search results needs to be displayed and enables
me to write restrictive query.
my search queries looks like this..
Query1:
for each tablename use-index idxindex1
no-lock
where tablename.st1 = <value-from-variable>
and tablename.st2 >= <alphabet-to-start search>
and tablename.st2 < <next-letter just after search alphabet>
and tablename.st3 = '<value-from-variable>':
<statements-purely-display the results no computations>.
end.
Query2:
for each tablename use-index idxindex2
no-lock
where tablename.st1 = <value-from-variable>
and tablename.st3 = '<value-from-variable>'
and tablename.st2 >= <alphabet-to-start search>
and tablename.st2 < <next-letter just after search alphabet>:
<statements-purely-display the results no computations>.
end.
Now, the problem is comes to Bracketing the values by the index.
Query1 works well if there is a large number of records which
satisfies the condition tablename.st3 = '<value-from-variable>'
in the query.However it fails if the number of records which satisfy the above condition are too few.This is where Query2 works well,in other words if there are too few rows which satisfy the above condition
records are returned quickly.
However, to return the results in a 20-30 second span of TIME from a volumnous table like tablename I need to build the above 'Logic' in a program which has the intelligence to first try Query1 if it takes more than 10 seconds to return results, abort Query1 and try Query2 until the timeout.
I am Looking forward for some brlliant ideas at progress talk of accomplishing this.
Many Thanks,
Karthik
Its been a long time since I posted something here...
I am into a deep problem at the moment.
Looking forward for best solutions at progresstalk.
Scenario:
I have a table which contains 22 Million Records in it.
Which contains data that can't be archived or deleted but to be kept in there and unfortunately the table needs to be searched and its a Online service(Hmmm....that makes it a uphill task).
The table structure looks like this.
Tablename: searchtable
Field & DataType:
st1 integer
st2 character
st3 character
st4 character
st5 character
st6 decimal
st7 character
st8 character
st9 character
st10 character
Please note that there is no primary unique index on this table.
st6 stores both date and time in the form of decimal.
Index details
idxindex1 components(all in ascending order)
(st1,st2,st3,st4,st5,st6)
idxindex2 components(all in ascending order)
(st1,st3,st4,st2,st5,st6)
The Index is built keeping in mind the sorted order required
when the search results needs to be displayed and enables
me to write restrictive query.
my search queries looks like this..
Query1:
for each tablename use-index idxindex1
no-lock
where tablename.st1 = <value-from-variable>
and tablename.st2 >= <alphabet-to-start search>
and tablename.st2 < <next-letter just after search alphabet>
and tablename.st3 = '<value-from-variable>':
<statements-purely-display the results no computations>.
end.
Query2:
for each tablename use-index idxindex2
no-lock
where tablename.st1 = <value-from-variable>
and tablename.st3 = '<value-from-variable>'
and tablename.st2 >= <alphabet-to-start search>
and tablename.st2 < <next-letter just after search alphabet>:
<statements-purely-display the results no computations>.
end.
Now, the problem is comes to Bracketing the values by the index.
Query1 works well if there is a large number of records which
satisfies the condition tablename.st3 = '<value-from-variable>'
in the query.However it fails if the number of records which satisfy the above condition are too few.This is where Query2 works well,in other words if there are too few rows which satisfy the above condition
records are returned quickly.
However, to return the results in a 20-30 second span of TIME from a volumnous table like tablename I need to build the above 'Logic' in a program which has the intelligence to first try Query1 if it takes more than 10 seconds to return results, abort Query1 and try Query2 until the timeout.
I am Looking forward for some brlliant ideas at progress talk of accomplishing this.
Many Thanks,
Karthik