This discussion is archived
0 Replies Latest reply: Jun 12, 2012 2:21 AM by 943067 RSS

Order By Performance improved by using NOT LIKE clause - No logical reason

943067 Newbie
Currently Being Moderated
1. Order by is supposed to be potentially slow

2. In my case of an sort query performs by adding a dummy where clause. The dummy where clause is NOT LIKE some dummy value and this NOT LIKE column is any column from the base table, it does not matter which column, the query performs.

3. This has no logical meaning and i fail to understand the logic behind the query performing faster as compared to both the below cases :

a. Having a hint for the query. (takes around 36 seconds to come up with the result set)
b. Plain order by query i.e. no hint, no dummy where clause (takes around a minute and a half to come up with the result set)

Original Query (takes1 and 1/2 minute)

select * from ( select invtxn0_.IV_RECTYPE as col_0_0_, invtxn0_.IV_STR4 as col_1_0_, csthdr2_.CS_REF1 as col_2_0_, csthdr2_.CS_NAME as col_3_0_, stshdr1_.ST_NAME
as col_4_0_, invtxn0_.IV_STR9 as col_5_0_, invtxn0_.IV_STR8 as col_6_0_, invtxn0_.IV_TERMS as col_7_0_, invtxn0_.IV_SHREF as col_8_0_,
invtxn0_.IV_RESP as col_9_0_, invtxn0_.IV_GPINVNO as col_10_0_, invtxn0_.IV_STR11 as col_11_0_, invtxn0_.IV_STR6 as col_12_0_,
invtxn0_.IV_SLSORDNO as col_13_0_, invtxn0_.IV_STR5 as col_14_0_, invtxn0_.IV_ORDNO as col_15_0_, invtxn0_.IV_BLDATEFRO as col_16_0_,
invtxn0_.IV_BLDATETO as col_17_0_, invtxn0_.IV_TLAMT as col_18_0_, accrtxn4_.AR_OUTAMT as col_19_0_, invtxn0_.IV_DATE as col_20_0_,
invtxn0_.IV_DUEDATE as col_21_0_, trunc(current_date-invtxn0_.IV_DUEDATE) as col_22_0_, invtxn0_.IV_MODDT as col_24_0_, invtxn0_.IV_STR12 as
col_25_0_, invtxn0_.IV_STR13 as col_26_0_, invtxn0_.IV_STR7 as col_27_0_,
invtxn0_.IV_ID as col_29_0_, invtxn0_.CS_ID as col_30_0_,
invtxn0_.IV_STID as col_31_0_, stshdr1_.ST_ICON as col_32_0_ from INV_TXN invtxn0_, STS_HDR stshdr1_, CST_HDR csthdr2_,
ACCR_TXN accrtxn4_
where invtxn0_.IV_STID=stshdr1_.ST_ID(+)
and invtxn0_.CS_ID=csthdr2_.CS_ID and invtxn0_.IV_ID=accrtxn4_.IV_ID
and invtxn0_.IV_DATE=accrtxn4_.IV_DATE and (invtxn0_.EN_ID in (4060)) and invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 order by csthdr2_.csName ASC,
invtxn0_.IV_ID ASC,invtxn0_.IV_DATE ASC, invtxn0_.IV_GPINVNO ASC ) where rownum <= ?

Query with index/hint (takes around 36 seconds)

