Oracle Novice with notes 9

Source: Internet
Author: User

1. You cannot delete some rows directly in the view, because the view is a virtual table, and to delete rows of data in the view, it is equivalent to deleting the base table, so it can only be removed from the base table.

2. Instead of triggers can only be created on views, cannot have before or after options

3. System triggers can only be built on the schema (schema) or database


4. When a trigger has a calling function, procedure, or package, and these functions, procedures or packages are modified or deleted, the state of the trigger is marked as invalid.
You can recompile a trigger that is already common, with the following statements:
Alter TRIGGER [schema.] Trigger_name compile;

5. Delete trigger requires system permissions for drop any trigger (this is to delete the trigger that is built on the schema)
Deleting triggers requires system permissions for Adminster Database trigger (this is to delete triggers that are built on the database)

6. Alter TRIGGER trigger_name [disable|enable], which can only modify the state of a trigger
ALTER TABLE table_name [disable|enable] All trigger this is modifying a trigger on a table, modifying multiple triggers

7. A DDL statement cannot be invoked directly in a PL/SQL block, so the exec_ddl_statement procedure in Oracle's built-in package dbms_utilify is used to create the trigger from which it executes the DDL statement.


8. Table type definition: The type variable name is the table of type index by Binary_integer;
Cursor Type: Type cursor variable name is REF CURSOR;
Record type: type name is record (variable 1, ...). )
Array type: Type array name is Varray of types


Two. Dynamic SQL Summary:

9. If a select Into,fetch into or returning into clause references a collection, you should use the bulk collect clause to merge
1) Dynamic SQL refers to DDL and indeterminate DML (that is, DML with parameters)
2) The binding parameter list is the input parameter list, which is the type in type, which is bound at run time with the parameters in the dynamic SQL statement (actually placeholders, which can be understood as the formal parameters inside the function).
3) The output parameter list is the list of parameters returned after the dynamic SQL statement executes.
4) Since dynamic SQL is determined at runtime, it can lose some system performance in exchange for its flexibility relative to static.
Example:
Create or Replace procedure Find_info (p_id number) as
V_name VARCHAR2 (10);
V_salary number;
Begin
Execute Immediate '
Select Name,salary from emp
where Id=:1 '
Using p_id
returning into V_name,v_salary; --Dynamic SQL for Query statements
Dbms_output.put_line (V_name | | ' The income is: ' | | To_char (v_salary));
exception
When others then
Dbms_output.put_line (' No corresponding data found ');
End Find_info;
Note: The dynamic SQL statement in procedure two uses the placeholder ": 1", which is actually equivalent to the formal parameter of the function, using ":" As the prefix,
The using statement is then used to replace the p_id at run time: 1, where p_id is equivalent to the argument in the function.
In addition, the cursor opened in the process three is a dynamic cursor, it also belongs to the category of dynamic SQL, its entire compilation and development process is similar to execute immediate execution, here is not to repeat.


Three. Here is the use of forall for the approval of the series, which will be a summary of the situation of the batch-processing:
1) If a statement such as Insert,delete,update within a loop references a collection element, you can move it to a forall statement.
2) If a select Into,fetch into or returning into clause references a collection, it should be merged using the bulk collect clause.
3) If possible, use a host array to pass parameters between the program and the database server.
Example:
Declare
Type num_list is Varray (a) of number;
v_id num_list: =num_list (100,101);
Begin
...
ForAll i in V_id.first. V_id.last Loop
...
Execute immediate ' update emp
Set =salary*1.2
where Id=:1 '
Using v_id (i);
End Loop;
End (Loops are forall instead of for)

ForAll and bulk collect are two important ways to implement bulk SQL

The difference is a for-loop select into line-by-row extraction and binding to a record variable, while bulk collect extracts all rows at once and binds to the record variable. That is, batch binding.

The BULK COLLECT clause retrieves the result in bulk, binding the result set to a single collection variable at a time and sending it from the SQL engine to the PL/SQL engine. You can usually use bulk in the select INTO, FETCH into, and returning into clauses COLLECT


