Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › sql语句调优

dbman

0 积分	0 好友	1 主题

发消息

[RAC性能调优] sql语句调优

1^#

发表于 2012-12-11 14:58:19 | 查看: 5316| 回复: 10

本帖最后由 dbman 于 2012-12-11 16:10 编辑

刘大，帮忙优化一下这条语句，我试了一些方法基本都没有效果，谢谢！
sql语句：
SELECT *
  FROM (SELECT INFO.*, ROWNUM RNUM
      FROM (SELECT distinct INF.STCO_ID          AS STCO_ID,
                              AOU.SSG_SSI_STUDENT_ID,
                              INF.SSI_STUDENT_NAME,
                              INF.SSI_CARD_NUM,
                              INF.SSI_COMPANY_NAME,
                              BCS.SCO_NAME          AS SCO_NAME,
                              CTY.CTCE_NAME       AS CTCE_NAME,
                              SUT.STCO_NAME
               FROM SAC_STUDENT_GRADE AOU
               LEFT JOIN SAC_STUDENT_INFO INF
                  ON INF.SSI_STUDENT_ID = AOU.SSG_SSI_STUDENT_ID
               LEFT JOIN sex_code BCS
                  ON INF.SCO_ID = BCS.SCO_ID
               LEFT JOIN certificates_type_code CTY
                  ON CTY.CTCE_ID = INF.CTCE_ID
               LEFT JOIN SAC_COURSE_INFO SCI
                  ON SCI.SCI_COURSE_ID = AOU.SSG_EXAM_COURSE_CODE
               LEFT JOIN student_type_code SUT
                  ON SUT.STCO_ID = INF.STCO_ID
               WHERE 1 = 1
               AND inf.stco_id = '01'
               ORDER BY SSI_CARD_NUM desc) INFO
      WHERE ROWNUM < = 10)
WHERE RNUM > = 1

执行计划：
????:  00: 00: 22.73

----------------------------------------------------------
Plan hash value: 3536158082

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation    | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |       |    10 |  2220 |       | 142K  (1)| 00:28:30 |
|*  1 |  VIEW    |       |    10 |  2220 |       | 142K  (1)| 00:28:30 |
|*  2 | COUNT STOPKEY    |       |       |       |       |    |       |
| 3 | VIEW    |       |  1576K| 314M|       | 142K  (1)| 00:28:30 |
|*  4 |    SORT ORDER BY STOPKEY    |       |  1576K| 132M| 144M| 142K  (1)| 00:28:30 |
| 5 |    HASH UNIQUE    |       |  1576K| 132M| 144M| 110K  (1)| 00:22:07 |
| 6 |    MERGE JOIN OUTER    |       |  1576K| 132M|       | 78615 (1)| 00:15:44 |
| 7 |       MERGE JOIN OUTER    |       |  1576K| 118M|       | 78613 (1)| 00:15:44 |
| 8 | SORT JOIN    |       |  1576K| 105M|       | 78609 (1)| 00:15:44 |
| 9 |    MERGE JOIN OUTER    |       |  1576K| 105M|       | 78609 (1)| 00:15:44 |
|  10 |    SORT JOIN    |       |  1576K|    94M|       | 78604 (1)| 00:15:44 |
|* 11 |    HASH JOIN    |       |  1576K|    94M|    24M| 78604 (1)| 00:15:44 |
|* 12 |       TABLE ACCESS FULL    | SAC_STUDENT_INFO       | 405K|    20M|       | 22698 (1)| 00:04:33 |
|  13 |       TABLE ACCESS FULL    | SAC_STUDENT_GRADE       |    11M| 119M|       | 42130 (1)| 00:08:26 |
|* 14 |    SORT JOIN    |       |    3 |    21 |       |    5  (20)| 00:00:01 |
|  15 |    TABLE ACCESS FULL    | SEX_CODE       |    3 |    21 |       |    4 (0)| 00:00:01 |
|* 16 | SORT JOIN    |       |    3 |    27 |       |    4  (25)| 00:00:01 |
|  17 |    TABLE ACCESS FULL    | CERTIFICATES_TYPE_CODE |    3 |    27 |       |    3 (0)| 00:00:01 |
|* 18 |       SORT JOIN    |       |    1 |    9 |       |    2  (50)| 00:00:01 |
|  19 | TABLE ACCESS BY INDEX ROWID| STUDENT_TYPE_CODE       |    1 |    9 |       |    1 (0)| 00:00:01 |
|* 20 |    INDEX UNIQUE SCAN    | PK_STUDENT_TYPE_CODE |    1 |       |       |    0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RNUM">=1)
2 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
  11 - access("INF"."SSI_STUDENT_ID"="AOU"."SSG_SSI_STUDENT_ID")
  12 - filter("INF"."STCO_ID"='01')
  14 - access("INF"."SCO_ID"="BCS"."SCO_ID"(+))
   filter("INF"."SCO_ID"="BCS"."SCO_ID"(+))
  16 - access("CTY"."CTCE_ID"(+)="INF"."CTCE_ID")
   filter("CTY"."CTCE_ID"(+)="INF"."CTCE_ID")
  18 - access("SUT"."STCO_ID"(+)="INF"."STCO_ID")
   filter("SUT"."STCO_ID"(+)="INF"."STCO_ID")
  20 - access("SUT"."STCO_ID"(+)='01')

