Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the use of virtual columns in oracle11g. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Due to the low efficiency of a previous sql, a variety of writing methods have been tried. Although the execution plan is excellent, the execution efficiency is still very low (the result requires more than 3 seconds). The data volume of the table itself is about 320W, and the statistics of the whole table. In fact, the business requirements themselves are not complex to understand, that is, to judge a column of a table and count the number of this column in different ranges. So remember that this business can use a virtual column to calculate the value of this column.

Virtual column introduction: virtual column is a new feature introduced by oracle11g, it is based on the current table column values calculated from other columns, this column data is not stored in the data file, it is only stored as an expression in the data dictionary, therefore, this column can not be DML operation, in addition, virtual columns can not refer to virtual columns.

You can do the following in the virtual column:

1. Indexes can be built on virtual columns

2. Constraints can be established on virtual columns

3. Partition operations can be performed based on virtual columns.

The table structure is as follows:

Create table people (id varchar2 (32), name varchar2 (10), id_card varchar2 (40), medisecu varchar2 (50)

Comment on column people.name is' name'

Comment on column people.id_card is'ID card number'

Comment on column people.medisecu is' type of insurance'

After inserting test data:

Select * from people

The business needs are as follows, statistics on how many people have bought single insurance and how many people have bought more insurance. Therefore, I would like to have a separate column in this table to judge whether the person's insurance is single insurance or multi-insurance.

This requirement requires the use of custom functions to establish user-defined functions:

Create or replace function fn_medisecu (i_id in varchar2,i_medisecu in varchar2 default null)

Return number deterministic-- oracle requires that for user-defined functions, the deterministic of the function must be declared.

As

The data type v_count pls_integer:=0;---pls_integer is worth paying attention to, and its efficiency is higher than that of number,pls_integer and number. For introduction of numerical types, please move to http://blog.itpub.net/30485601/viewspace-2151857/.

Begin

Select count (people_id)

Into v_count

From diagninfo

Where people_id=i_id and en_disease_code is not null and disease_jzlx in (1, 2, 3, 4);-- this is a condition that this person needs to meet.

If i_medisecu is not null and v_count0

Then

If itemized dispatch 10'

Then return 0

Else

If length (replace (itemmedisecujime')) = 1muri-single insurance

Then return 1

Elsif length (replace (itemmedisecujime')) > 1 Murray-multiple Insurance

Then return 2

End if

End if

Else

Return 0

End if

End

Create a virtual column:

Alter table people add vir_medisecu number generated always as (fn_medisecu (id,medisecu)) virtual;--, where generated and always are optional keywords, can be written or not, there is little difference. If you ignore the data type of the virtual column, oracle will determine the data type of the virtual column based on the final data type of the expression result after as.

Index on the virtual column and collect statistics:

Create index people_vir_medisecu on people (vir_medisecu)

Begin

Dbms_stats.gather_table_stats (ownname = > 'QJ',tabname = >' PEOPLE')

End

Begin

Dbms_stats.gather_index_stats (ownname = > 'QJ',indname = >' PEOPLE_VIR_MEDISECU')

End

Again, the statistics of single insurance and multi-insurance are carried out, and the result is reduced to 0.2s.

Issues to pay attention to in virtual columns:

1. The use of virtual columns will bring other problems. Tables containing virtual columns cannot omit the column list during the insert operation. Therefore, you must make sure with the developer that all insertions to the virtual list are fully column, otherwise the program will report an error.

2. You cannot use create table as select to create a table that contains virtual columns. You can only add virtual columns again after creating the table.

This is the end of this article on "what is the use of virtual columns in oracle11g?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.