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.

Table scan when joining on primary key

2 posts in General Discussion Last posting was on 2009-07-15 11:05:25.0Z
Chris Mathrusse Posted on 2009-07-14 19:54:04.0Z
Reply-To: "Chris Mathrusse" <christopher.mathrusse@sybase.com>
From: "Chris Mathrusse" <christopher.mathrusse@sybase.com>
Newsgroups: sybase.public.ase.general
Subject: Table scan when joining on primary key
Lines: 594
Organization: Sybase Inc.
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5ce25c$4@forums-3-dub.sybase.com>
Date: 14 Jul 2009 12:54:04 -0700
X-Trace: forums-3-dub.sybase.com 1247601244 10.22.241.152 (14 Jul 2009 12:54:04 -0700)
X-Original-Trace: 14 Jul 2009 12:54:04 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28007
Article PK: 77255

I have a query that is joining on several tables that are all joined by
primary/foreign key relationships, but is causing a table scan from a join
on a primary key.

Why?

Below is the SQL and show plan. Table SEVERITY is being scanned but is OK as
this table is very small (4 records). The table I am concerned with is
PROCESS as it is large.

Thanks for the help...

select @@version

Adaptive Server Enterprise/15.0.2/EBF 15681 ESD#5/P/RS6000/AIX
5.2/ase1502/2528/64-bit/FBO/Tue Jun 17 11:37:12 2008

?

SELECT

t5.ID

, t5.CONTENT

, t5.MOD_DATE

, t5.CREATE_DATE

, t5.ADDITIONAL_INFO

, t5.VERSION

, t5.PROCESS_ID

, t5.RESOURCE_ID

, t5.SEVERITY

, t0.ID

, t0.discriminator

, t0.MOD_DATE

, t0.CREATE_DATE

, t0.VERSION

, t0.STATE

, t1.ID

, t1.SOURCE_RECORD_DATE

, t1.ORDER_NO

, t1.ORDER_CODE

, t1.OWNER

, t2.ID

, t2.START_DATE

, t2.END_DATE

, t2.REPORT_DOC_ID

, t2.ORGANIZATION

, t3.ID

, t3.RESPONSE_DOC_ID

, t3.ADDRESS_ID

, t3.REQUEST_DOC_ID

, t4.ID

, t4.ADDRESS_ID

, t4.REQUEST_DOC_ID

, t6.ID

, t6.CONTENT

, t6.MOD_DATE

, t6.CREATE_DATE

, t6.VERSION

, t6.SEVERITY

, t7.ID

, t7.MOD_DATE

, t7.CREATE_DATE

, t7.VERSION

FROM

PROCESS t0

LEFT OUTER JOIN ORDER_PROCESS t1

ON

(

t1.ID = t0.ID

)

LEFT OUTER JOIN REPORT_PROCESS t2

ON

(

t2.ID = t0.ID

)

LEFT OUTER JOIN ADDRESS_PROCESS t3

ON

(

t3.ID = t0.ID

)

LEFT OUTER JOIN UPDATE_PROCESS t4

ON

(

t4.ID = t0.ID

)

, SEVERITY t7

, RESOURCE_DEFINITION t6

, EVENTLOG t5

WHERE

(

(

(

t5.CREATE_DATE > '2009-07-13 00:00:00.0'

)

AND

(

t5.CREATE_DATE < '2009-07-14 00:00:00.0'

)

)

AND

(

(

(

t0.ID = t5.PROCESS_ID

)

AND

(

t6.ID = t5.RESOURCE_ID

)

)

AND

(

t7.ID = t5.SEVERITY

)

)

);

10:44:31 [SELECT - 39 row(s), 1.437 secs] Result set fetched

Code: 6248 SQL State: 01ZZZ ---

Code: 6289 SQL State: 01ZZZ --- QUERY PLAN FOR STATEMENT 1 (at line 1).

Code: 6248 SQL State: 01ZZZ ---

Code: 6201 SQL State: 01ZZZ --- STEP 1

Code: 6203 SQL State: 01ZZZ --- The type of query is EXECUTE.

Code: 10258 SQL State: 01ZZZ --- Executing a previously cached statement
(SSQL_ID = 1093285456).

Code: 6248 SQL State: 01ZZZ ---

Code: 3630 SQL State: 01ZZZ --- Total estimated I/O cost for statement 1 (at
line 1): 0.

Code: 6248 SQL State: 01ZZZ ---

Code: 6289 SQL State: 01ZZZ --- QUERY PLAN FOR STATEMENT 1 (at line 1).

Code: 6248 SQL State: 01ZZZ ---