select * from (select /*+ INDEX(invtxns2_ INV_TXN_PK_IDX) */ invtxn0_.IV_RECTYPE as col_0_0_, invtxn0_.IV_STR4 as col_1_0_, csthdr2_.CS_REF1 as col_2_0_, csthdr2_.CS_NAME as col_3_0_, stshdr1_.ST_NAME
as col_4_0_, invtxn0_.IV_STR9 as col_5_0_, invtxn0_.IV_STR8 as col_6_0_, invtxn0_.IV_TERMS as col_7_0_, invtxn0_.IV_SHREF as col_8_0_,
invtxn0_.IV_RESP as col_9_0_, invtxn0_.IV_GPINVNO as col_10_0_, invtxn0_.IV_STR11 as col_11_0_, invtxn0_.IV_STR6 as col_12_0_,
invtxn0_.IV_SLSORDNO as col_13_0_, invtxn0_.IV_STR5 as col_14_0_, invtxn0_.IV_ORDNO as col_15_0_, invtxn0_.IV_BLDATEFRO as col_16_0_,
invtxn0_.IV_BLDATETO as col_17_0_, invtxn0_.IV_TLAMT as col_18_0_, accrtxn4_.AR_OUTAMT as col_19_0_, invtxn0_.IV_DATE as col_20_0_,
invtxn0_.IV_DUEDATE as col_21_0_, trunc(current_date-invtxn0_.IV_DUEDATE) as col_22_0_, as col_23_0_, invtxn0_.IV_MODDT as col_24_0_, invtxn0_.IV_STR12 as
col_25_0_, invtxn0_.IV_STR13 as col_26_0_, invtxn0_.IV_STR7 as col_27_0_,
as col_28_0_, invtxn0_.IV_ID as col_29_0_, invtxn0_.CS_ID as col_30_0_,
invtxn0_.IV_STID as col_31_0_, stshdr1_.ST_ICON as col_32_0_ from INV_TXN invtxn0_, STS_HDR stshdr1_, CST_HDR csthdr2_,
ACCR_TXN accrtxn4_
where invtxn0_.IV_STID=stshdr1_.ST_ID(+)
and invtxn0_.CS_ID=csthdr2_.CS_ID and invtxn0_.IV_ID=accrtxn4_.IV_ID
and invtxn0_.IV_DATE=accrtxn4_.IV_DATE and (invtxn0_.EN_ID in (4060)) and invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 order by csthdr2_.csName ASC,
invtxn0_.IV_ID ASC,invtxn0_.IV_DATE ASC, invtxn0_.IV_GPINVNO ASC ) where rownum <= ?

Query with Dummy where Clause (takes around 4 to 5 seconds)

select * from ( select invtxn0_.IV_RECTYPE as col_0_0_, invtxn0_.IV_STR4 as col_1_0_, csthdr2_.CS_REF1 as col_2_0_, csthdr2_.CS_NAME as col_3_0_, stshdr1_.ST_NAME
as col_4_0_, invtxn0_.IV_STR9 as col_5_0_, invtxn0_.IV_STR8 as col_6_0_, invtxn0_.IV_TERMS as col_7_0_, invtxn0_.IV_SHREF as col_8_0_,
invtxn0_.IV_RESP as col_9_0_, invtxn0_.IV_GPINVNO as col_10_0_, invtxn0_.IV_STR11 as col_11_0_, invtxn0_.IV_STR6 as col_12_0_,
invtxn0_.IV_SLSORDNO as col_13_0_, invtxn0_.IV_STR5 as col_14_0_, invtxn0_.IV_ORDNO as col_15_0_, invtxn0_.IV_BLDATEFRO as col_16_0_,
invtxn0_.IV_BLDATETO as col_17_0_, invtxn0_.IV_TLAMT as col_18_0_, accrtxn4_.AR_OUTAMT as col_19_0_, invtxn0_.IV_DATE as col_20_0_,
invtxn0_.IV_DUEDATE as col_21_0_, trunc(current_date-invtxn0_.IV_DUEDATE) as col_22_0_, invtxn0_.IV_MODDT as col_24_0_, invtxn0_.IV_STR12 as
col_25_0_, invtxn0_.IV_STR13 as col_26_0_, invtxn0_.IV_STR7 as col_27_0_,
invtxn0_.IV_ID as col_29_0_, invtxn0_.CS_ID as col_30_0_,
invtxn0_.IV_STID as col_31_0_, stshdr1_.ST_ICON as col_32_0_ from INV_TXN invtxn0_, STS_HDR stshdr1_, CST_HDR csthdr2_,
ACCR_TXN accrtxn4_
where invtxn0_.IV_STID=stshdr1_.ST_ID(+)
and invtxn0_.CS_ID=csthdr2_.CS_ID and invtxn0_.IV_ID=accrtxn4_.IV_ID
and invtxn0_.IV_DATE=accrtxn4_.IV_DATE and (invtxn0_.EN_ID in (4060)) and invtxn0_.IV_TYPE='Invoice' and invtxn0_.IV_CLOSED=0 AND (invtxn0_.IV_RECTYPE NOT LIKE '-1%')
AND (invtxn0_.IV_RECTYPE NOT LIKE '-2%')
AND (invtxn0_.IV_RECTYPE NOT LIKE '-3%') order by csthdr2_.csName ASC,
invtxn0_.IV_ID ASC,invtxn0_.IV_DATE ASC, invtxn0_.IV_GPINVNO ASC ) where rownum <= ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points