Re: SQL tuning

Assuming you have indexed most of your search columns, - those grouped by AND in separate indexes -
replacing the ORs by UNIONing the whole SELECT for each set of ANDed predicates should help.

sarek_at_cais.com (Scott T. Johnson) wrote:
>
> Can anyone make some tuning suggestions for the following SQL.
> TKProf stats are also included. The second stats are after an analyze
> on the table
>
> Thanks,
> Scott
>
> SELECT ROWID,SLUG,PUBDATE,EDDESK,STORYTYPE,BUDGET,BUDGETTOTAL,PACKAGE,
> ALLDESKS,EDITOR,AUTHOR,COPYEDITOR,COPYDEADLINE,COPYINCHES,ARTINCHES,
> HEADINCHES,STAR,SECTIONFRONT,MUSTRUN,RUNIF,WPHOTO,WGRAPHIC,NOTCOUNTED,
> LEADPAGE,EDCOMMENT,A1COMMENT,PUBSECTIONID,JUMPSECTIONID,CUTBACKSECTIONID,
> UPDATEDBY,TIMESTAMP,PUBID,PACKAGEID,BUDGETID,REFERENCEID,STORYID,BUDGETSEQ,
> STORYSEQ,LAYOUTSEQ,DESCRIPTION
> FROM
> Story WHERE ( (Story.PubID = 500) AND (Story.PubDate >= TO_DATE('5/1/95',
> 'MM/DD/YY') OR (Story.Pubdate = TO_DATE('01/01/90','MM/DD/YY'))) AND
> (Story.EdDesk = 'ME' ) AND ((Story.Package = -1 AND Story.Budget = -1) OR
> (Story.Budget = -1 AND Story.Package=0) OR (Story.Package=0 AND
> Story.Budget=0)) AND (Story.StoryType=0)) ORDER BY Story.BudgetSeq,
> Story.Pubdate, Story.StorySeq
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 3 0.03 0.01 0 0 0 0
> Execute 3 0.05 0.05 0 0 1 0
> Fetch 158 0.74 1.24 252 258 544 158
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 164 0.82 1.30 252 258 545 158
>
> Misses in library cache during parse: 1
> Optimizer hint: CHOOSE
> Parsing user id: 8 (INT)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT HINT: CHOOSE
> 0 SORT (ORDER BY)
> 0 TABLE ACCESS (FULL) OF 'STORY'
>
>
> ************************************************************************************
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 2 0.01 0.01 0 0 0 0
> Execute 1 0.00 0.00 0 0 0 0
> Fetch 3 0.35 0.48 51 129 273 3
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 6 0.36 0.49 51 129 273 3
>
> Misses in library cache during parse: 0
> Optimizer hint: CHOOSE
> Parsing user id: 8 (INT)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT HINT: CHOOSE
> 0 SORT (ORDER BY)
> 0 TABLE ACCESS HINT: ANALYZED (FULL) OF 'STORY'
>
>
Received on Mon Jul 17 1995 - 00:00:00 CEST