sql优化，表优化

[zhanghaoyicunzai]

执行计划输出：

SELECT STATEMENT, GOAL = CHOOSE			                 12	1	189
 SORT ORDER BY			                                 12	1	189
  NESTED LOOPS			                                  9	1	189
   NESTED LOOPS			                                  9	1	185
    HASH JOIN SEMI			                          8	1	32
     TABLE ACCESS FULL	NEWDOC	T_INHOSPITAL_ACTION	          2	218	4142
     VIEW	SYS	VW_NSO_1	                          5	274	3562
      SORT GROUP BY			                          5	274	4658
       TABLE ACCESS FULL	NEWDOC	T_INHOSPITAL_ACTION	  2	274	4658
    TABLE ACCESS BY INDEX ROWID	NEWDOC	T_IN_PATIENT	          1	1	153
     INDEX UNIQUE SCAN	NEWDOC	PK_T_IN_PATIENT		                58	
   INDEX UNIQUE SCAN	NEWDOC	PK_T_SICKBEDINFO		        750	3000

以下是sql部分

select 
a.id,a.patient_name,a.gender_code,a.birthday,a.pid,a.age,a.sick_doctor_id, a.sick_doctor_name,a.sick_area_id,a.sick_area_name,a.section_id, a.section_name,a.in_time,a.state,a.sick_bed_id,a.sick_bed_no,a.age_unit, a.Sick_Degree,
a.NURSE_LEVEL,a.insection_id,a.insection_name,a.in_area_id, a.in_area_name,a.Die_flag,a.Sick_Group_Id,Marriage_State,Medicare_NO,Home_Address, Homepostal_Code,Home_Phone,Office,Office_Address,Office_Phone,Career,Relation_Name,Relation,
 Relation_Address, Relation_Phone,RelationPOS_Code,OfficePOS_Code,InHospital_Count,Birth_Place,Folk_Code, CERT_ID,Pay_Manner,IN_Circs,native_place,a.card_id,a.patient_Id 
from t_in_patient a  
inner join t_inhospital_action b 
on a.id=b.patient_Id
inner join t_sickbedinfo c
on a.sick_bed_id=c.bed_id 
where a.section_id=5251 and  b.action_state=4 and 
b.id in (select max(id) from t_inhospital_action group by patient_Id) 
order by cast(sick_bed_id as number)

2个关联的表有索引了。查询数据只有13行，速度比较慢。

[啊彪123]

跟你说下优化规则吧：
1.oracle的处理规则，他是从后面到前面的。所以同一个语句能尽量使数据量变小的条件要放后面，大表放前面。
2.建议你分步处理，使用临时表。

[啊彪123]

lz,数据库查询的效率，不是用结果来衡量的。是他要遍历多少数据才查到结果数据。