Re: small table takes more time as compared to bigger table

From: onedbguru <onedbguru_at_yahoo.com>
Date: Tue, 17 May 2011 09:46:30 -0700 (PDT)
Message-ID: <824a154d-91d2-45ac-a924-2949811137d9_at_d28g2000yqf.googlegroups.com>



On May 17, 11:56 am, MadhavC <choudhar..._at_gmail.com> wrote:
> Hello Experts,
>
> I am working on an enterprise application and facing some issue while
> working on DB queries.
>
> I have a table - TABLE1 (having total 5 million rows)
> col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
>
> I have another table - TABLE2 (having total only 1000 rows)
> col1, col4, col6
>
> TABLE2 contains redundent information for col1, col4, and col6 from
> TABLE1
>
> I run the query as below for 16000 times for different values of col4
> in where clause-
> select col1, col4, col6 from TABLE1 where col4='SOMEVALUE'
> This finish in few milliseconds
>
> I run the following query for 16000 times for different values of col4
> in where clause
> select col1, col4, col6 from TABLE2 where col4='SOMEVALUE'
> Even though TABLE2 is a very small table as compared to TABLE1, this
> time these 16000 queries takes around 2 seconds. Almost 5-6 times more
> than that of the query for TABLE1.
>
> TABLE1 and TABLE2 are indexed on col1.
>
> Can you please suggest what parameters I should be looking for to
> understand and fix this.
>
> Thanks in advance for your help.

make sure statistics are up to date on both tables. What does explain plan say for both?

sqlplus
set autotrace on
set time on
select .....

OR - just use table1.... Received on Tue May 17 2011 - 11:46:30 CDT

Original text of this message