How can I optimize sql code beside to indexing?

Question

I wrote an sql code like below. Later, added some indexes according to where condition but the performance is not enough. Is there any general optimization rule for sql or is there any suggestion for below sql? How can I improve my sql code?

    SELECT COUNT(*) OVER() AS tot_count,
    PG.DESCR as partner, 
    THAF.APPDATA 
    FROM   transhist th, 
    trans_app thaf, 
    partnergroup pg, 
    partnergrpkey pgk 

    WHERE TH.CreateDt >= to_date('?startdate', 'yyyymmddhh24mi') 
    AND TH.CreateDt <= to_date('?enddate', 'yyyymmddhh24mi')+1/1440 


    AND TH.TRANSSET = '820' 
    AND THAF.TRANSHISTID = th.id 
    AND THAF.COLUMNID = 74 

    AND THAF.APPDATA LIKE '%|?acctNo|%' 

    AND TH.TRNSLTPARTNERKEY = PGK.DOCHISTPARTNERKEY 
    AND PG.ID = PGK.PARTNERGROUPID 

    AND pg.ID IN (?tpValues) 


    ORDER BY TH.ID;

Indexing like below:

CREATE INDEX TRANS_APP_ALL ON TRANS_APP(TRANSHISTID, COLUMNID, APPDATA);
CREATE INDEX TRANS_CREATEDT ON TRANSHIST(TRANSSET, CREATEDT, TRNSLTPARTNERKEY);

Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! (Unrelated to optimization.) — jarlh, Jan 17 '19 at 11:25
It is difficult to say, as your question is similar to "I have a blue car which is slow. Why?". Performance tuning is not a simple task, it depends of a lot of things. There are *books* written about it, so ... try to start from scratch, selecting from a single table (the largest one). Make it work as fast as you can, (re)viewing explain plan output. Then add another table, check explain plan, tune it if possible. And so forth. Hopefully, you'll succeed. — Littlefoot, Jan 17 '19 at 11:37
*"Is there any general optimization rule for sql?"* If there were those consultants specialising in Oracle performance tuning could not charge the fat fees which they do charge. Please read @marmitebomber's excellent note on asking Oracle performance tuning questions. [Find it here](https://stackoverflow.com/a/34975420/146325). It illustrates how much information is needed before we can make useful suggestions (as opposed to blind guesses). — APC, Jan 17 '19 at 11:49
Incidentally, there is no performance gain from using the ANSi 92 explicit join syntax. Personally I find it clearer but I know several Oracle veterans who keep using the old syntax without any impact on the efficiency of their code. On this site it's main benefit is that if you don't write explicit joins certain people will tell you off for using the old syntax. — APC, Jan 17 '19 at 11:56
Right, @APC - if it *was* about cars, solution would be to paint it red. As it is about computers, solution is to push the TURBO button. — Littlefoot, Jan 17 '19 at 12:06
@littlefoot - Yeah. In the old days of the OTN forum we would joke about setting the undocumented `init.ora` parameter `_go_faster` to true. But we had to stop because too many people thought it was a real parameter ;-) — APC, Jan 17 '19 at 12:12
And it would sometimes work, because it made the problem query a new cursor which then got re-parsed and sometimes by luck the new plan was better than the cached one. — William Robertson, Jan 17 '19 at 20:02

Gordon Linoff · Answer 1 · 2019-01-17T12:53:34.490

First, write the query using proper, explicit, standard join syntax:

select count(*) over () AS tot_count,
       PG.DESCR as partner, 
       THAF.APPDATA 
from transhist th join 
     partnergrpkey pgk 
     on TH.TRNSLTPARTNERKEY = PGK.DOCHISTPARTNERKEY join
     partnergroup pg
     on PG.ID = PGK.PARTNERGROUPID join
     trans_app thaf
     on THAF.TRANSHISTID = th.id
where TH.CreateDt >= to_date('?startdate', 'yyyymmddhh24mi') and
      TH.CreateDt <= to_date('?enddate', 'yyyymmddhh24mi')+1/1440 and
      TH.TRANSSET = '820' and
      THAF.COLUMNID = 74 and
      THAF.APPDATA LIKE '%|?acctNo|%' and
      pg.ID IN (?tpValues) 
order by TH.ID;

For this query, I would expect the following indexes to help:

transhist(TRANSSET, CreateDt, TRNSLTPARTNERKEY, id)
partnergrpkey(DOCHISTPARTNERKEY, PARTNERGROUPID)
partnergroup(id)
trans_app(TRANSHISTID, COLUMNID, APPDATA)

These are similar to the indexes that you have. You might want to check the execution plan to see if the indexes are actually being used.

Gordon, why do you think these indexes will help, given that there is no information regarding cardinalities etc? — BobC, Feb 05 '19 at 05:35

How can I optimize sql code beside to indexing?

1 Answers1