????
----------------------------------------------------------
420  recursive calls
   13  db block gets
   238112  consistent gets
   53751  physical reads
   0  redo size
   1686  bytes sent via SQL*Net to client
524  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   4  sorts (memory)
   2  sorts (disk)
   10  rows processed

数据量：
SAC_STUDENT_INFO          300W
SAC_STUDENT_GRADE    1100W
其他表数据为几条，十几条

distinct SAC_STUDENT_INFO.SSI_STUDENT_ID             290W
distinct SAC_STUDENT_GRADE.SSG_SSI_STUDENT_ID 300W
distinct SAC_STUDENT_INFO.STCO_ID                         7条

sqlhc_sac_sac1_11.2.0.3.0_gz9u3dz8xnu4w_20121211154208.html (41.94 KB, 下载次数: 752)

1.txt (47.36 KB, 下载次数: 1046)

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

2^#

发表于 2012-12-11 15:01:21

请使用 sql health check 脚本分析该SQL 然后上传HTML
http://www.oracledatabase12g.com ... h-check-script.html

同时用 gather_plan_statistics获得正确的基数并上传

使用方法：

http://www.askmaclean.com/archiv ... tatistics-hint.html

点评回复只看该作者道具举报

Ling.QIu 发表于 2012-12-11 15:03: 你这Link 比较坑爹吧

