Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to avoid view recalculation

4 posts in General Discussion Last posting was on 2011-09-09 14:41:52.0Z
Vojislav Depalov <vojislav.depalov Posted on 2011-09-01 16:19:12.0Z
From: Vojislav Depalov <vojislav.depalov@remove_this_to_reach_me_dba-sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0.1) Gecko/20110830 Thunderbird/6.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: How to avoid view recalculation
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e5fb080$1@forums-1-dub>
Date: 1 Sep 2011 09:19:12 -0700
X-Trace: forums-1-dub 1314893952 10.22.241.152 (1 Sep 2011 09:19:12 -0700)
X-Original-Trace: 1 Sep 2011 09:19:12 -0700, vip152.sybase.com
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30512
Article PK: 72687

Hello,

I have following SQL statement(logically written below):

select * from table1 inner join
huge_view_which_calculation_requires_lots_of_time on....

In most cases table1 is empty and view on right side of join is always
recalculated, although result of join will be empty record set.

I've found some workaround checking if there are data in table1 and
according to that info execute appropriate select statement.

My question: is it possible to tell optimizer or use some other
statement to tell Sybase that there is no need to do view recalculation
in join with empty table?

Best regards,
Vojislav Depalov


"Mark A. Parsons" <iron_horse Posted on 2011-09-01 17:01:51.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to avoid view recalculation
References: <4e5fb080$1@forums-1-dub>
In-Reply-To: <4e5fb080$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e5fba7f@forums-1-dub>
Date: 1 Sep 2011 10:01:51 -0700
X-Trace: forums-1-dub 1314896511 10.22.241.152 (1 Sep 2011 10:01:51 -0700)
X-Original-Trace: 1 Sep 2011 10:01:51 -0700, vip152.sybase.com
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30514
Article PK: 72689

It sounds like the optimizer may be choosing to first access some of the tables that make up your view before it tries
to access table1, thus leading to some lengthy run times even though table1 is empty.

You could try wrapping your query in a 'set forceplan on/off' (with table1 listed first in the query) to see if that
helps, though there are a couple potential issues with this approach:

1 - the forced join order could lead to even worse performance if the tables that make up the view are accessed in a
less-than-efficient manner; redefining the order of the tables in the view definition *might* help

2 - if you're using ASE 15.x there are a handful of known issues with the optimizer ignoring forceplan wrappers; it's a
rather convoluted issue which includes deferred compilation (in procs), accessing views, ignoring forceplans, etc.

Have you insured that stats are up to date for all of the tables referenced by your query?

If you're using ASE 15.x, have you tried running the query under different optimization goals? I've found that
allrows_mixed can lead to some really crappy query plans, while allrows_oltp is usually better, and basic_optimization
often provides an even better query plan (and typically much reduced overhead for the parse-n-compile phase).

-----

There are a lot of potential explanations for your query's performance, but we'll need more details from you.

To start a diagnosis you'll need to post your @@version string, the query and the associated query plan.

At some point it may also be necessary to post the view definition, your table structures, and the optdiag info for the
various tables.

On 09/01/2011 12:19, Vojislav Depalov wrote:
> Hello,
>
> I have following SQL statement(logically written below):
>
> select * from table1 inner join huge_view_which_calculation_requires_lots_of_time on....
>
> In most cases table1 is empty and view on right side of join is always recalculated, although result of join will be
> empty record set.
>
> I've found some workaround checking if there are data in table1 and according to that info execute appropriate select
> statement.
>
> My question: is it possible to tell optimizer or use some other statement to tell Sybase that there is no need to do
> view recalculation in join with empty table?
>
> Best regards,
> Vojislav Depalov
>
>
>


Vojislav Depalov <vojislav.depalov Posted on 2011-09-02 10:29:31.0Z
From: Vojislav Depalov <vojislav.depalov@remove_this_to_reach_me_dba-sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0.1) Gecko/20110830 Thunderbird/6.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to avoid view recalculation
References: <4e5fb080$1@forums-1-dub> <4e5fba7f@forums-1-dub>
In-Reply-To: <4e5fba7f@forums-1-dub>
Content-Type: multipart/mixed; boundary="------------070305050201000704050908"
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e60b00b@forums-1-dub>
Date: 2 Sep 2011 03:29:31 -0700
X-Trace: forums-1-dub 1314959371 10.22.241.152 (2 Sep 2011 03:29:31 -0700)
X-Original-Trace: 2 Sep 2011 03:29:31 -0700, vip152.sybase.com
Lines: 378
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30515
Article PK: 72690

Hello,

