Re: Please Help Me !! Performance Yuning Problem

> I am writing a query which is running much longer than I expect (need).
> I am hoping that somebody can spot a problem with the query or
> offer a suggestion as to how to tune it.
> =
 

> - This is a single table query.
> - The table is appx 90,000,000 rows
> - It is running on a 24 node SP2 machine.
> - In order to help decifer the "sum(round((instr" selection I will
> attempt to put it here in english. The intent is to return a value
> of 1 from the round function if the value in the A.USOC field matches
> the quoted string. I have tested this and it works.
> - This query runs for 12+ hours (in fact it has never finished). I
> guess it should run 1-2 hours.
> - I am unable to modify the table in any way. No new columns, indexes,
> etc...
> =

As I see reading your query, your WHERE clause contains LOTS of functions (SUBSTR). According what I heared about, if you are using functions in a where clause, indexes are not used by Oracle (it was true at least with version 6.x) =

An idea should be first splitting your phone numbers in area code, prefix (2 more fields) and index these 2 fields. It should speed up the execution of the query even if you have to run an 'update' query to initialise these 3 fields.

Luc Par=E9 Received on Mon Apr 01 1996 - 00:00:00 CEST