Re: Optimizing a SQL statement

Amir,

When I see a query like this, my first suggestion is to use dynamic SQL and solve the list of predicates at the application layer. In other words, the SQL would only include the very few predicates with actual value on their place holder. Let’s say the user passes only a value for a product_id, and nothing else, in such case an incarnation of your query would only include predicate product.product_id LIKE :p1.

Even with dynamic SQL and building a different SQL text as per actual values passed, the use of LIKE would reduce the options for the CBO. Ideally you would want to use equality predicates instead. The problem with “LIKE :b” is that it allows to pass on :b values such as “abc%”, “abcd”, “%abc%” and “%”, then you pretty much have to scan the whole table or index.

Seeing your ORDER BY and ROWNUM clauses, I see you could explore using FIRST_ROWS optimization, but I would only consider it after you simplify predicate and replace LIKE.

In my experience, any application can be changed. Anyways, this is just my two cents. And of course, this SQL would be perfect for an Exadata PoC.

Peace — Carlos

> On Dec 6, 2019, at 00:09, Stefan Koehler <contact_at_soocs.de> wrote:
>
> Hello Amir,
> unfortunately one of the most important information (access/filter predicates and column projections) is missing in your case but let's do a little bit of guess work here.
>

>> Almost all of the columns in the statement are indexed. Tables PRODUCT and PLACE have 4,576,690 and 1,892,243 rows respectively.

>> "Hameed, Amir" <Amir.Hameed_at_xerox.com> hat am 4. Dezember 2019 um 18:05 geschrieben: 
>> 
>> 
>> 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

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