Sybase version is
Adaptive Server Enterprise/12.5.3/EBF 13067 ESD#5/P/ia64/HP-UX
B.11.23/ase1253/1939/64-bit/FBO/Tue Dec 6 02:53:02 2005

Query is
SELECT T2.*, T1.action_state
FROM Prepared T1
INNER JOIN VW_HIST T2 ON
T1.id = T2.id AND T1.TIME_FROM = T2.TIME_FROM
ORDER BY T2.id, T1.action_state, T2.DIMENSION1


Create statement for table Prepared (this is empty table!) is given:
create table Prepared
(
id int,
TIME_FROM DATETIME,
TIME_TO DATETIME,
ID_DOC int,
ID_DOC_TO int,
action_state int)

VW_HIST is that huge view with lots of joins and lots of data inside.

Query plan is attached.

Best regards,
Vojislav Depalov

On 9/1/2011 7:01 PM, Mark A. Parsons wrote:
> It sounds like the optimizer may be choosing to first access some of the
> tables that make up your view before it tries to access table1, thus
> leading to some lengthy run times even though table1 is empty.
>
> You could try wrapping your query in a 'set forceplan on/off' (with
> table1 listed first in the query) to see if that helps, though there are
> a couple potential issues with this approach:
>
> 1 - the forced join order could lead to even worse performance if the
> tables that make up the view are accessed in a less-than-efficient
> manner; redefining the order of the tables in the view definition
> *might* help
>
> 2 - if you're using ASE 15.x there are a handful of known issues with
> the optimizer ignoring forceplan wrappers; it's a rather convoluted
> issue which includes deferred compilation (in procs), accessing views,
> ignoring forceplans, etc.
>
> Have you insured that stats are up to date for all of the tables
> referenced by your query?
>
> If you're using ASE 15.x, have you tried running the query under
> different optimization goals? I've found that allrows_mixed can lead to
> some really crappy query plans, while allrows_oltp is usually better,
> and basic_optimization often provides an even better query plan (and
> typically much reduced overhead for the parse-n-compile phase).
>
> -----
>
> There are a lot of potential explanations for your query's performance,
> but we'll need more details from you.
>
> To start a diagnosis you'll need to post your @@version string, the
> query and the associated query plan.
>
> At some point it may also be necessary to post the view definition, your
> table structures, and the optdiag info for the various tables.
>
>
>
> On 09/01/2011 12:19, Vojislav Depalov wrote:
>> Hello,
>>
>> I have following SQL statement(logically written below):
>>
>> select * from table1 inner join
>> huge_view_which_calculation_requires_lots_of_time on....
>>
>> In most cases table1 is empty and view on right side of join is always
>> recalculated, although result of join will be
>> empty record set.
>>
>> I've found some workaround checking if there are data in table1 and
>> according to that info execute appropriate select
>> statement.
>>
>> My question: is it possible to tell optimizer or use some other
>> statement to tell Sybase that there is no need to do
>> view recalculation in join with empty table?
>>
>> Best regards,
>> Vojislav Depalov
>>
>>
>>

------------------------ Execute ------------------------

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.
Executing a previously cached statement.


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable2 created, in allpages locking mode, for DISTINCT.

FROM TABLE
ORACLE_DMS.TSM_MV_ADD
TSMMVADD
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
ORACLE_DMS.BAY_EL_BLC
BAYELBLC
Nested iteration.
Index : BAY_EL_BLC_I_ID
Forward scan.
Positioning by key.
Keys are:
ID_BAY_BELB ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
ORACLE_DMS.TSM_BAS
TSMBAS
Nested iteration.
Index : TSM_BAS_I_ID
Forward scan.
Positioning by key.
Keys are:
ID_OBJ_TSMB ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable2.

STEP 2
The type of query is INSERT.
The update mode is direct.
This step involves sorting.

FROM TABLE
Worktable2.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable3 created for REFORMATTING.

FROM TABLE
ORACLE_DMS.TSM_MV_ADD
TSMMVADD
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable3.

STEP 2
The type of query is INSERT.
The update mode is direct.

FROM TABLE
ORACLE_DMS.TSM_BAS
TSMBAS
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable3.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable4 created, in allpages locking mode, for DISTINCT.

FROM TABLE
ORACLE_DMS.TSM_LV_ADD
TSMLVADD
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
ORACLE_DMS.BAY_EL_BLC
BAYELBLC
Nested iteration.
Index : BAY_EL_BLC_I_ID
Forward scan.
Positioning by key.
Keys are:
ID_BAY_BELB ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
ORACLE_DMS.TSM_BAS
TSMBAS
Nested iteration.
Index : TSM_BAS_I_ID
Forward scan.
Positioning by key.
Keys are:
ID_OBJ_TSMB ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable4.

