SQL tuning

From: Scott T. Johnson <sarek_at_cais.com>
Date: 1995/07/11
Message-ID: <sarek.24.00263595_at_cais.com>#1/1

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 Tue Jul 11 1995 - 00:00:00 CEST