Different indexes being used for the same query in two databases

I have the below query and when I run the query in two different databases with different data, the explain plan seems to be different.

      SELECT *
      FROM   voucher_row_tab g,
             voucher_type_tab v
      WHERE  g.company            = '12'
      AND    (g.voucher_date BETWEEN to_date('2022-12-10', 'YYYY-MM-DD')  AND ('2023-12-10', 'YYYY-MM-DD'))
      AND    (g.account      BETWEEN  '100'    AND '200')
      AND    g.company            = v.company
      AND    g.voucher_type       = v.voucher_type  
      AND    v.simulation_voucher  != 'TRUE'
      AND    'TRUE' = (SELECT TEST_API.CHECK (g.company, g.pc_id) FROM DUAL);

I will call the two databases A and B.

When I check the explain plan in A it uses an index defined on the columns <COMPANY, ACCOUNTING_PERIOD, ACCOUNTING_YEAR, VOUCHER_TYPE> and B uses an index defined on the columns <PC_ID, COMPANY, ACCOUNTING_YEAR>. Both the databases have both the indexes defined, but upon the execution of the same query, the databases behave differently.

Anyone has any idea why this happens? Does the data in the tables have any impact on this?

Yes, the different data present in the databases influences the use of indexes.

For databases with cost-based query optimisers, this is exactly the desired behaviour as using an index is not necessarily the best (=fastest) option to compute the query in all situations.

One example for such a situation is when the table itself is fairly small and reading the whole table turns out to be quicker than first reading the index, finding which part of the table to read, and only read the table then.

Explaining the details in sufficient depth is beyond the scope of this answer - the Oracle databases documentation has a reasonably good introduction into this broad topic.

A good place to start understanding the different execution plans is to review the EXPLAIN PLAN output on both databases and compare the expected rows/costs for each step.

Oracle's cost-based optimizer (CBO) does a complex computation based on statistics of various kinds (segment, system, (near)real-time, dynamic sampling, etc..) considering rowcounts, distinct value counts, distribution, block counts, high and low values, histograms, etc.. and attempts to predict the time cost for potentially thousands of different plan permutations, then selects what it believes will be the lowest cost. This means that the plan is not stable - as the data changes and the environment shifts in various ways, plans change without warning. You can lock them down, but normally they are free to shift with the changing reality of data and Oracle's knowledge of that data (statistics). This is the side-effect of a program trying to be intelligent and adaptive.

Therefore, you should never assume or expect the same plan in two different databases, or even in the same database tomorrow vs. today. However, because of the complexity involved Oracle (or any other RDBMS) quite often makes mistakes and its calculated costs are wrong, in which case the plan will be wrong. This is a very common occurance. For most SQLs the difference is undetectable and we don't bother worrying about it. But sometimes the mistake causes really bad execution plans to cause us to miss our SLAs, and those require our attention.

If you wish to force certain plan steps, then utilize hints to limit the optimizer's set of choices. While hints are non-portable and often discouraged in the development community, they do a very good job of stabilizing plans when plan variability threatens our SLAs. But they should be used only by a knowledge person who has a good understanding of what they're doing, or they can work against you. This is complex subject that unfortunately cannot be covered here. For situations where hints cannot be used or there isn't enough knowledge to use them correctly, the best thing you can do is ensure that each object (tables and indexes) involved in a query have good, non-stale statistics. That will resolve at least a good portion of issues, though not all.

First let me rewrite it a bit.

SELECT  g.*, v.*
    FROM  voucher_row_tab AS g
    JOIN  voucher_type_tab AS v
       ON  g.company = v.company
      AND  g.voucher_type = v.voucher_type
    WHERE  g.company = '12'
      AND  g.voucher_date BETWEEN '2022-12-10' AND '2023-12-10'
      AND  g.account BETWEEN '100' AND '200'
      AND  ( SELECT  TEST_API.CHECK (g.company, g.pc_id) FROM  DUAL ) = 'TRUE'
      AND  v.simulation_voucher != 'TRUE'
    ;

Question: Is 'TRUE' a 4-character string? Or a Boolean value?

Question: Do you want the 10th of December from both years?

These might help with efficiency:

g:  INDEX(company, voucher_date)
v:  INDEX(simulation_voucher, company,  voucher_type)