Liu Maclean(刘相兵

3^#

发表于 2012-12-11 16:16:27

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:35.45 | 238K| 53752 | 53752 | | | | |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:35.45 | 238K| 53752 | 53752 | | | | |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:35.45 | 238K| 53752 | 53752 | | | | |
| 3 | VIEW | | 1 | 1576K| 10 |00:00:35.45 | 238K| 53752 | 53752 | | | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 1576K| 10 |00:00:35.45 | 238K| 53752 | 53752 | 9216 | 9216 | 8192 (0)| |
| 5 | HASH UNIQUE | | 1 | 1576K| 501K|00:00:35.35 | 238K| 53752 | 53752 | 43M| 4005K| 57M (0)| |
| 6 | MERGE JOIN OUTER | | 1 | 1576K| 3403K|00:00:33.89 | 238K| 53752 | 53752 | | | | |
| 7 | MERGE JOIN OUTER | | 1 | 1576K| 3403K|00:00:29.23 | 238K| 53752 | 53752 | | | | |
| 8 | SORT JOIN | | 1 | 1576K| 3403K|00:00:24.37 | 238K| 53752 | 53752 | 236M| 5131K| 80M (0)| 210K|
| 9 | MERGE JOIN OUTER | | 1 | 1576K| 3403K|00:00:21.14 | 238K| 26878 | 26878 | | | | |
| 10 | SORT JOIN | | 1 | 1576K| 3403K|00:00:15.17 | 238K| 26878 | 26878 | 236M| 5131K| 56M (1)| 210K|
|* 11 | HASH JOIN | | 1 | 1576K| 3403K|00:00:10.66 | 238K| 0 | 0 | 42M| 4387K| 52M (0)| |
|* 12 | TABLE ACCESS FULL | SAC_STUDENT_INFO | 1 | 405K| 502K|00:00:02.40 | 83973 | 0 | 0 | | | | |
| 13 | TABLE ACCESS FULL | SAC_STUDENT_GRADE | 1 | 11M| 11M|00:00:04.03 | 154K| 0 | 0 | | | | |
|* 14 | SORT JOIN | | 3403K| 3 | 3402K|00:00:03.53 | 2 | 0 | 0 | 2048 | 2048 | 2048 (0)| |
| 15 | TABLE ACCESS FULL | SEX_CODE | 1 | 3 | 3 |00:00:00.01 | 2 | 0 | 0 | | | | |
|* 16 | SORT JOIN | | 3403K| 3 | 3403K|00:00:02.07 | 2 | 0 | 0 | 2048 | 2048 | 2048 (0)| |
| 17 | TABLE ACCESS FULL | CERTIFICATES_TYPE_CODE | 1 | 3 | 3 |00:00:00.01 | 2 | 0 | 0 | | | | |
|* 18 | SORT JOIN | | 3403K| 1 | 3403K|00:00:02.38 | 2 | 0 | 0 | 2048 | 2048 | 2048 (0)| |
| 19 | TABLE ACCESS BY INDEX ROWID| STUDENT_TYPE_CODE | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | | |
|* 20 | INDEX UNIQUE SCAN | PK_STUDENT_TYPE_CODE | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | 0 | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=1)
2 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
11 - access("INF"."SSI_STUDENT_ID"="AOU"."SSG_SSI_STUDENT_ID")
12 - filter("INF"."STCO_ID"='01')
14 - access("INF"."SCO_ID"="BCS"."SCO_ID")
filter("INF"."SCO_ID"="BCS"."SCO_ID")
16 - access("CTY"."CTCE_ID"="INF"."CTCE_ID")
filter("CTY"."CTCE_ID"="INF"."CTCE_ID")
18 - access("SUT"."STCO_ID"="INF"."STCO_ID")
filter("SUT"."STCO_ID"="INF"."STCO_ID")
20 - access("SUT"."STCO_ID"='01')

复制代码

回复只看该作者道具举报

