Re: Optimizing a SQL statement

Hi Amir!

As soon as I see so many parentheses, I know that there is something wrong.

1. Break that monstrosity into multiple queries and join them, possibly using a GTT.
2. Consider using text indexes instead of "like". That is why Oracle has text indexes.

Regards

On 12/4/19 12:05 PM, Hameed, Amir wrote:
>
> SELECT * FROM ( SELECT place.name,place.place_id FROM  product   LEFT
>
> OUTER JOIN place  ON  product.place_id_owned_by = place.place_id
>
> WHERE (((((((((((((((((((((
>
> product.product_id LIKE :p1 OR
>
> product.model_id LIKE :p2) OR
>
> product.part_id LIKE :p3) OR
>
> product.serial_id LIKE :p4) OR
>
> product.serial_id LIKE :p5) OR
>
> product.serial_id LIKE :p6) OR
>
> product.serial_id LIKE :p7) OR
>
> product.place_id_owned_by LIKE :p8) OR
>
> product.place_id LIKE :p9) OR
>
> product.temporary_serial LIKE :p10) OR
>
> product.temporary_serial LIKE :p11) OR
>
> product.temporary_serial LIKE :p12) OR
>
> product.temporary_serial LIKE :p13) OR
>
> product.condition_code LIKE :p14) OR
>
> product.lot_id LIKE :p15) OR
>
> product.lot_id LIKE :p16) OR
>
> product.lot_id LIKE :p17) OR
>
> product.lot_id LIKE :p18) OR
>
> product.asset_id LIKE :p19) OR
>
> product.asset_id LIKE :p20) OR
>
> product.asset_id LIKE :p21) OR
>
> product.asset_id LIKE :p22)
>
> ORDER BY
>
> product.product_id ASC   )
>
> WHERE ROWNUM <= 500
>
> ;
>
>

--
http://www.freelists.org/webpage/oracle-l