DBA Colleagues:
had an interesting problem last night with adding a partitions and it having

a dramatic diminishing affect on a query. added a partition for December on
an existing partitioned table:

alter table PDOWNER.DATE_PORT_DETAILS
ADD PARTITION PORT_DATE9 VALUES LESS THAN (TO_DATE('2002-01-01
00:00:00','SYYYY-MM-DD-HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE
PD_D9 NOLOGGING;

The SQL that was having trouble was is:
SELECT DISTINCT TO_CHAR (startdate, 'YYYY MM'), otc_trade_seq,
counterpartyshortname, tradetype, SUM (notional_qty),
valuation_pr, TO_CHAR (tradedate, 'Mon DD, YYYY')
FROM v_rms2sas_fwd_np
WHERE RTRIM (LOWER (strategydescription)) = 'entergy pk cleco evangeline
hedge'
AND startdate >= TO_DATE ('2001-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND tradetype IN ('FPPHY', 'FPF', 'FPSWA', 'OSPHY')
AND archivedate = '29-NOV-01'
GROUP BY archivedate, otc_trade_seq, tradedate,
TO_CHAR (startdate, 'YYYY MM'),
counterpartyshortname, tradetype, valuation_pr
ORDER BY otc_trade_seq, TO_CHAR (startdate, 'YYYY MM'),
counterpartyshortname

I removed the newly added partition, recompiled and flushed the shared_pool
then had the query run again. At first this did not seem to have any
effect. But After about 1 hour it started to work. To verify that the
partition was part of the problem I removed the partition from a staging
environment and duplicated the run. It finished in the normal expected
time. I then added the partiton back with a modified date of '2001-12-31
00:00:00' This caused the query to stop running again. I want to note that
the table was fine. The problem is either with the views that hit this
table or the queries that use the views that hit this table.

if anyone has any clues or ideas, please let me know.

Oracle LazyDBA home page