今天，某省的同事来告诉我，表重组后，他用于统计的一个sql脚本运行变慢了，之前只需要17、8分钟能出来的结果，现在1小时40分钟左右才能出来结果。 我们一起来看看脚本中的一个sql：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SQL> explain plan for 2 select a.startdate,b.subsid from tab_1 a,tab_2 b where 3 a.servid='025001003681' and a.status!='C' and a.mid=b.mid; Explained. Elapsed: 00:00:00.03 SQL> select * from table(dbms_xplan.display) SQL> / PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 369 | 23985 | 980 | | | | 1 | NESTED LOOPS | | 369 | 23985 | 980 | | | | 2 | PARTITION HASH ALL | | | | | 1 | 4 | |* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| tab_1 | 369 | 14022 | 242 | 1 | 4 | |* 4 | INDEX RANGE SCAN | IDX_tab_1_SERVID | 492 | | 10 | 1 | 4 | | 5 | PARTITION HASH ITERATOR | | | | | KEY | KEY | | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| tab_2 | 1 | 27 | 2 | KEY | KEY | |* 7 | INDEX UNIQUE SCAN | PK_tab_2_MID | 1 | | 1 | KEY | KEY | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("A"."STATUS"<>'C') 4 - access("A"."SERVID"='025001003681') 7 - access("A"."MID"="B"."MID") Note: cpu costing is off 22 rows selected. Elapsed: 00:00:00.56 |
我们看到这个sql是通过索引后在走nested l […]