Oracle sql 优化

慢sql优化，A表有七千多条数据就查询不出来了，user_id，create_user，org_id，create_time ，这些字段都加了索引


```sql

 select
          t4.app_id,
          t5.org_name,
          t1.*
        from
          A t1,
          B t5,
          C t4,(
            select
              t2.user_id
            from
              A t2
            start with
              t2.user_id = 200000100
            connect by
              prior t2.user_id = t2.create_user
          ) t3
        where
          t1.user_id = t4.user_id
          and t1.org_id = t5.org_id
          and t1.create_user = t3.user_id
          and t4.app_id = 1
          AND t1.flag = 1
        order by
          t1.create_time desc nulls last

执行计划如下：

```sql
==================================================================
|ID|OPERATOR                 |NAME               |EST. ROWS|COST |
------------------------------------------------------------------
|0 |SORT                     |                   |1887     |16009|
|1 | HASH JOIN               |                   |1887     |10606|
|2 |  SUBPLAN SCAN           |VIEW1              |513      |2482 |
|3 |   NESTED-LOOP CONNECT BY|                   |513      |2475 |
|4 |    SUBPLAN SCAN         |VIEW2              |10       |183  |
|5 |     TABLE SCAN          |T2(idx_user_id)    |10       |183  |
|6 |    SUBPLAN SCAN         |VIEW3              |50       |217  |
|7 |     TABLE SCAN          |T2(idx_create_user)|50       |216  |
|8 |  HASH JOIN              |                   |1383     |6856 |
|9 |   TABLE SCAN            |T5                 |507      |197  |
|10|   HASH JOIN             |                   |1399     |5041 |
|11|    TABLE SCAN           |T4                 |276      |107  |
|12|    TABLE SCAN           |T1                 |3620     |2954 |
==================================================================

