Re: Improve Query sqlm_bshtdhgmsvqad

Dear All,

Thank you for all of your input.

The query is generated by OBIEE, so there is little room for modification; nevertheless, because this is standard Oracle code, we will contact Oracle to see if they can help us rebuild the query in the proposed method. On the other side, I will sample the session to see if I can find out about the migrated rows in the lines table and notify you.

Best Regards,
AMIT On Sat, Nov 2, 2024 at 10:30 PM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 11/1/24 9:07 PM, Sayan Malakshinov wrote:
>
> Hi Amit,
>
> 1. Avoid such ORs:
> (RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE>=TO_DATE(SUBSTR('2024-10-18
> 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS')
> OR
> RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE>=TO_DATE(SUBSTR('2024-10-18
> 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS'))
>
> It's better to rewrite it as:
> (
> select ...
> from
> APPS.RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_ALL
> INNER JOIN APPS.RA_CUSTOMER_TRX_LINES_ALL
> RA_CUSTOMER_TRX_LINES_ALL
> ON
> RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID=RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID
> where RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG='Y'
> and RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE>=TO_DATE(SUBSTR('2024-10-18
> 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS')
> union
> select ...
> APPS.RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_ALL
> INNER JOIN APPS.RA_CUSTOMER_TRX_LINES_ALL
> RA_CUSTOMER_TRX_LINES_ALL
> ON
> RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID=RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID
> where RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG='Y'
> and
> RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE>=TO_DATE(SUBSTR('2024-10-18
> 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS')
> )
>
> The optimizer often can re-write such things using OR_EXPAND or USE_CONCAT
> transformations, but your query is too complex, so it would be better if
> you optimize it.
>
> 2. Avoid TO_CHAR in such cases:
>
> LEFT OUTER JOIN (APPS.OE_ORDER_LINES_ALL OE_ORDER_LINES_ALL
> INNER JOIN APPS.OE_ORDER_HEADERS_ALL
> OE_ORDER_HEADERS_ALL
> ON
> OE_ORDER_LINES_ALL.HEADER_ID=OE_ORDER_HEADERS_ALL.HEADER_ID)
> ON
> RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(OE_ORDER_LINES_ALL.LINE_ID))
>
> It's better to rewrite like
>
> TO_NUMBER(RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6 DEFAULT NULL
> ON CONVERSION ERROR) = OE_ORDER_LINES_ALL.LINE_ID
>
> --
> Kind regards,
> Sayan
>
> Hi Sayan,
>
> I believe that it would be better to change the data model. People have to
> understand that most of the huge and unwieldy queries like the OP has given
> us the opportunity to study are most of the time a result of lacking data
> model. Missing objects and inadequate indexing can cause that. Also,
> translating an OLTP data model to the data warehouse environment can do
> that. B*Tree indexes are not al ways the best. Unfortunately, bitmap
> indexes can have very interesting consequences in an OLTP environment so
> they're normally not used for that. Oracle offers a ton of options
> (clustering, hash clusters, IoT) which are rarely used. Fixing the model
> first is probably the best advice for Amit.
>
> Regards
> --
> Mladen Gogala
> Database Consultant
> https://dbwhisperer.wordpress.com
> -- http://www.freelists.org/webpage/oracle-l

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