STEP 2
The type of query is INSERT.
The update mode is direct.
This step involves sorting.

FROM TABLE
Worktable4.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable5 created for REFORMATTING.

FROM TABLE
ORACLE_DMS.TSM_LV_ADD
TSMLVADD
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable5.

STEP 2
The type of query is INSERT.
The update mode is direct.

FROM TABLE
ORACLE_DMS.TSM_BAS
TSMBAS
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable5.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable6 created, in allpages locking mode, for ORDER BY.

FROM TABLE
Prepared
T1
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable1.
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable6.

STEP 2
The type of query is SELECT.
This step involves sorting.

FROM TABLE
Worktable6.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.

The sort for Worktable2 is done in Serial
The sort for Worktable3 is done in Serial
The sort for Worktable4 is done in Serial
The sort for Worktable5 is done in Serial
The sort for Worktable6 is done in Serial
(0 rows affected)
------------------------- Done --------------------------


Mark A. Parsons Posted on 2011-09-09 14:41:52.0Z
Sender: 505f.4e6a228f.1804289383@sybase.com
From: Mark A. Parsons
Newsgroups: sybase.public.ase.general
Subject: Re: How to avoid view recalculation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e6a25b0.511c.1681692777@sybase.com>
References: <4e60b00b@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Sep 2011 07:41:52 -0700
X-Trace: forums-1-dub 1315579312 10.22.241.41 (9 Sep 2011 07:41:52 -0700)
X-Original-Trace: 9 Sep 2011 07:41:52 -0700, 10.22.241.41
Lines: 418
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30526
Article PK: 72705

You could try updating stats on the empty table before
running your query ... to see if the 0rows has an effect on
the optimizer.

The next idea would be a forced join order on the main
query, though the effectiveness of this idea really depends
on the design of the view (eg, does a forceplan push you
into a poor join order of the view's base tables; are you
joining to actual columns or aggregated/computed columns?)
and availability of usable indexes on the underlying tables.

If you are joining with base table columns (in the view
definition), make sure the main query's join columns are
defined with the same datatypes.

How does this query perform when there's some data in the
Prepared/T1 table?

Is this a new view? {Wondering if you have any other
queries that perform well when joined with the view, and if
so, how do those query plans differ from the one for this
problematic query?)

