Search Response Time Improvement
I support an application that has a SQL search in Oracle 10g. It is searching an activity table that stores all kinds of different types of activities and has grown to about 5 million records. I have done the basics in the last couple of years to keep it at a reasonable response time level but it is not doing well. I am not a DBA adn I am not a SQL expert. I use it sporadically developing packages that do certain functions for the application.
I do not know of better use of Oracle 10g for speeding up the database and I would like to get some advice as to what I should look at to improve the response time. I can look at different things online but I thought that I should ask the question here to avoid spending too much time reiventing the wheel. I am sure someone in the group will have done this type of development in the past and could give me some good hints.
Thank you very much
I do not know of better use of Oracle 10g for speeding up the database and I would like to get some advice as to what I should look at to improve the response time. I can look at different things online but I thought that I should ask the question here to avoid spending too much time reiventing the wheel. I am sure someone in the group will have done this type of development in the past and could give me some good hints.
Thank you very much
Although types of queries has already been asked, I'm after more targeted requirements (I think).
How you define "SQL search".
Are you doing a much of LIKE queries against several columns at once?
Oracle Text allows you to 'index' tokens (basically words) in many different ways to allow for very flexible searching of 'text' in your data.
How you define "SQL search".
Are you doing a much of LIKE queries against several columns at once?
Oracle Text allows you to 'index' tokens (basically words) in many different ways to allow for very flexible searching of 'text' in your data.
I'm looking for actual sql statements, which would include "=" vs "like" vs "in" vs "exists" vs "contains" etc conditions as well as the coresponding negations
ASKER
Thank you for the replies. I can post the sql if necessary but I was hoping more for possible solutions in terms of design changes if that is what needs to be done. I have a search that allows for selection of number of IDS, type of call, date range, search of notes fields for a specif word and the same for names. The search is taking too long and this is a call center where the response time is in many wyas crucial. We are looking at the network as well but changing the search is definitely something I would like to do. I have heard of virtual tables that probably work a little like the views but I do not know how that is applied in reality. I am starting an investigation on possible solutions and this is one of my resources.
ASKER
here is the sql before it hits the database
sql-example.png
sql-example.png
We need something to go on to be able to provide anything resembling help.
Specifically what search piece(s) are 'slow'?
Is it the search by date or notes or id's or ???
Is it a certain combination?
My guess is it is the notes and name field searches.
My other guess is you are using LIKE.
Other things we need to know is how the app behaves.
If a search hits on 1 million rows, does the app expect all 1 million rows to come back?
If it does paging, does it do it on the app server? If so, try moving paging to the database server. This is what we did and performance was greatly improved.
Specifically what search piece(s) are 'slow'?
Is it the search by date or notes or id's or ???
Is it a certain combination?
My guess is it is the notes and name field searches.
My other guess is you are using LIKE.
Other things we need to know is how the app behaves.
If a search hits on 1 million rows, does the app expect all 1 million rows to come back?
If it does paging, does it do it on the app server? If so, try moving paging to the database server. This is what we did and performance was greatly improved.
>>here is the sql before it hits the database
Please post as text.
Preferably in a code block.
Please post as text.
Preferably in a code block.
This is a code block. It is created by clicking 'Code' in the text window and placing the text between the delimiters.
I see multiple things in your query that tend to produce slow performance:
1. an "outer join" operator (+)
2. using operators like: upper, substr, nvl, etc. on database column values - they will prevent the use of indexes on those columns (unless you have "function-based" indexes that exactly match this syntax).
3. "like '%%%" - That will match to anything/everything, so that doesn't help Oracle use an index to improve efficiency.
1. an "outer join" operator (+)
2. using operators like: upper, substr, nvl, etc. on database column values - they will prevent the use of indexes on those columns (unless you have "function-based" indexes that exactly match this syntax).
3. "like '%%%" - That will match to anything/everything, so that doesn't help Oracle use an index to improve efficiency.
ASKER
Ok these options produce slower performance but how do you replace them? That is the kind of help I am trying to get.
I cannot take them out without consequences. On the percent for example, in many cases the users are not sure if the name is Smith or smythe and so they will go Sm% and other examples that you for sure know. I use the upper so that every occurrence is found and not just the ones that match exactly. I can take them out but obviously the results are not going to be as in depth.
I cannot take them out without consequences. On the percent for example, in many cases the users are not sure if the name is Smith or smythe and so they will go Sm% and other examples that you for sure know. I use the upper so that every occurrence is found and not just the ones that match exactly. I can take them out but obviously the results are not going to be as in depth.
You are still being vague. Why?
So, based on your last post, you are only concerned with the text column searches?
If you want to tune the text search piece, I would look at using Oracle Text. I can post a working example but need more information about your exact requirements.
Will the be searching for sm% on one column, several columns, etc...
We need more information about the specifics of what you are trying to do.
What you have given us so far: I have a car. It needs to go faster.
Well, I can strip off the body, remove most of the frame, resize the engine, only provide one seat and two wheels: Basically convert it into a motorcycle. It will go twice as fast and get twice the gas mileage.
But you might have left off: It must seat 4 people and one baby and meet crash standards.
Then my suggestions would have been worthless.
Sample data and expected results would help a great deal.
For example, Oracle Text has the ability to do synonym searches so if you tell it that Bill and William are the same, then it will return 'William Smith' when you search for 'Bill Smith'.
'make it faster' can't work if we don't have more specifics.
Without them, I can make it twice as fast: Quadruple you existing hardware.
So, based on your last post, you are only concerned with the text column searches?
If you want to tune the text search piece, I would look at using Oracle Text. I can post a working example but need more information about your exact requirements.
Will the be searching for sm% on one column, several columns, etc...
We need more information about the specifics of what you are trying to do.
What you have given us so far: I have a car. It needs to go faster.
Well, I can strip off the body, remove most of the frame, resize the engine, only provide one seat and two wheels: Basically convert it into a motorcycle. It will go twice as fast and get twice the gas mileage.
But you might have left off: It must seat 4 people and one baby and meet crash standards.
Then my suggestions would have been worthless.
Sample data and expected results would help a great deal.
For example, Oracle Text has the ability to do synonym searches so if you tell it that Bill and William are the same, then it will return 'William Smith' when you search for 'Bill Smith'.
'make it faster' can't work if we don't have more specifics.
Without them, I can make it twice as fast: Quadruple you existing hardware.
ASKER
I apologize if I you think I am being vague but that is not the intention, I would not have sent you the sql if I was trying to be vague. Let me read again your message and see how I can help to not sound vague.
What I'm personally looking for is something I can use to create a working test case.
Example table with sample data and some example queries with expected results would be perfect.
Some information about the app that "have a search that allows for selection of number of IDS, type of call, date range, search of notes fields for a specif word and the same for names" and how it works would be icing on the cake.
Sorry to do this but your exact Oracle version (all 4 numbers) would help as well. This way we don't provide an 11.2.0.3 only example if you are on 10.2.0.3.
Example table with sample data and some example queries with expected results would be perfect.
Some information about the app that "have a search that allows for selection of number of IDS, type of call, date range, search of notes fields for a specif word and the same for names" and how it works would be icing on the cake.
Sorry to do this but your exact Oracle version (all 4 numbers) would help as well. This way we don't provide an 11.2.0.3 only example if you are on 10.2.0.3.
>> I can post the sql if necessary
>>I have a search that allows for selection of number of IDS, type of call, date range, search of notes fields for a specif word and the same for names.
That is where we need to start.
Yes, please post the SQL, and please post the table definition and its indexes too.
You state that there is one "activity table", perhaps it should be more than one table?
(we don't know this yet, don't panic)
I'd like to see some sample data from this table (some old, some recent)
anything considered private like names can be "scrubbed"
Excel might be convenient for this.
this of course can be the same sample data slightwv has asked for
>>I was hoping more for possible solutions in terms of design changes if that is what needs to be done.
we are not at that point yet, until we know more we can't make reasoned suggestions
>>I have a search that allows for selection of number of IDS, type of call, date range, search of notes fields for a specif word and the same for names.
That is where we need to start.
Yes, please post the SQL, and please post the table definition and its indexes too.
You state that there is one "activity table", perhaps it should be more than one table?
(we don't know this yet, don't panic)
I'd like to see some sample data from this table (some old, some recent)
anything considered private like names can be "scrubbed"
Excel might be convenient for this.
this of course can be the same sample data slightwv has asked for
>>I was hoping more for possible solutions in terms of design changes if that is what needs to be done.
we are not at that point yet, until we know more we can't make reasoned suggestions
ASKER
I am sorry I ddi not reply to all the messages but I got a bit overwhelmed with the comments and requests. I made a couple of changes myself and it improved a bit but not what I expected. I am not sure I want to continue detailing the case. It is not easy to provide all that is requested although I realize that one needs it for possible investigation. Maybe this is just too complex to be done this way. I am going to think about my next step.
thank you
thank you
Every problem needs to be accurately stated before a solution can be provided.
If you aren't willing to provide the details, then we cannot provide anything resembling a specific solution.
Increase performance generalities:
Make sure database statistics are up to date.
Make sure you have acceptable indexes (not too many, not too few).
Will parallelism help?
If you are licensed for it, will partitioning help?
And as I have posted earlier: Throw hardware at it.
Again, I'm not asking for production tables and data. Just something that simulates it that I can use to build a test case.
If you aren't willing to provide the details, then we cannot provide anything resembling a specific solution.
Increase performance generalities:
Make sure database statistics are up to date.
Make sure you have acceptable indexes (not too many, not too few).
Will parallelism help?
If you are licensed for it, will partitioning help?
And as I have posted earlier: Throw hardware at it.
Again, I'm not asking for production tables and data. Just something that simulates it that I can use to build a test case.
I'm not going to attempt typing out your query code in full, please just paste it here.
some observations about that image:
If you provided the sql query as text I could have edited that and posted it back here for you to try.
i.e. Please don't provide queries as images.
{+ 2 edits as I noticed more things in the image that would have been obvious as formatted text}
some observations about that image:
user_id IN ( big long list of values here ) -- potential performance problem
and activity_date >= ... and activity_date <= ...+1 -- should be less than, not less than or equal
and upper(type_of_call_code) = 'A' -- use this instead: type_of_call_code in ('A','a')
and upper(nvl(notes,0)) like '%%%' --use this: notes IS NOT NULL
and upper(nvl(person_complaine
and substr(type_of_call_code,1
If you provided the sql query as text I could have edited that and posted it back here for you to try.
i.e. Please don't provide queries as images.
{+ 2 edits as I noticed more things in the image that would have been obvious as formatted text}
ASKER
Thank you PortletPaul for the suggestions
This code is prepared by a Powerbuilder datawindow and it looks like this before hitting the database. This is just one of the examples
I believe this is the way you wanted it pasted here correct? I hope so.
This code is prepared by a Powerbuilder datawindow and it looks like this before hitting the database. This is just one of the examples
I believe this is the way you wanted it pasted here correct? I hope so.
SELECT "C_AFFAIRS"."C_ACTIVITY"."ACTIVITY_SEQ",
"C_AFFAIRS"."C_ACTIVITY"."TYPE_OF_CALL_SEQ",
"C_AFFAIRS"."C_ACTIVITY"."ACTIVITY_TYPE_SEQ",
"C_AFFAIRS"."C_ACTIVITY"."COMPLAINT_SEQ",
"C_AFFAIRS"."C_ACTIVITY"."IN_OUT_CALL",
"C_AFFAIRS"."C_ACTIVITY"."ACTIVITY_DATE",
"C_AFFAIRS"."C_ACTIVITY"."DURATION_MINUTES",
"C_AFFAIRS"."C_ACTIVITY"."NOTES",
"C_AFFAIRS"."C_ACTIVITY"."NAME",
"C_AFFAIRS"."C_ACTIVITY"."ZIP",
"C_AFFAIRS"."C_ACTIVITY"."USER_ID",
"C_AFFAIRS"."C_ACTIVITY"."AGENCY_SEQ",
"C_AFFAIRS"."C_ACTIVITY"."USER_ID_ENT_BY",
"C_AFFAIRS"."C_ACTIVITY"."DT_ENT",
"C_AFFAIRS"."C_ACTIVITY"."USER_ID_CHG_BY",
"C_AFFAIRS"."C_ACTIVITY"."DT_CHG",
"C_AFFAIRS"."C_ACTIVITY"."WP_DOC_SEQ",
"C_AFFAIRS"."C_ACTIVITY"."BUSINESS_SEQ",
"C_AFFAIRS"."C_ACTIVITY"."LICENSE_SEQ",
"C_AFFAIRS"."C_ACTIVITY"."MESSAGE_FOR_ID",
"C_AFFAIRS"."C_ACTIVITY"."MESSAGE_DONE",
"C_AFFAIRS"."C_ACTIVITY"."MESSAGE_DONE_DATE",
"C_AFFAIRS"."C_ACTIVITY"."MESSAGE_NO_NOTIFY",
TO_DATE('14-Jul-90 00:00:00', 'DD-MON-YY HH24:MI:SS') from_date,
TO_DATE('14-Jul-90 00:00:00', 'DD-MON-YY HH24:MI:SS') TO_DATE,
'0' search_field,
'0' search_notes,
"C_AFFAIRS"."C_ACTIVITY"."MSG_FOR_USER_ID",
NVL(c_affairs."C_ACTIVITY"."USER_ID", c_affairs."C_ACTIVITY"."MESSAGE_FOR_ID") m_for_id,
"C_AFFAIRS"."C_TYPE_OF_CALL"."TYPE_OF_CALL_CODE",
"C_AFFAIRS"."C_ACTIVITY"."PERSON_COMPLAINED_ABOUT"
FROM "C_AFFAIRS"."C_ACTIVITY", "C_AFFAIRS"."C_TYPE_OF_CALL"
WHERE (c_affairs.c_activity.type_of_call_seq = c_affairs.c_type_of_call.type_of_call_seq(+))
AND c_affairs."C_ACTIVITY"."USER_ID" IN ('ABRAMLA',
'ABROLPA',
'ABUBASA',
'ALBERKA',
'ALLORFR',
'AMBROLI',
'ARESHMI',
'ARMSTRO',
'BANMAAN',
'BASKEDA',
'BEEKMVA',
'BERGEDE',
'BEWLEJA',
'BOMAKBO',
'BOMAKBOA',
'BOWDILY',
'BOYCHDE',
'BREAKBR',
'BROADJA',
'BROUGCH',
'BUDZARA',
'BURNSJO',
'CARRUKA',
'CASTILO',
'CHANEMA',
'CHANTLE',
'CHEUNAL',
'CHOIKA',
'CHOYDAN',
'CHUBBAN',
'COFFIDE',
'CUFFJU',
'CURRICH',
'CYREPA',
'DARDAKE',
'DASHKMY',
'DAVISME',
'DAWSORE',
'DAYDEBO',
'DECHAAN',
'DECKEDA',
'DEGAGRE',
'DIAKOKA',
'DORAMAN',
'DOXU',
'EDWARSU',
'ELDRIAL',
'ELROSHA',
'ENSKASU',
'ESAUSC',
'FEIREIR',
'FISCHJA',
'FISHEDA',
'FLANAKE',
'FLEMILI',
'FLEMILIA',
'FREEBGA',
'FRIGAAN',
'FURNAMI',
'GAWDELI',
'GAWDEMI',
'GEHRIDA',
'GELINAM',
'GOUDIAM',
'GRAYPE',
'GREENAN',
'HALKOSH',
'HANSOHE',
'HAUGHBE',
'HENDELY',
'HILLMA',
'HIST003',
'HIST04',
'HIST05',
'HIST06',
'HIST07',
'HIST1998',
'HIST1999',
'HIST2000',
'HIST2001',
'HIST2002',
'HODGIKI',
'HOEKSGA',
'HOODSA',
'HOODSC',
'HORIATI',
'HORNULO',
'HRYNIBR',
'HUNKALI',
'HUSSERE',
'JAMIEMI',
'JENKIBE',
'JENSEWE',
'JOHNSTH',
'JONESAN',
'KESSITE',
'KETCHBR',
'KETLETH',
'KLEINJO',
'KOESTER',
'KUCHIAA',
'KUHTEDI',
'KUNGLSU',
'KUTYNSH',
'LAINGCH',
'LANIGNA',
'LAUJO',
'LEBLARE',
'LEEAM',
'LEESOIS',
'LEHMALU',
'LOWRETR',
'LUNDLYN',
'LUNTYBA',
'LUSNELI',
'MACLECA',
'MAHABER',
'MAHERBR',
'MALZAES',
'MARTIKE',
'MARTIPA',
'MASONAL',
'MCARTEL',
'MCCOMME',
'MCCUNBR',
'MCEWIPA',
'MCFADJE',
'MCFAZJE',
'MCINTJO',
'MCKENKI',
'MCLEAAL',
'MERCERI',
'MICHETE',
'MILBUDA',
'MILETFR',
'MILLEBR',
'MILLEDO',
'MOOREMA',
'MORRIJA',
'MORRIJO',
'MUNROIA',
'MURPHER',
'NAHAYDO',
'NEPUNDO',
'NOBLEWE',
'NORWIHO',
'OKOYE',
'OKOYENN',
'OSCOFKA',
'PARKECH',
'PARTIMI',
'PASKAM',
'PAWLULO',
'PAYNEER',
'PECKHGA',
'PEEBLJE',
'PENNIMA',
'PERRATI',
'PERRISH',
'PHILLRO',
'PICKEVI',
'PIPERGR',
'PITTSLA',
'PLUMBKA',
'POCHKR',
'POTTETI',
'PURPULI',
'RAMAGBI',
'REEDEJE',
'REFAHKE',
'RICHALI',
'ROBERDA',
'ROBINCH',
'ROUSSGA',
'ROYMART',
'RYANJA',
'SAVASA',
'SCHERBO',
'SCHULTR',
'SEVILMA',
'SHAULKE',
'SHAWCY',
'SHEPPJE',
'SHERWKA',
'SIDHUNA',
'SIUME',
'SKRENCY',
'SLUSAGE',
'SMITHTY',
'SOKOLAL',
'SOLKORI',
'SOMMECH',
'SORKEPA',
'STEINNO',
'STETSKA',
'STEVELY',
'STREEDA',
'SUMNEKE',
'SWEENLA',
'SZELEEV',
'TAYLOGE',
'THERIMA',
'THOMADA',
'TIMMEMA',
'TULLYMA',
'TWERDJE',
'UPPALJA',
'VINCEKE',
'VINEYCA',
'WAGENMI',
'WALDBCA',
'WASNESA',
'WILCOSH',
'WILLIPE',
'WIWCHBR',
'WONGCH',
'WOODPAU',
'WOYTOAD',
'WOYWIDE',
'WRIGHLA',
'WRIGHLI',
'YATESGE',
'ZACHETO',
'',
'',
'HOOD',
'')
AND c_affairs."C_ACTIVITY"."ACTIVITY_DATE" >=
TO_DATE('13-11-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
AND c_affairs."C_ACTIVITY"."ACTIVITY_DATE" <=
TO_DATE('13-11-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') + 1
AND UPPER(NVL(c_affairs."C_ACTIVITY"."NOTES", 0)) LIKE '%HOOD%'
AND UPPER(c_affairs."C_ACTIVITY"."NAME") LIKE '%%%'
AND UPPER(NVL(c_affairs."C_ACTIVITY"."PERSON_COMPLAINED_ABOUT", 0)) LIKE '%%%'
AND SUBSTR(c_affairs."C_TYPE_OF_CALL"."TYPE_OF_CALL_CODE", 1, 1) <> 'Z'
AND NVL(c_affairs.c_activity.activity_type_seq, 0) <> 52
Can you connect to the database using sqlplus and post the execution plan?
explain plan for
SELECT "C_AFFAIRS"."C_ACTIVITY"."
...
then:
SELECT * FROM TABLE(dbms_xplan.display);
explain plan for
SELECT "C_AFFAIRS"."C_ACTIVITY"."
...
then:
SELECT * FROM TABLE(dbms_xplan.display);
Pasting the code is great, thank you.
There is one where condition in particular that interests me (line 268 above):
AND SUBSTR(c_affairs."C_TYPE_O
It is the only condition on table C_TYPE_OF_CALL
and because of this condition there is no purpose to the left join
i.e. if that field is NULL not only it will that be ignored, but C_ACTIVITY records with no match to C_TYPE_OF_CALL will also be ignored. It causes the effect of an inner join.
Q: Do you want to ignore C_ACTIVITY records with no match to C_TYPE_OF_CALL?
There is one where condition in particular that interests me (line 268 above):
AND SUBSTR(c_affairs."C_TYPE_O
It is the only condition on table C_TYPE_OF_CALL
and because of this condition there is no purpose to the left join
i.e. if that field is NULL not only it will that be ignored, but C_ACTIVITY records with no match to C_TYPE_OF_CALL will also be ignored. It causes the effect of an inner join.
Q: Do you want to ignore C_ACTIVITY records with no match to C_TYPE_OF_CALL?
If comparing the original query image to the pasted image there is quite a difference between the "IN LIST" of user_id's. It also seems that you add to this list for non-user_id reasons.
e.g. in the image we see "SMITH" added in 2 places and "HOOD" now in 2 places (but not the same)
Q: Could you explain how you know what user_id's go into these lists?
Q: Why is "SMITH", or "HOOD", added in 2 places?
the image below highlights just some of the differences (not all of them)
user-id-IN-LIST-CHANGES.png
e.g. in the image we see "SMITH" added in 2 places and "HOOD" now in 2 places (but not the same)
Q: Could you explain how you know what user_id's go into these lists?
Q: Why is "SMITH", or "HOOD", added in 2 places?
the image below highlights just some of the differences (not all of them)
user-id-IN-LIST-CHANGES.png
ASKER
First I will answer the questions on your second message:
I am aware of a problem when the user list is being put together and I am working on this. The problems you point out will not be happening. The list is put together by a loop that goes through a table that is displayed as a drop down box I am attaching a pic of the first screen where users select the criteria just so you can understand better what I am talking about. So I am taking care of this issue.
The second question is the same problem.
screen-1.png
I am aware of a problem when the user list is being put together and I am working on this. The problems you point out will not be happening. The list is put together by a loop that goes through a table that is displayed as a drop down box I am attaching a pic of the first screen where users select the criteria just so you can understand better what I am talking about. So I am taking care of this issue.
The second question is the same problem.
screen-1.png
ASKER
As far as your first message question - I believe the intention was to eliminate the calls that start with a z from the results.
Yes unfortunately this table stores activities for licenses, for complaints and also for a call centre. The system is 18 years old and with time it is obvious that these tables should have been separate. So the c_type_of_call brings in only the ones related to the call center. Mow the z ones were added from another call center that is also using this application but do not relate to the people that are using this search. I hope this makes sense.
Yes unfortunately this table stores activities for licenses, for complaints and also for a call centre. The system is 18 years old and with time it is obvious that these tables should have been separate. So the c_type_of_call brings in only the ones related to the call center. Mow the z ones were added from another call center that is also using this application but do not relate to the people that are using this search. I hope this makes sense.
Please post the execution plan.
ASKER
Hi Slight
I run the explain to the example I pasted and did the select after and I got the file I am attaching. I am not sure this is exactly what you were looking for.
Let me know - thank you
plan.txt
I run the explain to the example I pasted and did the select after and I got the file I am attaching. I am not sure this is exactly what you were looking for.
Let me know - thank you
plan.txt
Thanks. That is exactly what I was looking for.
Give us some time to go over it.
Give us some time to go over it.
The limit on: c_type_of_call does not help this query return results any faster. Yes, I understand that it is needed to filter out only the desired results. But, unfortunately, the desired records for this query are stored in a table that also has other values for: c_type_of_call. Oracle does *NOT* retrieve individual records from a table. Oracle must retrieve entire blocks of data (usually 2k, 4k or 8k at a time) then evaluate the individual records in that block to see if they meet the criteria in the "where" clause or not. If not, they are discarded, If yes, they are accumulated to be returned to the user/application.
And when the actual value you are filtering on is:
SUBSTR(c_affairs."C_TYPE_O
there is a double performance penalty:
1. The "not equals" comparison (<>) always prevent an index from being used
2. The "substr" operator on a database column value also prevents an index on this column from being used.
Maybe an index on this column wouldn't help much anyway, since it is likely that almost every block of records in the table contains at least one record that matches this criteria, so this "where" clause line by itself doesn't help Oracle avoid reading every record in the table.
The big list of names in the "IN" list may (or may not) allow Oracle to use an index on this column, depending on what percentage of the total records in the table this list represents. If this list respresents 10% or more of all names in the table, then an index on this column likely won't be used either, since there would be more overhead in reading all of the needed index blocks, then fetching the corresponding (but scattered) data blocks, than simply reading all of the data blocks of the table in order.
The filter on dates may help performance, but again maybe not very much, since the range of dates in this example:
AND c_affairs."C_ACTIVITY"."AC
TO_DATE('13-11-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
AND c_affairs."C_ACTIVITY"."AC
TO_DATE('13-11-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') + 1
is rather wide: a two-year time span. With 18 years of history in the table, a two-year time span is aound 10% of the total, assuming a somewhat equal distribution per year. If later years have more, then the problem is worse, not better from a performance perspective, Since the rule of thumb is: if more than 10% of the records in a table need to be fetched, don't bother with an index, just read the entire table.
Some things that could help:
1. Data compression and partitioning. If you are licenced for Oracle Enterprise server, you could consider recreating this table as a partitioned table with a separate partition for each year (or possbily by quarter or month), using the ACTIVITY_DATE column. Then, assuming that all historical records do not get modified, you could compress all of the histrorical partitions. This can save 25-50% (or more) of the space, and the result is an increase in query performance almost equal to the space savings.
2. Physically re-ordering the records based on their c_type_of_call value, and indexing that value, and changing your query to avoid both the "substr" and the "not equals" like this:
c_affairs."C_TYPE_OF_CALL"
And when the actual value you are filtering on is:
SUBSTR(c_affairs."C_TYPE_O
there is a double performance penalty:
1. The "not equals" comparison (<>) always prevent an index from being used
2. The "substr" operator on a database column value also prevents an index on this column from being used.
Maybe an index on this column wouldn't help much anyway, since it is likely that almost every block of records in the table contains at least one record that matches this criteria, so this "where" clause line by itself doesn't help Oracle avoid reading every record in the table.
The big list of names in the "IN" list may (or may not) allow Oracle to use an index on this column, depending on what percentage of the total records in the table this list represents. If this list respresents 10% or more of all names in the table, then an index on this column likely won't be used either, since there would be more overhead in reading all of the needed index blocks, then fetching the corresponding (but scattered) data blocks, than simply reading all of the data blocks of the table in order.
The filter on dates may help performance, but again maybe not very much, since the range of dates in this example:
AND c_affairs."C_ACTIVITY"."AC
TO_DATE('13-11-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
AND c_affairs."C_ACTIVITY"."AC
TO_DATE('13-11-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') + 1
is rather wide: a two-year time span. With 18 years of history in the table, a two-year time span is aound 10% of the total, assuming a somewhat equal distribution per year. If later years have more, then the problem is worse, not better from a performance perspective, Since the rule of thumb is: if more than 10% of the records in a table need to be fetched, don't bother with an index, just read the entire table.
Some things that could help:
1. Data compression and partitioning. If you are licenced for Oracle Enterprise server, you could consider recreating this table as a partitioned table with a separate partition for each year (or possbily by quarter or month), using the ACTIVITY_DATE column. Then, assuming that all historical records do not get modified, you could compress all of the histrorical partitions. This can save 25-50% (or more) of the space, and the result is an increase in query performance almost equal to the space savings.
2. Physically re-ordering the records based on their c_type_of_call value, and indexing that value, and changing your query to avoid both the "substr" and the "not equals" like this:
c_affairs."C_TYPE_OF_CALL"
>> If you are licenced for Oracle Enterprise server, you could consider recreating this table as a partitioned table
Partitioning is an additional option. It is not included in the Enterprise Edition license.
Partitioning is an additional option. It is not included in the Enterprise Edition license.
Ah yes, that is licensed separately, but having the Enterprise Edition is a pre-requisit. In my opinion, the extra cost of partioning more than pays for itself.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
None of the solutions presented to me made any difference
what does your table look like and what are the actions (queries/sql) against it?
In a more general sense:
Do you have indexes on the columns you use in your where clauses?
Do you have up to date statistics?
Are you queries trying to return all 5 million rows?
What are your current response times?
What is considered acceptable response time? ("fast as possible" isn't a useful answer)