Performance impact of in-line view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Performance impact of in-line view

  1. #1
    Join Date
    Apr 2001
    Posts
    122

    Performance impact of in-line view

    Hi,

    I try to use inline-view in the following query, but I am not sure if there is a better way to do this, because this query is heavily used in an web application, I want to make sure it doesn't run more than 1 second. Any suggestions are welcome.

    Thanks a lot in advance!

    Code:
    Select 
    table1.col2, 
    table1.col3,
    table3.col15,
    CASE WHEN (Sum(table4.col16)> 0) THEN
      (Sum(table4.col17)/Sum(table4.col18)) * 100
    ELSE
      0
    END,
    COUNT(invw1.table5_ID),
    COUNT(invw2.table5_ID),
    COUNT(invw3.table5_ID)
     From  table1, table2, table3, table4, 
           (select table5_ID, col7 from table5) invw1,
           (select table5_ID, col7 from table5 where col19 = 'XY') invw2,
           (select table5_ID, col7 from table5 where col19 = 'YZ') invw3
     WHERE table1.table1_ID=table2.table2_ID(+)
     AND table1.col3=table4.col4(+)
     AND table1.col5=table3.col6(+)
     AND table1.col3=invw1.col7(+)
     AND table1.col3=invw2.col7(+)
     AND table1.col3=invw3.col7(+)
     AND table1.col8 = 'AB'
     AND table1.col9 = 'DB'
     AND table2.col10 = 'CD'
     AND table2.col11 = 'DE'
     AND table2.col12 = 'EF'
     AND table3.col13 = 'FG'
     AND table3.col14 = 'HI'
     GROUP BY table1.col2, table1.col3
     ORDER BY table1.col2

  2. #2
    Join Date
    Apr 2001
    Posts
    122
    Hi, just want to mention that I check Tom's Effective Oracle by Design, on Page 510 - 511, he mentioned that performance of scalar subquery is better than inline view, so I change the query to the following, any comments are welcome. Thanks

    Code:
    select 
    table1.col2, 
    table1.col3,
    table3.col15,
    CASE WHEN (Sum(table4.col16)> 0) THEN
      (Sum(table4.col17)/Sum(table4.col18)) * 100
    ELSE
      0
    END,
    (select COUNT(1) from table5 where table5.col7 = table1.col3),
    (select COUNT(1) from table5 where table5.col7 = table1.col3 and col19 = 'XY'),
    (select COUNT(1) from table5 where table5.col7 = table1.col3 and col19 = 'YZ')
     From  table1, table2, table3, table4, 
     WHERE table1.table1_ID=table2.table2_ID(+)
     AND table1.col3=table4.col4(+)
     AND table1.col5=table3.col6(+)
     AND table1.col8 = 'AB'
     AND table1.col9 = 'DB'
     AND table2.col10 = 'CD'
     AND table2.col11 = 'DE'
     AND table2.col12 = 'EF'
     AND table3.col13 = 'FG'
     AND table3.col14 = 'HI'
     GROUP BY table1.col2, table1.col3
     ORDER BY table1.col2

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I really doubt that Tom said "scalar subqueries are better than inline views" without qualifying that to give specific circumstances in which that is true.

    Inline views are neither good nor bad for performance, they are just a feature. How well (or not) does your query perform right now?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Let me see the trace files for both queries.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width