Outputs & filters: 
-------------------------------------
  0 - output([T4.APP_ID(0x7eeee6ea79e0)], [T5.ORG_NAME(0x7eeee6ea94f0)], [T1.USER_ID(0x7eeee6ea3d90)], [T1.USER_NAME(0x7eeee6ea97f0)], [T1.PASSWORD(0x7eeee6ea9ae0)], [T1.USER_TYPE(0x7eeee6ea9dd0)], [T1.EMP_ID(0x7eeee6eaa0c0)], [T1.ORG_ID(0x7eeee6ea51d0)], [T1.DEPT_NAME(0x7eeee6eaa3b0)], [T1.LAST_LOGIN_TIME(0x7eeee6eaa6a0)], [T1.PWD_UPDATE_TIME(0x7eeee6eaa990)], [T1.ENABLED(0x7eeee6eaac80)], [T1.PHYSICS_FLAG(0x7eeee6ea8ac0)], [T1.SALT(0x7eeee6eacf80)], [T1.REMARK(0x7eeee6ead270)], [T1.FLD_S1(0x7eeee6ead560)], [T1.FLD_S2(0x7eeee6ead850)], [T1.FLD_S3(0x7eeee6eadb40)], [T1.FLD_N1(0x7eeee6eade30)], [T1.FLD_N2(0x7eeee6eae120)], [T1.FLD_N3(0x7eeee6eae410)], [T1.CREATE_USER(0x7eeee6ea6610)], [T1.CREATE_EMP(0x7eeee6eae700)], [T1.CREATE_TIME(0x7eeee6eae9f0)], [T1.UPDATE_TIME(0x7eeee6eaece0)], [T1.MOBILE(0x7eeee6eaefd0)], [T1.EMAIL(0x7eeee6eaf2c0)], [T1.FLD_S4(0x7eeee6eaf5b0)], [T1.FLD_S5(0x7eeee6eb8720)], [T1.FLD_S6(0x7eeee6ec1890)], [T1.WX_USER_ID(0x7eeee6ecaa00)], [T1.WX_OPEN_ID(0x7eeee6ed3b80)], [T1.UM_NAME(0x7eeee6edcd00)], [T1.STAFF_NOTES_ID(0x7eeee6edcff0)], [T1.STAFF_PHONE(0x7eeee6ee6170)], [T1.UM_SIGN(0x7eeee6eef2f0)], [T1.USER_IDENTIFICATION(0x7eeee6ef8470)]), filter(nil), sort_keys([T1.CREATE_TIME(0x7eeee6eae9f0), DESC])
  1 - output([T4.APP_ID(0x7eeee6ea79e0)], [T5.ORG_NAME(0x7eeee6ea94f0)], [T1.USER_ID(0x7eeee6ea3d90)], [T1.USER_NAME(0x7eeee6ea97f0)], [T1.PASSWORD(0x7eeee6ea9ae0)], [T1.USER_TYPE(0x7eeee6ea9dd0)], [T1.EMP_ID(0x7eeee6eaa0c0)], [T1.ORG_ID(0x7eeee6ea51d0)], [T1.DEPT_NAME(0x7eeee6eaa3b0)], [T1.LAST_LOGIN_TIME(0x7eeee6eaa6a0)], [T1.PWD_UPDATE_TIME(0x7eeee6eaa990)], [T1.ENABLED(0x7eeee6eaac80)], [T1.PHYSICS_FLAG(0x7eeee6ea8ac0)], [T1.SALT(0x7eeee6eacf80)], [T1.REMARK(0x7eeee6ead270)], [T1.FLD_S1(0x7eeee6ead560)], [T1.FLD_S2(0x7eeee6ead850)], [T1.FLD_S3(0x7eeee6eadb40)], [T1.FLD_N1(0x7eeee6eade30)], [T1.FLD_N2(0x7eeee6eae120)], [T1.FLD_N3(0x7eeee6eae410)], [T1.CREATE_USER(0x7eeee6ea6610)], [T1.CREATE_EMP(0x7eeee6eae700)], [T1.CREATE_TIME(0x7eeee6eae9f0)], [T1.UPDATE_TIME(0x7eeee6eaece0)], [T1.MOBILE(0x7eeee6eaefd0)], [T1.EMAIL(0x7eeee6eaf2c0)], [T1.FLD_S4(0x7eeee6eaf5b0)], [T1.FLD_S5(0x7eeee6eb8720)], [T1.FLD_S6(0x7eeee6ec1890)], [T1.WX_USER_ID(0x7eeee6ecaa00)], [T1.WX_OPEN_ID(0x7eeee6ed3b80)], [T1.UM_NAME(0x7eeee6edcd00)], [T1.STAFF_NOTES_ID(0x7eeee6edcff0)], [T1.STAFF_PHONE(0x7eeee6ee6170)], [T1.UM_SIGN(0x7eeee6eef2f0)], [T1.USER_IDENTIFICATION(0x7eeee6ef8470)]), filter(nil), 
      equal_conds([T1.CREATE_USER(0x7eeee6ea6610) = VIEW1.T2.USER_ID(0x7eeee6f68130)(0x7eeee6ea5ef0)]), other_conds(nil)
  2 - output([VIEW1.T2.USER_ID(0x7eeee6f68130)]), filter(nil), 
      access([VIEW1.T2.USER_ID(0x7eeee6f68130)])
  3 - output([VIEW3.T2.USER_ID(0x7edb30553000)], [VIEW3.T2.CREATE_USER(0x7edb305532f0)]), filter(nil), 
      conds(nil), nl_params_([VIEW2.T2.USER_ID(0x7edb30552a20)]), batch_join=false
  4 - output([VIEW2.T2.USER_ID(0x7edb30552a20)], [VIEW2.T2.CREATE_USER(0x7edb30552d10)]), filter(nil), 
      access([VIEW2.T2.USER_ID(0x7edb30552a20)], [VIEW2.T2.CREATE_USER(0x7edb30552d10)])
  5 - output([T2.USER_ID(0x7edb305bdfa0)], [T2.CREATE_USER(0x7edb305be290)]), filter(nil), 
      access([T2.USER_ID(0x7edb305bdfa0)], [T2.CREATE_USER(0x7edb305be290)]), partitions(p0), 
      is_index_back=true, 
      range_key([T2.USER_ID(0x7edb305bdfa0)], [T2.__pk_increment(0x7edb305dfd20)]), range(200000100,MIN ; 200000100,MAX), 
      range_cond([T2.USER_ID(0x7edb305bdfa0) = 200000100(0x7edb305be580)])
  6 - output([VIEW3.T2.USER_ID(0x7edb30553000)], [VIEW3.T2.CREATE_USER(0x7edb305532f0)]), filter(nil), 
      access([VIEW3.T2.USER_ID(0x7edb30553000)], [VIEW3.T2.CREATE_USER(0x7edb305532f0)])
  7 - output([T2.USER_ID(0x7ebd01eaf110)], [T2.CREATE_USER(0x7ebd01eaf400)]), filter(nil), 
      access([T2.USER_ID(0x7ebd01eaf110)], [T2.CREATE_USER(0x7ebd01eaf400)]), partitions(p0), 
      is_index_back=true, 
      range_key([T2.CREATE_USER(0x7ebd01eaf400)], [T2.__pk_increment(0x7ebd01ed0c80)]), range(MIN,MIN ; MAX,MAX)always true, 
      range_cond([? = T2.CREATE_USER(0x7ebd01eaf400)(0x7ebd01eaf6f0)])
  8 - output([T4.APP_ID(0x7eeee6ea79e0)], [T5.ORG_NAME(0x7eeee6ea94f0)], [T1.USER_ID(0x7eeee6ea3d90)], [T1.USER_NAME(0x7eeee6ea97f0)], [T1.PASSWORD(0x7eeee6ea9ae0)], [T1.USER_TYPE(0x7eeee6ea9dd0)], [T1.EMP_ID(0x7eeee6eaa0c0)], [T1.ORG_ID(0x7eeee6ea51d0)], [T1.DEPT_NAME(0x7eeee6eaa3b0)], [T1.LAST_LOGIN_TIME(0x7eeee6eaa6a0)], [T1.PWD_UPDATE_TIME(0x7eeee6eaa990)], [T1.ENABLED(0x7eeee6eaac80)], [T1.PHYSICS_FLAG(0x7eeee6ea8ac0)], [T1.SALT(0x7eeee6eacf80)], [T1.REMARK(0x7eeee6ead270)], [T1.FLD_S1(0x7eeee6ead560)], [T1.FLD_S2(0x7eeee6ead850)], [T1.FLD_S3(0x7eeee6eadb40)], [T1.FLD_N1(0x7eeee6eade30)], [T1.FLD_N2(0x7eeee6eae120)], [T1.FLD_N3(0x7eeee6eae410)], [T1.CREATE_USER(0x7eeee6ea6610)], [T1.CREATE_EMP(0x7eeee6eae700)], [T1.CREATE_TIME(0x7eeee6eae9f0)], [T1.UPDATE_TIME(0x7eeee6eaece0)], [T1.MOBILE(0x7eeee6eaefd0)], [T1.EMAIL(0x7eeee6eaf2c0)], [T1.FLD_S4(0x7eeee6eaf5b0)], [T1.FLD_S5(0x7eeee6eb8720)], [T1.FLD_S6(0x7eeee6ec1890)], [T1.WX_USER_ID(0x7eeee6ecaa00)], [T1.WX_OPEN_ID(0x7eeee6ed3b80)], [T1.UM_NAME(0x7eeee6edcd00)], [T1.STAFF_NOTES_ID(0x7eeee6edcff0)], [T1.STAFF_PHONE(0x7eeee6ee6170)], [T1.UM_SIGN(0x7eeee6eef2f0)], [T1.USER_IDENTIFICATION(0x7eeee6ef8470)]), filter(nil), 
      equal_conds([T1.ORG_ID(0x7eeee6ea51d0) = T5.ORG_ID(0x7eeee6ea54c0)(0x7eeee6ea4ab0)]), other_conds(nil)
  9 - output([T5.ORG_ID(0x7eeee6ea54c0)], [T5.ORG_NAME(0x7eeee6ea94f0)]), filter(nil), 
      access([T5.ORG_ID(0x7eeee6ea54c0)], [T5.ORG_NAME(0x7eeee6ea94f0)]), partitions(p0), 
      is_index_back=false, 
      range_key([T5.ORG_ID(0x7eeee6ea54c0)]), range(MIN ; MAX)always true
  10 - output([T4.APP_ID(0x7eeee6ea79e0)], [T1.USER_ID(0x7eeee6ea3d90)], [T1.USER_NAME(0x7eeee6ea97f0)], [T1.PASSWORD(0x7eeee6ea9ae0)], [T1.USER_TYPE(0x7eeee6ea9dd0)], [T1.EMP_ID(0x7eeee6eaa0c0)], [T1.ORG_ID(0x7eeee6ea51d0)], [T1.DEPT_NAME(0x7eeee6eaa3b0)], [T1.LAST_LOGIN_TIME(0x7eeee6eaa6a0)], [T1.PWD_UPDATE_TIME(0x7eeee6eaa990)], [T1.ENABLED(0x7eeee6eaac80)], [T1.PHYSICS_FLAG(0x7eeee6ea8ac0)], [T1.SALT(0x7eeee6eacf80)], [T1.REMARK(0x7eeee6ead270)], [T1.FLD_S1(0x7eeee6ead560)], [T1.FLD_S2(0x7eeee6ead850)], [T1.FLD_S3(0x7eeee6eadb40)], [T1.FLD_N1(0x7eeee6eade30)], [T1.FLD_N2(0x7eeee6eae120)], [T1.FLD_N3(0x7eeee6eae410)], [T1.CREATE_USER(0x7eeee6ea6610)], [T1.CREATE_EMP(0x7eeee6eae700)], [T1.CREATE_TIME(0x7eeee6eae9f0)], [T1.UPDATE_TIME(0x7eeee6eaece0)], [T1.MOBILE(0x7eeee6eaefd0)], [T1.EMAIL(0x7eeee6eaf2c0)], [T1.FLD_S4(0x7eeee6eaf5b0)], [T1.FLD_S5(0x7eeee6eb8720)], [T1.FLD_S6(0x7eeee6ec1890)], [T1.WX_USER_ID(0x7eeee6ecaa00)], [T1.WX_OPEN_ID(0x7eeee6ed3b80)], [T1.UM_NAME(0x7eeee6edcd00)], [T1.STAFF_NOTES_ID(0x7eeee6edcff0)], [T1.STAFF_PHONE(0x7eeee6ee6170)], [T1.UM_SIGN(0x7eeee6eef2f0)], [T1.USER_IDENTIFICATION(0x7eeee6ef8470)]), filter(nil), 
      equal_conds([T1.USER_ID(0x7eeee6ea3d90) = T4.USER_ID(0x7eeee6ea4080)(0x7eeee6ea3670)]), other_conds(nil)
  11 - output([T4.USER_ID(0x7eeee6ea4080)], [T4.APP_ID(0x7eeee6ea79e0)]), filter(nil), 
      access([T4.USER_ID(0x7eeee6ea4080)], [T4.APP_ID(0x7eeee6ea79e0)]), partitions(p0), 
      is_index_back=false, 
      range_key([T4.APP_ID(0x7eeee6ea79e0)], [T4.USER_ID(0x7eeee6ea4080)]), range(1,MIN ; 1,MAX), 
      range_cond([T4.APP_ID(0x7eeee6ea79e0) = 1(0x7eeee6ea72c0)])
  12 - output([T1.USER_ID(0x7eeee6ea3d90)], [T1.ORG_ID(0x7eeee6ea51d0)], [T1.CREATE_USER(0x7eeee6ea6610)], [T1.PHYSICS_FLAG(0x7eeee6ea8ac0)], [T1.USER_NAME(0x7eeee6ea97f0)], [T1.PASSWORD(0x7eeee6ea9ae0)], [T1.USER_TYPE(0x7eeee6ea9dd0)], [T1.EMP_ID(0x7eeee6eaa0c0)], [T1.DEPT_NAME(0x7eeee6eaa3b0)], [T1.LAST_LOGIN_TIME(0x7eeee6eaa6a0)], [T1.PWD_UPDATE_TIME(0x7eeee6eaa990)], [T1.ENABLED(0x7eeee6eaac80)], [T1.SALT(0x7eeee6eacf80)], [T1.REMARK(0x7eeee6ead270)], [T1.FLD_S1(0x7eeee6ead560)], [T1.FLD_S2(0x7eeee6ead850)], [T1.FLD_S3(0x7eeee6eadb40)], [T1.FLD_N1(0x7eeee6eade30)], [T1.FLD_N2(0x7eeee6eae120)], [T1.FLD_N3(0x7eeee6eae410)], [T1.CREATE_EMP(0x7eeee6eae700)], [T1.CREATE_TIME(0x7eeee6eae9f0)], [T1.UPDATE_TIME(0x7eeee6eaece0)], [T1.MOBILE(0x7eeee6eaefd0)], [T1.EMAIL(0x7eeee6eaf2c0)], [T1.FLD_S4(0x7eeee6eaf5b0)], [T1.FLD_S5(0x7eeee6eb8720)], [T1.FLD_S6(0x7eeee6ec1890)], [T1.WX_USER_ID(0x7eeee6ecaa00)], [T1.WX_OPEN_ID(0x7eeee6ed3b80)], [T1.UM_NAME(0x7eeee6edcd00)], [T1.STAFF_NOTES_ID(0x7eeee6edcff0)], [T1.STAFF_PHONE(0x7eeee6ee6170)], [T1.UM_SIGN(0x7eeee6eef2f0)], [T1.USER_IDENTIFICATION(0x7eeee6ef8470)]), filter([T1.PHYSICS_FLAG(0x7eeee6ea8ac0) = 1(0x7eeee6ea83a0)]), 
      access([T1.USER_ID(0x7eeee6ea3d90)], [T1.ORG_ID(0x7eeee6ea51d0)], [T1.CREATE_USER(0x7eeee6ea6610)], [T1.PHYSICS_FLAG(0x7eeee6ea8ac0)], [T1.USER_NAME(0x7eeee6ea97f0)], [T1.PASSWORD(0x7eeee6ea9ae0)], [T1.USER_TYPE(0x7eeee6ea9dd0)], [T1.EMP_ID(0x7eeee6eaa0c0)], [T1.DEPT_NAME(0x7eeee6eaa3b0)], [T1.LAST_LOGIN_TIME(0x7eeee6eaa6a0)], [T1.PWD_UPDATE_TIME(0x7eeee6eaa990)], [T1.ENABLED(0x7eeee6eaac80)], [T1.SALT(0x7eeee6eacf80)], [T1.REMARK(0x7eeee6ead270)], [T1.FLD_S1(0x7eeee6ead560)], [T1.FLD_S2(0x7eeee6ead850)], [T1.FLD_S3(0x7eeee6eadb40)], [T1.FLD_N1(0x7eeee6eade30)], [T1.FLD_N2(0x7eeee6eae120)], [T1.FLD_N3(0x7eeee6eae410)], [T1.CREATE_EMP(0x7eeee6eae700)], [T1.CREATE_TIME(0x7eeee6eae9f0)], [T1.UPDATE_TIME(0x7eeee6eaece0)], [T1.MOBILE(0x7eeee6eaefd0)], [T1.EMAIL(0x7eeee6eaf2c0)], [T1.FLD_S4(0x7eeee6eaf5b0)], [T1.FLD_S5(0x7eeee6eb8720)], [T1.FLD_S6(0x7eeee6ec1890)], [T1.WX_USER_ID(0x7eeee6ecaa00)], [T1.WX_OPEN_ID(0x7eeee6ed3b80)], [T1.UM_NAME(0x7eeee6edcd00)], [T1.STAFF_NOTES_ID(0x7eeee6edcff0)], [T1.STAFF_PHONE(0x7eeee6ee6170)], [T1.UM_SIGN(0x7eeee6eef2f0)], [T1.USER_IDENTIFICATION(0x7eeee6ef8470)]), partitions(p0), 
      is_index_back=false, filter_before_indexback[false], 
      range_key([T1.__pk_increment(0x7edb304465c0)]), range(MIN ; MAX)always true

Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$1" ("VIEW1"@"SEL$1" ("SYS.T5"@"SEL$1" ("SYS.T4"@"SEL$1" "SYS.T1"@"SEL$1" ))))
      USE_HASH(@"SEL$1" ("SYS.T5"@"SEL$1" "SYS.T4"@"SEL$1" "SYS.T1"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("SYS.T5"@"SEL$1" "SYS.T4"@"SEL$1" "SYS.T1"@"SEL$1" ) LOCAL LOCAL)
      LEADING(@"SEL$3" ("VIEW2"@"SEL$3" "VIEW3"@"SEL$3" ))
      USE_NL(@"SEL$3" ("VIEW3"@"SEL$3" ))
      PQ_DISTRIBUTE(@"SEL$3" ("VIEW3"@"SEL$3" ) LOCAL LOCAL)
      NO_USE_NL_MATERIALIZATION(@"SEL$3" ("VIEW3"@"SEL$3" ))
      INDEX(@"SEL$4" "SYS.T2"@"SEL$4" "idx_user_id")
      INDEX(@"SEL$5" "SYS.T2"@"SEL$5" "idx_create_user")
      USE_HASH(@"SEL$1" ("SYS.T4"@"SEL$1" "SYS.T1"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("SYS.T4"@"SEL$1" "SYS.T1"@"SEL$1" ) LOCAL LOCAL)
      FULL(@"SEL$1" "SYS.T5"@"SEL$1")
      USE_HASH(@"SEL$1" ("SYS.T1"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("SYS.T1"@"SEL$1" ) LOCAL LOCAL)
      FULL(@"SEL$1" "SYS.T4"@"SEL$1")
      FULL(@"SEL$1" "SYS.T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------






T2:table_rows:7240, physical_range_rows:10, logical_range_rows:10, index_back_rows:10, output_rows:10, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_user_id], pruned_index_name[idx_user_name,idx_create_user,idx_org_id1,idx_time], unstable_index_name[CMS_USER_TEST], estimation info[table_id:1103909674370678, (table_type:1, version:0-1725991210154974-1725991210154974, logical_rc:10, physical_rc:10), (table_type:7, version:1725991203937451-1725991210154974-1725991240648555, logical_rc:0, physical_rc:0), (table_type:5, version:1725991203937451-1725991210154974-1725991240648555, logical_rc:0, physical_rc:0), (table_type:0, version:1725991240648555-1725991240648555-9223372036854775807, logical_rc:0, physical_rc:0)]


T2:table_rows:7240, physical_range_rows:49, logical_range_rows:49, index_back_rows:49, output_rows:49, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_create_user], pruned_index_name[idx_user_id,idx_user_name,idx_org_id1,idx_time], unstable_index_name[CMS_USER_TEST], estimation info[table_id:1103909674370681, (table_type:1, version:0-1725991210154974-1725991210154974, logical_rc:7240, physical_rc:7240), (table_type:7, version:1725991203937451-1725991210154974-1725991240648555, logical_rc:0, physical_rc:0), (table_type:5, version:1725991203937451-1725991210154974-1725991240648555, logical_rc:0, physical_rc:0), (table_type:0, version:1725991240648555-1725991240648555-9223372036854775807, logical_rc:0, physical_rc:0)]


T5:table_rows:507, physical_range_rows:507, logical_range_rows:507, index_back_rows:0, output_rows:507, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[CMS_ORG_RELATION], pruned_index_name[IDX_ORG_RELATION_ORG_NAME], estimation info[table_id:1103909674338408, (table_type:1, version:0-1725991210154974-1725991210154974, logical_rc:507, physical_rc:507), (table_type:7, version:1725991207049221-1725991210154974-1725991244014982, logical_rc:0, physical_rc:0), (table_type:5, version:1725991207049221-1725991210154974-1725991244014982, logical_rc:0, physical_rc:0), (table_type:0, version:1725991244014982-1725991244014982-9223372036854775807, logical_rc:0, physical_rc:0)]


T4:table_rows:912, physical_range_rows:276, logical_range_rows:276, index_back_rows:0, output_rows:276, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_user_id1,CMS_APP_USER], estimation info[table_id:1103909674337826, (table_type:1, version:0-1725991210154974-1725991210154974, logical_rc:276, physical_rc:276), (table_type:7, version:1725991206572567-1725991210154974-1725991243352430, logical_rc:0, physical_rc:0), (table_type:5, version:1725991206572567-1725991210154974-1725991243352430, logical_rc:0, physical_rc:0), (table_type:0, version:1725991243352430-1725991243352430-9223372036854775807, logical_rc:0, physical_rc:0)]

T1:table_rows:7240, physical_range_rows:7240, logical_range_rows:7240, index_back_rows:0, output_rows:3620, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_user_id,idx_create_user,idx_org_id1,idx_time,CMS_USER_TEST], pruned_index_name[idx_user_name], estimation info[table_id:1103909674370677, (table_type:1, version:0-1725991210154974-1725991210154974, logical_rc:7240, physical_rc:7240), (table_type:7, version:1725991203937451-1725991210154974-1725991240648555, logical_rc:0, physical_rc:0), (table_type:5, version:1725991203937451-1725991210154974-1725991240648555, logical_rc:0, physical_rc:0), (table_type:0, version:1725991240648555-1725991240648555-9223372036854775807, logical_rc:0, physical_rc:0)]
Parameters
-------------------------------------

