Use the char type in Oracle 11g

Use the char type in Oracle 11g

In Oracle data tables and program types, character types may be the most commonly used. Historically, Oracle first introduced a fixed-length char field, and then launched varchar2. Currently, mainstream Oracle Application Development has accepted varchar2 as the data table field type representation. If there are extra-long characters, you should consider using large objects such as CLOB for storage.

However, in practice, we will still find many char fields in various legacy systems. In addition, the char type often appears in the downstream systems associated with these legacy systems. In this context, developers and O & M personnel still have many opportunities to access char.

The largest feature of Char is fixed length storage. For example, the defined length is char (10), and the saved string is 'kkk 'with three-digit length. When saving, Oracle will automatically fill in spaces at the end of the bucket. This ensures that each field stored in char (10) is 10 characters in length. According to my estimation, the char strategy is a compromise strategy proposed in the context of Oracle's weak storage space management capabilities. Then, the emergence of varchar2 gradually replaced this alternative.

In some cases, the author may still encounter some friend problems: when the full table is retrieved, the string value is xxx, but when SQL is used to add the where condition, there is no search result, it seems like "hell ". The root cause is that char is often "messy" in it. After the where condition, add a number of spaces or use the rpad command to complete the problem.

This article describes several common application scenarios in 11g. Note: with the upgrade of the version, Oracle's support for char is also constantly upgraded (silently), so as to bring it closer to the effect of varchar2. Therefore, the experiment content in this article may have different test results in other versions. Note that.

1. Environment Description

This article uses Oracle 11gR2 for testing. The specific version is 11.2.0.4.

SQL> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

Create an experiment data table T.

SQL> create table t (chr_a char (100), vchar_a varchar2 (100 ));

Table created

SQL> desc t;

Name Type Nullable Default Comments

-------------------------------------------

Chr_a-char (100) Y

VCHAR_A VARCHAR2 (100) Y

SQL> insert into t select owner, owner from dba_objects;

119498 rows inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats (user, 't', cascade => true );

PL/SQL procedure successfully completed

The number of records with the data value 'Scott 'is 6.

SQL> select count (*) from t where vchar_a = 'Scott ';

COUNT (*)

----------

6

2. Constant where condition test

First, let's test the situation of writing constant conditions separately.

SQL> select count (*) from t where chr_a = 'Scott ';

COUNT (*)

----------

6

SQL> select length (chr_a) from t where chr_a = 'Scott ';

LENGTH (CHR_A)

-------------

100

100

100

100

100

100

6 rows selected

The preceding two SQL statements show that when a where statement using constants as the condition is used, even if the field type is char, the system will automatically perform the "fill" action of the right space to satisfy the char condition, obtain the correct result. With the length function, we can also determine the length to 100.

I have always wanted to understand the process of "Stealing days and changing days", and I cannot see the actual situation from the execution plan perspective.

SQL> explain plan for select count (*) from t where chr_a = 'Scott ';

Explained

SQL> select * from table (dbms_xplan.display (format => 'advanced '));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

---------------------------------------------------------------------------

| 0 | select statement | 1 | 101 | 510 (1) | 00:00:07 |

| 1 | sort aggregate | 1 | 101 |

| * 2 | table access full | T | 11 | 1111 | 510 (1) | 00:00:07 |

---------------------------------------------------------------------------

Query Block Name/Object Alias (identified by operation id ):

-------------------------------------------------------------

1-SEL $1

2-SEL $1/T @ SEL $1

Outline Data

-------------

