Model Management for new features of Oracle 11G (1)

DDL Wait options

JillAcme Retailers DBA) tries to change the table named SALES and add a column of TAX_CODE to it. This is a common task; she executes the following SQL statement:

SQL> alter table sales add (tax_code varchar2(10));

However, she received the following message instead of "Table altered:

alter table sales add (tax_code varchar2(10))*ERROR at line 1:ORA-00054: resource busy and acquire withNOWAIT specified or timeout expired

The error message describes that the table may currently be used by a transaction, so it is unlikely to obtain the exclusive lock for the table. Of course, the rows in the table will not be locked forever. When the session executes the commit action, it will release the lock on these rows, but before that, due to the long release period, other sessions may update other rows in the table-in this way, the time to obtain the table's exclusive lock disappears. In a typical business environment, window locking tables in an exclusive manner opens regularly, but DBA may not be able to execute the alter command at that time.

Of course, Jill can also repeatedly type the same command until it gets an exclusive lock or fails to get the same command first ).

In Oracle Database 11 GB, Jill has a better choice: DDL Wait option. She can execute the following command:

SQL> alter session set ddl_lock_timeout = 10; Session altered.

Now, if the DDL statement in the session is not exclusively locked, no error message is displayed. Instead, it will wait for 10 seconds. Within these 10 seconds, it will retry the DDL operation until the operation succeeds or times out ). If you execute the following command:

SQL> alter table sales add (tax_code varchar2(10));

This statement will be suspended and no error message will be displayed. In this way, Jill outsourced repeated attempts to the Oracle database for 11 GB, just like calling a program to retry busy numbers), without having to repeat attempts to obtain the elusive exclusive lock-up time.

Now, Jill enjoys this feature and shares it with all other DBAs. Since everyone encountered the same problem when changing tables during system busy periods, they found this new feature very helpful. Therefore, Jill wants to know whether it can set this behavior as the default behavior, so that it does not need to execute the alter session Statement every time?
Yes, yes. If you execute alter system set DDL_LOCK_TIMEOUT = 10, the session will automatically wait for this period of time during the DDL operation. Like any other alter system statement, this statement can be overwritten by the alter session Statement.

Add columns with default values

Despite being satisfied with this feature, Jill is still considering another issue related to the first issue. She wants to add the TAX_CODE column, but this column cannot be empty. Apparently, when she adds a non-empty column to a non-empty table, she must also specify the default value "XX ". Therefore, she compiled the following SQL:

Alter table sales add tax_code varchar2 (20) default 'xx' not null;

But she stopped here. The SALES table is huge, with approximately 0.4 billion rows. She knows that Oracle will immediately add this column when executing this statement, but will update the value "XX" in all rows before returning control to her ". It takes not only a long time to update the 0.4 billion rows, but also to fill the restore segment, generate a large number of redo tasks, and generate great performance overhead. Therefore, Jill must be in "quiet period", that is, during the downtime) before this change can be made. Is there a better solution for Oracle Database 11 GB?

Of course. The preceding statement will not update all records in the table. Although this is not a problem for new records whose column values are automatically set to "XX", when you select this column for an existing record, this will return NULL, right?
This is not the case. When you select an existing record column, Oracle obtains the default value from the data dictionary and returns it to the user. In this way, you can define a new column as a non-empty column with a default value without any overhead of redo and restoration. Awesome!

Virtual Column

The Acme database contains a table named SALES, as shown in the preceding figure. The table structure is as follows:

SALES_ID NUMBER CUST_ID NUMBER SALES_AMT NUMBER

Some users want to add a column named SALE_CATEGORY to identify the sales type based on the sales volume and the current customer: LOW, MEDIUM, HIGH, and ULTRA. This column will help them identify the corresponding action records and route the records to relevant personnel for processing. The logic of column values is as follows:

If sale_amt is greater:	And sale_amt is less than or equal:	Sale_category is
0	1000	LOW
10001	100000	MEDIUM
100001	1000000	HIGH
1000001	Unlimited	ULTRA

Although this column is an important business requirement, the development team does not want to change the code to create the necessary logic. Of course, you can add a new column named sale_category to the table and write a trigger to fill the column with the above logic-a fairly simple operation. However, context switching with the trigger code may cause performance problems.

In Oracle Database 11 GB, you do not need to write any trigger code. You only need to add a virtual column. Virtual columns provide you with the flexibility to add columns that convey business awareness without increasing complexity or performance impact.

Follow these steps to create a table:

SQL＞ create table sales 2 ( 3 sales_id number, 4 cust_id number, 5 sales_amt number, 6 sale_category varchar2(6) 7 generated always as 8 ( 9 case 10 when sales_amt ＜= 10000 then 'LOW' 11 when sales_amt ＞ 10000 and sales_amt ＜= 100000 then 'MEDIUM' 12 when sales_amt ＞ 100000 and sales_amt ＜= 1000000 then 'HIGH' 13 else 'ULTRA' 14 end 15 ) virtual 16 );

Note that rows 6-7 are specified as "generated always as", which means that the column value is generated at runtime rather than stored as part of the table. The clause is followed by a detailed CASE statement to calculate the value. Finally, in row 15th, "virtual" is specified to enhance the fact that this is a virtual column. Now, if you insert some records:

SQL＞ insert into sales (sales_id, cust_id, sales_amt) values (1,1,100); 1 row created. SQL＞ insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500); 1 row created. SQL＞insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000); 1 row created. SQL＞ commit; Commit complete. SQL＞ select * from sales; SALES_ID CUST_ID SALES_AMT SALE_C ---------- ---------- ---------- ------ 1 1 100 LOW 2 102 1500 LOW 3 102 100000 MEDIUM 3 rows selected.

The virtual column values will be filled as usual. Even if the column is not stored, You can regard it as any other column in the table, or even create an index on it.

SQL> create index in_sales_cat on sales (sale_category); Index created.

The result is a function-based index.

SQL＞ select index_type 2 from user_indexes 3 where index_name = 'IN_SALES_CAT'; INDEX_TYPE --------------------------- FUNCTION-BASED NORMAL SQL＞ select column_expression 2 from user_ind_expressions 3 where index_name = 'IN_SALES_CAT'; COLUMN_EXPRESSION -------------------------------------------- CASE WHEN "SALES_AMT"＜=10000 THEN 'LOW' WHEN ("SALES_AMT"＞10000 AND "SALES_AMT" ＜=100000) THEN CASE WHEN "CUST_ID"＜101 THEN 'LOW' WHEN ("CUST_ID"＞=101 AND "CUS T_ID"＜=200) THEN 'MEDIUM' ELSE 'MEDIUM' END WHEN ("SALES_AMT"＞100000 AND "SALES _AMT"＜=1000000) THEN CASE WHEN "CUST_ID"＜101 THEN 'MEDIUM' WHEN ("CUST_ID"＞=101 AND "CUST_ID"＜=200) THEN 'HIGH' ELSE 'ULTRA' END ELSE 'ULTRA' END

You can even partition on this column, as described in this series partition article. However, you cannot enter a value for this column. If you try to enter a value,

An error message will be received soon:

insert into sales values (5,100,300,'HIGH','XX') * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns