Re: Execution Order of SQL Query

It depends how your Oracle server is configured. If it's using CBO (Cost based optimizer), it wouldn't matter the sequencing of the tables in the FROM clause. If it's Rule-Based then it would.

If it's rule based, it's advisable to keep the driving table as the one which will return the least number of rows (Note:Not the one which "contains" the least number of rows). So find that table and keep that table as the last one in the From clause and then it becomes the driving table. Also, from your query it looks like that you are using BIF (Built-in-Functions - like Trunc) that will disable the indexes on those columns. So another choice might be to create function based indexes.

• Zoom "SATYA PAL" <spgangwar_at_yahoo.com> wrote in message news:a7b8a58b.0110191105.5a36850a_at_posting.google.com...
  > Hi There,
  >
  > I have a requirement for reports which have complex queries. I am bit
  > confused about the execution order of the queries.
  >
  > Here is an example of the query--
  >
  > SELECT
  > FMS_BG_BAD_ORD.BAD_ORD_SEQ,
  > FMS_BG_EQP.BG_ID,
  > FMS_BG_BAD_ORD.BAD_ORD_DESIG_CD,
  > FMS_BG_BAD_ORD.BAD_ORD_DESC,
  > FMS_BG_BAD_ORD.BAD_ORD_STA_CD,
  > IBIS_GL_ACCT_NBR.GL_ACCT_DESC,
  > FMS_BG_BAD_ORD.CMPL_BY,
  > FMS_BG_BAD_ORD.CMPL_DATE
  > FROM
  > FMS_BG_BAD_ORD,
  > IBIS_GL_ACCT_NBR,
  > RTMS_TRIP,
  > FMS_BG_EQP,
  > RTMS_BG_CURR
  > WHERE
  > FMS_BG_BAD_ORD.BG_SEQ = FMS_BG_EQP.BG_SEQ AND
  > RTMS_BG_CURR.BG_SEQ = FMS_BG_EQP.BG_SEQ AND
  > RTMS_BG_CURR.TRIP_SEQ = RTMS_TRIP.TRIP_SEQ AND
  > FMS_BG_BAD_ORD.GL_ACCT_NBR = IBIS_GL_ACCT_NBR.GL_ACCT_NBR
  > AND(TRUNC(FMS_BG_BAD_ORD.RPT_DATE) >=
  > TRUNC(NVL(BadOrderReportedDate,FMS_BG_BAD_ORD.RPT_DATE))
  > OR
  > TRUNC(FMS_BG_BAD_ORD.CMPL_DATE) >=
  > TRUNC(NVL(BadOrderCompletionDate,FMS_BG_BAD_ORD.CMPL_DATE))
  > OR
  > TRUNC(FMS_BG_BAD_ORD.AUDIT_CRE_DATE) >=
  > TRUNC(NVL(BadOrderCreationDate,FMS_BG_BAD_ORD.AUDIT_CRE_DATE))
  > OR
  > TRUNC(FMS_BG_BAD_ORD.AUDIT_UPD_DATE) >=
  > TRUNC(NVL(BadOrderUpdatedDate,FMS_BG_BAD_ORD.AUDIT_UPD_DATE)))
  > AND
  > INSTR(NVL(BadOrderDesignationCode,FMS_BG_BAD_ORD.BAD_ORD_DESIG_CD),
  > FMS_BG_BAD_ORD.BAD_ORD_DESIG_CD) > 0 AND
  > INSTR(NVL(TripBargeServiceTypeCode,RTMS_TRIP.BG_STYP_CD),
  > RTMS_TRIP.BG_STYP_CD) > 0 AND
  > INSTR(NVL(BargeEquipmentTypeCode,FMS_BG_EQP.BG_EQP_TYPE_CD),
  > FMS_BG_EQP.BG_EQP_TYPE_CD) > 0 AND
  > DECODE(ShowPmBadOrder,'Y',DECODE(BG_PVENT_MAINT_CD,NULL,1,2),2) > 1
  > ORDER BY
  > FMS_BG_EQP.BG_ID;
  >
  > I just wanted to know that what is the execution order of the queries
  > in FROM and WHERE Clause. I mean is it make any difference if I
  > mention any table first or at last in FROM Clause and similarly for
  > WHERE Clause. Your comments would be appreciated and will be helpful
  > for me to write performance oriented queries.
  >
  > Regards
  > Satya