We have a table called ADQ_TX_FACTURABLES with 1.7B rows. Over this table a stream declared as
create or replace stream RAW.BDOT.STREAM_ADQ_TX_FACTURABLES on table ADQ_TX_FACTURABLES;
Every day, in a batch process, like 1.7 million rows were loaded into the table so the stream caught that info into another table, the last query profile showed:
The time of process of this merge was less than a minute.
Now, we have a final table that is loaded as the result of multiple joins with over 6.7B rows.
Over this table we have a stream defined as:
CREATE STREAM IF NOT EXISTS REFINED.BUT.STREAM_OVER_TX_TARIFICADA ON TABLE REFINED.BUT.TX_TARIFICADA;
In a final task we are executing this script:
CREATE OR REPLACE TEMPORARY TABLE REFINED.BUT.TEMP_1 AS SELECT * FROM REFINED.BUT.STREAM_OVER_TX_TARIFICADA WHERE METADATA$ACTION = 'INSERT';
But this seemingly easy script is taken over an hour and a half to finish.
Can’t see the specific query profile but using the ‘get_query_operator_stats’ we can see (i hope the image is clear) that, in order to use the new data on that stream (for one day is like 8 to 10 million rows) is scanning the table two times, then joining the results to finale obtain the result.
We don’t understand why is doing this when other streams that are defined in the same way are very fast. We know the difference in the amount of row is like 5 to 1, but the process of the data using this streams is different and don’t understand why.
The only difference between the two processes is that the first stream (the one that’s taking a less that a minute) is that stream is never recreated, in the other hand, the second stream after using it in the creation of the temporary table is recreated.
Edit 1:
- what process are you using to load data into the final table?
A task is called every hour and when the first stream described above, the one that is fast, has data, a dbt model is called orchestrated using oracle CAWA, this model makes a merge using the stream, this stream usually has for this model something between 1 or 2 million rows, but another model load another 6 or 7 million rows to the same final table.
-
When the final table described in the first point has new data, the second stream described in the main post “has data”, so another task is called to times a day, at 5 am and 11 am. At this particular times of the day, all of the streams in task that are consulted in the WHEN condition has data. Is declared like this:
WHEN SYSTEM$STREAM_HAS_DATA(‘REFINED.BUT.STREAM_OVER_TX_TARIFICADA’) OR SYSTEM$STREAM_HAS_DATA(‘REFINED.BUT.STREAM_OVER_TX_ADQUIRIDA’) OR SYSTEM$STREAM_HAS_DATA(‘REFINED.BUT.STREAM_OVER_TX_ABONADA’) OR SYSTEM$STREAM_HAS_DATA(‘REFINED.BUT.STREAM_OVER_TX_PTLF’)
Then an IF condition is executed like this:
IF (SYSTEM$STREAM_HAS_DATA('REFINED.BUT.STREAM_OVER_TX_TARIFICADA') = true) THEN
CREATE OR REPLACE TEMPORARY TABLE REFINED.BUT.TEMP_1 AS SELECT * FROM REFINED.BUT.STREAM_OVER_TX_TARIFICADA WHERE METADATA$ACTION = 'INSERT'; -- This is the section where is taking the longest to complete
INSERT INTO REFINED.BUT.TEMP_TABLE
SELECT fch_procesamiento, cdg_tipo_tx, 'TARI' origen, to_timestamp(convert_timezone('America/Santiago', current_timestamp())) tms_carga FROM REFINED.BUT.TEMP_1 WHERE ((cdg_tipo_tx NOT IN ('107')) OR (fch_procesamiento >= (SELECT DATEADD(day, -2, MAX(TO_DATE(fch_procesamiento))) FROM REFINED.BUT.TEMP_1)));
select_statement_stream := 'SELECT fch_procesamiento FROM REFINED.BUT.TEMP_TABLE WHERE fch_procesamiento IS NOT NULL GROUP BY fch_procesamiento';
ELSEIF
(SYSTEM$STREAM_HAS_DATA('REFINED.BUT.STREAM_OVER_TX_ADQUIRIDA') = true) THEN
... -- same with other streams
END IF;
After this another validations are made and for each process date in the “select_statement_stream” variable (that almost always is one process date, and each process date is about 8 to 10 million rows) a stored procedure is called, the call is made sending a variable with each process date to be loaded, using a CURSOR,
After each process date was or wasn’t loaded (i say was or wasn’t because another validation is made if that process date is full loaded in a couple of tables that are used to load the next final table that joins 4 tables (including this TX_TARIFICADA over which the stream STREAM_OVER_TX_TARIFICADA is created)), the next script is executed,
CREATE OR REPLACE STREAM REFINED.BUT.STREAM_OVER_TX_TARIFICADA ON TABLE REFINED.BUT.TX_TARIFICADA;
7