Code: 6201 SQL State: 01ZZZ --- STEP 1

Code: 10262 SQL State: 01ZZZ --- The type of query is SELECT.

Code: 6248 SQL State: 01ZZZ ---

Code: 6248 SQL State: 01ZZZ --- 18 operator(s) under root

Code: 6248 SQL State: 01ZZZ ---

Code: 6248 SQL State: 01ZZZ --- |ROOT:EMIT Operator (VA = 18)

Code: 6248 SQL State: 01ZZZ --- |

Code: 6248 SQL State: 01ZZZ --- | |RESTRICT Operator (VA =
17)(0)(0)(0)(0)(11)

Code: 6248 SQL State: 01ZZZ --- | |

Code: 6248 SQL State: 01ZZZ --- | | |MERGE JOIN Operator (Join Type: Inner
Join) (VA = 16)

Code: 10263 SQL State: 01ZZZ --- | | | Using Worktable7 for internal
storage.

Code: 6248 SQL State: 01ZZZ --- | | | Key Count: 3

Code: 6248 SQL State: 01ZZZ --- | | | Key Ordering: ASC ASC ASC

Code: 6248 SQL State: 01ZZZ --- | | |

Code: 6248 SQL State: 01ZZZ --- | | | |NESTED LOOP JOIN Operator (VA = 13)
(Join Type: Inner Join)

Code: 6248 SQL State: 01ZZZ --- | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | |MERGE JOIN Operator (Join Type:
Left Outer Join) (VA = 11)

Code: 10263 SQL State: 01ZZZ --- | | | | | Using Worktable5 for internal
storage.

Code: 6248 SQL State: 01ZZZ --- | | | | | Key Count: 1

Code: 6248 SQL State: 01ZZZ --- | | | | | Key Ordering: ASC

Code: 6248 SQL State: 01ZZZ --- | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | |MERGE JOIN Operator (Join Type:
Left Outer Join) (VA = 9)

Code: 10263 SQL State: 01ZZZ --- | | | | | | Using Worktable4 for internal
storage.

Code: 6248 SQL State: 01ZZZ --- | | | | | | Key Count: 1

Code: 6248 SQL State: 01ZZZ --- | | | | | | Key Ordering: ASC

Code: 6248 SQL State: 01ZZZ --- | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | |MERGE JOIN Operator (Join Type:
Left Outer Join) (VA = 7)

Code: 10263 SQL State: 01ZZZ --- | | | | | | | Using Worktable3 for internal
storage.

Code: 6248 SQL State: 01ZZZ --- | | | | | | | Key Count: 1

Code: 6248 SQL State: 01ZZZ --- | | | | | | | Key Ordering: ASC

Code: 6248 SQL State: 01ZZZ --- | | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | | |MERGE JOIN Operator (Join
Type: Left Outer Join) (VA = 5)

Code: 10263 SQL State: 01ZZZ --- | | | | | | | | Using Worktable2 for
internal storage.

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | Key Count: 1

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | Key Ordering: ASC

Code: 6248 SQL State: 01ZZZ --- | | | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | |SORT Operator (VA = 3)

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | Average Row width is
77.000000

Code: 10263 SQL State: 01ZZZ --- | | | | | | | | | Using Worktable1 for
internal storage.

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | |NESTED LOOP JOIN Operator
(VA = 2) (Join Type: Inner Join)

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | | |SCAN Operator (VA = 0)

Code: 6215 SQL State: 01ZZZ --- | | | | | | | | | | | FROM TABLE

Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | | | SEVERITY

Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | | | t7

Code: 6223 SQL State: 01ZZZ --- | | | | | | | | | | | Table Scan.

Code: 6276 SQL State: 01ZZZ --- | | | | | | | | | | | Forward Scan.

Code: 6278 SQL State: 01ZZZ --- | | | | | | | | | | | Positioning at start
of table.

Code: 10240 SQL State: 01ZZZ --- | | | | | | | | | | | Using I/O Size 2
Kbytes for data pages.

Code: 10239 SQL State: 01ZZZ --- | | | | | | | | | | | With LRU Buffer
Replacement Strategy for data pages.

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | | |SCAN Operator (VA = 1)

Code: 6215 SQL State: 01ZZZ --- | | | | | | | | | | | FROM TABLE

Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | | | PROCESS

Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | | | t0

Code: 6223 SQL State: 01ZZZ --- | | | | | | | | | | | Table Scan.

Code: 6276 SQL State: 01ZZZ --- | | | | | | | | | | | Forward Scan.

Code: 6278 SQL State: 01ZZZ --- | | | | | | | | | | | Positioning at start
of table.

