Hi Experts ,
I aplogize for such a big para but i thought more info might help .
Could you please help me I am Struggling to reduce a time taken by query .
I have one table which has 2 Billion record . Finally only 26k gets deleted . Query is taking 3 hours to completed :(.
Query I am using is ( PI of both the tables TICN and TIC is Column A ) ( 50% SKewness on column B I am not sure whether this could be the reason if it is please comment on this too )
DEL FROM TIC
( SEL 1 FROM TICN
WHERE TICN.A = TIC.A
AND TICN.B = TIC.B
AND TICN.C <> TIC.C
AND TICN.B = 36
AND TIC.B = 36
Explain plan looks like -:
We do an all-AMPs JOIN step from TIC by way of an
all-rows scan with a condition of ("(B
(FLOAT, FORMAT '-9.99999999999999E-999'))= 3.60000000000000E 001"),
which is joined to TICN by way of an all-rows
scan with a condition of ("(TICN.B (FLOAT,
FORMAT '-9.99999999999999E-999'))= 3.60000000000000E 001").
TIC and TICN are joined using an
inclusion merge join, with a join condition of (
((TICN.B = TIC.B) AND
((TICN.A = TIC.A) AND
((NOT (TIC.A ISNULL )) AND
((TIC.B (FLOAT, FORMAT
'-9.99999999999999E-999'))= 3.60000000000000E 001 ))))"). The
input table TICN will not be cached in memory.
The result goes into Spool 2 (all_amps), which is built locally on
the AMPs. Then we do a SORT to order Spool 2 by the hash code of
(TIC.ROWID) the sort key in spool field1 eliminating
duplicate rows. The size of Spool 2 is estimated with no
confidence to be 19 rows (342 bytes). The estimated time for this
step is 0.01 seconds.
5) We do an all-AMPs MERGE DELETE to TIC from Spool 2
(Last Use) via the row id. The size is estimated with no
confidence to be 19 rows. The estimated time for this step is
2 Hour 36 Minutes .
Now I changed my query to
DEL FROM TIC
TICN.A = TIC.A
TICN.B = TIC.B
TICN.C <> TIC.C
TICN.B= 36 ;
Now my explain plan
2) Next, we lock a distinct DB_REPRISE."pseudo table" for write on a
RowHash to prevent global deadlock for DB_REPRISE.TIC.
3) We lockTICN for read, and we lock
TIC for write.
4) We do an all-AMPs MERGE DELETE to TIC from
TICN by way of a RowHash match scan.
The size is estimated with low confidence to be 56 rows. The
estimated time for this step is 10.11 seconds.
Although Second plan looks better to me but still it takes 3.5 Hour and When I took stats on PI column A time in explain plan increased to 5 hours . Please comments which plan looks better to you .
Although join on PI with stats its showing more time in explain . Could you please help me reducing the query time .
Thanks in advance .
what's the PI/partitioning of both tables?
Are there any Secondary Indexes on TIC?
And you're using the wrong datatype for column B: according to the explain it's a (VAR)CHAR, but you use a numeric value in WHERE. Should probably be B= '36' instead.
This is OBIEE generated query as per business joins; it was running for long time, I did some kind of initial tuning but still it runs for a long time, would you please help me to tune this query
sum(case when T66199."PER_NAME_MONTH" = '2014 / 03' then T298388."NUM_JC_CLS" end ) ,
sum(case when T66199."PER_NAME_MONTH" = '2014 / 03' then A."NUM_JC_CLS" end )
"W_DAY_D" T66199 ,
where ( T66199."YEAR_AGO_WID" = T298388."DATE_WID"
and T66199."TWO_YR_AGO_WID" = A."DATE_WID"
INDEX's and STATS are gathered as we've mentined below.
CREATE INDEX W_DAY_D_SI ( YEAR_AGO_WID) ON DEV_OLAP.W_DAY_D;
COLLECT STATS ON DEV_OLAP.W_DAY_D INDEX (YEAR_AGO_WID);
CREATE INDEX W_DAY_D_SI_1 ( TWO_YR_AGO_WID) ON DEV_OLAP.W_DAY_D;
COLLECT STATS ON DEV_OLAP.W_DAY_D INDEX (TWO_YR_AGO_WID);
COLLECT STATISTICS WC_JOBCARD_A COLUMN DATE_WID;
COLLECT STATISTICS W_DAY_D COLUMN TWO_YR_AGO_WID;
OBIEE has generated two queries, this was one of them which would not give good result. They refreshed same report with content level changes at RPD, Now it refreshing report for a seconds.