Merge Statement not utilising index
Hi All,
I'm experimenting with using a merge statement to insert data in from a
temporary table into a large table (~12m rows).
Sqexplain output below...
QUERY: (OPTIMIZATION TIMESTAMP: 12-22-2010 11:45:38)
------
SELECT FIRST 1 * FROM stan:tagsamples INTO TEMP new_samples WITH NO LOG
Estimated Cost: 2515454
Estimated # of Rows Returned: 12790438
1) sdev.tagsamples: SEQUENTIAL SCAN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 tagsamples
t2 new_samples
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 12790438 5 00:00.11 2515454
type table rows_ins time
-----------------------------------
insert t2 1 00:00.13
QUERY: (OPTIMIZATION TIMESTAMP: 12-22-2010 11:45:38)
------
MERGE INTO tagsamples AS s
USING new_samples AS n
ON s.group_id = n.group_id AND s.sample_dt = n.sample_dt
WHEN MATCHED THEN
UPDATE SET
s.value1 = NVL(n.value1, s.value1)
WHEN NOT MATCHED THEN
INSERT (s.sample_dt, s.group_id,
s.value1
)
VALUES (n.sample_dt, n.group_id, n.value1
)
Estimated Cost: 9996166
Estimated # of Rows Returned: 1
1) sdev.n: SEQUENTIAL SCAN (Serial, fragments: ALL)
2) sdev.s: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: (sdev.s.sample_dt = sdev.n.sample_dt AND sdev.s.group_id
= sdev.n.group_id )
The actual query has a lot more columns in it, but simplified it to post
here...
There is a unique index on tagsamples like so.
create unique index i_ts_dt on tagsamples (sample_dt, group_id);
I would have expected the merge statement to make use of this index. Instead
it sequential scans both tables and runs out of temporary dbspace on my test
box.
Shouldn't the merge statement only sequentially scan the source table and
utilize the index to check if the rows exist before inserting/updating the
target?
I have tried running update statistics, doesn't seem to help :)
IFX 11.50.UC5 - RHEL4
Cheers,
James Brunskill
Process Information Engineer
james.brunskill@fonterra.com direct +64 7 849 2411 (ext 77808)
Fonterra Co-operative Group Limited, Te Rapa Dairy Factory, Hamilton, New
Zealand
DISCLAIMER
This email contains information that is confidential and which may be legally
privileged. If you have received this email in error, please notify the sender
immediately and delete the email. This email is intended solely for the use of
the intended recipient and you may not use or disclose this email in any way.
This sounds like you have OPTCOMPIND set to 2. You can try setting it to 0
and bouncing the instance, but you can also use an optimizer directive to
force the use of the index.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Dec 21, 2010 at 5:51 PM, James Brunskill <
James.Brunskill@fonterra.com> wrote:
> Hi All,
>
> I'm experimenting with using a merge statement to insert data in from a
> temporary table into a large table (~12m rows).
>
> Sqexplain output below...
>
> QUERY: (OPTIMIZATION TIMESTAMP: 12-22-2010 11:45:38)
> ------
> SELECT FIRST 1 * FROM stan:tagsamples INTO TEMP new_samples WITH NO LOG>
> Estimated Cost: 2515454
> Estimated # of Rows Returned: 12790438
>
> 1) sdev.tagsamples: SEQUENTIAL SCAN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 tagsamples
> t2 new_samples
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 1 12790438 5 00:00.11 2515454
>
> type table rows_ins time
> -----------------------------------
> insert t2 1 00:00.13
>
> QUERY: (OPTIMIZATION TIMESTAMP: 12-22-2010 11:45:38)
> ------
> MERGE INTO tagsamples AS s
>
> USING new_samples AS n
>
> ON s.group_id = n.group_id AND s.sample_dt = n.sample_dt
> WHEN MATCHED THEN
>
> UPDATE SET
>
> s.value1 = NVL(n.value1, s.value1)
>
> WHEN NOT MATCHED THEN
>
> INSERT (s.sample_dt, s.group_id,
>
> s.value1
>
> )
>
> VALUES (n.sample_dt, n.group_id, n.value1
>
> )
>
> Estimated Cost: 9996166
> Estimated # of Rows Returned: 1
>
> 1) sdev.n: SEQUENTIAL SCAN (Serial, fragments: ALL)
>
> 2) sdev.s: SEQUENTIAL SCAN
>
> DYNAMIC HASH JOIN
>
> Dynamic Hash Filters: (sdev.s.sample_dt = sdev.n.sample_dt AND
> sdev.s.group_id
> = sdev.n.group_id )
>
> The actual query has a lot more columns in it, but simplified it to post
> here...
>
> There is a unique index on tagsamples like so.
> create unique index i_ts_dt on tagsamples (sample_dt, group_id);>
> I would have expected the merge statement to make use of this index.
> Instead
> it sequential scans both tables and runs out of temporary dbspace on my
> test
> box.
>
> Shouldn't the merge statement only sequentially scan the source table and
> utilize the index to check if the rows exist before inserting/updating the
> target?
>
> I have tried running update statistics, doesn't seem to help :)
>
> IFX 11.50.UC5 - RHEL4
>
> Cheers,
>
> James Brunskill
> Process Information Engineer
> james.brunskill@fonterra.com direct +64 7 849 2411 (ext 77808)
> Fonterra Co-operative Group Limited, Te Rapa Dairy Factory, Hamilton, New
> Zealand
>
> DISCLAIMER
> This email contains information that is confidential and which may be
> legally
> privileged. If you have received this email in error, please notify the
> sender
> immediately and delete the email. This email is intended solely for the use
> of
> the intended recipient and you may not use or disclose this email in any
> way.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3054ace3a4512b0497fee2e0
Thanks Art,
Didn't work though unfortunately. I've tried bouncing the engine with
OPTCOMPIND set to 0 and using optimizer directives.
If a put a directive after the 'MERGE' I get two lines in my sqexplain
DIRECTIVES FOLLOWED:
DIRECTIVES NOT FOLLOWED:
But there doesn't appear to be any change to the query plan.
QUERY: (OPTIMIZATION TIMESTAMP: 12-23-2010 09:20:16)
------
MERGE {+INDEX i_ts_dt } INTO tagsamples AS s
USING new_samples AS n
ON s.group_id = n.group_id AND s.sample_dt = n.sample_dt WHEN MATCHED THEN
UPDATE SET
s.value1 = NVL(n.value1, s.value1)
WHEN NOT MATCHED THEN
INSERT (s.sample_dt, s.group_id,
s.value1
)
VALUES (n.sample_dt, n.group_id, n.value1
)
DIRECTIVES FOLLOWED:
DIRECTIVES NOT FOLLOWED:Estimated Cost: 9996166
Estimated # of Rows Returned: 1
1) sdev.n: SEQUENTIAL SCAN (Serial, fragments: ALL)
2) sdev.s: SEQUENTIAL SCAN
Cheers,
James Brunskill
Process Information Engineer
james.brunskill@fonterra.com direct +64 7 849 2411 (ext 77808)
Fonterra Co-operative Group Limited, Te Rapa Dairy Factory, Hamilton, New
Zealand
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Thursday, December 23, 2010 1:28 AM
To: ids@iiug.org
Subject: Re: Merge Statement not utilising index [22284]
This sounds like you have OPTCOMPIND set to 2. You can try setting it to 0 and
bouncing the instance, but you can also use an optimizer directive to force
the use of the index.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors
(art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Dec 21, 2010 at 5:51 PM, James Brunskill <
James.Brunskill@fonterra.com> wrote:
> Hi All,
>
> I'm experimenting with using a merge statement to insert data in from
> a temporary table into a large table (~12m rows).
>
> Sqexplain output below...
>
> QUERY: (OPTIMIZATION TIMESTAMP: 12-22-2010 11:45:38)
> ------
> SELECT FIRST 1 * FROM stan:tagsamples INTO TEMP new_samples WITH NO> LOG
>
> Estimated Cost: 2515454
> Estimated # of Rows Returned: 12790438
>
> 1) sdev.tagsamples: SEQUENTIAL SCAN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 tagsamples
> t2 new_samples
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 1 12790438 5 00:00.11 2515454
>
> type table rows_ins time
> -----------------------------------
> insert t2 1 00:00.13
>
> QUERY: (OPTIMIZATION TIMESTAMP: 12-22-2010 11:45:38)
> ------
> MERGE INTO tagsamples AS s
>
> USING new_samples AS n
>
> ON s.group_id = n.group_id AND s.sample_dt = n.sample_dt WHEN MATCHED
> THEN
>
> UPDATE SET
>
> s.value1 = NVL(n.value1, s.value1)
>
> WHEN NOT MATCHED THEN
>
> INSERT (s.sample_dt, s.group_id,
>
> s.value1
>
> )
>
> VALUES (n.sample_dt, n.group_id, n.value1
>
> )
>
> Estimated Cost: 9996166
> Estimated # of Rows Returned: 1
>
> 1) sdev.n: SEQUENTIAL SCAN (Serial, fragments: ALL)
>
> 2) sdev.s: SEQUENTIAL SCAN
>
> DYNAMIC HASH JOIN
>
> Dynamic Hash Filters: (sdev.s.sample_dt = sdev.n.sample_dt AND
> sdev.s.group_id = sdev.n.group_id )
>
> The actual query has a lot more columns in it, but simplified it to
> post here...
>
> There is a unique index on tagsamples like so.
> create unique index i_ts_dt on tagsamples (sample_dt, group_id);>
> I would have expected the merge statement to make use of this index.
> Instead
> it sequential scans both tables and runs out of temporary dbspace on
> my test box.
>
> Shouldn't the merge statement only sequentially scan the source table
> and utilize the index to check if the rows exist before
> inserting/updating the target?
>
> I have tried running update statistics, doesn't seem to help :)
>
> IFX 11.50.UC5 - RHEL4
>
> Cheers,
>
> James Brunskill
> Process Information Engineer
> james.brunskill@fonterra.com direct +64 7 849 2411 (ext 77808)
> Fonterra Co-operative Group Limited, Te Rapa Dairy Factory, Hamilton,
> New Zealand
>
> DISCLAIMER
> This email contains information that is confidential and which may be
> legally privileged. If you have received this email in error, please
> notify the sender immediately and delete the email. This email is
> intended solely for the use of the intended recipient and you may not
> use or disclose this email in any way.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3054ace3a4512b0497fee2e0
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Time to call IBM and open a support case.
Art
On Dec 22, 2010 4:10 PM, "James Brunskill" <James.Brunskill@fonterra.com>
wrote:
> Thanks Art,
>
> Didn't work though unfortunately. I've tried bouncing the engine with
> OPTCOMPIND set to 0 and using optimizer directives.
>
> If a put a directive after the 'MERGE' I get two lines in my sqexplain
> DIRECTIVES FOLLOWED:
> DIRECTIVES NOT FOLLOWED:>
> But there doesn't appear to be any change to the query plan.
>
> QUERY: (OPTIMIZATION TIMESTAMP: 12-23-2010 09:20:16)
> ------
> MERGE {+INDEX i_ts_dt } INTO tagsamples AS s
>
> USING new_samples AS n
>
> ON s.group_id = n.group_id AND s.sample_dt = n.sample_dt WHEN MATCHED THEN
>
> UPDATE SET
>
> s.value1 = NVL(n.value1, s.value1)
>
> WHEN NOT MATCHED THEN
>
> INSERT (s.sample_dt, s.group_id,
>
> s.value1
>
> )
>
> VALUES (n.sample_dt, n.group_id, n.value1
>
> )
>
> DIRECTIVES FOLLOWED:
> DIRECTIVES NOT FOLLOWED:> Estimated Cost: 9996166
> Estimated # of Rows Returned: 1
>
> 1) sdev.n: SEQUENTIAL SCAN (Serial, fragments: ALL)
>
> 2) sdev.s: SEQUENTIAL SCAN
>
> Cheers,
>
> James Brunskill
> Process Information Engineer
> james.brunskill@fonterra.com direct +64 7 849 2411 (ext 77808)
> Fonterra Co-operative Group Limited, Te Rapa Dairy Factory, Hamilton, New
> Zealand
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Thursday, December 23, 2010 1:28 AM
> To: ids@iiug.org
> Subject: Re: Merge Statement not utilising index [22284]
>
> This sounds like you have OPTCOMPIND set to 2. You can try setting it to 0
and
> bouncing the instance, but you can also use an optimizer directive to
force
> the use of the index.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors
> (art@iiug.org)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, Dec 21, 2010 at 5:51 PM, James Brunskill <
> James.Brunskill@fonterra.com> wrote:
>
>> Hi All,
>>
>> I'm experimenting with using a merge statement to insert data in from
>> a temporary table into a large table (~12m rows).
>>
>> Sqexplain output below...
>>
>> QUERY: (OPTIMIZATION TIMESTAMP: 12-22-2010 11:45:38)
>> ------
>> SELECT FIRST 1 * FROM stan:tagsamples INTO TEMP new_samples WITH NO>> LOG
>>
>> Estimated Cost: 2515454
>> Estimated # of Rows Returned: 12790438
>>
>> 1) sdev.tagsamples: SEQUENTIAL SCAN
>>
>> Query statistics:
>> -----------------
>>
>> Table map :
>> ----------------------------
>> Internal name Table name
>> ----------------------------
>> t1 tagsamples
>> t2 new_samples
>>
>> type table rows_prod est_rows rows_scan time est_cost
>> -------------------------------------------------------------------
>> scan t1 1 12790438 5 00:00.11 2515454
>>
>> type table rows_ins time
>> -----------------------------------
>> insert t2 1 00:00.13
>>
>> QUERY: (OPTIMIZATION TIMESTAMP: 12-22-2010 11:45:38)
>> ------
>> MERGE INTO tagsamples AS s
>>
>> USING new_samples AS n
>>
>> ON s.group_id = n.group_id AND s.sample_dt = n.sample_dt WHEN MATCHED
>> THEN
>>
>> UPDATE SET
>>
>> s.value1 = NVL(n.value1, s.value1)
>>
>> WHEN NOT MATCHED THEN
>>
>> INSERT (s.sample_dt, s.group_id,
>>
>> s.value1
>>
>> )
>>
>> VALUES (n.sample_dt, n.group_id, n.value1
>>
>> )
>>
>> Estimated Cost: 9996166
>> Estimated # of Rows Returned: 1
>>
>> 1) sdev.n: SEQUENTIAL SCAN (Serial, fragments: ALL)
>>
>> 2) sdev.s: SEQUENTIAL SCAN
>>
>> DYNAMIC HASH JOIN
>>
>> Dynamic Hash Filters: (sdev.s.sample_dt = sdev.n.sample_dt AND
>> sdev.s.group_id = sdev.n.group_id )
>>
>> The actual query has a lot more columns in it, but simplified it to
>> post here...
>>
>> There is a unique index on tagsamples like so.
>> create unique index i_ts_dt on tagsamples (sample_dt, group_id);>>
>> I would have expected the merge statement to make use of this index.
>> Instead
>> it sequential scans both tables and runs out of temporary dbspace on
>> my test box.
>>
>> Shouldn't the merge statement only sequentially scan the source table
>> and utilize the index to check if the rows exist before
>> inserting/updating the target?
>>
>> I have tried running update statistics, doesn't seem to help :)
>>
>> IFX 11.50.UC5 - RHEL4
>>
>> Cheers,
>>
>> James Brunskill
>> Process Information Engineer
>> james.brunskill@fonterra.com direct +64 7 849 2411 (ext 77808)
>> Fonterra Co-operative Group Limited, Te Rapa Dairy Factory, Hamilton,
>> New Zealand
>>
>> DISCLAIMER
>> This email contains information that is confidential and which may be
>> legally privileged. If you have received this email in error, please
>> notify the sender immediately and delete the email. This email is
>> intended solely for the use of the intended recipient and you may not
>> use or disclose this email in any way.
>>
>>
>>
>>
>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
> --20cf3054ace3a4512b0497fee2e0
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--485b393aafa5cc6823049806a8de