Code: 10240 SQL State: 01ZZZ --- | | | | | | | | | | | Using I/O Size 16
Kbytes for data pages.

Code: 10239 SQL State: 01ZZZ --- | | | | | | | | | | | With LRU Buffer
Replacement Strategy for data pages.

Code: 6248 SQL State: 01ZZZ --- | | | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | | | |SCAN Operator (VA = 4)

Code: 6215 SQL State: 01ZZZ --- | | | | | | | | | FROM TABLE

Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | ORDER_PROCESS

Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | t1

Code: 6224 SQL State: 01ZZZ --- | | | | | | | | | Using Clustered Index.

Code: 6225 SQL State: 01ZZZ --- | | | | | | | | | Index :
ORDER_PROC_7645267262

Code: 6276 SQL State: 01ZZZ --- | | | | | | | | | Forward Scan.

Code: 6282 SQL State: 01ZZZ --- | | | | | | | | | Positioning at index
start.

Code: 6272 SQL State: 01ZZZ --- | | | | | | | | | Using I/O Size 2 Kbytes
for index leaf pages.

Code: 6273 SQL State: 01ZZZ --- | | | | | | | | | With LRU Buffer
Replacement Strategy for index leaf pages.

Code: 10240 SQL State: 01ZZZ --- | | | | | | | | | Using I/O Size 2 Kbytes
for data pages.

Code: 10239 SQL State: 01ZZZ --- | | | | | | | | | With LRU Buffer
Replacement Strategy for data pages.

Code: 6248 SQL State: 01ZZZ --- | | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | | |SCAN Operator (VA = 6)

Code: 6215 SQL State: 01ZZZ --- | | | | | | | | FROM TABLE

Code: 6217 SQL State: 01ZZZ --- | | | | | | | | REPORT_PROCESS

Code: 6217 SQL State: 01ZZZ --- | | | | | | | | t2

Code: 6224 SQL State: 01ZZZ --- | | | | | | | | Using Clustered Index.

Code: 6225 SQL State: 01ZZZ --- | | | | | | | | Index :
REPORT_PRO_10685278092

Code: 6276 SQL State: 01ZZZ --- | | | | | | | | Forward Scan.

Code: 6282 SQL State: 01ZZZ --- | | | | | | | | Positioning at index start.

Code: 6272 SQL State: 01ZZZ --- | | | | | | | | Using I/O Size 2 Kbytes for
index leaf pages.

Code: 6273 SQL State: 01ZZZ --- | | | | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.

Code: 10240 SQL State: 01ZZZ --- | | | | | | | | Using I/O Size 2 Kbytes for
data pages.

Code: 10239 SQL State: 01ZZZ --- | | | | | | | | With LRU Buffer Replacement
Strategy for data pages.

Code: 6248 SQL State: 01ZZZ --- | | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | | |SCAN Operator (VA = 8)

Code: 6215 SQL State: 01ZZZ --- | | | | | | | FROM TABLE

Code: 6217 SQL State: 01ZZZ --- | | | | | | | ADDRESS_PROCESS

Code: 6217 SQL State: 01ZZZ --- | | | | | | | t3

Code: 6224 SQL State: 01ZZZ --- | | | | | | | Using Clustered Index.

Code: 6225 SQL State: 01ZZZ --- | | | | | | | Index : ADDRESS_PR_10205276382

Code: 6276 SQL State: 01ZZZ --- | | | | | | | Forward Scan.

Code: 6282 SQL State: 01ZZZ --- | | | | | | | Positioning at index start.

Code: 6272 SQL State: 01ZZZ --- | | | | | | | Using I/O Size 2 Kbytes for
index leaf pages.

Code: 6273 SQL State: 01ZZZ --- | | | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.

Code: 10240 SQL State: 01ZZZ --- | | | | | | | Using I/O Size 16 Kbytes for
data pages.

Code: 10239 SQL State: 01ZZZ --- | | | | | | | With LRU Buffer Replacement
Strategy for data pages.

Code: 6248 SQL State: 01ZZZ --- | | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | | |SCAN Operator (VA = 10)

Code: 6215 SQL State: 01ZZZ --- | | | | | | FROM TABLE

Code: 6217 SQL State: 01ZZZ --- | | | | | | UPDATE_PROCESS

Code: 6217 SQL State: 01ZZZ --- | | | | | | t4

Code: 6224 SQL State: 01ZZZ --- | | | | | | Using Clustered Index.

Code: 6225 SQL State: 01ZZZ --- | | | | | | Index : UPDATE_PRO_5405259282

Code: 6276 SQL State: 01ZZZ --- | | | | | | Forward Scan.

