Re: Joining two disparate tables
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 23 Mar 2009 07:37:17 -0700 (PDT)
Message-ID: <52188fe4-057b-4c9b-819b-d71594c33788_at_33g2000yqm.googlegroups.com>
On Mar 23, 10:15 am, Michael Austin <maus..._at_firstdbasource.com> wrote:
> 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.
>
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 23 Mar 2009 07:37:17 -0700 (PDT)
Message-ID: <52188fe4-057b-4c9b-819b-d71594c33788_at_33g2000yqm.googlegroups.com>
On Mar 23, 10:15 am, Michael Austin <maus..._at_firstdbasource.com> wrote:
> 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.
>
> 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- Hide quoted text -
>
> - Show quoted text -
I will second Michael's thoughts and add this. Yes, increasing the quantity of data can effect query performance if it increases the amount of data you need to seach in order to return your result set. On the other hand if your primary query just grabs 50 rows of data associated with a key value and those 50 rows are normally stored in only one or two blocks then the quantity of data has very little effect on query performance.
The real questions are how does your application work? How much data is being sought? How is Oracle going about getting the data?
Make sure the statistics are current.
HTH -- Mark D Powell -- Received on Mon Mar 23 2009 - 09:37:17 CDT