The impact of SQL view on performance

The

has recently captured some column SQL statements in the production library ADDM, most of which can be done through index optimizations, but the following SQL statement is very difficult. Through the index optimization effect has been not ideal, and finally further analysis of the statement that the upper layer of the sentence with X query out the ACH large data table of all the fields (millions of records, 50 field tables), and we only used a few fields in SQL, and decided to discard the view, Present the relevant logic in the view directly in the upper SQL. Through this action, the query cost of the key table is reduced greatly, and can be optimized efficiently.

Problem SQL statement: Select Count (Distinct e.entry_id) from V_ace e Where E.agent_code =: B8 and not Exists (select ed.entry_id to AED Ed Where e.entry_id = ed.entry_id and Ed.deleted_flag = ' 0 ') and (: B7 is Null Or e.entry_ Id =: B7) and (: B6 is null or E.container_num =: B6) and (: B5 = ' 0 ' Or e.decl_port =: B5) and (: B4 is null or Trunc (e.d_date) >=: B3) and (: B2 is Null Or Trunc (e.d_date) <=: B1) Let's look at the view: v_ace Create Or Replace View V_ace as Select tmp.*, Status.cus_operation_status, Status.control_type, Status.data_source, STATUS.C Ustoms_mode, Status.predigest_mode, Status.goods_type, Status.risk_type, Status.control_date, STATUS.E  _supervision_type from (select RowNum as id,head.*,con.container_num to AEC t inner join Aeh head On t.entry_id = head.entry_id inner join AC con on t.container_id = con.id and head.deleted_flag= ' 0 ' and con   . deleted_flag= ' 0 ') tmp 
         Left join AESN the status on tmp.entry_id=status.entry_id the order by tmp.id ASC---------------------------- ----------------------------------------------------Execution Plan------------------------------------------------------- -------------------------[Execution plan information]------------------------------------------------- -----------------------------------------------------------------------    
| Operation |  Phv/object Name | Rows | bytes|    
Cost | ---------------------------------------------------------------------------------------------------------------       ---------|000[000]select STATEMENT |----2192985071.0----|      |  |    
10761 |                               |001[001]sort GROUP by |     |   1 |        89 |    
|                             |002[002] NESTED LOOPS OUTER       |     |   1 |  89 |    
10761 |                               |003[003] HASH JOIN Right ANTI |     |   1 |  70 |    
10760 | |004[004] INDEX FAST full SCAN |   Idx_a_e_c |   685k|    13m|    
841 |                               |005[004] VIEW |   |   386k|   18m|    
7675 |                               |006[005] COUNT |       |      |        |    
|                               |007[006] HASH JOIN |   |   386k|   29m|    
7675 | |008[007] INDEX FAST full SCAN |   Idx_test3 |   780k|    14m|    
830 |                               |009[007] HASH JOIN |   |   385k|   22m|    
4289 |             |010[008] INDEX FAST full SCAN               |   Idx_a_e_h |   362k|   13m|    
1346 | |011[008] TABLE ACCESS Full |   AEC |   757k|    15m|    
766 | |012[003] INDEX UNIQUE SCAN |     Pk_a_e_s_n |   1 |      19 |    
1 |    
         
        
------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------Optimization Method------------------------ --------------------------------------------------------1, new indexed CREATE index idx_adcphc_container_3 on Adcphc_cont    
  Ainer (Deleted_flag, ID, container_num) tablespace tbl_index pctfree 2 Initrans 255    
        
Storage (initial 64K minextents 1 Maxextents Unlimited); 2. Rewrite SQL statement Select Count (Distinct head.entry_id) from AEC t iNner join A_e_h head in t.entry_id = head.entry_id and head.deleted_flag= ' 0 ' and head. Agent_code =: B8 and (: B7 is Null Or head. entry_id =: B7) and (: B5 = ' 0 ' Or head. Decl_port =: B5) and (: B4 are null or Trunc (head.d_date) >=: B3) and (: B2 is null or Trunc (head.d_date) <= : B1) INNER join AC con on t.container_id = con.id and con.deleted_flag= ' 0 ' and (: B6 is Null Or con. Container_num =: B6) LEFT join AESN status in head.entry_id=status.entry_id and not Exists (Select ed.entry_i D from AED, Ed Where head. entry_id = ed.entry_id and Ed.deleted_flag = ' 0 ')------------------------------------------------------ --------------------------expect the optimized execution plan--cost from 10761 to 1462, increasing efficiency by 7 times times on the basis of index optimization---------------------------------------- ----------------------------------------SELECT STATEMENT, GOAL = all_rows cost=1462 C    
 Ardinality=1 bytes=81 SORT GROUP by Cardinality=1 bytes=81 NESTED LOOPS OUTER cost=1462 cardinality=1 bytes=81 NESTED LOOPS                                        cost=1460 Cardinality=1 bytes=81 NESTED LOOPS cost=1458 Cardinality=1 bytes=62 INDEX FAST Full SCAN object Owner=operation object name=idx_a_e_ H_m cost=1456 cardinality=1 bytes=40 INDEX RANGE SCAN Object Owner=operation Object name=idx_a _e_c cost=2 cardinality=1 bytes=22 INDEX RANGE SCAN Object Owner=operation Object Name=id X_test3 cost=2 cardinality=1 bytes=19 VIEW Object owner=sys Cos t=2 cardinality=1 FILTER INDEX UNIQUE SCAN Object Owner=operation Object Name=pk_a_e_s_n cost=2 cardinality=1 bytes=19 INDEX RANGE SCAN Object owner=operation obje CT name=idx_adcphc_entry_declare cost=3 Cardinality=1 bytes=21