The query plan and main statistical data are as follows:
Execution Plan :-----------------------------------------...... 2 1 hash join (Cost = 5 Card = 14 Bytes = 224) 3 2 table access (FULL) OF 'dept' (Cost = 2 Card = 4 Bytes = 52) 4 2 table access (FULL) OF 'emp' (Cost = 2 Card = 14 Bytes = 42) main statistics: ------------------------------------------- 305 recursive cballs 46 consistent gets create materialized view EMP_DEPT: create materialized view emp_dept build immediate refresh on demand enable query rewrite as select dept. deptno, dept. dname, count (*) from Emp, dept where emp. deptno = dept. deptno group by dept. deptno, dept. dname/run the query again. The execution plan and main statistics are as follows: Execution Plan :-------------------------------------...... 1 0 table access (FULL) OF 'emp_dept' (Cost = 2 Card = 327 Bytes = 11445) main statistics: -------------------------------------- 79 recursive cballs 28 consistent gets
It can be seen that before creating a materialized view, the system first performs a full table scan for the two tables, then performs a HASH connection, and then performs group sorting and selection. After the materialized view is created, CBO automatically converts the preceding complex operations to a full scan of the materialized view EMP_DEPT, and the related statistical data has been greatly improved. The number of recursive calls has been reduced from 305 to 79, logic I/O (CONSISTENT GETS) is reduced from 46 to 28. 4.2.3 read frequently accessed small tables into the CACHE
Logical I/O is always faster than physical I/O. If there are small tables frequently accessed by applications in the database, you can forcibly read these tables into the KEEP pool to avoid the occurrence of physical I/O. 4.3 multi-Table connection Optimization
Multi-table join operations often consume a lot of CPU time and memory, therefore, multi-table join query performance optimization is often the key and difficulty of SQL optimization. 4.3.1 eliminate external connections
By eliminating external connections, the query is easier to read and the performance is often improved. The general idea is to query in the following forms: SELECT ..., OUTER_JOINED_TABLE.COLUMN FROM SOME_TABLE, OUTER_JOINED_TO_TABLE WHERE... = OUTER_JOINED_TO_TABLE (+) can be converted to the following Query format: SELECT ..., (Select column from outer _ JOINED_TO_TABLE WHERE ...) FROM SOME_TABLE; 4.3.2 predicate, optimize intermediate results
The performance of Multi-table join is low most because the order of Join Operations and filtering operations is unreasonable. Most users always perform join operations before applying filter conditions when writing multi-table join queries, this causes the server to do too much useless work. To solve such problems, the optimization idea is to push the filter predicate as much as possible, so that records that do not meet the conditions are filtered out in advance, and only a few records that meet the conditions are connected for processing, in this way, the SQL query efficiency can be doubled.
The star model shown in shows the sales performance of goods purchased in the last three months in various sales channels. Figure 2 query the standard connection of the star model sold by the product: Select. prod_name, sum (B. sale_quant), sum (c. sale_quant), sum (d. sale_quant) From product a, tele_sale B, online_sale c, store_sale d Where. prod_id = B. prod_id and. prod_id = c. prod_id and. prod_id = d. prod_id And. order_date> sysdate-90 Group by. prod_id; Enable embedded view and Set Condition. order_date> sysdate-90 forward, the optimized code is as follows: Select. prod_name, B. tele_sale_sum, c. online_sale_sum, d. store_sale_sum From product a, (select sum (sal_quant) tele_sale_sum from product, tele_sale Where product. order_date> sysdate-90 and product. prod_id = tele_sale.prod_id) B, (select sum (sal_quant) online_sale_sum from product, tele_sale Where product. order_date> sysdate-90 and product. prod_id = online_sale.prod_id) c, (select sum (sal_quant) store_sale_sum from product, store_sale
Where product. order_date> sysdate-90 and product. prod_id = store_sale.prod_id) d,
Where a. prod_id = B. prod_id and a. prod_id = c. prod_id and a. prod_id = d. prod_id;
