Home » RDBMS Server » Performance Tuning » Query to a partition table run to slow (2 Merged) (Oracle 9, HP-UX)
| Query to a partition table run to slow (2 Merged) [message #481593] |
Thu, 04 November 2010 08:30  |
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
|
Member |
|
|
Hello,
One of the query takes too long to run, i think the main problem is in two places:
- In the calculating of values TT_CUST, which uses a GROUP BY to perform the SUM multiple values.
- In the clause WHERE to search dates in an interval of days (BETWEEN 180 days). The table TAB_INPUT is partitioned by CITY and DT_REG, the query will search in different partitions. I can't remove the condition BETWEEN because the table has five years of data.
With the query is intended to give a total volume of sales (TT_CUST), date of last sale (DT_LAST_DAY) and sales were made during 5 / 60 consecutive days, during the last 180 days.
the query
with TAB_INPUT as
(
SELECT 'LONDON' as CITY, TO_DATE('20101104', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'MADRID' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 50 as VAL_EXP01, 50 as VAL_EXP02, 50 as VAL_EXP03, 50 as VAL_EXP04, 50 as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10020121' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, NULL as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101102', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FEERS' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 10 as VAL_EXP01, 200 as VAL_EXP02, 30 as VAL_EXP03, 150 as VAL_EXP04, 50 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSEFF' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 60 as VAL_EXP01, NULL as VAL_EXP02, 70 as VAL_EXP03, 50 as VAL_EXP04, 150 as VAL_EXP05, 0.04 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 50 as VAL_EXP01, 30 as VAL_EXP02, 10 as VAL_EXP03, NULL as VAL_EXP04, NULL as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTGHB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 30 as VAL_EXP01, 30 as VAL_EXP02, 10 as VAL_EXP03, NULL as VAL_EXP04, 60 as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSEFF' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 30 as VAL_EXP01, 30 as VAL_EXP02, 70 as VAL_EXP03, NULL as VAL_EXP04, 10 as VAL_EXP05, 0.03 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTRAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101030', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 300 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101029', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 200 as VAL_EXP01, NULL as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '40' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101028', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSLAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101027', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, NULL as VAL_EXP02, NULL as VAL_EXP03, NULL as VAL_EXP04, NULL as VAL_EXP05, 0 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSLAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100608', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 400 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100508', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 500 as VAL_EXP04, NULL as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTRAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100408', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.04 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100308', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, NULL as VAL_EXP05, 0.04 as VAL_TAX, '40' as STATE, 'N' as FLSBARN, 'FTGHB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100208', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.045 as VAL_TAX, '51' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100108', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, NULL as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL
)
SELECT CITY, CLIENT_ID, :DT_PROCESS as DT_REG,
MAX(TO_CHAR(DT_REG, 'YYYYMMDD')) as DT_LAST_DAY,
MAX(DECODE(DT_60DAYSREG, DT_REG+60, 60,DECODE(DT_5DAYSREG , DT_REG+5, 5, 0))) as NUM_DAYS_CONSECT,
SUM(TT_CUST) as TT_CUST
FROM (
SELECT CITY, CLIENT_ID, DT_REG,
LEAD (DT_REG, 5, NULL) OVER (PARTITION BY CITY, CLIENT_ID ORDER BY CITY, CLIENT_ID, DT_REG) DT_5DAYSREG,
LEAD (DT_REG, 60, NULL) OVER (PARTITION BY CITY, CLIENT_ID ORDER BY CITY, CLIENT_ID, DT_REG) DT_60DAYSREG,
TT_CUST
FROM (SELECT CITY, DT_REG, CLIENT_ID,
SUM(NVL(VAL_EXP01, 0) * VAL_TAX) + SUM(NVL(VAL_TAX, 0) * NVL(VAL_EXP02, 0)) + SUM(NVL(VAL_EXP03, 0))
+ SUM(NVL(VAL_EXP04, 0))+ SUM(NVL(VAL_EXP05, 0)) AS TT_CUST
FROM TAB_INPUT
WHERE DT_REG between (TO_DATE(:DT_PROCESS, 'YYYYMMDD') -180) and TO_DATE(:DT_PROCESS, 'YYYYMMDD')
AND CITY = :WSCITY
AND STATE NOT IN ('10', '11', '21', '33')
AND FLSBARN = 'N'
AND CTRTTYPE <> 'RAFTH'
GROUP BY CITY, DT_REG, CLIENT_ID
ORDER BY CLIENT_ID, DT_REG)
WHERE TT_CUST > 0
)
GROUP BY CITY, CLIENT_ID;
the variables
DT_PROCESS = '20101104'
WSCITY = 'LONDON'
Accepts suggestions to optimization the query.
|
|
|
|
| Query to a partition table run to slow [message #481594 is a reply to message #481593] |
Thu, 04 November 2010 08:31  |
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
|
Member |
|
|
Hello,
One of the query takes too long to run, i think the main problem is in two places:
- In the calculating of values TT_CUST, which uses a GROUP BY to perform the SUM multiple values.
- In the clause WHERE to search dates in an interval of days (BETWEEN 180 days). The table TAB_INPUT is partitioned by CITY and DT_REG, the query will search in different partitions. I can't remove the condition BETWEEN because the table has five years of data.
With the query is intended to give a total volume of sales (TT_CUST), date of last sale (DT_LAST_DAY) and sales were made during 5 / 60 consecutive days, during the last 180 days.
the query
with TAB_INPUT as
(
SELECT 'LONDON' as CITY, TO_DATE('20101104', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'MADRID' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 50 as VAL_EXP01, 50 as VAL_EXP02, 50 as VAL_EXP03, 50 as VAL_EXP04, 50 as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10020121' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, NULL as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101102', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FEERS' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 10 as VAL_EXP01, 200 as VAL_EXP02, 30 as VAL_EXP03, 150 as VAL_EXP04, 50 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSEFF' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 60 as VAL_EXP01, NULL as VAL_EXP02, 70 as VAL_EXP03, 50 as VAL_EXP04, 150 as VAL_EXP05, 0.04 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 50 as VAL_EXP01, 30 as VAL_EXP02, 10 as VAL_EXP03, NULL as VAL_EXP04, NULL as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTGHB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 30 as VAL_EXP01, 30 as VAL_EXP02, 10 as VAL_EXP03, NULL as VAL_EXP04, 60 as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSEFF' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 30 as VAL_EXP01, 30 as VAL_EXP02, 70 as VAL_EXP03, NULL as VAL_EXP04, 10 as VAL_EXP05, 0.03 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTRAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101030', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 300 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101029', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 200 as VAL_EXP01, NULL as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '40' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101028', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSLAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101027', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, NULL as VAL_EXP02, NULL as VAL_EXP03, NULL as VAL_EXP04, NULL as VAL_EXP05, 0 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSLAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100608', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 400 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100508', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 500 as VAL_EXP04, NULL as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTRAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100408', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.04 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100308', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, NULL as VAL_EXP05, 0.04 as VAL_TAX, '40' as STATE, 'N' as FLSBARN, 'FTGHB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100208', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.045 as VAL_TAX, '51' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100108', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, NULL as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL
)
SELECT CITY, CLIENT_ID, :DT_PROCESS as DT_REG,
MAX(TO_CHAR(DT_REG, 'YYYYMMDD')) as DT_LAST_DAY,
MAX(DECODE(DT_60DAYSREG, DT_REG+60, 60,DECODE(DT_5DAYSREG , DT_REG+5, 5, 0))) as NUM_DAYS_CONSECT,
SUM(TT_CUST) as TT_CUST
FROM (
SELECT CITY, CLIENT_ID, DT_REG,
LEAD (DT_REG, 5, NULL) OVER (PARTITION BY CITY, CLIENT_ID ORDER BY CITY, CLIENT_ID, DT_REG) DT_5DAYSREG,
LEAD (DT_REG, 60, NULL) OVER (PARTITION BY CITY, CLIENT_ID ORDER BY CITY, CLIENT_ID, DT_REG) DT_60DAYSREG,
TT_CUST
FROM (SELECT CITY, DT_REG, CLIENT_ID,
SUM(NVL(VAL_EXP01, 0) * VAL_TAX) + SUM(NVL(VAL_TAX, 0) * NVL(VAL_EXP02, 0)) + SUM(NVL(VAL_EXP03, 0))
+ SUM(NVL(VAL_EXP04, 0))+ SUM(NVL(VAL_EXP05, 0)) AS TT_CUST
FROM TAB_INPUT
WHERE DT_REG between (TO_DATE(:DT_PROCESS, 'YYYYMMDD') -180) and TO_DATE(:DT_PROCESS, 'YYYYMMDD')
AND CITY = :WSCITY
AND STATE NOT IN ('10', '11', '21', '33')
AND FLSBARN = 'N'
AND CTRTTYPE <> 'RAFTH'
GROUP BY CITY, DT_REG, CLIENT_ID
ORDER BY CLIENT_ID, DT_REG)
WHERE TT_CUST > 0
)
GROUP BY CITY, CLIENT_ID;
the variables
DT_PROCESS = '20101104'
WSCITY = 'LONDON'
Accepts suggestions to optimization the query.
Thanks in advance for your help
regards
|
|
|
|
|
|
| Re: Query to a partition table run to slow [message #481647 is a reply to message #481598] |
Thu, 04 November 2010 18:29 |
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
|
Member |
|
|
Hi,
same information that could help to analyze the "slow" query.
1. Post your SQL
with TAB_INPUT as
(
SELECT 'LONDON' as CITY, TO_DATE('20101104', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'MADRID' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 50 as VAL_EXP01, 50 as VAL_EXP02, 50 as VAL_EXP03, 50 as VAL_EXP04, 50 as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101103', 'YYYYMMDD') as DT_REG, '10020121' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, NULL as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101102', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FEERS' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 10 as VAL_EXP01, 200 as VAL_EXP02, 30 as VAL_EXP03, 150 as VAL_EXP04, 50 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSEFF' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101101', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 60 as VAL_EXP01, NULL as VAL_EXP02, 70 as VAL_EXP03, 50 as VAL_EXP04, 150 as VAL_EXP05, 0.04 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 50 as VAL_EXP01, 30 as VAL_EXP02, 10 as VAL_EXP03, NULL as VAL_EXP04, NULL as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTGHB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 30 as VAL_EXP01, 30 as VAL_EXP02, 10 as VAL_EXP03, NULL as VAL_EXP04, 60 as VAL_EXP05, 0.02 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSEFF' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101031', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 30 as VAL_EXP01, 30 as VAL_EXP02, 70 as VAL_EXP03, NULL as VAL_EXP04, 10 as VAL_EXP05, 0.03 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTRAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101030', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 300 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101029', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 200 as VAL_EXP01, NULL as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '40' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101028', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSLAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20101027', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, NULL as VAL_EXP02, NULL as VAL_EXP03, NULL as VAL_EXP04, NULL as VAL_EXP05, 0 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'RSLAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100608', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 400 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.06 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100508', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, NULL as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 500 as VAL_EXP04, NULL as VAL_EXP05, 0.06 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FTRAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100408', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.04 as VAL_TAX, '43' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100308', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, NULL as VAL_EXP05, 0.04 as VAL_TAX, '40' as STATE, 'N' as FLSBARN, 'FTGHB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100208', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, 100 as VAL_EXP02, 100 as VAL_EXP03, NULL as VAL_EXP04, 100 as VAL_EXP05, 0.045 as VAL_TAX, '51' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL UNION
SELECT 'LONDON' as CITY, TO_DATE('20100108', 'YYYYMMDD') as DT_REG, '10010111' as CLIENT_ID, 100 as VAL_EXP01, NULL as VAL_EXP02, 100 as VAL_EXP03, 100 as VAL_EXP04, 100 as VAL_EXP05, 0.05 as VAL_TAX, '50' as STATE, 'N' as FLSBARN, 'FELAB' as CTRTTYPE FROM DUAL
)
SELECT CITY, CLIENT_ID, :DT_PROCESS as DT_REG,
MAX(TO_CHAR(DT_REG, 'YYYYMMDD')) as DT_LAST_DAY,
MAX(DECODE(DT_60DAYSREG, DT_REG+60, 60,DECODE(DT_5DAYSREG , DT_REG+5, 5, 0))) as NUM_DAYS_CONSECT,
SUM(TT_CUST) as TT_CUST
FROM (
SELECT CITY, CLIENT_ID, DT_REG,
LEAD (DT_REG, 5, NULL) OVER (PARTITION BY CITY, CLIENT_ID ORDER BY CITY, CLIENT_ID, DT_REG) DT_5DAYSREG,
LEAD (DT_REG, 60, NULL) OVER (PARTITION BY CITY, CLIENT_ID ORDER BY CITY, CLIENT_ID, DT_REG) DT_60DAYSREG,
TT_CUST
FROM (SELECT CITY, DT_REG, CLIENT_ID,
SUM(NVL(VAL_EXP01, 0) * VAL_TAX) + SUM(NVL(VAL_TAX, 0) * NVL(VAL_EXP02, 0)) + SUM(NVL(VAL_EXP03, 0))
+ SUM(NVL(VAL_EXP04, 0))+ SUM(NVL(VAL_EXP05, 0)) AS TT_CUST
FROM TAB_INPUT
WHERE DT_REG between (TO_DATE(:DT_PROCESS, 'YYYYMMDD') -180) and TO_DATE(:DT_PROCESS, 'YYYYMMDD')
AND CITY = :WSCITY
AND STATE NOT IN ('10', '11', '21', '33')
AND FLSBARN = 'N'
AND CTRTTYPE <> 'RAFTH'
GROUP BY CITY, DT_REG, CLIENT_ID
ORDER BY CLIENT_ID, DT_REG)
WHERE TT_CUST > 0
)
GROUP BY CITY, CLIENT_ID;
2 - Post the EXPLAIN PLAN that Oracle uses to execute your SQL.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 54 | | | | | |
| 1 | SORT GROUP BY | | 1 | 50 | 54 | | | 12,03 | P->S | QC (RAND) |
| 2 | SORT GROUP BY | | 1 | 50 | 54 | | | 12,02 | P->P | HASH |
| 3 | VIEW | | 1 | 50 | 48 | | | 12,02 | PCWP | |
| 4 | WINDOW SORT | | 1 | 117 | 48 | | | 12,02 | PCWP | |
| 5 | FILTER | | | | | | | 12,02 | PCWC | |
| 6 | SORT GROUP BY | | 1 | 117 | 48 | | | 12,01 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 117 | 48 | | | 12,00 | P->P | HASH |
| 8 | FILTER | | | | | | | 12,00 | PCWC | |
| 9 | PARTITION RANGE ITERATOR | | | | | KEY | KEY | 12,00 | PCWP | |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB_INPUT | 1 | 117 |43,3525| KEY | KEY | 12,00 | PCWP | |
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | | | 12,00 | PCWP | |
| 12 | BITMAP INDEX RANGE SCAN | I_TAB_INPUT_DT_REG | | | | KEY | KEY | 12,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
3 - Post the DDL used to create your tables and their indexes
Note : I had removed the partition
DROP TABLE TAB_INPUT CASCADE CONSTRAINTS;
--
-- TAB_INPUT (Table)
--
CREATE TABLE TAB_INPUT
(
CITY VARCHAR2(10 BYTE),
DT_REG DATE,
IDFGRT CHAR(2 BYTE),
IDFGDJ CHAR(13 BYTE),
IDAUX VARCHAR2(16 BYTE),
CTRTTYPE VARCHAR2(8 BYTE),
STATE CHAR(2 BYTE),
IDBVDRET CHAR(8 BYTE),
FLSBARN CHAR(3 BYTE),
IDSNOTIME CHAR(10 BYTE),
IDEURMME CHAR(7 BYTE),
IDEURMMEAM CHAR(15 BYTE),
IDEUROME CHAR(7 BYTE),
IDEUROMEAM CHAR(15 BYTE),
CLIENT_ID CHAR(10 BYTE),
IDAMRBME CHAR(7 BYTE),
IDAMRBMEAM CHAR(15 BYTE),
IDAFRCBME CHAR(8 BYTE),
IDTYPOCB CHAR(2 BYTE),
IDCTRTDOME CHAR(13 BYTE),
CDTYCL CHAR(2 BYTE),
IDAGTEMIME CHAR(8 BYTE),
IDAGTCBME CHAR(8 BYTE),
IDEE CHAR(6 BYTE),
IDWW CHAR(5 BYTE),
IDSGMCB CHAR(8 BYTE),
IDSGMCBINI CHAR(8 BYTE),
CDTIPES CHAR(1 BYTE),
CDRES CHAR(1 BYTE),
CDSECT CHAR(6 BYTE),
CDOGESDS CHAR(8 BYTE),
CDGESDS CHAR(8 BYTE),
CDCAPTIER CHAR(2 BYTE),
DICAPTIER DATE,
DTVCAPTIER DATE,
INREACAPT CHAR(1 BYTE),
NUREACAPT NUMBER(4),
DTREACAPT DATE,
DFCAPTIER DATE,
CDFECCAPT CHAR(2 BYTE),
CDREAG CHAR(1 BYTE),
NUCAPTIER NUMBER(9),
CDOGESCTRT CHAR(8 BYTE),
CDGESCTRT CHAR(8 BYTE),
CDTPRD CHAR(5 BYTE),
VAL_EXP01 NUMBER(15,2),
VAL_EXP02 NUMBER(15,2),
VAL_EXP03 NUMBER(15,2),
VAL_EXP04 NUMBER(15,2),
VAL_EXP05 NUMBER(15,2),
VAL_EXP06 NUMBER(15,2),
VAL_EXP07 NUMBER(15,2),
VAL_EXP08 NUMBER(15,2),
VAL_EXP09 NUMBER(15,2),
VAL_EXP10 NUMBER(15,2),
VAL_EXP11 NUMBER(15,2),
VAL_EXP12 NUMBER(15,2),
VAL_TAX NUMBER(10,9),
VAL_TAX_AB NUMBER(10,9),
VAL_TAX_DF NUMBER(10,9),
CPEMPSEC CHAR(1 BYTE),
CDINSTSEC CHAR(4 BYTE),
CTPERDAO CHAR(15 BYTE),
INPERJMORA CHAR(1 BYTE),
PDPRCTR CHAR(2 BYTE),
DFVALCTR DATE,
DTINCUMPCTR DATE,
DICREVENC DATE,
DTULTJMORA DATE,
DFCREVENC DATE,
PPCTRCT NUMBER(8),
INESTSBRC CHAR(2 BYTE),
INESTAORI CHAR(5 BYTE),
CDCLINC CHAR(2 BYTE),
CDCLINCAN CHAR(2 BYTE),
DTACT DATE,
INDOCSC CHAR(1 BYTE),
INISELO CHAR(1 BYTE),
MTESTREC NUMBER(15,2),
INCOMPROC CHAR(1 BYTE),
INCTBJUR CHAR(1 BYTE),
DTCTBCTR DATE,
DTPASREPC DATE,
DTPEDPASRE DATE,
MDCTRDV NUMBER(15,2),
CTPLALIQ CHAR(1 BYTE),
CTAXA CHAR(2 BYTE),
TXSPREAD NUMBER(12,11),
CDSINALSPR CHAR(1 BYTE),
DTRVTXSPR DATE,
CDFINCRE CHAR(5 BYTE),
MTOTCTRDV NUMBER(15,2),
NUSQMT NUMBER(4),
STATEA CHAR(2 BYTE),
NUPLREC NUMBER(4),
CTPLREC CHAR(2 BYTE),
DIPLANO DATE,
PDPRPLREC CHAR(2 BYTE),
DTPPREPL DATE,
DTUPREPL DATE,
DIVENPLREC NUMBER(4),
NUMTASSPL NUMBER(4),
DTVALPLREC DATE,
NDPERISEN NUMBER(4),
DTINCUMPPR DATE,
CDESTPL CHAR(2 BYTE),
DTREVPPL DATE,
DTACTPL DATE,
DFPLANO DATE,
INMOTFPL CHAR(2 BYTE),
TXJURPL NUMBER(10,9),
NUPREPLREC NUMBER(4),
CTINTERV CHAR(2 BYTE),
ANOCALPRV NUMBER(4),
EMCALCPRV NUMBER(4),
INCTRSEC CHAR(1 BYTE),
CTPROVISAO CHAR(1 BYTE),
DTEXCOLCPRV DATE,
DTCTBPRV DATE,
CDCALPRV CHAR(1 BYTE),
MTPRVCAMESV NUMBER(15,2),
MTPRVCAMESD NUMBER(15,2),
MTDIFACANV NUMBER(15,2),
MTDIFACAND NUMBER(15,2),
APLEMIORI CHAR(2 BYTE),
INENTRADACV CHAR(1 BYTE),
INSAIDACV CHAR(1 BYTE),
INPASSJUR CHAR(1 BYTE),
INREENTRACV CHAR(1 BYTE),
CDRSCARC CHAR(3 BYTE),
DTPRESC DATE,
INGARREAIS CHAR(1 BYTE),
NUINCCV CHAR(2 BYTE),
IDRBES VARCHAR2(20 BYTE),
DIRF DATE,
DFRF DATE,
IDTRANS CHAR(3 BYTE),
CTSITREG CHAR(1 BYTE),
MTIMPSUTIL NUMBER(15,2),
MTIMPSUTDV NUMBER(15,2),
INBIC NUMBER(1),
MTBONPREST NUMBER(15,2),
MTBONAUF NUMBER(15,2),
MTBONIFTOT NUMBER(15,2),
VAL_EXP07TOT NUMBER(15,2),
MCEFEREC NUMBER(15,2),
MCEFERECDV NUMBER(15,2),
MTIMPCOMREC NUMBER(15,2),
MTIMPCOMRECDV NUMBER(15,2),
INISEISCOM CHAR(1 BYTE),
NUPRSGRI CHAR(11 BYTE),
NUATPRES NUMBER(4),
INREACTRT CHAR(1 BYTE),
IDCTRTLPGL CHAR(11 BYTE),
INCTBDES CHAR(1 BYTE),
MDJUDICTB NUMBER(15,2),
MDOUTCTB NUMBER(15,2),
CDCTRCONTBAL CHAR(1 BYTE)
)
TABLESPACE STSBCRV
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (DT_REG, CITY)
(
PARTITION P20100421_LONDON VALUES LESS THAN (TO_DATE(' 2010-04-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), 'LONDON')
TABLESPACE STSBCRV
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_MADRID VALUES LESS THAN (TO_DATE(' 2010-04-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), 'MADRID')
TABLESPACE STSBCRV
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_PARIS VALUES LESS THAN (TO_DATE(' 2010-04-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), 'PARIS')
TABLESPACE STSBCRV
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
.... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... ....
PARTITION P20101010_LONDON VALUES LESS THAN (TO_DATE(' 2010-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), 'LONDON')
TABLESPACE STSBCRV
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20101010_MADRID VALUES LESS THAN (TO_DATE(' 2010-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), 'MADRID')
TABLESPACE STSBCRV
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20101010_PARIS VALUES LESS THAN (TO_DATE(' 2010-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), 'PARIS')
TABLESPACE STSBCRV
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOMONITORING;
--
-- IDX_TAB_INPUT (Index)
--
CREATE INDEX IDX_TAB_INPUT ON TAB_INPUT
(CPEMPSEC, IDFGDJ, IDAUX)
TABLESPACE SISBCRV
INITRANS 2
MAXTRANS 255
LOCAL (
PARTITION P20100421_LONDON
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_MADRID
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_PARIS
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
.... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... ....
PARTITION P20101010_LONDON
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20101010_MADRID
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20101010_PARIS
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
);
--
-- IDX_TAB_INPUT_IDDICREVENC (Index)
--
CREATE INDEX IDX_TAB_INPUT_IDDICREVENC ON TAB_INPUT
(IDFGDJ, IDAUX, DICREVENC)
TABLESPACE SISBCRV
INITRANS 2
MAXTRANS 255
LOCAL (
PARTITION P20100421_LONDON
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_MADRID
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_PARIS
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
.... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... ....
);
--
-- IDX_TAB_INPUTME (Index)
--
CREATE INDEX IDX_TAB_INPUTME ON TAB_INPUT
(IDFGDJ, IDAUX, STATE, CITY)
TABLESPACE SISBCRV
INITRANS 2
MAXTRANS 255
LOCAL (
PARTITION P20100421_LONDON
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_MADRID
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_PARIS
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
.... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... ....
);
--
-- I_TAB_INPUT_IDSNOTIME (Index)
--
CREATE INDEX I_TAB_INPUT_IDSNOTIME ON TAB_INPUT
(IDSNOTIME, CITY)
TABLESPACE SISBCRV
INITRANS 2
MAXTRANS 255
LOCAL (
PARTITION P20100421_LONDON
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_MADRID
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_PARIS
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
.... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... ....
);
--
-- I_TAB_INPUT_DT_REG (Index)
--
CREATE BITMAP INDEX I_TAB_INPUT_DT_REG ON TAB_INPUT
(DT_REG, CITY)
TABLESPACE SISBCRV
INITRANS 2
MAXTRANS 255
LOCAL (
PARTITION P20100421_LONDON
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_MADRID
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P20100421_PARIS
TABLESPACE SISBCRV
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
.... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... .... ....
);
4 - Post a Trace of the problem SQL when it is running
ALTER SESSION SET SQL_TRACE=TRUE; -- DONE
ALTER SESSION SET SQL_TRACE=FALSE; --DONE
SHOW PARAMETER SMCRTBW -- Return an error message
Additional information : the table TAB_INPUT is partitioned and is another db, we are accessing to this table using dblink
Any kind of any help, will be very useful
|
|
|
|
| Re: Query to a partition table run to slow [message #481866 is a reply to message #481647] |
Sun, 07 November 2010 21:27 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
>>Additional information : the table TAB_INPUT is partitioned and is another db, we are accessing to this table using dblink
So why don't I see the REMOTE keyword in your plan? Could it be that you have not posted the plan from the same server on which you are running the query?
Rather than range partitioning on two columns, you would do better to use COMPOSITE partitioning (RANGE and LIST). This would allow you to perform partition pruning on the CITY.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Tue Apr 30 15:51:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