Code: 6282 SQL State: 01ZZZ --- | | | | | | Positioning at index start.

Code: 6272 SQL State: 01ZZZ --- | | | | | | Using I/O Size 2 Kbytes for
index leaf pages.

Code: 6273 SQL State: 01ZZZ --- | | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.

Code: 10240 SQL State: 01ZZZ --- | | | | | | Using I/O Size 16 Kbytes for
data pages.

Code: 10239 SQL State: 01ZZZ --- | | | | | | With LRU Buffer Replacement
Strategy for data pages.

Code: 6248 SQL State: 01ZZZ --- | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | |SCAN Operator (VA = 12)

Code: 6215 SQL State: 01ZZZ --- | | | | | FROM TABLE

Code: 6217 SQL State: 01ZZZ --- | | | | | RESOURCE_DEFINITION

Code: 6217 SQL State: 01ZZZ --- | | | | | t6

Code: 6224 SQL State: 01ZZZ --- | | | | | Using Clustered Index.

Code: 6225 SQL State: 01ZZZ --- | | | | | Index : RESOURCE_D_13565288352

Code: 6276 SQL State: 01ZZZ --- | | | | | Forward Scan.

Code: 6282 SQL State: 01ZZZ --- | | | | | Positioning at index start.

Code: 6272 SQL State: 01ZZZ --- | | | | | Using I/O Size 2 Kbytes for index
leaf pages.

Code: 6273 SQL State: 01ZZZ --- | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.

Code: 10240 SQL State: 01ZZZ --- | | | | | Using I/O Size 2 Kbytes for data
pages.

Code: 10239 SQL State: 01ZZZ --- | | | | | With LRU Buffer Replacement
Strategy for data pages.

Code: 6248 SQL State: 01ZZZ --- | | |

Code: 6248 SQL State: 01ZZZ --- | | | |SORT Operator (VA = 15)

Code: 6248 SQL State: 01ZZZ --- | | | | Average Row width is 200.583740

Code: 10263 SQL State: 01ZZZ --- | | | | Using Worktable6 for internal
storage.

Code: 6248 SQL State: 01ZZZ --- | | | |

Code: 6248 SQL State: 01ZZZ --- | | | | |SCAN Operator (VA = 14)

Code: 6215 SQL State: 01ZZZ --- | | | | | FROM TABLE

Code: 6217 SQL State: 01ZZZ --- | | | | | EVENTLOG

Code: 6217 SQL State: 01ZZZ --- | | | | | t5

Code: 6225 SQL State: 01ZZZ --- | | | | | Index : x_EVENTLOG_CREATE_DATE

Code: 6276 SQL State: 01ZZZ --- | | | | | Forward Scan.

Code: 6281 SQL State: 01ZZZ --- | | | | | Positioning by key.

Code: 6287 SQL State: 01ZZZ --- | | | | | Keys are:

Code: 6288 SQL State: 01ZZZ --- | | | | | CREATE_DATE ASC

Code: 6272 SQL State: 01ZZZ --- | | | | | Using I/O Size 2 Kbytes for index
leaf pages.

Code: 6273 SQL State: 01ZZZ --- | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.

Code: 10240 SQL State: 01ZZZ --- | | | | | Using I/O Size 16 Kbytes for data
pages.

Code: 10239 SQL State: 01ZZZ --- | | | | | With LRU Buffer Replacement
Strategy for data pages.

Code: 6248 SQL State: 01ZZZ ---

Code: 3630 SQL State: 01ZZZ --- Total estimated I/O cost for statement 1 (at
line 1): 98341.

Code: 6248 SQL State: 01ZZZ ---

... 1 statement(s) executed, 39 row(s) affected, exec/fetch time:
1.437/0.063 sec [1 successful, 0 warnings, 0 errors]

?


Chris Mathrusse
christopher.mathrusse@sybase.com
Sybase, Inc


Rob V [Sybase] Posted on 2009-07-15 11:05:25.0Z
Reply-To: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4a5ce25c$4@forums-3-dub.sybase.com>
Subject: Re: Table scan when joining on primary key
Lines: 639
Organization: Sypron BV / TeamSybase / Sybase Inc
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5db7f5@forums-3-dub.sybase.com>
Date: 15 Jul 2009 04:05:25 -0700
X-Trace: forums-3-dub.sybase.com 1247655925 10.22.241.152 (15 Jul 2009 04:05:25 -0700)
X-Original-Trace: 15 Jul 2009 04:05:25 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28010
Article PK: 77259