Liu Maclean(刘相兵

4^#

发表于 2012-12-11 16:20:19

12 - filter("INF"."STCO_ID"='01')    => 502K 这个过滤条件基本没意义但是这一步是驱动动作

20 - access("SUT"."STCO_ID"='01')    =>  A-Rows 1 第20行 INDEX UNIQUE SCAN       | PK_STUDENT_TYPE_CODE 的选择性很好，源于 SUT.STCO_ID = INF.STCO_ID ，隐含 SUT.STCO_ID='01'

inf.stco_id 上是否有合理的索引？

考虑在子查询内加入  SUT.STCO_ID='01'的隐含条件

  SUT.STCO_ID='01'的隐含条件，先使用RULE或FIRST_ROWS_1的HINT看下执行计划并贴出来

回复只看该作者道具举报

dbman

5^#

发表于 2012-12-11 16:43:20

Liu Maclean(刘相兵发表于 2012-12-11 16:20
12 - filter("INF"."STCO_ID"='01') => 502K 这个过滤条件基本没意义但是这一步是驱动动作

20 - ac ...

刘大，distinct inf.stco_id只有七个不同的值，所以我没有建立索引。根据您的建议，生成的执行计划如下：

----------------------------------------------------------
Plan hash value: 684694182
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2220 | | 86272 (1)| 00:17:16 |
|* 1 | VIEW | | 10 | 2220 | | 86272 (1)| 00:17:16 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 214K| 42M| | 86272 (1)| 00:17:16 |
|* 4 | SORT ORDER BY STOPKEY | | 214K| 18M| 19M| 86272 (1)| 00:17:16 |
| 5 | HASH UNIQUE | | 214K| 18M| 19M| 81923 (1)| 00:16:24 |
|* 6 | HASH JOIN RIGHT OUTER | | 214K| 18M| | 77575 (1)| 00:15:31 |
| 7 | TABLE ACCESS FULL | CERTIFICATES_TYPE_CODE | 3 | 27 | | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 214K| 16M| | 77570 (1)| 00:15:31 |
| 9 | TABLE ACCESS FULL | SEX_CODE | 3 | 21 | | 4 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 214K| 14M| 3944K| 77565 (1)| 00:15:31 |
| 11 | NESTED LOOPS | | 55263 | 3292K| | 22699 (1)| 00:04:33 |
| 12 | TABLE ACCESS BY INDEX ROWID| STUDENT_TYPE_CODE | 1 | 9 | | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_STUDENT_TYPE_CODE | 1 | | | 0 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | SAC_STUDENT_INFO | 55263 | 2806K| | 22698 (1)| 00:04:33 |
| 15 | TABLE ACCESS FULL | SAC_STUDENT_GRADE | 11M| 119M| | 42130 (1)| 00:08:26 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=1)
2 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
6 - access("CTY"."CTCE_ID"(+)="INF"."CTCE_ID")
8 - access("INF"."SCO_ID"="BCS"."SCO_ID"(+))
10 - access("INF"."SSI_STUDENT_ID"="AOU"."SSG_SSI_STUDENT_ID")
13 - access("SUT"."STCO_ID"='01')
14 - filter("INF"."STCO_ID"='01' AND "SUT"."STCO_ID"="INF"."STCO_ID")
????
----------------------------------------------------------
36 recursive calls
2 db block gets
238119 consistent gets
4247 physical reads
0 redo size
1686 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

复制代码

回复只看该作者道具举报

Liu Maclean(刘相兵

6^#

发表于 2012-12-11 16:51:04

以上SQL仍不理想，至少把你改好的SQL语句贴出来！！！！！

回复只看该作者道具举报

dbman

7^#

发表于 2012-12-11 16:59:43

Liu Maclean(刘相兵发表于 2012-12-11 16:51
以上SQL仍不理想，至少把你改好的SQL语句贴出来！！！！！

alter session set optimizer_mode = first_rows_10;

SELECT *
  FROM (SELECT INFO.*, ROWNUM RNUM
      FROM (SELECT /*+first_rows(10)*/  distinct INF.STCO_ID          AS STCO_ID,
                              AOU.SSG_SSI_STUDENT_ID,
                              INF.SSI_STUDENT_NAME,
                              INF.SSI_CARD_NUM,
                              INF.SSI_COMPANY_NAME,
                              BCS.SCO_NAME          AS SCO_NAME,
                              CTY.CTCE_NAME       AS CTCE_NAME,
                              SUT.STCO_NAME
               FROM SAC_STUDENT_GRADE AOU
               LEFT JOIN SAC_STUDENT_INFO INF
                  ON INF.SSI_STUDENT_ID = AOU.SSG_SSI_STUDENT_ID
               LEFT JOIN sex_code BCS
                  ON INF.SCO_ID = BCS.SCO_ID
               LEFT JOIN certificates_type_code CTY
                  ON CTY.CTCE_ID = INF.CTCE_ID
               LEFT JOIN SAC_COURSE_INFO SCI
                  ON SCI.SCI_COURSE_ID = AOU.SSG_EXAM_COURSE_CODE
               LEFT JOIN student_type_code SUT
                  ON SUT.STCO_ID = INF.STCO_ID
               WHERE 1 = 1
               AND inf.stco_id = '01'
               And SUT.STCO_ID = '01'
               ORDER BY SSI_CARD_NUM desc) INFO
      WHERE ROWNUM < = 10)
WHERE RNUM > = 1

????: 00: 00: 11.60
????
----------------------------------------------------------
Plan hash value: 684694182
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2220 | | 86272 (1)| 00:17:16 |
|* 1 | VIEW | | 10 | 2220 | | 86272 (1)| 00:17:16 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 214K| 42M| | 86272 (1)| 00:17:16 |
|* 4 | SORT ORDER BY STOPKEY | | 214K| 18M| 19M| 86272 (1)| 00:17:16 |
| 5 | HASH UNIQUE | | 214K| 18M| 19M| 81923 (1)| 00:16:24 |
|* 6 | HASH JOIN RIGHT OUTER | | 214K| 18M| | 77575 (1)| 00:15:31 |
| 7 | TABLE ACCESS FULL | CERTIFICATES_TYPE_CODE | 3 | 27 | | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 214K| 16M| | 77570 (1)| 00:15:31 |
| 9 | TABLE ACCESS FULL | SEX_CODE | 3 | 21 | | 4 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 214K| 14M| 3944K| 77565 (1)| 00:15:31 |
| 11 | NESTED LOOPS | | 55263 | 3292K| | 22699 (1)| 00:04:33 |
| 12 | TABLE ACCESS BY INDEX ROWID| STUDENT_TYPE_CODE | 1 | 9 | | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_STUDENT_TYPE_CODE | 1 | | | 0 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | SAC_STUDENT_INFO | 55263 | 2806K| | 22698 (1)| 00:04:33 |
| 15 | TABLE ACCESS FULL | SAC_STUDENT_GRADE | 11M| 119M| | 42130 (1)| 00:08:26 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=1)
2 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
6 - access("CTY"."CTCE_ID"(+)="INF"."CTCE_ID")
8 - access("INF"."SCO_ID"="BCS"."SCO_ID"(+))
10 - access("INF"."SSI_STUDENT_ID"="AOU"."SSG_SSI_STUDENT_ID")
13 - access("SUT"."STCO_ID"='01')
14 - filter("INF"."STCO_ID"='01' AND "SUT"."STCO_ID"="INF"."STCO_ID")
????
----------------------------------------------------------
36 recursive calls
2 db block gets
238113 consistent gets
4247 physical reads
0 redo size
1686 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

复制代码

回复只看该作者道具举报

Liu Maclean(刘相兵

8^#

发表于 2012-12-11 17:01:05

在 INF.STCO_ID 和 SSI_CARD_NUM 2个列上建一个联合索引

回复只看该作者道具举报

dbman

9^#

发表于 2012-12-11 17:10:44

Liu Maclean(刘相兵发表于 2012-12-11 17:01
在 INF.STCO_ID 和 SSI_CARD_NUM 2个列上建一个联合索引

create index idx_complex on SAC_STUDENT_info(STCO_ID ,SSI_CARD_NUM ) tablespace sac_test_idx；
刘大，执行计划如下：

????: 00: 00: 13.60
????
----------------------------------------------------------
Plan hash value: 684694182
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2220 | | 86272 (1)| 00:17:16 |
|* 1 | VIEW | | 10 | 2220 | | 86272 (1)| 00:17:16 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 214K| 42M| | 86272 (1)| 00:17:16 |
|* 4 | SORT ORDER BY STOPKEY | | 214K| 18M| 19M| 86272 (1)| 00:17:16 |
| 5 | HASH UNIQUE | | 214K| 18M| 19M| 81923 (1)| 00:16:24 |
|* 6 | HASH JOIN RIGHT OUTER | | 214K| 18M| | 77575 (1)| 00:15:31 |
| 7 | TABLE ACCESS FULL | CERTIFICATES_TYPE_CODE | 3 | 27 | | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 214K| 16M| | 77570 (1)| 00:15:31 |
| 9 | TABLE ACCESS FULL | SEX_CODE | 3 | 21 | | 4 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 214K| 14M| 3944K| 77565 (1)| 00:15:31 |
| 11 | NESTED LOOPS | | 55263 | 3292K| | 22699 (1)| 00:04:33 |
| 12 | TABLE ACCESS BY INDEX ROWID| STUDENT_TYPE_CODE | 1 | 9 | | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_STUDENT_TYPE_CODE | 1 | | | 0 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | SAC_STUDENT_INFO | 55263 | 2806K| | 22698 (1)| 00:04:33 |
| 15 | TABLE ACCESS FULL | SAC_STUDENT_GRADE | 11M| 119M| | 42130 (1)| 00:08:26 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=1)
2 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
6 - access("CTY"."CTCE_ID"(+)="INF"."CTCE_ID")
8 - access("INF"."SCO_ID"="BCS"."SCO_ID"(+))
10 - access("INF"."SSI_STUDENT_ID"="AOU"."SSG_SSI_STUDENT_ID")
13 - access("SUT"."STCO_ID"='01')
14 - filter("INF"."STCO_ID"='01' AND "SUT"."STCO_ID"="INF"."STCO_ID")
????
----------------------------------------------------------
131 recursive calls
2 db block gets
238191 consistent gets
4247 physical reads
0 redo size
1686 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
10 rows processed

复制代码

回复只看该作者道具举报

Liu Maclean(刘相兵

10^#

发表于 2012-12-11 17:15:01

未用到索引，你贴执行计划请连SQL一起贴出来！！！

简单来说　思路是　你的这个查询　只要返回几行数据而已，　用HASH JOIN+ SORT是不和核算的，因为要JOIN所有的数据并排序。

为什么要有SSI_CARD_NUM 上的索引？因为　这样对该索引的扫描＋NESTED LOOP可以避免全数据排序，而只返回前10来行数据就完成了！！

回复只看该作者道具举报

dbman

11^#

发表于 2012-12-11 17:22:29

Liu Maclean(刘相兵发表于 2012-12-11 17:15
未用到索引，你贴执行计划请连SQL一起贴出来！！！

简单来说　思路是　你的这个查询　只要返回几行数据 ...

嗯我按您的提示再整理一下自己的思路，刚刚有教无类问了我一些问题也给了我提示，distinct可以去掉。谢谢刘大和有教无类！
sql语句：
SELECT *
  FROM (SELECT INFO.*, ROWNUM RNUM
      FROM (SELECT /*+first_rows(10)*/  distinct INF.STCO_ID          AS STCO_ID,
                              AOU.SSG_SSI_STUDENT_ID,
                              INF.SSI_STUDENT_NAME,
                              INF.SSI_CARD_NUM,
                              INF.SSI_COMPANY_NAME,
                              BCS.SCO_NAME          AS SCO_NAME,
                              CTY.CTCE_NAME       AS CTCE_NAME,
                              SUT.STCO_NAME
               FROM SAC_STUDENT_GRADE AOU
               LEFT JOIN SAC_STUDENT_INFO INF
                  ON INF.SSI_STUDENT_ID = AOU.SSG_SSI_STUDENT_ID
               LEFT JOIN sex_code BCS
                  ON INF.SCO_ID = BCS.SCO_ID
               LEFT JOIN certificates_type_code CTY
                  ON CTY.CTCE_ID = INF.CTCE_ID
               LEFT JOIN SAC_COURSE_INFO SCI
                  ON SCI.SCI_COURSE_ID = AOU.SSG_EXAM_COURSE_CODE
               LEFT JOIN student_type_code SUT
                  ON SUT.STCO_ID = INF.STCO_ID
               WHERE 1 = 1
               AND inf.stco_id = '01'
               And SUT.STCO_ID = '01'
               ORDER BY SSI_CARD_NUM desc) INFO
      WHERE ROWNUM < = 10)
WHERE RNUM > = 1

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册