Oracle temporary table optimization query speed -- Aspx. CN technical data center operating system program design graphics image media animation office software database certification trainingWeb Development Network Principles network management network setup mechanical electronic Routing Technology
Current location: Aspx. cn Technical Information Center homepage
For permission control in A project, you need to use the Organization class to control the data that can be accessed. For example, the person in organization A can view the data of the personnel in its subordinate organization, or only organization A is organization B.
For permission control in A project, you need to use the Organization class to control the data that can be accessed. For example, the person in organization A can view the data of the personnel in its subordinate organization, or
The syntax error caused by the field with the same name as appears in the table. Proportional to the following statement: SELECT E.empno, E.ename, E.job, E.sal, E.deptno, D.name From EMP E, Dept D WHERE E.deptno = d.deptno and e.deptno = 20; 3. Replace distinct with exists When querying between tables in a one-to-many relationship, assume that you want to reject the repeated rows in the results. The ability to consider replacing distinct with exists (combined subquery). Like what: Select Deptno
the system tables. Then insert.24. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.25. Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.26. Before using a cursor-based method or temporal table method, you should fi
insert. 24. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables. 25. Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data. 26. Before using a cursor-based method or temporal table method, you should first look for a set-ba
Oracle optimization query Skills 1. connection sequence in WHERE clause: ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the connection between tables must be written before other WHERE conditions, the conditions that can filter out the maximum number of records must be writte
oracle| temporary table | speed | optimization
1. Preface
At present, all use Oracle as database support platform application, most of the data volume of the system, that is, table data volume is generally in millions above data volume. Of course it's a good choice to create partitions in Oracle, but when you find that
Oracle Query Optimization
1. Reasonably sort query Conditions
Oracle uses the bottom-up sequence to parse WHERE data. From the perspective of performance optimization, we recommend that you write the conditions that can filter out
Oracle Database query performance optimization
Querying, adding, modifying, and deleting databases are important factors that affect web application performance indicators. The following are some common suggestions to improve query performance.
1. Optimize JDBC connections
The database connection pool mechanism can be
Description: Usually very little contact with big data paging, today is interested in the database insert 10 million data to test, after the query related information to get the following description:The author has a millions record in the work of the table, in the JSP page to the table to be paged display, then consider using rownum to do, the following is the specific method (each page shows 20):Statement: "SELECT * from TabName where rownumBut foun
;end;
The running time of a hash Partition Table is 46.156 s, 33.39 s, 40.516 s 34.875 s, and 38.938 s, respectively.
BeginInsert into t_rankSelect object_id,Rank () over (partition by object_type order by object_id) r_object_id,Rank () over (partition by object_type order by subobject_name) r_subobject_name,Rank () over (partition by object_type order by created) r_created,Rank () over (partition by object_type order by last_ddl_time) r_last_ddl_time,Rank () over (partition by
= ', there is no result set
- SELECTEname,translate (ename,'LE',NULL) fromEmp--when C is null or ' ', the return value is null instead of '
- --translate can be used to delete some useless information, such as the name of a number is generally not happening, if any, you can use the following statement to remove
the SELECTTranslate (ename,'-1234567890','-') fromEmp--refer to the third basic usage, this statement is to remove the number
- --handling Null values sort NULLS first/nulls last
- S
">
2. Merge into statement:
For the top statement, you can use the merge into statement
--Merge into implementation method
merge into EMP
using (SELECT * from
emp_new1
) E on
(e.empno = emp.empno)
when matched then
update set emp.empdesc = E.empdesc;The execution results are the same as the results in 1, assuming inference. It is recommended to use the merge into method.
Because merge into only visits once emp_new1.
Iii. Deleting delete op
Oracle Performance Optimization Operation 7: Index improves query efficiency when data distribution is uneven
Index selection is low, but the data value distribution is very different, you can still use the index to improve efficiency.
A. In special cases where the data distribution is uneven, the index with low selectivity should also be created.
The ServiceI
The conditions in the WHERE clause in the execution order of Oracle are always precedence over other conditions, and the higher the right, the higher the precedence, including the group BY and ORDER BY clauses.According to the order of execution, we can summarize many laws that can optimize the query:1, will be able to filter out the minimum entries of the conditions at the far right of the Where condition2
?1. Principles of Index use?Do not use full blur on the index, but like ' asdf% ' is possible, that is, do not contains, available StartwithDo not perform functions on the index, expression operations, or use is null to determine, otherwise the full table scan will be usedDoes not equal also causes a full table scan, using column If one column has an index and the other has no index, then C1 or C2 will cause a full table scan;A composite index query,
operation is the same as in 1, and the merge into method is recommended if you make a decision. Because merge into accesses only one emp_new1.
Iii. Deleting delete operations
1. Delete duplicate records:
There are many kinds of methods, here only one, using the analysis function grouping, determine whether the group number is greater than 1.
Delete from emp where rowid in (select Rid from (select rowID as RIDs, row_num
the sort space, and finally returns the result set.
If the amount of table data is large, it may result in sorting by disk.
Recommendation: Use the union ALL operator instead of union because the union all operation simply merges two results and returns.
32. As far as possible to avoid hermit type conversion, easy to cause the failure of the Oracle index many reasons:
1) and use functions on the indexed columns. such as SUBSTR,DECODE,INSTR, and so
Oracle Query optimization
1> Select the most efficient table name order: Put a table with fewer records on the last side of the FROM clause. 2. If there are more than 3 table join queries, then you need to select the Crosstab table (intersection table) as the underlying table, which refers to the table that is referenced by the other tables. Cause: The
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.