写回答
好问题 0 提建议
关注问题
分享
邀请回答
编辑收藏删除结题
收藏举报

1条回答默认最新

关注

码龄粉丝数原力等级 --

被采纳

被点赞

采纳率
一轮明月照丘壑 2024-09-11 17:08
关注
以下回复参考：皆我百晓生、券券喵儿等免费微信小程序作答:

从提供的SQL和执行计划来看，该查询涉及多个表的连接操作，并且使用了复杂的子查询。针对这种情况，以下是一些可能的优化建议：

索引优化：

确保所有涉及的连接字段（如user_id, org_id, create_user等）都已经建立了索引。
对于A表的flag字段，如果它经常用于过滤，也应该建立索引。
考虑是否可以使用复合索引来提高查询性能。

子查询优化：

子查询start with ... connect by语句用于查找以特定user_id开始的用户层级结构。这个子查询本身已经比较高效，因为Oracle的CONNECT BY操作在处理层次结构数据时非常有效。
如果可能的话，考虑是否可以将这个子查询的结果存储在临时表中，并在主查询中直接使用这个临时表，以减少子查询的开销。

查询重写：

尝试将查询重写为使用JOIN操作代替FROM和WHERE子句中的隐式连接。
尽量减少使用隐式连接，因为它们可能会降低查询性能。

