You are right C112 filed is queried with wild cards and hence no use of indexing it. Thanks for the link also.
On Thursday, March 8, 2012 1:30:48 PM UTC-6, Axton wrote: > > Other than the fact that the index won't be used? The queries against > this column will contain leading and trailing wildcards, which an > index won't help with. Take a look at the SQL that is generated when > you query the change form. It is going to take this form: > > SELECT T123.C1,... > FROM T123 > WHERE (T123.C112 LIKE '%;''ABC123'';%') OR (T123.C112 LIKE '%;0;%')... > ORDER BY 1 ASC > > A better approach is to look at the SQL Area on the Oracle dynamic > performance views to identify inefficient queries and address those. > To start down this path, take a look at the disk intensive sql query > on this page. It will help you identify the bad queries that are > floating around in your system. > > http://vsbabu.org/oracle/sect14.html > > A more targeted approach is to look at the sql logs for a particular > action and identify inefficient queries and address those. The C112 > part of the query should be accompanied by additional where clauses > that narrow the range of results. Oracle is smart enough to perform > the parsing on the range of rows (versus the whole table), assuming > you have a better/more efficient part to your query. > > Axton Grams > > On Thu, Mar 8, 2012 at 12:50 PM, patchsk <vamsi...@gmail.com> wrote: > > ** Do you guys see any probable issues by indexing Assignee Group -- C112 > > field, specifically on change tkt form. > > We are running our ITSM in as a single tenant option, so I do not expect > our > > C112 field length to grow more than 256 char. So row level access > should > > not get impacted. > > We have done some analysis on overview console taking longer time, seems > > like some of the resulting queries specifically change tkt related > queries > > are doing table scans and we are hoping by indexing on c112, it would > reduce > > the time to render overview console. > > We are on ITSM 7.6.03 with Windows 2008 and Oracle 11g. > > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"