And_equal and composite index optimize an SQL statement

When the system load is high, capture an SQL statement and output 152 rows of select a around 1 minute 23 seconds. case_id,. case_type_id,. case_subject,. case_content,. create_employee_id,. case_create_time,. customer_id,. dept_id,. lock_employee_id,. order_id,. order_type_id,. PRI,. customer_name,. operate_time,. case_require_finish_time,. case_status, B. employee_id, B. case_op_finish_time, C. case_type_name, D. employee_name, E. employee_name as lock_employee, F. dept_name, G. position_name, H. dept_name as create_dept_name, f_geturgencyorder (. order_id) isurgency from t_s_case a left join t_s_case_operation_record B on. case_id = B. case_op_id left join t_s_employee D on D. employee_id =. create_employee_id left join t_s_employee e on E. employee_id =. lock_employee_id left join t_s_department F on. dept_id = f. dept_id left join t_s_department h on D. de Pt_id = H. dept_id inner join t_sd_case_type C on. case_type_id = C. case_type_id left join t_sd_emplyee_position g on G. position_id =. position_id where. case_status = 0 and. position_id in (6017) order by isurgency DESC,. case_create_time,. pri desc select * from table (dbms_xplan.display) plan hash value: 3249879879 ---------------------------------------------------------------------------- ------------------------------------------ | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | bytes | 0 | SELECT statement | 19273 | 5778k | 4401 (4) | 00:00:53 | 1 | sort order by | 19273 | 5778k | 12m | 4401 (4) | 00:00:53 | * 2 | hash join right outer | 192 73 | 5778k | 3121 (5) | 00:00:38 | 3 | table access full | t_s_employee | 2018 | 28252 | 8 (0) | 00:00:01 | * 4 | hash join right outer | 19273 | 5514k | 3111 (5) | 00:00:38 | 5 | table access full | t_s_department | 84 | 1764 | 5 (0) | 00:00:01 | * 6 | hash join right outer | 19273 | bytes 9k | 3105 (5) | 00:00:38 | 7 | table access full | t_s_employee | 2018 | 36324 | 8 (0) | 00:00:01 | * 8 | hash join right outer | 19273 | 4780k | 3096 (5) | 00:00:38 | 9 | table access full | t_s_department | 84 | 1764 | 5 (0) | 00:00:01 | * 10 | hash join | 19273 | 4385k | 3090 (5) | 00:00:38 | 11 | table access full | t_sd_case_type | 17 | 323 | 6 (0) | 00:00:01 | * 12 | hash join outer | 19273 | 4027k | 3920k | 3082 (5) | 00:00:37 | 13 | merge join outer | 19273 | shard k | 754 (1) | 0 0:00:10 | * 14 | table access by index rowid | t_s_case | 19273 | 3425k | 752 (1) | 00:00:10 | * 15 | index range scan | idx_t_s_case_status | 51528 | 33 (4) | 00:00:01 | * 16 | sort join | 1 | 14 | 2 (50) | 00:00:01 | 17 | table access by index rowid | t_sd_emplyee_position | 1 | 14 | 1 (0) | 00:00:01 | * 18 | index unique scan | pk_t_sd_emplyee_position | 1 | 1 (0) | 00:00:01 | 19 | T Able access full | t_s_case_operation_record | 589k | 10 M | 1257 (6) | 00:00:16 | protected predicate information (identified by Operation ID): Limit 2-access ("e ". "employee_id" (+) = "". "lock_employee_id") 4-access ("D ". "dept_id" = "H ". "dept_id" (+) 6-access ("D ". "employee_id" (+) = "". "create_employee_id") 8-access ("". "dept_id" = "F ". "dept_id" (+) 10-access ("". "case_type_id" = "C ". "case_type_id") 12-access ("". "case_id" = "B ". "case_op_id" (+) 14-filter ("". & quot; position_id & quot; = 6017) 15-access (& quot; A & quot ". "case_status" = 0) 16-access ("G ". "position_id" (+) = "". "position_id") filter ("G ". "position_id" (+) = "". "position_id") 18-access ("G ". "position_id & Quot; (+) = 6017 & quot) ========================================================== ============================== analysis: select count (*) from t_s_case where case_status = 0 -- 56869 select count (*) from t_s_case where position_id in (6017) -- 783131 both columns have indexes on them, the execution plan uses the index above case_status. My first response was to create a composite index for the two columns, but later I found that case_status already exists in another composite index, to access a table with these two indexes at the same time, only the hints of and_equal can be used. In CBO, two indexes are not selected for access from the same table. ========================================================== ====================================== Add hints SQL, 6 seconds output result select/* + and_equal (A idx_t_s_case_status idx_t_s_case_position) */. case_id,. case_type_id,. case_subject,. case_content,. create_employee_id,. case_create_time,. customer_id,. dept_id,. lock_employee_id,. order_id,. order_type_id,. PRI,. customer_name,. operate_time,. case_require_finish_time,. c Ase_status, B. employee_id, B. case_op_finish_time, C. case_type_name, D. employee_name, E. employee_name as lock_employee, F. dept_name, G. position_name, H. dept_name as create_dept_name, f_geturgencyorder (. order_id) isurgency from t_s_case a left join t_s_case_operation_record B on. case_id = B. case_op_id left join t_s_employee D on D. employee_id =. create_employee_id left join t_s_employee e on E. E Mployee_id =. lock_employee_id left join t_s_department F on. dept_id = f. dept_id left join t_s_department h on D. dept_id = H. dept_id inner join t_sd_case_type C on. case_type_id = C. case_type_id left join t_sd_emplyee_position g on G. position_id =. position_id where. case_status = 0 and. position_id in (6017) order by isurgency DESC,. case_create_time,. PRI descplan hash value: 3864087155 Operation | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | bytes | 0 | SELECT statement | 19273 | 5778k | 9431 (2) | 00:01:54 | 1 | sort order by | 19273 | 5778k | 12 M | 9431 (2) | 00:01:54 | * 2 | hash join right outer | 19273 | 5778k | 8151 (2) | 00:01:38 | 3 | table access full | t_s_employee | 2018 | 28252 | 8 (0) | 00:00:01 | * 4 | hash join right outer | 19273 | 5514k | 8142 (2) | 00:01:38 | 5 | table access full | t_s_department | 84 | 1764 | 5 (0) | 00:00:01 | * 6 | hash join right outer | 19273 | bytes 9k | 8136 (2) | 00:01:38 | | 7 | table access full | t_s_employee | 2018 | 36324 | 8 (0) | 00:00:01 | * 8 | hash join right outer | 19273 | 4780k | 8126 (2) | 00:01:38 | 9 | table access full | t_s_department | 84 | 1764 | 5 (0) | 00:00:01 | * 10 | hash join | 19273 | 4385k | 8120 (2) | 00:01:38 | 11 | table access full | t_sd_case_type | 17 | 323 | 6 (0) | 00:00:01 | * 12 | hash join outer | 19273 | 4027k | 3920 K | 8113 (2) | 00:01:38 | 13 | merge join outer | 19273 | shard k | 5784 (1) | 00:01:10 | * 14 | table access by index rowid | t_s_case | 19273 | 3425k | 5782 (1) | 00:01:10 | 15 | and-equal | * 16 | index range scan | idx_t_s_case_status | 51528 | 33 (4) | 00:00:01 | * 17 | index range scan | idx_t_s_case_position | 762k | 413 (4) | 00:00:05 | * 18 | sort join | 1 | 14 | 2 (50) | 00:00:01 | 19 | table access by index rowid | t_sd_emplyee_position | 1 | 14 | 1 (0) | 00:00:01 | * 20 | index unique scan | pk_t_sd_emplyee_position | 1 | 1 (0) | 00:00:01 | 21 | table access full | t_s_case_operation_record | 589k | 10 M | 1257 (6) | 00:00:16 | minimum predicat E information (identified by Operation ID): --------------------------------------------------- 2-access ("e ". "employee_id" (+) = "". "lock_employee_id") 4-access ("D ". "dept_id" = "H ". "dept_id" (+) 6-access ("D ". "employee_id" (+) = "". "create_employee_id") 8-access ("". "dept_id" = "F ". "dept_id" (+) 10-access ("". "case_type_id" = "C ". "case_type_id") 12-access ("". "case_id" = "B ". "case_op_id" (+) 14-fi LTER ("". "case_status" = 0 and "". & quot; position_id & quot; = 6017) 16-access (& quot; A & quot ". "case_status" = 0) 17-access ("". & quot; position_id & quot; = 6017) 18-access (& quot; G & quot ". "position_id" (+) = "". "position_id") filter ("G ". "position_id" (+) = "". "position_id") 20-access ("G ". "position_id" (+) = 6017) ========================================================== ============================================ fast and fast, however, the SQL statements are spelled out, not necessarily the two columns each time. There is also a way to delete the original composite index. The original Composite Index consists of case_status, DEPT _ Create index ind_zu_case_dept_pos on t_s_case (case_status, dept_id, position_id, I also delete drop index idx_t_s_case_status and run it again to use the composite index. The difficulty is that the pre-column of the composite index cannot have an independent index. The final execution plan is as follows, and the result is less than 1 second: Plan hash value: 3915948668 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | percent | 0 | SELECT statement | 20799 | 6215k | 5225 (3) | 00:01:03 | 1 | sort order by | 20799 | 6215k | 13m | 5225 (3) | 00:01:03 | * 2 | hash join right outer | 20799 | 6215k | 3848 (4) | 00:00:47 | 3 | table access full | t_s_employee | 2018 | 28252 | 8 (0) | 00:00:01 | * 4 | hash join right outer | 20799 | 5930k | 3838 (4) | 00:00:47 | 5 | table access full | t_s_department | 84 | 1764 | 5 (0) | 00:00:01 | * 6 | hash join right outer | 20799 | 5504k | 3832 (4) | 00:00:46 | 7 | table access full | t_s_employee | 2018 | 36324 | 8 (0) | 00:00:01 | * 8 | hash join right outer | 20799 | 5138k | 3822 (4) | 00:00:46 | 9 | table access full | t_s_department | 84 | 1764 | 5 (0) | 00:00:01 | * 10 | hash join | 20799 | 4712k | 3816 (4) | 00:00:46 | 11 | table access full | t_sd_case_type | 17 | 323 | 6 (0) | 00:00:01 | * 12 | hash join outer | 20799 | 4326k | 4208k | 3809 (4) | 00:00:46 | 13 | merge join outer | 20799 | 3960k | 1466 (1) | 00:00:18 | 14 | table access by index rowid | t_s_case | 20799 | 3676k | 1464 (1) | 00:00:18 | * 15 | index range scan | ind_zu_case_dept_pos | 21600 | 41 (3) | 00:00:01 | * 16 | sort join | 1 | 14 | 2 (50) | 00:00:01 | 17 | table access by index rowid | t_sd_emplyee_position | 1 | 14 | 1 (0) | 00:00:01 | * 18 | index unique scan | pk_t_sd_emplyee_position | 1 | 1 (0) | 00:00:01 | 19 | table access full | t_s_case_operation_record | 589k | 10m | 1257 (6) | 00:00:16 | descripredicate information (identified by Operation ID ): ------------------------------------------------- 2-access ("e ". "employee_id" (+) = "". "lock_employee_id") 4-access ("D ". "dept_id" = "H ". "dept_id" (+) 6-access ("D ". "employee_id" (+) = "". "create_employee_id") 8-access ("". "dept_id" = "F ". "dept_id" (+) 10-access ("". "case_type_id" = "C ". "case_type_id") 12-access ("". "case_id" = "B ". "case_op_id" (+) 15-access ("". "case_status" = 0 and "". & quot; position_id & quot; = 6017) filter (& quot; A & quot ". & quot; position_id & quot; = 6017) 16-access (& quot; G & quot ". "position_id" (+) = "". "position_id") filter ("G ". "position_id" (+) = "". "position_id") 18-access ("G ". "position_id" (+) = 6017)