减少数据：

在WHERE子句中添加更多过滤条件，以减少查询返回的数据量。
尤其是APP_ID = 1和flag = 1这样的过滤条件，可以帮助减少参与连接的行数。

排序优化：

尝试将排序操作（ORDER BY）移到子查询中，以减少最终排序的数据量。
如果可能，考虑使用ORDER BY的索引排序。

查询分析和调整：

使用Oracle的EXPLAIN PLAN语句或DBMS_XPLAN.DISPLAY来进一步分析查询的执行计划。
根据分析的结果，调整查询或索引以优化性能。

考虑分区：

如果A表非常大，考虑使用分区来提高查询性能。

最后，请记住，优化SQL是一个迭代的过程，可能需要多次尝试和错误调整来达到最佳性能。同时，也要确保在更改后测试查询的准确性和性能，以确保没有引入新的问题。
解决无用
评论打赏
分享
举报

评论

按下Enter换行，Ctrl+Enter发表内容

报告相同问题？

关注问题

OracleSQL的优化.pdf
2023-05-17 13:20

Oracle SQL 优化 Oracle SQL 优化是数据库性能优化的关键部分。为了提高数据库的性能，我们需要从五个方面进行调整：去掉不必要的大型表的全表扫描、缓存小型表的全表扫描、检验优化索引的使用、检验优化的连接技术...
Oracle SQL性能优化方法.docx
2022-04-27 10:22

