Re: Joining two disparate tables
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Mon, 23 Mar 2009 09:15:14 -0500
Message-ID: <2OMxl.10380$jZ1.1097_at_flpi144.ffdc.sbc.com>
ahanjura_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.
Date: Mon, 23 Mar 2009 09:15:14 -0500
Message-ID: <2OMxl.10380$jZ1.1097_at_flpi144.ffdc.sbc.com>
ahanjura_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.
No, not enough information.
Oracle version x.x.x.x?
OS platform?
Have you used "explain plan" to determine how your data is being accessed? Are you using the appropriate indexing to support the WHERE clause/JOIN clause? or is it trying to pull everything with little selectivity (little hope of fixing this easily)?
Have you investigated the use of optimizer hints (assuming, of course, you are using CBO) Google can be your friend:
http://www.lmgtfy.com/?q=oracle+hint+optimizer Received on Mon Mar 23 2009 - 09:15:14 CDT