Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 7th, 2005, 11:29 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default query performance question

I do not do a lot of Oracle SQL, nor do I have any Oracle SQL analysis tools. I am asking if there are any obvious tweaks or changes to the query listed below to make Oracle handle it better. I dont know if there is a better way to specify joins and/or sub-queries that would help performance. The query is returning in about 3-4 minutes currently. I have included what I thought was other relevent information but let me know if more info would help.

Thanks for any suggestions.
KB.

Oracle 9i, I dont have the exact HW specs but this is a heafty production server.

The inner select is returning about 30 rows. The idp table has about 19 million rows total but only about 80,000 should match each of the V values from the inner select.
There is an index on the item column for idp and ki tables.
There is an index on the dg and V column for the idp table.
================================================== ===============
select /*+ RULE*/ distinct V,
     MAX( TO_CHAR(keyvaluedate, 'YYYYMM')) as INVDate
 from idp inner join ki on idp.item = ki.item
 where dg = 128 and V in
     (select distinct V from pp
       where dg = 128 and back = 1)
 group by V
 order by V, INVDate
================================================== ===============
  #2 (permalink)  
Old February 14th, 2005, 03:22 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

just a bump - no ideas from the experts ??

  #3 (permalink)  
Old February 14th, 2005, 03:45 PM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Is there a reason that you are forcing the query to use the rule based optimizer rather than the cost based optimizer?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
  #4 (permalink)  
Old February 14th, 2005, 04:42 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am not sure. Adding the Rule hint seemed to speed it up by a minute or so and I had one dba that may prevent a table scan. But with out the ability to analyse the query plan, I am not sure what effect this has.

Just looking for suggestions on whether there is a more effective way to structure the query.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Question on scalability and performance ThanhD BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 4 April 17th, 2007 02:13 PM
indexing performance question BinFrog SQL Server 2000 1 February 23rd, 2005 11:47 PM
Query Performance pilmart Access ASP 0 April 22nd, 2004 07:16 AM
performance question.. gbianchi Pro VB 6 6 October 8th, 2003 11:21 AM
Performance Question Kenny Alligood VB Databases Basics 2 August 11th, 2003 08:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.