### Oracle SQL性能优化方法 #### 综述 Oracle数据库因其高度可扩展性和强大的事务处理能力，在企业级应用中被广泛采用。随着业务复杂度的增加和技术的发展，如何优化Oracle SQL性能成为了确保系统高效运行的关键...
Sql优化大数据访问量
2013-11-10 16:43

以下是一些针对SQL优化大数据访问量的主要策略和方法： 1. **查询优化**： - **避免全表扫描**：大数据量的全表扫描会消耗大量资源，应尽量使用索引来定位数据。 - **合理使用JOIN操作**：减少JOIN的层次和数量，...
解析Oracle数据扫描Oracle SQL查询优化
2020-12-15 00:50

之前我们讨论了在数据库中数据读取操作的...通过这七种方法，其实我们探讨了如何引导控制优化器进行局部范围扫描的具体方法，以及如何通过局部范围扫描思想，来改进出现性能问题的SQL语句，从而提升语句执行性能的应
Oracle--SQL性能优化与提升策略
2025-04-22 17:36

恐龙让Lee的博客本文主要探讨了SQL优化的策略和技巧。首先分析了导致性能问题的内在原因，包括CPU、内存和I/O占用率过高，并指出PL/SQL优化的核心是减少磁盘I/O访问。接着，详细介绍了多种SQL优化方法，如选择高效的表名顺序、优化...
oracle sql优化
2022-08-15 10:26

