Re: SQL resource usage and monitoring

Another approach that can help is to use *shared sort sequence. Here's a quote from the SQL Programming manual -

When selection is made using that sort sequence and that index, the character or UCS-2 graphic keys do not need to be converted prior to comparison. This improves the performance of the query.

The index uses the sort sequence in effect when the index is created. The main thing - the need for the UPPER function goes away.

On 3/17/2015 7:13 AM, rob@xxxxxxxxx wrote:

It's often said that if you can modify your comparison variable to match
the format of the column within the table it will perform better.
For example
where tabledate > datetonum(:InputDate)
will perform better than
where numtodate(tabledate) > :InputDate
because a key may be on tabledate and it can use that key.

However the OP may have a tougher time with UPPER because if the table
stores it in all sorts of cases and they want it based on some input,
irregardless of the case as to how it's stored.
I ran these statements but I've not run them through any advisor to see if
they helped.

CREATE TABLE ROB.MycharTbl (MYCHAR CHAR (5 ) NOT NULL WITH DEFAULT)

create index rob.mycharidx
on rob.mychartbl (
upper(mychar) as mycharup )
rcdfmt mycharr add all columns


Rob Berendt