Oracle Training To Help Make Your

Systems Better, Faster and Smarter

For more valuable Oracle tips and tricks just subscribe to our ezine and every month we'll send you some tips and tricks to help you make your Oracle systems better, faster and smarter.

Contents

• Query optimistation issue From:Parthiban G Pillai- The following script is taking a 3 min to give the result when performed on 1000 entries. Can u pls help me out in optimising this query so as to give faster result. The table is not indexed. Query is:-- SELECT nvl(a.FLD7,'x') ,nvl(a.FLD11,'x') ,nvl(a.FLD14,'x') ,nvl(a.FLD25,'x') ,nvl(a.FLD30,'x') ,nvl(a.FLD32,'x') ,min(decode (d.FLD11,'8' ,d.FLD15, '999999')) tfmin15 , min(a.id) ID from (select * from partial_cdr where leg_id=1) a ,(select * from partial_cdr where leg_id = 3) b ,partial_cdr c ,(select * from partial_cdr where fld11='8') d where nvl(a.FLD7,'x') = nvl(b.FLD7,'x') AND nvl(a.FLD11,'x') = nvl(b.FLD11,'x') . . . AND nvl(d.FLD32 (+),'x') = nvl(b.FLD32,'x') group by nvl(a.FLD7,'x') ,nvl(a.FLD11,'x') ,nvl(a.FLD14,'x') ,nvl(a.FLD25,'x') ,nvl(a.FLD30,'x') ,nvl(a.FLD32,'x') having max(b.fld15) = count(distinct c.fid15) The first thing we noticed about this SQL statement was that the table partial_cdr was being joined to itself 4 times. This may be the only way of doing things - without knowing the application and the Oracle database structure we can't tell. However, we would recommend combining the sub queries on partial_cdr so that it is only referenced once. The next thing we noticed was that you also use the NVL function on every column. in the where clause. This prevents any indexes on those columns being used. You could rewrite each clause to be like this: where (a.FLD7=b.FLD7 OR (a.FLD7 IS NULL AND b.FLD7 IS NULL)) to allow indexes to be used when the fields were not null. See the many free articles and Oracle tutorials we have on our site for more help or consider taking some of our formal Oracle training courses.  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs?Training is a highly cost-effective and proven method of boosting productivity.  Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training.  See our scheduled Oracle training courses, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England Need help? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.     View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.