Re: Joining two disparate tables
Date: Mon, 23 Mar 2009 11:20:01 -0700 (PDT)
Message-ID: <fd7d5b20-09d3-4d1e-980b-b2b4a28a085d_at_j8g2000yql.googlegroups.com>
On Mar 23, 6:16 am, "ahanj..._at_gmail.com" <ahanj..._at_gmail.com> wrote:
> I have 4-5 tables joined to do a query from multiple tables with the
> number of rows in those tables ranging from 10^5 to 10^7. Though I
> already take a severe performance hit, I had stated that as a
> limitation in my product and the customer lived with it and also the
> response time wasn't really critical at that time.
>
> Now, my problem is that the range of records has been widened
> (primarily because of 1 table I was joining earlier) and the new range
> of number of records is 10^2 to 10^7 and I still do the same join and
> my response time requirements are more stringent than before.
>
> I am using OCI to connect to the database and my question is whether
> there is a way to avoid the join on the table having just 100 rows and
> does my assumption that having a higher range of records really affect
> the performance of the query.
>
> I hope what I have stated makes good sense for you to respond.
> Thanks in advance.
You probably want to carefully study the Peformance Tuning Guide (oracle manuals are available at tahiti.oracle.com, among other places), particularly the section about the query optimizer and tuning sql statements - there is even a section on understanding joins. You need to understand the various ways the optimizer can access data, and be able to figure out if how it is doing that is proper for your range of records. Once you get the basics (or maybe especially if you don't get it), check out Jonathan Lewis' optimizer book.
Once you get the basics, you can try to improve what is happening, using the selectivity of your small table to limit the amount of probing, or not as the case may be. You may want to use different access methods with 5 orders of magnitude differences of the amount of data filtered (if I understand your question correctly). The optimizer may be smart enough to figure that out by itself, if you give it enough information. How best to get it may differ by version, oracle options (google partition pruning), data skew, hardware, etc. What you can't do is predict exactly what will happen solely from the code (unless you are using the rule based optimizer on some ancient version of Oracle, in which case you would deserve abuse :-) . If your response time service agreement is tightening up, you may well find it necessary to learn to delve into traces - plenty on the web about how to do that. But get conversant with plans, like yesterday.
In general, it is best to let the optimizer have its way and just be sure it is doing what you expect, but there are certain times when you may know better, only then should you use hints. See Plan Stability in the docs.
No clue about your sql, but I'll say "see this" anyways: http://www.jlcomp.demon.co.uk/inline_1.html
jg
-- _at_home.com is bogus. Gigi! http://www3.signonsandiego.com/stories/2009/mar/22/1m22laughead231349-woman-used-heiress-tale-steal-h/?uniontribReceived on Mon Mar 23 2009 - 13:20:01 CDT