Autotrace from SYS - Some things appear to work but don't really

Autotrace from SYS - Some things appear to work but don't really

We use autotrace to get the Execution Plan and Statistics. It appear to work but don't really from SYS user. We see that.

SQL> create table t ( num number(2), name varchar2(10));

Table created.



SQL> insert into t values(1,'A');

1 row created.

SQL> insert into t values(2,'A');

1 row created.

SQL> select * from t;

NUM NAME
---------- ----------
1 A
2 A



SQL> set autotrace on;
SQL> select * from t;

NUM NAME
---------- ----------
1 A
2 A
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
We see the same thing from the SYS user.

SQL> conn sys/password@oraprc as sysdba
Connected.
SQL> set autotrace on;
SQL> select * from system.t;

NUM NAME
---------- ----------
1 A
2 A


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SYSDBA, SYSOPER, "internal" and sys in general shouldn't be used for anything other then admin.

Read More

CHAR Vs VARCHAR

CHAR Vs VARCHAR:

We see the information about the CHAR and VARCHAR in this Blog.

Create table t ( X varchar2(30) , Y char(30));
Insert into t values ('a','a');
The above table is having the two fields X & Y and corresponding data types are varchar2 and char. The CHAR is nothing more than a VARCHAR2 that is blank padded out to the maximum length. That is difference between the column X and Y.
The field X consumes 3 bytes – ( NULL indicator, leading byte length , 1 byte for ‘a’).

The field Y consumes 32 bytes–(NULL indicator, leading byte length, 30 byte for ‘a ’).

Need to consider the below points when we use the CHAR data type:

1. The a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments.

2. Another important reason to avoid CHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. The Below scripts proves that.

SQL@ORA9i> create table t
2 ( char_column char(20),
3 varchar2_column varchar2(20)
4 );

Table created.

SQL@ORA9i> insert into t values ( 'Hello World', 'Hello World' );

1 row created.

SQL@ORA9i> select * from t;

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

SQL@ORA9i> select * from t where char_column = 'Hello World';

CHAR_COLUMN VARCHAR2_COLUMN
------------ --------------------
Hello World Hello World

SQL@ORA9i> select * from t where varchar2_column = 'Hello World';

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

The above result looks like identical but, in fact, some implicit conversion has taken place and the CHAR(11) literal ‘Hello World’ has been promoted to a CHAR(20) and blank padded when compared to the CHAR column. The reason is, ‘Hello World ’ is not the same as ‘Hello World’ without the trailing spaces. We can confirm that these two strings are different.

SQL@ORA9i> select * from t where char_column = varchar2_column;
no rows selected

They are not equal to each other. We would have to either blank pad out the VARCHAR2_COLUMN to be 20 bytes in length or trim the trailing blanks from the CHAR_COLUMN, as follows:

SQL@ORA9i> select * from t where trim(char_column) = varchar2_column;

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

SQL@ORA9i> select * from t where char_column = rpad( varchar2_column, 20 );

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
The problem arises with applications that use variable length strings when they bind inputs, with the resulting “no data found”


SQL@ORA9i> variable varchar2_bv varchar2(20)
SQL@ORA9i> exec :varchar2_bv := 'Hello World';

PL/SQL procedure successfully completed.

SQL@ORA9i> select * from t where char_column = :varchar2_bv;

no rows selected

SQL@ORA9i> select * from t where varchar2_column = :varchar2_bv;

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
The above search for VARCHAR2 string worked but not for CHAR. The VARCHAR2 bind variable will not be promoted to a CHAR(20) in the same way as a character string literal. At this point, many programmers form the opinion that “bind variables don’t work; we have to use literals.” That would be a very bad decision indeed.

The solution is to bind using a CHAR type:

SQL@ORA9i> variable char_bv char(20)
SQL@ORA9i> exec :char_bv := 'Hello World';

PL/SQL procedure successfully completed.

SQL@ORA9i> select * from t where char_column = :char_bv;

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- -------------------
Hello World Hello World

SQL@ORA9i> select * from t where varchar2_column = :char_bv;

no rows selected
We will be running into this issue constantly if we mix and match CHAR and VARCHAR.

Read More