Re^2: Excessive execution time passing parameters to Oracle

Thanks jhourcle, This is a common query. I used two ranges of same table because the range clause is exactly as

AND ((UCSII.REC_STATUS = 'A'
AND UCSII.UCSIIU_RECEIPT_AT_MOT_DT between 
to_date(?,'mm/dd/yyyy') and to_date(?,'mm/dd/yyyy') ) 
-- <1.start date, 2.end date>
OR (UCSII.UCSIIU_RECEIPT_AT_MOT_DT between 
to_date(?,'mm/dd/yyyy') and to_date(?,'mm/dd/yyyy')  
-- <3.start date, 4.end date>
AND UCSII.REC_STATUS = 'I'
AND UCSII.UCSIIU_CIF_SENT_TO_ITA_CNT > 0))
[download]

Can this range be combind into one? This is a common query. Could you please explain me little bit more on special index which you mentioned. both the column mean, which columns? Thanks a lot for your valuable comments. Regards, Ram

Comment on Re^2: Excessive execution time passing parameters to Oracle Download Code

Replies are listed 'Best First'.
Re^3: Excessive execution time passing parameters to Oracle by jhourcle (Prior) on Sep 27, 2006 at 15:19 UTC
Can this range be combind into one? Based on what you're saying, no. (you may get slightly different performance using a 'UNION ALL', as it doesn't have to perform the sort-merge, but if you're using any aggregate functions, you'll erase the benefit) (you won't get duplicates as REC_STATUS wouldn't match between the two sub-queries) Could you please explain me little bit more on special index which you mentioned. both the column mean, which columns? Sure -- instead of specifying indexes in with the individual column definitions, you do the following: `CREATE INDEX index_name ON table_name ( column1, column2 column3 )` (this also allows you to specify an alternate tablespace for your index, to reduce disk contention on writes) Now, if you run queries that act on those columns, it can use the single index, rather than needing to merge between multiple indexes. However, index design is tricky -- if you're just searching on a single column, this could be worse than a single column index on that value. It's probably more than I can really go into, and this isn't really Perl related anymore ... try searching for 'multicolumn index' or 'composite index') ... Oh -- and it seems that the use of bind variables and histograms may cause problems I wasn't aware of -- since 9i, Oracle does 'bind variable peeking', (ie, it looks at the bind variables to determine the best execution plan). Unfortunately, it then caches the plan, and it may be optimized for an outlier case (the first one run). Some folks are actually recommending not using histograms on skewed data	[reply] [d/l]
Re^3: Excessive execution time passing parameters to Oracle by Anonymous Monk on Sep 26, 2006 at 09:52 UTC
Also, please note that this script is running smoothly on test server. The query takes few minutes to execute on test server, however on production this is getting hanged on the step when query is executed.	[reply]