Second, ①, limit the amount of fetch data using limits
When using the bulk COLLECT clause, for collection types such as nested tables, union arrays are automatically initialized and extended (as in the example below). Therefore, if you use the bulk collect clause to manipulate the collection, you do not need to initialize and extend the collection. Because of the bulk characteristics of the bulk collect, if the amount of data is large and the collection is automatically extended at this time, to avoid the performance degradation caused by too large datasets, use the limit clause to limit the amount of data fetched at one time. The limit clause allows only the bulk of the fetch operation statement to appear.
Usage: FETCH ... BULK COLLECT into ... [Limit rows]--Limit the number of rows returned at one time

②, using bulk collect to return a value to a set variable does not need to be added after the set variable (i), because this is a batch processing

Declare
Type V_EMP1 is record (V_ename emp.ename%type,v_empno emp.empno%type,v_deptno emp.deptno%type);
Type v_emptable is table of V_EMP1 index by Binary_integer;
V_delete v_emptable;
Begin
Delete from EMP where deptno=10--note that the delete and returning into statement here is a sentence that will delete
Returning Ename,empno,deptno bulk collect into v_delete; The columns in the row are returned to the variable, and note that there is no semicolon behind the delete
If V_delete.count >0 Then
For I in V_delete.first. V_delete.last Loop
Dbms_output.put_line (V_delete (i). V_ename | | v_delete (i). V_empno | | v_delete (i). V_deptno);
End Loop;
End If;
End
/

9. Fetch (bulk collect) into
Returning (bulk collect) into
Select (Bulk collect) into

Execute immediate (when using dynamic SQL), where the statement is available, for example in the For loop, begin subject sentence;

11. Parsing functions cannot be nested, but you can use subqueries to implement nested query results, such as: The syntax of a lag (First_value (comm,1), 1) clause is wrong.

The AWR report is a performance collection and analysis tool provided under Oracle 10g that provides a report of the overall system resource usage over a period of time, through which we can understand the entire operation of a system, which is like a person's comprehensive medical report

13. The optimizer (CBO) is an extremely complex software, its main input has three: statistics, environment-related configuration, SQL itself its working essence is to compute the fastest way to get the specific data set specified by the SQL statement;
(1). The collection of statistical information is not synchronized with the data itself, so the statistical information is also a certain time lag, which will lead to the CBO will be based on the old statistical information to determine the implementation plan,
This is the first reason for the instability of the execution plan.
(2). Changes in the operating environment, such as the number of CPUs, the environment has changed, and the CBO may come up with a new execution plan.
(3). For the same SQL statement, if the value of the view or bound variable passed in is changed, then the execution plan will change accordingly.

14. Histogram: It allows the CBO to know the exact distribution of the data is not uniform, general CBO know the statistics straight is approximate, for example, a table has 100 rows, there are 10 distinct values, this is the CBO know the statistics, but do not know the details inside, in the absence of a histogram, It defaults to the fact that no matter what value you take to the (100/10) line, it's wrong, because, this 100 line may be 1 ... The 89 numbers were the same, and the last 9 figures accounted for 91 rows.

15. The peering of a bound variable: The system will allow the CBO to see the value of the first pass of the bound variable, and then decide on the execution plan based on the histogram, the problem is, only allow you to see the first time, when the previous bound variable is used to pass the value, the CBO will default to select the first execution plan, This is the biggest problem. For example: A table has 99 rows y, there are 1 rows of x, the first time to use the binding variable is to find the Y value, then the full table scan will be selected, the second pass x value, you will also select Full table scan, which affects the performance of the query.
Note: We can turn off the binding variable peering feature by setting the parameter: _optim_peek_user_binds=false

SQL Plan directives I understand that it is used to prompt the CBO for possible or incorrect statistics, such as the expected return row Erow is 20, but the actual return row is 10000, which is the SQL execution plan that reminds the CBO that this statistic is inaccurate, and the next time you execute this SQL, The CBO will re-parse this SQL to get a better execution plan

17. Binding variable snooping and statistical changes are the most important causes of program instability.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Novice with notes 9

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.