P_SNDxu的博客对于sql优化一般从三个方面下手，分别是逻辑层面，表层面和语句层面。
Oracle SQL学习资料大全.zip
2024-03-05 09:45

- **SQL调优顾问**：Oracle提供的工具用于诊断和优化SQL查询。这个压缩包内的学习资料可能涵盖了以上所有内容，通过系统学习和实践，可以全面掌握Oracle SQL的相关技能，无论是为了日常开发还是进一步的数据库...
使用SQL生成大数据序列
2021-04-04 06:35

本话题将围绕“使用SQL生成大数据序列”展开，探讨如何在MySQL、Oracle、SQL Server 2008以及MS Access等数据库系统中通过笛卡尔积和小数据表的方法生成大规模数据。首先，我们来看一个小数据表的方法。创建一个只...
数据库—如何进行SQL优化
2025-03-22 09:00

五道书童的博客如何进行SQL 优化，适用于主流数据库（如 MySQL、PostgreSQL、Oracle 等）。通过以上方法，可以显著提升 SQL 性能。实际优化时需结合业务场景和数据分布，持续监控和调整策略。
ORACLE性能优化31条.docx
2023-09-12 22:27

Oracle数据库的性能优化是数据库管理的关键任务之一，它涉及到如何高效地执行SQL查询，从而提升系统整体性能。本文主要讨论了Oracle的三种优化器模式、访问表的方式、共享SQL语句、选择有效的表名顺序以及WHERE子句...
没有解决我的问题, 去提问

问题事件

关注

码龄粉丝数原力等级 --

被采纳

被点赞

采纳率
创建了问题 9月11日

Oracle sql 优化

1条回答 默认 最新

问题事件

1条回答默认最新