Question about the results from the Optimiser (version 6)

SQL Optimizer SQL Optimizer for Oracle
1

I have a question regarding some of the results we got from the optimisation.

We are on Oracle 9i and use Toad version 9.0.1.8.

The query is very simple. The original query was:

select t.transaction_id,
t.transaction_id_reversed,
t.tran_status_code,
t.branch_id
from jbs_transaction t
where t.tran_status_code = ‘U’ and
t.transaction_id_reversed is not null

execution time is 9 seconds.

After running the query through optimiser it came with following options (among others):

select /*+ INDEX_JOIN(T) */ t.transaction_id,
t.transaction_id_reversed,
t.tran_status_code,
t.branch_id
from jbs_transaction t
where t.tran_status_code = ‘U’
and t.transaction_id_reversed is not null

this one took 2 seconds to complete

and one more:

select /*+ FIRST_ROWS */ t.transaction_id,
t.transaction_id_reversed,
t.tran_status_code,
t.branch_id
from jbs_transaction t
where ‘U’ = t.tran_status_code
AND 1E28 >= -t.transaction_id_reversed

this one takes only 16 ms.

The hint on its own does not give the performance improvement.
select /*+ FIRST_ROWS */ t.transaction_id,
t.transaction_id_reversed,
t.tran_status_code,
t.branch_id
from jbs_transaction t
where ‘U’ = t.tran_status_code
AND t.transaction_id_reversed is not null

takes 1:05 minutes.

Using just the magic number without the hint takes 11 seconds.

At the same time for some reason the optimiser did not come with
the suggestion to use:

select /*+ INDEX(T TRAN_TRANSTS_FK_I) */ t.transaction_id,
t.transaction_id_reversed,
t.tran_status_code,
t.branch_id
from jbs_transaction t
where t.tran_status_code = ‘U’
and t.transaction_id_reversed is not null

This takes 15 ms.

The index TRAN_TRANSTS_FK_I is defined
as not unique index on the column TRAN_STATUS_CODE, Asc.

The questions I have:

  1.  Why the optimiser did not consider using the TRAN_TRANSTS_FK_I, is there a setting
 we should switch on or change for the optimiser to try using the indexes?

  2.  What is the magic number 1E28 and is this something which can be reliably used
 to check for null of a column and does it only work with the FIRST_ROWS hint?
 I could not find any information about this.  Will it still work on the next version of Oracle.

The column transaction_id_reversed is defined as NUMBER(28).

Than you very much.

2

Hi Nadir,

  1. If you go to the Preferences window, under the Optimizer | Hints, there are options to control which hints to apply when we rewrite SQL. It may be that the INDEX hint option was not turned on so you don’t see this rewrite.

Another possibility is that there is another plan using the index already. Note that the optimizer will compare the plans from all alternatives to eliminate those duplicate. So if there is another rewrite having the same plan as the one using the INDEX hint, the rewrite using INDEX hint may not be displayed.

  1. The transformation was changing a condition
    Column is not null
    to
    Column >= -1E28
    so that index search on the column can be used. The number -1E28 is the smallest value you can have according to the datatype of the column. Since this is the smallest number, every not null value must be greater than or equal to -1E28.

You saw improvemment only when both the hint and the magic number were used because the hint affected how Oracle pick the plan. It is possible that without the hint, Oracle thinks that index scan should not be used even though the condition with the magic number allowed to do so. When the hint is there, Oracle considered the index scan and hence gave the SQL a different plan to run.

And regarding if this magic number will work in next Oracle release, the answer lies in whether the -1E28 would still be the smallest possible number for a Number(28) column. So I will say that the magic will still work in next Oracle release.

Let me know if I have not answered all your questions.

Thanks,
Alex

3

Thanks Alex. We will try your recommendations.

Nadir.