Re: Speeding up ORACLE queries .. HELP

In article <32cmo1$b24_at_pheidippides.axion.bt.co.uk>, Nicholas Kitchener <nkitchen_at_axion.bt.co.uk> wrote:
>
> As a student just in placement, I have a project to complete.
>
> My problem is to do with SQLplus (ORACLE 7) at speeding up SQL queries,
> the general jist of the set of SQL queries is
>
> WHERE ch_uid IN (SELECT ch_uid FROM pib_CR)
In will screw up performance. U could write this query with a join which will be much faster.mail me the query and i will join it. There was a posting similar to this last week where a guy had used the in clause and had poor performance. IN should be used in case where u want to compare some value in a column with constants say like
where ch_uid IN ('Y','N','U');
If the parametes of the in clause exceed a reasonable value performance would be screwed up because Oracle would would scan the in parameter list for every row and if u have a large parameter list with a large table u r asking for performance problem.
Also wonder why the Oracle query optimiser is not able to optimise this.

>
> I have created indices for the three tables I have created by :
> CREATE INDEX pib_icr ON pib_CR (ch_uid)
> The main point of my question is there a method for speeding up big multi
> table SQL queries ??
> If you have any thoughts Email me..
> Thanks in advance,
> Nick.
Thanks and Regards

Thanks and Regards
Mahesh Vallampati
M.S. In EE
Dept.of Electrical Engineering,
Texas A & M University.
Ph:(409)845-6189
\\ In the Beginning there was Codd..... Received on Thu Aug 11 1994 - 16:30:47 CEST