First off, if you post showplan output, it's a lot easier to read if you
don't put strings like "Code: 6248 SQL State: 01ZZZ ---" in front of every
line.
Also, for ASE 15 questions like these, it's not possible to say very much
without (i) the output of 'set statistics plancost on' (best added as an
attachement) and (ii) the optimization goal you've used.
Anyway, looking at the query, there are no filter predicates on any table
except t5, meaning that the join order is not driven by selectivity of the
the predicates but by density statistics. Also, your t0 (PROCESS) is joined
to most other tables, making it a reasonable candidate from coming early in
the join order. So the query plan may well be correct -- the question to ask
is: is there any reason to think a better plan should be chosen.
Make sure you have statistics on all columns, try allrows_dss and try
disabling merge joins (set merge_join off). If you cannot find any scenario
that's faster than your current plan, perhaps the conclusion must be that
your current plan is fine.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"Chris Mathrusse" <christopher.mathrusse@sybase.com> wrote in message
news:4a5ce25c$4@forums-3-dub.sybase.com...
>I have a query that is joining on several tables that are all joined by
>primary/foreign key relationships, but is causing a table scan from a join
>on a primary key.
>
> Why?
>
> Below is the SQL and show plan. Table SEVERITY is being scanned but is OK
> as this table is very small (4 records). The table I am concerned with is
> PROCESS as it is large.
>
> Thanks for the help...
>
> select @@version
>
> Adaptive Server Enterprise/15.0.2/EBF 15681 ESD#5/P/RS6000/AIX
> 5.2/ase1502/2528/64-bit/FBO/Tue Jun 17 11:37:12 2008
>
> ?
>
> SELECT
>
> t5.ID
>
> , t5.CONTENT
>
> , t5.MOD_DATE
>
> , t5.CREATE_DATE
>
> , t5.ADDITIONAL_INFO
>
> , t5.VERSION
>
> , t5.PROCESS_ID
>
> , t5.RESOURCE_ID
>
> , t5.SEVERITY
>
> , t0.ID
>
> , t0.discriminator
>
> , t0.MOD_DATE
>
> , t0.CREATE_DATE
>
> , t0.VERSION
>
> , t0.STATE
>
> , t1.ID
>
> , t1.SOURCE_RECORD_DATE
>
> , t1.ORDER_NO
>
> , t1.ORDER_CODE
>
> , t1.OWNER
>
> , t2.ID
>
> , t2.START_DATE
>
> , t2.END_DATE
>
> , t2.REPORT_DOC_ID
>
> , t2.ORGANIZATION
>
> , t3.ID
>
> , t3.RESPONSE_DOC_ID
>
> , t3.ADDRESS_ID
>
> , t3.REQUEST_DOC_ID
>
> , t4.ID
>
> , t4.ADDRESS_ID
>
> , t4.REQUEST_DOC_ID
>
> , t6.ID
>
> , t6.CONTENT
>
> , t6.MOD_DATE
>
> , t6.CREATE_DATE
>
> , t6.VERSION
>
> , t6.SEVERITY
>
> , t7.ID
>
> , t7.MOD_DATE
>
> , t7.CREATE_DATE
>
> , t7.VERSION
>
> FROM
>
> PROCESS t0
>
> LEFT OUTER JOIN ORDER_PROCESS t1
>
> ON
>
> (
>
> t1.ID = t0.ID
>
> )
>
> LEFT OUTER JOIN REPORT_PROCESS t2
>
> ON
>
> (
>
> t2.ID = t0.ID
>
> )
>
> LEFT OUTER JOIN ADDRESS_PROCESS t3
>
> ON
>
> (
>
> t3.ID = t0.ID
>
> )
>
> LEFT OUTER JOIN UPDATE_PROCESS t4
>
> ON
>
> (
>
> t4.ID = t0.ID
>
> )
>
> , SEVERITY t7
>
> , RESOURCE_DEFINITION t6
>
> , EVENTLOG t5
>
> WHERE
>
> (
>
> (
>
> (
>
> t5.CREATE_DATE > '2009-07-13 00:00:00.0'
>
> )
>
> AND
>
> (
>
> t5.CREATE_DATE < '2009-07-14 00:00:00.0'
>
> )
>
> )
>
> AND
>
> (
>
> (
>
> (
>
> t0.ID = t5.PROCESS_ID
>
> )
>
> AND
>
> (
>
> t6.ID = t5.RESOURCE_ID
>
> )
>
> )
>
> AND
>
> (
>
> t7.ID = t5.SEVERITY
>
> )
>
> )
>
> );
>
> 10:44:31 [SELECT - 39 row(s), 1.437 secs] Result set fetched
>
> Code: 6248 SQL State: 01ZZZ ---
>
> Code: 6289 SQL State: 01ZZZ --- QUERY PLAN FOR STATEMENT 1 (at line 1).
>
> Code: 6248 SQL State: 01ZZZ ---
>
> Code: 6201 SQL State: 01ZZZ --- STEP 1
>
> Code: 6203 SQL State: 01ZZZ --- The type of query is EXECUTE.
>
> Code: 10258 SQL State: 01ZZZ --- Executing a previously cached statement
> (SSQL_ID = 1093285456).
>
> Code: 6248 SQL State: 01ZZZ ---
>
> Code: 3630 SQL State: 01ZZZ --- Total estimated I/O cost for statement 1
> (at line 1): 0.
>
> Code: 6248 SQL State: 01ZZZ ---
>
> Code: 6289 SQL State: 01ZZZ --- QUERY PLAN FOR STATEMENT 1 (at line 1).
>
> Code: 6248 SQL State: 01ZZZ ---
>
> Code: 6201 SQL State: 01ZZZ --- STEP 1
>
> Code: 10262 SQL State: 01ZZZ --- The type of query is SELECT.
>
> Code: 6248 SQL State: 01ZZZ ---
>
> Code: 6248 SQL State: 01ZZZ --- 18 operator(s) under root
>
> Code: 6248 SQL State: 01ZZZ ---
>
> Code: 6248 SQL State: 01ZZZ --- |ROOT:EMIT Operator (VA = 18)
>
> Code: 6248 SQL State: 01ZZZ --- |
>
> Code: 6248 SQL State: 01ZZZ --- | |RESTRICT Operator (VA =
> 17)(0)(0)(0)(0)(11)
>
> Code: 6248 SQL State: 01ZZZ --- | |
>
> Code: 6248 SQL State: 01ZZZ --- | | |MERGE JOIN Operator (Join Type: Inner
> Join) (VA = 16)
>
> Code: 10263 SQL State: 01ZZZ --- | | | Using Worktable7 for internal
> storage.
>
> Code: 6248 SQL State: 01ZZZ --- | | | Key Count: 3
>
> Code: 6248 SQL State: 01ZZZ --- | | | Key Ordering: ASC ASC ASC
>
> Code: 6248 SQL State: 01ZZZ --- | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | |NESTED LOOP JOIN Operator (VA = 13)
> (Join Type: Inner Join)
>
> Code: 6248 SQL State: 01ZZZ --- | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | |MERGE JOIN Operator (Join Type:
> Left Outer Join) (VA = 11)
>
> Code: 10263 SQL State: 01ZZZ --- | | | | | Using Worktable5 for internal
> storage.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | Key Count: 1
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | Key Ordering: ASC
>
> Code: 6248 SQL State: 01ZZZ --- | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | |MERGE JOIN Operator (Join Type:
> Left Outer Join) (VA = 9)
>
> Code: 10263 SQL State: 01ZZZ --- | | | | | | Using Worktable4 for internal
> storage.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | Key Count: 1
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | Key Ordering: ASC
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | |MERGE JOIN Operator (Join
> Type: Left Outer Join) (VA = 7)
>
> Code: 10263 SQL State: 01ZZZ --- | | | | | | | Using Worktable3 for
> internal storage.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | Key Count: 1
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | Key Ordering: ASC
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | |MERGE JOIN Operator (Join
> Type: Left Outer Join) (VA = 5)
>
> Code: 10263 SQL State: 01ZZZ --- | | | | | | | | Using Worktable2 for
> internal storage.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | Key Count: 1
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | Key Ordering: ASC
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | |SORT Operator (VA = 3)
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | Average Row width is
> 77.000000
>
> Code: 10263 SQL State: 01ZZZ --- | | | | | | | | | Using Worktable1 for
> internal storage.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | |NESTED LOOP JOIN
> Operator (VA = 2) (Join Type: Inner Join)
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | | |SCAN Operator (VA =
> 0)
>
> Code: 6215 SQL State: 01ZZZ --- | | | | | | | | | | | FROM TABLE
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | | | SEVERITY
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | | | t7
>
> Code: 6223 SQL State: 01ZZZ --- | | | | | | | | | | | Table Scan.
>
> Code: 6276 SQL State: 01ZZZ --- | | | | | | | | | | | Forward Scan.
>
> Code: 6278 SQL State: 01ZZZ --- | | | | | | | | | | | Positioning at start
> of table.
>
> Code: 10240 SQL State: 01ZZZ --- | | | | | | | | | | | Using I/O Size 2
> Kbytes for data pages.
>
> Code: 10239 SQL State: 01ZZZ --- | | | | | | | | | | | With LRU Buffer
> Replacement Strategy for data pages.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | | | |SCAN Operator (VA =
> 1)
>
> Code: 6215 SQL State: 01ZZZ --- | | | | | | | | | | | FROM TABLE
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | | | PROCESS
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | | | t0
>
> Code: 6223 SQL State: 01ZZZ --- | | | | | | | | | | | Table Scan.
>
> Code: 6276 SQL State: 01ZZZ --- | | | | | | | | | | | Forward Scan.
>
> Code: 6278 SQL State: 01ZZZ --- | | | | | | | | | | | Positioning at start
> of table.
>
> Code: 10240 SQL State: 01ZZZ --- | | | | | | | | | | | Using I/O Size 16
> Kbytes for data pages.
>
> Code: 10239 SQL State: 01ZZZ --- | | | | | | | | | | | With LRU Buffer
> Replacement Strategy for data pages.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | | |SCAN Operator (VA = 4)
>
> Code: 6215 SQL State: 01ZZZ --- | | | | | | | | | FROM TABLE
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | ORDER_PROCESS
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | | | t1
>
> Code: 6224 SQL State: 01ZZZ --- | | | | | | | | | Using Clustered Index.
>
> Code: 6225 SQL State: 01ZZZ --- | | | | | | | | | Index :
> ORDER_PROC_7645267262
>
> Code: 6276 SQL State: 01ZZZ --- | | | | | | | | | Forward Scan.
>
> Code: 6282 SQL State: 01ZZZ --- | | | | | | | | | Positioning at index
> start.
>
> Code: 6272 SQL State: 01ZZZ --- | | | | | | | | | Using I/O Size 2 Kbytes
> for index leaf pages.
>
> Code: 6273 SQL State: 01ZZZ --- | | | | | | | | | With LRU Buffer
> Replacement Strategy for index leaf pages.
>
> Code: 10240 SQL State: 01ZZZ --- | | | | | | | | | Using I/O Size 2 Kbytes
> for data pages.
>
> Code: 10239 SQL State: 01ZZZ --- | | | | | | | | | With LRU Buffer
> Replacement Strategy for data pages.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | | |SCAN Operator (VA = 6)
>
> Code: 6215 SQL State: 01ZZZ --- | | | | | | | | FROM TABLE
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | | REPORT_PROCESS
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | | t2
>
> Code: 6224 SQL State: 01ZZZ --- | | | | | | | | Using Clustered Index.
>
> Code: 6225 SQL State: 01ZZZ --- | | | | | | | | Index :
> REPORT_PRO_10685278092
>
> Code: 6276 SQL State: 01ZZZ --- | | | | | | | | Forward Scan.
>
> Code: 6282 SQL State: 01ZZZ --- | | | | | | | | Positioning at index
> start.
>
> Code: 6272 SQL State: 01ZZZ --- | | | | | | | | Using I/O Size 2 Kbytes
> for index leaf pages.
>
> Code: 6273 SQL State: 01ZZZ --- | | | | | | | | With LRU Buffer
> Replacement Strategy for index leaf pages.
>
> Code: 10240 SQL State: 01ZZZ --- | | | | | | | | Using I/O Size 2 Kbytes
> for data pages.
>
> Code: 10239 SQL State: 01ZZZ --- | | | | | | | | With LRU Buffer
> Replacement Strategy for data pages.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | | |SCAN Operator (VA = 8)
>
> Code: 6215 SQL State: 01ZZZ --- | | | | | | | FROM TABLE
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | ADDRESS_PROCESS
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | | t3
>
> Code: 6224 SQL State: 01ZZZ --- | | | | | | | Using Clustered Index.
>
> Code: 6225 SQL State: 01ZZZ --- | | | | | | | Index :
> ADDRESS_PR_10205276382
>
> Code: 6276 SQL State: 01ZZZ --- | | | | | | | Forward Scan.
>
> Code: 6282 SQL State: 01ZZZ --- | | | | | | | Positioning at index start.
>
> Code: 6272 SQL State: 01ZZZ --- | | | | | | | Using I/O Size 2 Kbytes for
> index leaf pages.
>
> Code: 6273 SQL State: 01ZZZ --- | | | | | | | With LRU Buffer Replacement
> Strategy for index leaf pages.
>
> Code: 10240 SQL State: 01ZZZ --- | | | | | | | Using I/O Size 16 Kbytes
> for data pages.
>
> Code: 10239 SQL State: 01ZZZ --- | | | | | | | With LRU Buffer Replacement
> Strategy for data pages.
>
> Code: 6248 SQL State: 01ZZZ --- | | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | | |SCAN Operator (VA = 10)
>
> Code: 6215 SQL State: 01ZZZ --- | | | | | | FROM TABLE
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | UPDATE_PROCESS
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | | t4
>
> Code: 6224 SQL State: 01ZZZ --- | | | | | | Using Clustered Index.
>
> Code: 6225 SQL State: 01ZZZ --- | | | | | | Index : UPDATE_PRO_5405259282
>
> Code: 6276 SQL State: 01ZZZ --- | | | | | | Forward Scan.
>
> Code: 6282 SQL State: 01ZZZ --- | | | | | | Positioning at index start.
>
> Code: 6272 SQL State: 01ZZZ --- | | | | | | Using I/O Size 2 Kbytes for
> index leaf pages.
>
> Code: 6273 SQL State: 01ZZZ --- | | | | | | With LRU Buffer Replacement
> Strategy for index leaf pages.
>
> Code: 10240 SQL State: 01ZZZ --- | | | | | | Using I/O Size 16 Kbytes for
> data pages.
>
> Code: 10239 SQL State: 01ZZZ --- | | | | | | With LRU Buffer Replacement
> Strategy for data pages.
>
> Code: 6248 SQL State: 01ZZZ --- | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | |SCAN Operator (VA = 12)
>
> Code: 6215 SQL State: 01ZZZ --- | | | | | FROM TABLE
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | RESOURCE_DEFINITION
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | t6
>
> Code: 6224 SQL State: 01ZZZ --- | | | | | Using Clustered Index.
>
> Code: 6225 SQL State: 01ZZZ --- | | | | | Index : RESOURCE_D_13565288352
>
> Code: 6276 SQL State: 01ZZZ --- | | | | | Forward Scan.
>
> Code: 6282 SQL State: 01ZZZ --- | | | | | Positioning at index start.
>
> Code: 6272 SQL State: 01ZZZ --- | | | | | Using I/O Size 2 Kbytes for
> index leaf pages.
>
> Code: 6273 SQL State: 01ZZZ --- | | | | | With LRU Buffer Replacement
> Strategy for index leaf pages.
>
> Code: 10240 SQL State: 01ZZZ --- | | | | | Using I/O Size 2 Kbytes for
> data pages.
>
> Code: 10239 SQL State: 01ZZZ --- | | | | | With LRU Buffer Replacement
> Strategy for data pages.
>
> Code: 6248 SQL State: 01ZZZ --- | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | |SORT Operator (VA = 15)
>
> Code: 6248 SQL State: 01ZZZ --- | | | | Average Row width is 200.583740
>
> Code: 10263 SQL State: 01ZZZ --- | | | | Using Worktable6 for internal
> storage.
>
> Code: 6248 SQL State: 01ZZZ --- | | | |
>
> Code: 6248 SQL State: 01ZZZ --- | | | | |SCAN Operator (VA = 14)
>
> Code: 6215 SQL State: 01ZZZ --- | | | | | FROM TABLE
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | EVENTLOG
>
> Code: 6217 SQL State: 01ZZZ --- | | | | | t5
>
> Code: 6225 SQL State: 01ZZZ --- | | | | | Index : x_EVENTLOG_CREATE_DATE
>
> Code: 6276 SQL State: 01ZZZ --- | | | | | Forward Scan.
>
> Code: 6281 SQL State: 01ZZZ --- | | | | | Positioning by key.
>
> Code: 6287 SQL State: 01ZZZ --- | | | | | Keys are:
>
> Code: 6288 SQL State: 01ZZZ --- | | | | | CREATE_DATE ASC
>
> Code: 6272 SQL State: 01ZZZ --- | | | | | Using I/O Size 2 Kbytes for
> index leaf pages.
>
> Code: 6273 SQL State: 01ZZZ --- | | | | | With LRU Buffer Replacement
> Strategy for index leaf pages.
>
> Code: 10240 SQL State: 01ZZZ --- | | | | | Using I/O Size 16 Kbytes for
> data pages.
>
> Code: 10239 SQL State: 01ZZZ --- | | | | | With LRU Buffer Replacement
> Strategy for data pages.
>
> Code: 6248 SQL State: 01ZZZ ---
>
> Code: 3630 SQL State: 01ZZZ --- Total estimated I/O cost for statement 1
> (at line 1): 98341.
>
> Code: 6248 SQL State: 01ZZZ ---
>
> ... 1 statement(s) executed, 39 row(s) affected, exec/fetch time:
> 1.437/0.063 sec [1 successful, 0 warnings, 0 errors]
>
> ?
>
>
> Chris Mathrusse
> christopher.mathrusse@sybase.com
> Sybase, Inc
>
>
>
>