Re: Optimizing a SQL statement

I suggest you take a step back and rewrite that massive list of OR filters. Do you really want one statement to rule them all or will it be the case that your user is going to be using a reasonably selective filter?

You might be using an index on product to drive this query but it can’t actually be used as a filter unless every one of those columns is included in the index - I bet it’s just on the column you are ordering by so Oracle will go to every row in the table in that order eliminating rows that don’t match the rest of the filters as it goes. This is going to be very slow if your filters are actually decently selective - you might have to read the entire index and table a block at a time (sometimes reading the same table block multiple times) if less than 500 rows matches your OR list.

There is no way for Oracle to use an index on serial_id here (for example) as you can’t eliminate rows based on that filter - you would need to AND the filters together to achieve that.

Rewrite the query so that only the filters that are actually needed are used. Consider that not all of these filters should be LIKE filters. Consider that you probably want to AND the filters together.

For the query you’ve shared, I would suggest a full table scan of product would probably be the most efficient way to execute the query under most reasonable inputs.

Hope this helps you get started,
Andy

On Wed, 4 Dec 2019 at 17:06, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:

> Hi,
>
> The following statement does over 4.5 million LIOs per execution. It runs
> at an average of 3-4 times per minute. Almost all of the columns in the
> statement are indexed. Tables PRODUCT and PLACE have 4,576,690 and
> 1,892,243 rows respectively. The only way I have been able to optimize it
> is to add a hint to scan the PRODUCT table in PARALLEL, which does help
> with the elapsed time and the statement finishes within 10 seconds.
> However, because the statement runs quite frequently, I don’t believe using
> the parallel hint would be a good idea. What would be a good approach I
> should look into to help optimize it. The DB version is 11.2.0.4.
>
>
>
> Thanks
>
>
>
> 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
>
> ;
>
>
>
> call count cpu elapsed disk query
> current rows
>
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
>
> Parse 1 0.00 0.00 0 0
> 0 0
>
> Execute 1 0.01 0.01 0 0
> 0 0
>
> Fetch 2 57.02 72.37 200413 4570892
> 0 1
>
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
>
> total 4 57.04 72.39 200413 4570892
> 0 1
>
>
>
> Rows (1st) Rows (avg) Rows (max) Row Source Operation
>
> ---------- ---------- ----------
> ---------------------------------------------------
>
> 1 1 1 COUNT STOPKEY (cr=4570892 pr=200413 pw=0
> time=69235827 us)
>
> 1 1 1 VIEW (cr=4570892 pr=200413 pw=0
> time=69235818 us cost=1752 size=82164 card=501)
>
> 1 1 1 NESTED LOOPS OUTER (cr=4570892
> pr=200413 pw=0 time=69235815 us cost=1752 size=148797 card=501)
>
> 1 1 1 TABLE ACCESS BY INDEX ROWID PRODUCT
> (cr=4570888 pr=200411 pw=0 time=72376638 us cost=1003 size=511287210
> card=2157330)
>
> 4578439 4578439 4578439 INDEX FULL SCAN PRODUCTP1 (cr=20522
> pr=19831 pw=0 time=18481568 us cost=7 size=0 card=1063)(object id 2080845)
>
> 1 1 1 TABLE ACCESS BY INDEX ROWID PLACE
> (cr=4 pr=2 pw=0 time=243 us cost=2 size=60 card=1)
>
> 1 1 1 INDEX UNIQUE SCAN PLACEP1 (cr=3 pr=1
> pw=0 time=146 us cost=1 size=0 card=1)(object id 2080816)
>
>
>

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