Re: Optimizing a SQL statement

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 4 Dec 2019 13:18:48 -0500
Message-ID: <51d59c87-81da-6c21-d894-2f2ae04d711d_at_gmail.com>



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
Received on Wed Dec 04 2019 - 19:18:48 CET

Original text of this message