/* +

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

BEGIN_OUTLINE_DATA

FULL (@ "SEL $1" "T" @ "SEL $1 ")

OUTLINE_LEAF (@ "SEL $1 ")

ALL_ROWS

DB_VERSION ('11. 2.0.4 ')

OPTIMIZER_FEATURES_ENABLE ('11. 2.0.4 ')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id ):

---------------------------------------------------

2-filter ("CHR_A" = 'Scott ')

Column Projection Information (identified by operation id ):

-----------------------------------------------------------

1-(# keys = 0) COUNT (*) [22]

39 rows selected

From the execution plan, we don't see much clues. The chr_a value is not specially processed in the filter condition. This process is actually special for the char type. What if we add spaces after the where condition?

SQL> select count (*) from t where chr_a = 'Scott ';

COUNT (*)

----------

6

SQL> select count (*) from t where chr_a = 'Scott ';

COUNT (*)

----------

6

You can also find the result correctly. The corresponding execution plan is:

SQL> explain plan for select count (*) from t where chr_a = 'Scott ';

Explained

SQL> select * from table (dbms_xplan.display (format => 'advanced '));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

---------------------------------------------------------------------------

| 0 | select statement | 1 | 101 | 510 (1) | 00:00:07 |

| 1 | sort aggregate | 1 | 101 |

| * 2 | table access full | T | 11 | 1111 | 510 (1) | 00:00:07 |

---------------------------------------------------------------------------

Query Block Name/Object Alias (identified by operation id ):

-------------------------------------------------------------

1-SEL $1

2-SEL $1/T @ SEL $1

Outline Data

-------------

/* +

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

BEGIN_OUTLINE_DATA

FULL (@ "SEL $1" "T" @ "SEL $1 ")

OUTLINE_LEAF (@ "SEL $1 ")

ALL_ROWS

DB_VERSION ('11. 2.0.4 ')

OPTIMIZER_FEATURES_ENABLE ('11. 2.0.4 ')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id ):

---------------------------------------------------

2-filter ("CHR_A" = 'Scott ')

Column Projection Information (identified by operation id ):

-----------------------------------------------------------

1-(# keys = 0) COUNT (*) [22]

39 rows selected

There is no change in the execution plan.

The above experiment tells us how to use a char constant: If the constant value is used after the where condition, Oracle will automatically fill in the Space action according to the field type and constant conditions. Spaces manually added after a constant are also processed as automatic spaces.

What is the effect of the rpad function on constants?

3. Function operation results

In some earlier versions of Oracle, some developers have widely used the rpad function to avoid the influence of char spaces and automatically add spaces.

SQL> select count (*) from t where chr_a = rpad ('Scott ', 100 ,'');

COUNT (*)

----------

6

Normally, you can find the result. In the Execution Plan, the filter part clearly calculates the function value before processing.

SQL> explain plan for select count (*) from t where chr_a = rpad ('Scott ', 100 ,'');

Explained

SQL> select * from table (dbms_xplan.display (format => 'advanced '));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------

(Space reasons, omitted ......)

Predicate Information (identified by operation id ):

---------------------------------------------------

2-filter ("CHR_A" = 'Scott

')

Column Projection Information (identified by operation id ):

-----------------------------------------------------------

1-(# keys = 0) COUNT (*) [22]

40 rows selected

However, if you set the length of the function to be completed, it is not "One step in place", but part of the length. Oracle cannot find the corresponding result.

SQL> select count (*) from t where chr_a = rpad ('Scott ', 10 ,'');

COUNT (*)

----------

0

SQL> select count (*) from t where chr_a = rpad ('Scott ', 50 ,'');

COUNT (*)

----------

0

The auto-completion action when constants are used is no longer effective! This indicates that the optimizer has some special rule logic when processing constants, which will not be used after applying functions. Another possibility is that when type matching is performed for values at both ends of the equal sign, the value of constants and function quantities is different. Although rpad returns a character type, changing the length to a fixed length is not easy to determine. If Oracle recognizes constants as char and rpad results as varchar2, it is understandable that the results are different.

4. Bind Variable Processing

The SQL optimizer is currently one of the most complex components in Oracle. Many of these steps and logic require continuous testing and reasonable speculation. Variable binding is one of the technologies that we often use in actual development. Next we will test the use of variable binding in PL/SQL code snippets.

SQL> declare

(2 a char (100 );

3 coun number;

4 begin

5 a: = 'Scott ';

6 execute immediate 'select count (*) from t where chr_a =: 1'

7 into coun

8 using;

9 dbms_output.put_line ('result Is: '| to_char (coun ));

10 end;

11/

Result Is: 6

PL/SQL procedure successfully completed

One of the key issues with variable binding is the type of variable binding. In the code above, if a char-type variable is used, although there is no space after the value is defined, Oracle still sets the "auto-completion action" in the constant variable in advance ".

What if the value assignment ends with a space like a constant?

SQL> set serveroutput on size 10000;

SQL> declare

(2 a char (100 );

3 coun number;

4 begin

5 a: = 'Scott ';

6 execute immediate 'select count (*) from t where chr_a =: 1'

7 into coun

8 using;

9 dbms_output.put_line ('result Is: '| to_char (coun ));

10 end;

11/

Result Is: 6

PL/SQL procedure successfully completed

The results are the same. Then, we can draw a conclusion: If a variable of the char type is used, the effect is the same as that of a constant. So what is the effect if you bind a variable of the varchar2 type?

SQL> declare

2 a varchar (100 );

3 coun number;

4 begin

5 a: = 'Scott ';

6 execute immediate 'select count (*) from t where chr_a =: 1'

7 into coun

8 using;

9 dbms_output.put_line ('result Is: '| to_char (coun ));

10 end;

11/

Result Is: 0

PL/SQL procedure successfully completed

SQL> set serveroutput on size 10000;

SQL> declare

2 a varchar (100 );

3 coun number;

4 begin

5 a: = 'Scott ';

6 execute immediate 'select count (*) from t where chr_a =: 1'

7 into coun

8 using;

9 dbms_output.put_line ('result Is: '| to_char (coun ));

10 end;

11/

Result Is: 0

PL/SQL procedure successfully completed

If the variable to be bound is varchar2, the auto-completion effect does not occur! It has nothing to do with the problem with the previous function. If we use the rpad function in the char type to assign values, we can automatically complete the results.

SQL> set serveroutput on size 10000;

SQL> declare

(2 a char (100 );

3 coun number;

4 begin

5 a: = rpad ('Scott ', 10 ,'');

6 execute immediate 'select count (*) from t where chr_a =: 1'

7 into coun

8 using;

9 dbms_output.put_line ('result Is: '| to_char (coun ));

10 end;

11/

Result Is: 6

PL/SQL procedure successfully completed

The results are the same as expected.

5. Conclusion

Based on the above experimental analysis, we can draw the following conclusions:

• For the char type, the optimizer will have certain optimization actions, mainly in the Automatic completion of spaces at the end;
• However, the condition for Automatic completion is type matching, that is, the variable type must be char type during comparison;
• If it is of the varchar2 type, the auto-completion action will not appear;
• The return values of many string-related functions are of the varchar2 type.