> Hello,
>
> Sybase version is
> Adaptive Server Enterprise/12.5.3/EBF 13067
> ESD#5/P/ia64/HP-UX B.11.23/ase1253/1939/64-bit/FBO/Tue
> Dec 6 02:53:02 2005
>
> Query is
> SELECT T2.*, T1.action_state
> FROM Prepared T1
> INNER JOIN VW_HIST T2 ON
> T1.id = T2.id AND T1.TIME_FROM = T2.TIME_FROM
> ORDER BY T2.id, T1.action_state, T2.DIMENSION1
>
>
> Create statement for table Prepared (this is empty table!)
> is given: create table Prepared
> (
> id int,
> TIME_FROM DATETIME,
> TIME_TO DATETIME,
> ID_DOC int,
> ID_DOC_TO int,
> action_state int)
>
> VW_HIST is that huge view with lots of joins and lots of
> data inside.
>
> Query plan is attached.
>
> Best regards,
> Vojislav Depalov
>
> On 9/1/2011 7:01 PM, Mark A. Parsons wrote:
> > It sounds like the optimizer may be choosing to first
> > access some of the tables that make up your view before
> > it tries to access table1, thus leading to some lengthy
> run times even though table1 is empty. >
> > You could try wrapping your query in a 'set forceplan
> > on/off' (with table1 listed first in the query) to see
> > if that helps, though there are a couple potential
> issues with this approach: >
> > 1 - the forced join order could lead to even worse
> > performance if the tables that make up the view are
> > accessed in a less-than-efficient manner; redefining the
> > order of the tables in the view definition *might* help
> >
> > 2 - if you're using ASE 15.x there are a handful of
> > known issues with the optimizer ignoring forceplan
> > wrappers; it's a rather convoluted issue which includes
> > deferred compilation (in procs), accessing views,
> ignoring forceplans, etc. >
> > Have you insured that stats are up to date for all of
> > the tables referenced by your query?
> >
> > If you're using ASE 15.x, have you tried running the
> > query under different optimization goals? I've found
> > that allrows_mixed can lead to some really crappy query
> > plans, while allrows_oltp is usually better, and
> basic_optimization often provides an even better query
> > plan (and typically much reduced overhead for the
> parse-n-compile phase). >
> > -----
> >
> > There are a lot of potential explanations for your
> > query's performance, but we'll need more details from
> you. >
> > To start a diagnosis you'll need to post your @@version
> > string, the query and the associated query plan.
> >
> > At some point it may also be necessary to post the view
> > definition, your table structures, and the optdiag info
> for the various tables. >
> >
> >
> > On 09/01/2011 12:19, Vojislav Depalov wrote:
> >> Hello,
> >>
> >> I have following SQL statement(logically written
> below): >>
> >> select * from table1 inner join
> >> huge_view_which_calculation_requires_lots_of_time
> on.... >>
> >> In most cases table1 is empty and view on right side of
> join is always >> recalculated, although result of join
> will be >> empty record set.
> >>
> >> I've found some workaround checking if there are data
> in table1 and >> according to that info execute
> appropriate select >> statement.
> >>
> >> My question: is it possible to tell optimizer or use
> some other >> statement to tell Sybase that there is no
> need to do >> view recalculation in join with empty table?
> >>
> >> Best regards,
> >> Vojislav Depalov
> >>
> >>
> >>
>
>
>
> [Query plan.txt]
> ------------------------ Execute ------------------------
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is EXECUTE.
> Executing a previously cached statement.
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Worktable2 created, in allpages locking mode, for
> DISTINCT.
>
> FROM TABLE
> ORACLE_DMS.TSM_MV_ADD
> TSMMVADD
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> ORACLE_DMS.BAY_EL_BLC
> BAYELBLC
> Nested iteration.
> Index : BAY_EL_BLC_I_ID
> Forward scan.
> Positioning by key.
> Keys are:
> ID_BAY_BELB ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index
> leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> ORACLE_DMS.TSM_BAS
> TSMBAS
> Nested iteration.
> Index : TSM_BAS_I_ID
> Forward scan.
> Positioning by key.
> Keys are:
> ID_OBJ_TSMB ASC
> Using I/O Size 16 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index
> leaf pages.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable2.
>
> STEP 2
> The type of query is INSERT.
> The update mode is direct.
> This step involves sorting.
>
> FROM TABLE
> Worktable2.
> Using GETSORTED
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable1.
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Worktable3 created for REFORMATTING.
>
> FROM TABLE
> ORACLE_DMS.TSM_MV_ADD
> TSMMVADD
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable3.
>
> STEP 2
> The type of query is INSERT.
> The update mode is direct.
>
> FROM TABLE
> ORACLE_DMS.TSM_BAS
> TSMBAS
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> Worktable3.
> Nested iteration.
> Using Clustered Index.
> Forward scan.
> Positioning by key.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable1.
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Worktable4 created, in allpages locking mode, for
> DISTINCT.
>
> FROM TABLE
> ORACLE_DMS.TSM_LV_ADD
> TSMLVADD
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> ORACLE_DMS.BAY_EL_BLC
> BAYELBLC
> Nested iteration.
> Index : BAY_EL_BLC_I_ID
> Forward scan.
> Positioning by key.
> Keys are:
> ID_BAY_BELB ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index
> leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> ORACLE_DMS.TSM_BAS
> TSMBAS
> Nested iteration.
> Index : TSM_BAS_I_ID
> Forward scan.
> Positioning by key.
> Keys are:
> ID_OBJ_TSMB ASC
> Using I/O Size 16 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index
> leaf pages.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable4.
>
> STEP 2
> The type of query is INSERT.
> The update mode is direct.
> This step involves sorting.
>
> FROM TABLE
> Worktable4.
> Using GETSORTED
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable1.
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Worktable5 created for REFORMATTING.
>
> FROM TABLE
> ORACLE_DMS.TSM_LV_ADD
> TSMLVADD
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable5.
>
> STEP 2
> The type of query is INSERT.
> The update mode is direct.
>
> FROM TABLE
> ORACLE_DMS.TSM_BAS
> TSMBAS
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> Worktable5.
> Nested iteration.
> Using Clustered Index.
> Forward scan.
> Positioning by key.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable1.
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Worktable6 created, in allpages locking mode, for
> ORDER BY.
>
> FROM TABLE
> Prepared
> T1
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> Worktable1.
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> Worktable6.
>
> STEP 2
> The type of query is SELECT.
> This step involves sorting.
>
> FROM TABLE
> Worktable6.
> Using GETSORTED
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data
> pages.
>
> The sort for Worktable2 is done in Serial
> The sort for Worktable3 is done in Serial
> The sort for Worktable4 is done in Serial
> The sort for Worktable5 is done in Serial
> The sort for Worktable6 is done in Serial
> (0 rows affected)
> ------------------------- Done --------------------------
>
> [Attachment: Query plan.txt]