Question

Taking into acount the following table’s structure and data in an Oracle database

SQL> create table t ( x varchar2(1),
y varchar2(1) );
Table created.
SQL> insert into t values ( 'a', '1' );
1 row created.
SQL> insert into t values ( 'b', 'x' );

1 row created.

will the select below work?

SQL> select * from t where x = 'a' and y = 1

Answer

The answer is “it depends” because it depends on

- the optimizer mode in use

- and the availability of statistics about the table

If statistics were not generated then it doesn’t matter the optimizer mode: Oracle will always scan all table’s records and for each record

- compare column X with ‘a’

- convert column Y to number

- compare column Y with number 1

It will work for the first record but not for the second once Oracle cannot get a numeric representation of ‘x’. Therefore, the statement ends in error.

However, if the DBA generate the statistics - by running the command

SQL> analyze table t compute statistics;

AND the current optimizer mode is CHOOSE then the select will work fine.

It happens because

- the statistics will tell Oracle that there is only one record with value ‘a’ in column x,

- Oracle will choose the CBO mode

- CBO will generate an execution plan that will scan only records with value ‘a’ in column x

This way, the query’s execution never reaches the second record and it goes through.

To make sure that CBO will be used run the followin command before the select:

SQL> alter session set optimizer_goal=choose;

To confirm that even with statistics the select will fail if RULE is in use you can run the following command and then try to execute the query again:

SQL> alter session set optimizer_goal=rule;
 

[Home] [IT] [Oracle/Db] [SD] [PM] [Lendas Corporativas] [Diversas] [The Secret of Life] [About IT talks]