How to tune the below query for optimal performance

Is there other way to tune this query:
select pricing.in_or_out_icon as icon, SUM(to_number(pricing.pricing_cash)) as
sum_pricing_cash, SUM(to_number(pricing.present_cash)) as sum_present_cash,
count(pricing.pricing_cash) as count
from pricing_1 where
type = “pricing”
price_identity = “546”
(pricing.pricing_class_code == “X”
pricing.function_code NOT IN [“100”, “22”]
pricing.issue_code != “18”
pricing.issuer_settlement_code != “24”
pricing.card_acceptor_country_code = “24”
(EVERY trans_status IN message_status SATISFIES trans_status.code != “000” END)
group by pricing.in_or_out_icon

use following index.

CRATE INDEX ix1 ON pricing_1 (price_identity) WHERE type = “pricing”;

Hi Vsr1,
Index used by above query is
Create index ix1_pricing on pricing_1(price_identity,
pricing.pricing_class_code,, pricing.function_code,
pricing.issuer_settlement_code, pricing`.card_acceptor_country_code,
DISTINCT array v in v.status_code for v in message_status End)
where type=‘pricing’

Create index ix2_pricing on pricing_1(pricing.pricing_cash,pricing.present_cash,pricing`.pricing_cash)
where type=‘pricing’

You have multiple OR, NOT, EVERY predicates. I think best one be what i suggested. U can create one USE INDEX (ix1) and see how it performs.

EVERY doesn’t work for ARRAY index. ANY AND EVERY can use ARRAY index. it may not that useful in your case.
code != “000” is same all the time, you can add (EVERY trans_status IN message_status SATISFIES trans_status.code != “000” END) to Index WHERE clause and remove from index.

Like below (see if it covers).

CREATE INDEX ix2 ON pricing_1(price_identity, pricing.pricing_class_code, pricing.function_code,
                              pricing.issue_code, pricing.issuer_settlement_code, pricing.card_acceptor_country_code,
                              pricing.in_or_out_icon, pricing.pricing_cash, pricing.present_cash)
WHERE type = "pricing" AND (EVERY ts IN message_status SATISFIES ts.code != "000" END);

SELECT pricing.in_or_out_icon AS icon,
       SUM(to_number(pricing.pricing_cash)) AS sum_pricing_cash,
       SUM(to_number(pricing.present_cash)) AS sum_present_cash,
       COUNT(pricing.pricing_cash) AS count
FROM pricing_1
WHERE type = "pricing"
      AND price_identity = "546"
      AND (pricing.pricing_class_code == "X"
           OR pricing.function_code NOT IN ["100", "22"]
           OR pricing.issue_code != "18"
           OR pricing.issuer_settlement_code != "24"
           OR pricing.card_acceptor_country_code = "24"
      AND (EVERY ts IN message_status SATISFIES ts.code != "000" END)
GROUP BY pricing.in_or_out_icon;

ix2 index is not getting picked by query.

I tried 6.0.x it is picke up try with USE INDEX (ix2)

i tried reframing query and created covered partial index it worked.
Thanks vsr1.Your suggestion are always helpful