求此条SQL的优化建议

显示全部楼层 · 2011-11-1 16:23:26

本帖最后由 wushi8 于 2012-12-12 12:31 编辑
这条语句其实就是判断是否有not exists，所以输出结果是1，并且加了rownum=1，但是逻辑读还是有100多万，语句每天执行上万次，想要进一步优化下，大家给点建议哈，表的具体信息放在二楼了。
SQL> select * from v$version;
BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi

PL/SQL Release 10.2.0.2.0 - Production

CORE10.2.0.2.0Production

TNS for Linux: Version 10.2.0.2.0 - Production

NLSRTL Version 10.2.0.2.0 - Production

SQL> set autot trace;
SQL> SELECT 1
2FROM MTL_SYSTEM_ITEMS_B IT
3 WHERE ROWNUM = 1
4 AND NOT EXISTS
5 (SELECT NULL
6
FROM MTL_ITEM_CATEGORIES C
7 WHERE C.CATEGORY_SET_ID = 1100000027
8
AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
9
AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);

Execution Plan
----------------------------------------------------------

Plan hash value: 3643499159

--------------------------------------------------------------------------------
--------------

| Id| Operation | Name
| Rows| Bytes | Cost (%CP
U)| Time |

--------------------------------------------------------------------------------
--------------

| 0 | SELECT STATEMENT|
| 1 |27 | 6 (
0)| 00:00:01 |

|*1 |COUNT STOPKEY|
| | |

|
|

| 2 | NESTED LOOPS ANTI |
| 2 |54 | 6 (
0)| 00:00:01 |

| 3 |TABLE ACCESS FULL| MTL_SYSTEM_ITEMS_B | 2 |20 | 2 (
0)| 00:00:01 |

|*4 |INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_U1 | 1 |17 | 2 (
0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM=1)

