sql语句该怎么优化

显示全部楼层 · 2015-7-1 10:06:32

本帖最后由 happyjsl1 于 2015-8-10 15:04 编辑
INSERT
INTO SZHQUALITY.DISPATCH
(
UNIQUEPART_ID,
WHAT,
STATE
)
SELECT UNIQUEPART_ID,
'XML_ARCHIVE' AS WHAT,
1 AS STATE
FROM
(
SELECT DISTINCT U.UNIQUEPART_ID
FROM SZHQUALITY.UNIQUEPARTS U
LEFT JOIN SZHQUALITY.COMPONENTS C
ON U.UNIQUEPART_ID = C.COMPONENTIDENTIFIER
LEFT JOIN SZHQUALITY.DISPATCH D
ON U.UNIQUEPART_ID = D.UNIQUEPART_ID
AND D.WHAT = 'XML_ARCHIVE'
WHERE (U.ARCHIVE_FLAG = 1)
AND (:B3 = 100
OR U.RESULT_STATE = :B3 )
AND (:B2 IS NULL
OR PART_CLASS = :B2 )
AND (U.RESULT_DATEselect * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT
|
|
1 | 42 |
938K|
|
|
| 1 |LOAD TABLE CONVENTIONAL
| DISPATCH
|
|
|
|
|
|
| 2 | COUNT STOPKEY
|
|
|
|
|
|
|
| 3 |VIEW
|
|
1 | 42 |
938K|
|
|
| 4 | SORT GROUP BY STOPKEY
|
|
1 |
133 |
938K|
|
|
| 5 |FILTER
|
|
|
|
|
|
|
| 6 | NESTED LOOPS OUTER
|
|
127 | 16891 |
938K|
|
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 7 |FILTER
|
|
|
|
|
|
|
| 8 |
HASH JOIN OUTER
|
|
128 | 11648 |
485K|
|
|
| 9 |
TABLE ACCESS FULL
| UNIQUEPARTS | 12837 |
601K|
484K|
|
|
|10 |
TABLE ACCESS BY INDEX ROWID| DISPATCH
|
106K|4471K|
765 |
|
|
|11 |
INDEX RANGE SCAN
| DISPATCH_I1 |
106K|
|
374 |
|
|
|12 |PARTITION RANGE ALL
|
|1107K| 44M|3544 |
1 |1048575|
|13 |
INDEX RANGE SCAN
| COMPONENTS_I2 |1107K| 44M|3544 |
1 |1048575|
-----------------------------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
23 rows selected.

SQL> select table_name,index_name,COLUMN_POSITION from user_ind_columns where table_name = 'UNIQUEPARTS';
TABLE_NAME
INDEX_NAME
COLUMN_POSITION
------------------------------ ------------------------------ ---------------
UNIQUEPARTS
UNIQUEPARTS_INDEX
1
UNIQUEPARTS
UNIQUEPARTS_PK
1
UNIQUEPARTS
UNIQUEPARTS_I1
1
UNIQUEPARTS
UNIQUEPARTS_I1
2
UNIQUEPARTS
UNIQUEPARTS_I1
3
UNIQUEPARTS
UNIQUEPARTS_I1
4
UNIQUEPARTS
UNIQUEPARTS_I1
5
UNIQUEPARTS
UNIQUEPARTS_I1
6
UNIQUEPARTS
UNIQUEPARTS_I2
1
UNIQUEPARTS
UNIQUEPARTS_I2
2
UNIQUEPARTS
UNIQUEPARTS_I3
1
TABLE_NAME
INDEX_NAME
COLUMN_POSITION
------------------------------ ------------------------------ ---------------
UNIQUEPARTS
UNIQUEPARTS_I3
2
UNIQUEPARTS
UNIQUEPARTS_I4
1
UNIQUEPARTS
UNIQUEPARTS_I5
1
14 rows selected.

请问我该怎么做才能让UNIQUEPARTS 不走全表扫描呢，我对优化不懂，不知道是不是应该建个索引就能解决问题，还是说Oracle优化器已经做出了选择。

千问 · 2015-7-1 10:06:32

COLUMN index_nameFORMAT A30
COLUMN column_name FORMAT A30
SELECT a.index_name,
a.column_name,
a.column_position,
b.index_type
FROM all_ind_columns a,
all_indexes b
WHEREb.table_name = Upper('&&1')
ANDb.owner= Upper('&&2')
ANDb.index_name = a.index_name
ANDb.owner= a.index_owner
ORDER BY 1,3;

看看下面这些表的索引是怎么创建的
UNIQUEPARTS/COMPONENTS/DISPATCH

千问 · 2015-7-1 10:06:32

在UNIQUEPARTS建立组合索引(ARCHIVE_FLAG,RESULT_DATE,UNIQUEPART_ID).
在DISPATCH上面建立组合索引(UNIQUEPART_ID,WHAT).
你试试看行不?
看一下SET AUTOT TRACE
然后把结果贴出来啊

千问 · 2015-7-1 10:06:32

hippopod 发表于 2015-8-10 15:30
COLUMN index_nameFORMAT A30
COLUMN column_name FORMAT A30
SELECT a.index_name,
INDEX_NAME
COLUMN_NAME
COLUMN_POSITION INDEX_TYPE
------------------------------ ------------------------------ --------------- ---------------------------
UNIQUEPARTS_I1
SERIAL_NUMBER
1 FUNCTION-BASED NORMAL
UNIQUEPARTS_I1
ORDER_ID
2 FUNCTION-BASED NORMAL
UNIQUEPARTS_I1
PART_ATTRIBUTE
3 FUNCTION-BASED NORMAL
UNIQUEPARTS_I1
SYS_NC00022$
4 FUNCTION-BASED NORMAL
UNIQUEPARTS_I1
RESULT_STATE
5 FUNCTION-BASED NORMAL
UNIQUEPARTS_I1
BATCH
6 FUNCTION-BASED NORMAL
UNIQUEPARTS_I2
SYS_NC00022$
1 FUNCTION-BASED NORMAL
UNIQUEPARTS_I2
RESULT_STATE
2 FUNCTION-BASED NORMAL
UNIQUEPARTS_I3
LOTID
1 NORMAL
UNIQUEPARTS_I3
UNIQUEPART_ID
2 NORMAL
UNIQUEPARTS_I4
PACKAGE_ID
1 NORMAL
INDEX_NAME
COLUMN_NAME
COLUMN_POSITION INDEX_TYPE
------------------------------ ------------------------------ --------------- ---------------------------
UNIQUEPARTS_I5
SYS_NC00023$
1 FUNCTION-BASED NORMAL
UNIQUEPARTS_INDEX
SYS_NC00022$
1 FUNCTION-BASED NORMAL
UNIQUEPARTS_PK
UNIQUEPART_ID
1 NORMAL
14 rows selected.

千问 · 2015-7-1 10:06:32

happyjsl1 发表于 2015-8-10 17:32
INDEX_NAME
COLUMN_NAME
COLUMN_POSITION INDEX_TYPE
------------------------------ - ...
建议先建立组合索引，然后在看看执行计划，性能是否有提高.
在UNIQUEPARTS建立组合索引(ARCHIVE_FLAG,RESULT_DATE,UNIQUEPART_ID).
在DISPATCH上面建立组合索引(UNIQUEPART_ID,WHAT).
然后在看看
SELECT UNIQUEPART_ID, 'XML_ARCHIVE' AS WHAT, 1 AS STATE
FROM (SELECT DISTINCT U.UNIQUEPART_ID

FROM SZHQUALITY.UNIQUEPARTS U

LEFT JOIN SZHQUALITY.COMPONENTS C

ON U.UNIQUEPART_ID = C.COMPONENTIDENTIFIER

LEFT JOIN SZHQUALITY.DISPATCH D

ON U.UNIQUEPART_ID = D.UNIQUEPART_ID
AND D.WHAT = 'XML_ARCHIVE'
WHERE (U.ARCHIVE_FLAG = 1)
AND (:B3 = 100 OR U.RESULT_STATE = :B3)
AND (:B2 IS NULL OR PART_CLASS = :B2)
AND (U.RESULT_DATEcreate index UNIQUEPARTS_INDEX on UNIQUEPARTS (ARCHIVE_FLAG,RESULT_DATE,UNIQUEPART_ID) online ;
Index created.
SQL> select table_name,index_name,COLUMN_POSITION from user_ind_columns where table_name = 'UNIQUEPARTS';
TABLE_NAME
INDEX_NAME
COLUMN_POSITION
------------------------------ ------------------------------ ---------------
UNIQUEPARTS
UNIQUEPARTS_INDEX
3
UNIQUEPARTS
UNIQUEPARTS_INDEX
1
UNIQUEPARTS
UNIQUEPARTS_INDEX
2
UNIQUEPARTS
UNIQUEPARTS_PK
1
UNIQUEPARTS
UNIQUEPARTS_I1
1
UNIQUEPARTS
UNIQUEPARTS_I1
2
UNIQUEPARTS
UNIQUEPARTS_I1
3
UNIQUEPARTS
UNIQUEPARTS_I1
4
UNIQUEPARTS
UNIQUEPARTS_I1
5
UNIQUEPARTS
UNIQUEPARTS_I1
6
UNIQUEPARTS
UNIQUEPARTS_I2
1
TABLE_NAME
INDEX_NAME
COLUMN_POSITION
------------------------------ ------------------------------ ---------------
UNIQUEPARTS
UNIQUEPARTS_I2
2
UNIQUEPARTS
UNIQUEPARTS_I3
1
UNIQUEPARTS
UNIQUEPARTS_I3
2
UNIQUEPARTS
UNIQUEPARTS_I4
1
UNIQUEPARTS
UNIQUEPARTS_I5
1
16 rows selected.
SQL> COLUMN index_nameFORMAT A30
COLUMN column_name FORMAT A30
SELECT a.index_name,
a.column_name,
a.column_position,
b.index_type
FROM all_ind_columns a,
all_indexes b
WHEREb.table_name = Upper('&&1')
ANDb.owner= Upper('&&2')
ANDb.index_name = a.index_name
ANDb.owner= a.index_owner
ORDER BY 1,3;
SQL> SQL> 23456789 10 11old 7:WHEREb.table_name = Upper('&&1')
new 7:WHEREb.table_name = Upper('DISPATCH')
old 8:AND b.owner= Upper('&&2')
new 8:AND b.owner= Upper('SZHQUALITY')
INDEX_NAME
COLUMN_NAME
COLUMN_POSITION INDEX_TYPE
------------------------------ ------------------------------ --------------- ---------------------------
DISPATCH_I1
WHAT
1 NORMAL
DISPATCH_I1
STATE
2 NORMAL
DISPATCH_U1
UNIQUEPART_ID
1 NORMAL
DISPATCH_U1
LOCATION_ID
2 NORMAL
DISPATCH_U1
WHAT
3 NORMAL
DISPATCH_U1
STATE
4 NORMAL
6 rows selected.

SQL> explain plan for INSERT
INTO SZHQUALITY.DISPATCH
(
UNIQUEPART_ID,
WHAT,
STATE
)
SELECT UNIQUEPART_ID,
'XML_ARCHIVE' AS WHAT,
1 AS STATE
FROM
(
SELECT DISTINCT U.UNIQUEPART_ID
FROM SZHQUALITY.UNI23456789 10 11 12 13 14QUEPARTS U
LEFT JOIN SZHQUALITY.COMPONENTS C
ON U.UNIQUEPART_ID = C.COMPONENTIDENTIFIER
LEFT JOIN SZHQUALITY.DISPATCH D
ON U.UNIQUEPART_ID = D.UNIQUEPART_ID
AND D.WHAT = 'XML_ARCHIVE'
WHERE (U.ARCHIVE_FLAG = 1)
AND (:B3 = 1 15 16 17 18 19 20 2100
OR U.RESULT_STATE = :B3 )
AND (:B2 IS NULL
OR PART_CLASS = :B2 )
AND (U.RESULT_DATEselect * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT
|
|
1 | 42 |
938K|
|
|
| 1 |LOAD TABLE CONVENTIONAL
| DISPATCH
|
|
|
|
|
|
| 2 | COUNT STOPKEY
|
|
|
|
|
|
|
| 3 |VIEW
|
|
1 | 42 |
938K|
|
|
| 4 | SORT GROUP BY STOPKEY
|
|
1 |
133 |
938K|
|
|
| 5 |FILTER
|
|
|
|
|
|
|
| 6 | NESTED LOOPS OUTER
|
|
127 | 16891 |
938K|
|
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 7 |FILTER
|
|
|
|
|
|
|
| 8 |
HASH JOIN OUTER
|
|
128 | 11648 |
485K|
|
|
| 9 |
TABLE ACCESS FULL
| UNIQUEPARTS | 12837 |
601K|
484K|
|
|
|10 |
TABLE ACCESS BY INDEX ROWID| DISPATCH
|
106K|4471K|
765 |
|
|
|11 |
INDEX RANGE SCAN
| DISPATCH_I1 |
106K|
|
374 |
|
|
|12 |PARTITION RANGE ALL
|
|1107K| 44M|3544 |
1 |1048575|
|13 |
INDEX RANGE SCAN
| COMPONENTS_I2 |1107K| 44M|3544 |
1 |1048575|
-----------------------------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
23 rows selected.

千问 · 2015-7-1 10:06:32

还有一点我有疑问为什么执行计划里面显示table 'UNIQUEPARTS'的rows 是12837，从下面我搜集的统计信息应该是38192501。这是不是导致优化器选择全表扫描的原因？

SQL> select table_name,NUM_ROWS,LAST_ANALYZED from user_tables where table_name in ('DISPATCH','UNIQUEPARTS');
TABLE_NAME
NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
DISPATCH
106490 10-AUG-15
UNIQUEPARTS
38192501 10-AUG-15

SQL> select table_name,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED,GLOBAL_STATS,USER_STATS,STATTYPE_LOCKED,STALE_STATS from user_tab_statistics where table_name = 'UNIQUEPARTS';
TABLE_NAME
NUM_ROWS SAMPLE_SIZE LAST_ANAL GLO USE STATT STA
------------------------------ ---------- ----------- --------- --- --- ----- ---
UNIQUEPARTS
3819250138192501 10-AUG-15 YES NO
NO

千问 · 2015-7-1 10:06:32

如果你的SQL中，通过应用WHERE中与UNIQUEPARTS 表相关的条件后，该表中大部分的行都符合条件的话，用全表扫描比用索引的代价更低。

千问 · 2015-7-1 10:06:32

happyjsl1 发表于 2015-8-11 09:02
还有一点我有疑问为什么执行计划里面显示table 'UNIQUEPARTS'的rows 是12837，从下面我搜集的统计信息应该是 ...
你先把 'PLAN_TABLE' is old version 这个问题解决了。

千问 · 2015-7-1 10:06:32

happyjsl1 发表于 2015-8-11 09:02
还有一点我有疑问为什么执行计划里面显示table 'UNIQUEPARTS'的rows 是12837，从下面我搜集的统计信息应该是 ...
另外要SET AUTOT TRACE看一下下面的执行计划和逻辑读的情况:
SELECT DISTINCT U.UNIQUEPART_ID
FROM SZHQUALITY.UNIQUEPARTS U
LEFT JOIN SZHQUALITY.COMPONENTS C
ON U.UNIQUEPART_ID = C.COMPONENTIDENTIFIER
LEFT JOIN SZHQUALITY.DISPATCH D
ON U.UNIQUEPART_ID = D.UNIQUEPART_ID
AND D.WHAT = 'XML_ARCHIVE'
WHERE (U.ARCHIVE_FLAG = 1)
AND (:B3 = 100 OR U.RESULT_STATE = :B3)
AND (:B2 IS NULL OR PART_CLASS = :B2)
AND (U.RESULT_DATE <= SYSTIMESTAMP - (:B1 / 24))
AND (C.COMPONENTIDENTIFIER IS NULL)
AND (D.UNIQUEPART_ID IS NULL);

然后：
ALTER INDEX DISPATCH_I1 DISABLE;
SELECT /*+ USE_HASH(U D)SWAP_JOIN_INPUTS(D)*/ DISTINCT U.UNIQUEPART_ID
FROM SZHQUALITY.UNIQUEPARTS U
LEFT JOIN SZHQUALITY.COMPONENTS C
ON U.UNIQUEPART_ID = C.COMPONENTIDENTIFIER
LEFT JOIN SZHQUALITY.DISPATCH D
ON U.UNIQUEPART_ID = D.UNIQUEPART_ID
AND D.WHAT = 'XML_ARCHIVE'
WHERE (U.ARCHIVE_FLAG = 1)
AND (:B3 = 100 OR U.RESULT_STATE = :B3)
AND (:B2 IS NULL OR PART_CLASS = :B2)
AND (U.RESULT_DATE <= SYSTIMESTAMP - (:B1 / 24))
AND (C.COMPONENTIDENTIFIER IS NULL)
AND (D.UNIQUEPART_ID IS NULL);
在用SET AUTOT TRACE看看执行计划逻辑读等有没有提升