数据库：9207
操作系统：AIX 5.3
OE_ORDER_HEADERS_ALL 53,752 行数据
OE_ORDER_LINES_ALL 560,136 行数据
SELECT ROUND(NVL(SUM(DECODE(OTL.NAME,
'镸-^@M-^@璐ц-^L',
-1,
'镸-^@M-^@绁ㄨ-^L',
-1,
1) * OOLA.ORDERED_QUANTITY *
OOLA.UNIT_SELLING_PRICE),
0) / 10000,
4)
FROM OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
APPS.OE_LOOKUPS OL,
ONT.OE_TRANSACTION_TYPES_TL OTL
WHERE
DECODE(:B6, NULL, '1', CUX_OM_COMMON_PKG.GET_CHANNEL(OL.MEANING, 1, 1)) >=
NVL(:B6, '1')
AND DECODE(:B5, NULL, '1', CUX_OM_COMMON_PKG.GET_CHANNEL(OL.MEANING, 1, 1)) <=
NVL(:B5, '1')
AND DECODE(:B4, NULL, '1', CUX_OM_COMMON_PKG.GET_CHANNEL(OL.MEANING, 2, 1)) >=
NVL(:B4, '1')
AND DECODE(:B3, NULL, '1', CUX_OM_COMMON_PKG.GET_CHANNEL(OL.MEANING, 2, 1)) <=
NVL(:B3, '1')
AND DECODE(:B2, NULL, '1', CUX_OM_COMMON_PKG.GET_CHANNEL(OL.MEANING, 3, 1)) >=
NVL(:B2, '1')
AND DECODE(:B1, NULL, '1', CUX_OM_COMMON_PKG.GET_CHANNEL(OL.MEANING, 3, 1)) <=
NVL(:B1, '1')
AND OOLA.FLOW_STATUS_CODE NOT IN ('ENTERED', 'CANCELLED')
AND SYSDATE BETWEEN NVL(OL.START_DATE_ACTIVE, SYSDATE) AND
NVL(OL.END_DATE_ACTIVE, SYSDATE)
AND TO_NUMBER(DECODE(:B9,
'1',
SUBSTR(OOHA.ATTRIBUTE9, 6, 2),
'2',
TO_CHAR(OOHA.ORDERED_DATE, 'MM'),
'3',
TO_CHAR(OOHA.CREATION_DATE, 'MM'),
'4',
TO_CHAR(OOLA.REQUEST_DATE, 'mm'))) = :B8
AND TO_NUMBER(DECODE(:B9,
'1',
SUBSTR(OOHA.ATTRIBUTE9, 6, 2),
'2',
TO_CHAR(OOHA.ORDERED_DATE, 'MM'),
'3',
TO_CHAR(OOHA.CREATION_DATE, 'MM'),
'4',
TO_CHAR(OOLA.REQUEST_DATE, 'mm'))) = :B8
AND DECODE(:B9,
'1',
SUBSTR(OOHA.ATTRIBUTE9, 1, 4),
'2',
TO_CHAR(OOHA.ORDERED_DATE, 'YYYY'),
'3',
TO_CHAR(OOHA.CREATION_DATE, 'YYYY'),
'4',
TO_CHAR(OOLA.REQUEST_DATE, 'yyyy')) = TO_CHAR(:B10)
AND OOHA.HEADER_ID = OOLA.HEADER_ID
AND OL.LOOKUP_CODE = :B7
AND OL.LOOKUP_CODE = :B7
AND OL.LOOKUP_CODE = OOHA.SALES_CHANNEL_CODE
AND OTL.TRANSACTION_TYPE_ID = OOLA.LINE_TYPE_ID
AND OL.ENABLED_FLAG = 'Y'
AND OL.LOOKUP_TYPE = 'SALES_CHANNEL'
AND OTL.LANGUAGE = USERENV('LANG')
AND OOHA.ORDER_TYPE_ID = :B11
AND (TO_CHAR(OOHA.ORDERED_DATE, 'YYYY') = TO_CHAR(:B10) OR
(OOHA.ORDER_TYPE_ID = 1009 AND :B9 = 4))
SQL解释如下：
SELECT STATEMENT, GOAL = CHOOSE 358 1 158 358
SORT AGGREGATE 1 158
NESTED LOOPS 358 1 158 358
NESTED LOOPS 357 1 136 357
NESTED LOOPS 352 1 105 352
TABLE ACCESS BY INDEX ROWID APPLSYS FND_LOOKUP_VALUES 3 1 68 3
INDEX UNIQUE SCAN APPLSYS FND_LOOKUP_VALUES_U1 2 1 2
TABLE ACCESS FULL ONT OE_ORDER_HEADERS_ALL 349 1 37 349
TABLE ACCESS BY INDEX ROWID ONT OE_ORDER_LINES_ALL 5 1 31 5
INDEX RANGE SCAN ONT OE_ORDER_LINES_N1 2 10 2
TABLE ACCESS BY INDEX ROWID ONT OE_TRANSACTION_TYPES_TL 1 1 22 1
INDEX UNIQUE SCAN ONT OE_TRANSACTION_TYPES_TL_U1 1
建立的索引有：
------OE_ORDER_LINES_ALL的索引：
create index ONT.OE_ORDER_LINES_N1 on ONT.OE_ORDER_LINES_ALL (HEADER_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
-----OE_ORDER_HEADERS_ALL的索引
create unique index ONT.OE_ORDER_HEADERS_U1 on ONT.OE_ORDER_HEADERS_ALL (HEADER_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index ONT.OE_ORDER_HEADERS_N99 on ONT.OE_ORDER_HEADERS_ALL (TO_CHAR(ORDERED_DATE,'YYYY'))
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index ONT.OE_ORDER_HEADERS_N9 on ONT.OE_ORDER_HEADERS_ALL (QUOTE_NUMBER, ORDER_TYPE_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
------OE_TRANSACTION_TYPES_TL的索引
create unique index ONT.OE_TRANSACTION_TYPES_TL_U1 on ONT.OE_TRANSACTION_TYPES_TL (TRANSACTION_TYPE_ID, LANGUAGE)
tablespace APPS_TS_SEED
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
希望前辈指点一二，谢谢了！
zergduan
发布于2008-08-03 16:52:57
oradbHome
发布于2008-08-03 21:04:19
