Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #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.

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

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

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

Distributed Database Consulting, Inc.
  #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

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.


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 07:45 AM.

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