4 - access("C"."ORGANIZATION_ID"="IT"."ORGANIZATION_ID" AND

"C"."INVENTORY_ITEM_ID"="IT"."INVENTORY_ITEM_ID" AND "C"."CATEGORY
_SET_ID"=1100000027)

Statistics
----------------------------------------------------------

1recursive calls

0db block gets

1395380consistent gets

0physical reads

0redo size

508bytes sent via SQL*Net to client

492bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed

千问 · 2011-11-1 16:23:26

本帖最后由 wushi8 于 2012-12-12 12:34 编辑
SQL> SELECT table_name,
2 num_rows,
3 blocks,
4 empty_blocks,
5 avg_space,
6 chain_cnt,
7 avg_row_len,
8 global_stats,
9 user_stats,
10 sample_size,
11 last_analyzed
12FROM dba_tables
13 WHERE table_name = upper('MTL_SYSTEM_ITEMS_B')
14;
TABLE_NAME
NUM_ROWS BLOCKS EMPTY_BLOCKSAVG_SPACE
------------------------------ ---------- ---------- ------------ ----------
CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE LAST_ANAL

---------- ----------- --- --- ----------- ---------

MTL_SYSTEM_ITEMS_B
47130847639
0
0
0 528 YES NO 471308 11-DEC-12

SQL> SELECT table_name,
2 num_rows,
3 blocks,
4 empty_blocks,
5 avg_space,
6 chain_cnt,
7 avg_row_len,
8 global_stats,
9 user_stats,
10 sample_size,
11 last_analyzed
12FROM dba_tables
13 WHERE table_name = upper('MTL_ITEM_CATEGORIES');
TABLE_NAME
NUM_ROWS BLOCKS EMPTY_BLOCKSAVG_SPACE
------------------------------ ---------- ---------- ------------ ----------
CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE LAST_ANAL

---------- ----------- --- --- ----------- ---------

MTL_ITEM_CATEGORIES
473442851448
0
0
0
72 YES NO50092 11-DEC-12

SQL>
SELECT index_name,
uniqueness,
blevel blev,
leaf_blocks,
distinct_keys,
num_rows,
avg_leaf_blocks_per_key,
avg_data_blocks_per_key,
clustering_factor,
global_stats,
user_stats,
sample_size,
last_analyzed
FROM dba_indexes t
15WHERE table_name = upper('MTL_SYSTEM_ITEMS_B')
16;
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
CUX_MTL_SYSTEM_ITEMS_B_N2NONUNIQUE
2
2034
57
471308
35
1136
64770 YES
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_N1
NONUNIQUE
2
2421471308
471308
1
1
261603 YES
NO 471308 11-DEC-12
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
MTL_SYSTEM_ITEMS_B_N8
NONUNIQUE
2
1896
33
471308
57
1961
64722 YES
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_N4
NONUNIQUE
2
2100
33
471308
63
1961
64722 YES
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_N5
NONUNIQUE
0
0
0
0
0
0
0 YES
NO
0 11-DEC-12
MTL_SYSTEM_ITEMS_B_N2
NONUNIQUE
2
5534440392
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
459582
1
1
409036 YES
NO
93260 11-DEC-12
MTL_SYSTEM_ITEMS_B_N3
NONUNIQUE
2
3254
10
471308
325
8290
82906 YES
NO 471308 11-DEC-12

INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
MTL_SYSTEM_ITEMS_B_N10
NONUNIQUE
2
1946
74
471308
26
916
67834 YES
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_N9
NONUNIQUE
2
2109
583
471308
3
220
128572 YES
NO 471308 11-DEC-12
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
MTL_SYSTEM_ITEMS_B_N12
NONUNIQUE
2
2482
60
471308
41
1274
76454 YES
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_N13
NONUNIQUE
2
1841
34
471308
54
1903
64735 YES
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_N14
NONUNIQUE
2
1888
33
471308
57
1961
64722 YES
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_N11
NONUNIQUE
2
3172
39
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
471308
81
1667
65034 YES
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_N6
NONUNIQUE
2
2233
373
471268
5
530
197709 YES
NO 471268 11-DEC-12

INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
MTL_SYSTEM_ITEMS_B_N7
NONUNIQUE
2
1946
33
471308
58
1961
64722 YES
NO 471308 11-DEC-12
MTL_SYSTEM_ITEMS_B_U1
UNIQUE
2
1788471308
471308
1
1
444252 YES
NO 471308 11-DEC-12
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------

16 rows selected.

SQL>
SELECT index_name,
uniqueness,
blevel blev,
leaf_blocks,
distinct_keys,
num_rows,
avg_leaf_blocks_per_key,
avg_data_blocks_per_key,
clustering_factor,
global_stats,
user_stats,
sample_size,
last_analyzed
FROM dba_indexes t
15WHERE table_name = upper('MTL_ITEM_CATEGORIES');
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
MTL_ITEM_CATEGORIES_U1
UNIQUE
2
32507 4812104
4812104
1
1
565406 YES
NO 172456 11-DEC-12
MTL_ITEM_CATEGORIES_N3
NONUNIQUE
2
23278
725
4818874
32
1651
1197127 YES
NO 241997 11-DEC-12
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
MTL_ITEM_CATEGORIES_N1
NONUNIQUE
2
31022
627
4845753
49
1903
1193606 YES
NO 183225 11-DEC-12
MTL_ITEM_CATEGORIES_N2
NONUNIQUE
0
0
0
0
0
0
0 YES
INDEX_NAME
UNIQUENES BLEV LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------- ---------- ----------- -------------
NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR GLO
---------- ----------------------- ----------------------- ----------------- ---
USE SAMPLE_SIZE LAST_ANAL
--- ----------- ---------
NO
0 11-DEC-12

SQL>
select table_name, column_name, endpoint_number, endpoint_value
from dba_histograms
where table_name = 'MTL_ITEM_CATEGORIES'
and column_name in
5 ('ORGANIZATION_ID', 'CATEGORY_SET_ID', 'INVENTORY_ITEM_ID')
6;
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
ORGANIZATION_ID

422
104
MTL_ITEM_CATEGORIES
ORGANIZATION_ID

497
105
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
ORGANIZATION_ID

532
106
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------

570
109
MTL_ITEM_CATEGORIES
ORGANIZATION_ID

860
111
MTL_ITEM_CATEGORIES
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ORGANIZATION_ID

867
112
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
1139
249

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
1262
250
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
1548
251
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
1673
252
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1904
253
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
2179
254
MTL_ITEM_CATEGORIES
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ORGANIZATION_ID
2448
255
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
2597
256

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
2854
257
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
2861
481
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
3000
601
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
3001
681
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
3200
708
MTL_ITEM_CATEGORIES
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ORGANIZATION_ID
3471
821
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
3472
901

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
3715 1321
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
3969 1322
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
4203 1421
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
4463 1422
MTL_ITEM_CATEGORIES
ORGANIZATION_ID
4725 1501
MTL_ITEM_CATEGORIES
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ORGANIZATION_ID
4996 1502
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID

492 1100000021

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID

996 1100000024
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
1505 1100000025
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
1706 1100000026
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
2245 1100000027
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
2734 1100000028
MTL_ITEM_CATEGORIES
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
CATEGORY_SET_ID
3233 1100000029
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
3724 1100000030

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
4204 1100000031
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
4207 1100000042
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
4666 1100000043
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
4824 1100000044
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
4860 1100000063
MTL_ITEM_CATEGORIES
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
CATEGORY_SET_ID
4909 1100000064
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
4945 1100000065

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
4995 1100000066
MTL_ITEM_CATEGORIES
CATEGORY_SET_ID
4996 1100000103
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
MTL_ITEM_CATEGORIES
INVENTORY_ITEM_ID

0 1261
MTL_ITEM_CATEGORIES
INVENTORY_ITEM_ID
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------

12969330

46 rows selected.

千问 · 2011-11-1 16:23:26

最起码你要说说现在的情况和预期的情况啊

千问 · 2011-11-1 16:23:26

试试这段？
select 1 from dual
where EXISTS（
select INVENTORY_ITEM_ID，ORGANIZATION_ID from MTL_SYSTEM_ITEMS_B
minus
（select INVENTORY_ITEM_ID，ORGANIZATION_ID from MTL_ITEM_CATEGORIES whereCATEGORY_SET_ID = 1100000027）
）

千问 · 2011-11-1 16:23:26

执行计划一看就知道统计信息不正确

千问 · 2011-11-1 16:23:26

提几条建议：
1. rownum=1 放在这里是否正确？个人觉得是错误的
2. 执行计划中出现nest loop 有问题。MTL_SYSTEM_ITEMS_B是ERP里面物料的表，通常有几个G到几十G大小
3. 这是个大表，应该分区。为何执行计划中没有出现分区的信息？
4.语句每天执行上万次，是怎么执行的？是应用程序由用户调用还是接口(interface webmethod)调用？不同的调用方式可采用不同的方法

千问 · 2011-11-1 16:23:26

乱看到头大

千问 · 2011-11-1 16:23:26

1采集统计信息。
2说明割表的索引的字段？

千问 · 2011-11-1 16:23:26

把这条sql的执行计划贴出来

SELECT/*+push_subq(@xx) */1
FROM MTL_SYSTEM_ITEMS_B IT
WHERE ROWNUM = 1
AND NOT EXISTS
(SELECT /*+ QB_Name(xx) */ NULL

FROM MTL_ITEM_CATEGORIES C

WHERE C.CATEGORY_SET_ID = 1100000027
AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID

AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);

千问 · 2011-11-1 16:23:26

楼主：
请把这个执行计划贴出来
SELECT1
FROM MTL_SYSTEM_ITEMS_B IT
WHERE ROWNUM = 1
AND NOT EXISTS
(SELECT /*+ unnest */NULL

FROM MTL_ITEM_CATEGORIES C

WHERE C.CATEGORY_SET_ID = 1100000027

AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID

AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);