0

The view has several join, but no WHEREcloses. It helped our developers to have all the data the needed in one object, that could be easily used in the "low code" later on. In most cases, Appian add conditions to query the data on the view, in a subsequent WHERE clause like below:

 query: [Report on Record Type], order by: [[Sort[histoDateAction desc], Sort[id asc]]], 
filters:[((histoDateAction >= TypedValue[it=9,v=2022-10-08 22:00:00.0]) 
      AND (histoDateAction < TypedValue[it=9,v=2022-10-12 22:00:00.0]) 
      AND (histoUtilisateur = TypedValue[it=3,v=miwem6]))
]) (APNX-1-4198-000) (APNX-1-4205-031)

Now we start to have data in the database, and performances get low. Reason seems to be, from execution plan view, query do not use indexes when data is queried.

Here is how the query for view VIEW_A looks:

   SELECT
     <columns>   (not much transformation here)
   FROM A
   LEFT JOIN R on R.id=A.id_type1
   LEFT JOIN R on R.id=A.id_type2
   LEFT JOIN R on R.id=A.id_type3
   LEFT JOIN U on U.id=A.id_user     <500>
   LEFT JOIN C on D.id=A.id_customer <50000>
   LEFT JOIN P on P.id=A.id_prestati <100000>

and in the current, Appian added below clauses:

   where A.DATE_ACTION <  to_date('2022-10-12 22:00:00', 'YYYY-MM-DD HH24:MI:SS')
     and A.DATE_ACTION >= to_date('2022-10-08 22:00:00', 'YYYY-MM-DD HH24:MI:SS')
     and A.USER_ACTION = 'miwem6'

typically, when I show explain plan for the VIEW_A WHERE <conditions> , I have a cost around 6'000, and when I show explain plan for the <code of the view> where <clause>, the cost is 30.

Is it possible to use some Oracle hint to tell it: "Some day, someone will query this adding a WHERE clause on some columns, so don't be a stupid engine and use indexes when time comes"?

J. Chomel
  • 8,193
  • 15
  • 41
  • 69

1 Answers1

1

First, this isn't a great architecture. I can't tell you how many times folks have pulled me in to diagnose performance problems due to unpredictably dynamic queries where they are adding an assortment of unforseable WHERE predicates.

But if you have to do this, you can increase your likelihood of using indexes by lowering their cost. Like this:

SELECT /*+ opt_param('optimizer_index_cost_adj',1) */
<columns> (not much transformation here)
FROM A . . .

If you know for sure that nested loops + index use is the way you want to access everything, you can even disable the CBO entirely:

SELECT /*+ rule */
<columns> (not much transformation here)
FROM A . . .

But of course it's on you to ensure that there's an index on every high cardinality column that your system may use to significantly filter desired rows by. That's not every column, but it sounds like it may be quite a few.

Oh, and one more thing... please ignore COST. By definition Oracle always chooses what it computes as the lowest cost plan. When it makes wrong choices, it's because it's computation of cost is incorrect. Therefore by definition, if you are having problems, the COST numbers you see are wrong. Ignore them.

Paul W
  • 5,507
  • 2
  • 2
  • 13
  • thanks for the answer :) cost gets better :), but execution time becomes significantly larger... on my benchmark of 1 query :). Thanks for the advice on the architecture, that was my feeling too. – J. Chomel Feb 03 '23 at 06:55
  • 1
    Using indexes is not always the right thing to do. Everything depends on your predicates, your data, cardinalities, etc.. Tuning is beyond the capabilities of this forum to address properly. There are too many factors to consider. You will need to look at your execution plan and examine the wait data in v$active_session_history to see what it's doing, then make the appropriate choices to address it, whether that be rewrite, indexing, hints, etc.. it really must be done in-house by those who have visibility into your system. – Paul W Feb 03 '23 at 12:39