worktable understanding and clustered index details

[Former Member]

--- sybase version ---

Adaptive Server Enterprise/15.7.0/EBF 22640 SMP SP52 /P/RS6000/AIX 6.1/ase157sp5x/3293/64-bit/FBO/Tue May 20 21:09:25 2014

--- end ---

Hi community,

I have a SQL statement based on  derived tables and the first lines of the associated showplan (see attachment).

I'd like to know if it's possible to get some details on the worktable and its clustered index?

Based on the attachment, my understanding is that my worktable1 contains the whole table 'marketDataSet' and it has clustered index on  marketDataSet.M_ALIAS.

Am I correct?

Thanks for your feedback

Simon

sql_show_plan.txt.zip

[Mark_A_Parsons]

Try the following:

===============

dbcc traceon(3604)

go

set showplan on

set option show_code_gen long

go

>> query <<

go

===============

NOTE: This is going to generate a *lot* of output so I'd suggest redirecting output to a file.

When I ran the following test query:

===============

select s.sa_pwd, s.bs_suffix, s.maint_pwd, c.machine, c.home_dir

from servers s, servers_cb c

where (s.network = c.os and (s.server_name is NULL or c.server_id > 10))

plan "(use optgoal allrows_oltp)"

go

===============

... I received a query plan showing the servers_cb table being reformatted into a Worktable with a clustered index:

===============

... snip ...

       |   |   |STORE Operator (VA = 2)

       |   |   |  Worktable1 created, in allpages locking mode, for REFORMATTING.

       |   |   |  Creating clustered index.

       |   |   |

       |   |   |   |INSERT Operator (VA = 1)

       |   |   |   |  The update mode is direct.

       |   |   |   |

       |   |   |   |   |SCAN Operator (VA = 0)

       |   |   |   |   |  FROM TABLE

       |   |   |   |   |  servers_cb

       |   |   |   |   |  c

       |   |   |   |   |  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.

       |   |   |   |

       |   |   |   |  TO TABLE

       |   |   |   |  Worktable1.

... snip ...

===============

From the show_code_gen output I see the worktable is created with just the 4x servers_cb columns referenced in the query (the table has 17 columns), and the resulting index has a single column:

===============

... snip ...

( StoreInd

===Begin StoreInd Node (name="StoreInd")===

  [1] Subst List Count =  4

  [2] Sarg Pred Count =  0

  [3] Mixed Datatype Sarg Pred Count =  0

  [4] Filter Pred Count = 1

  [5] Expensive Pred Count =  0

  [6] Ind Sarg Pred Count =  1

  [7] Ind Mixed Datatype Sarg Pred Count =  0

  [8] Ind Filter Pred Count = 0

  [9] Work Table Cols Count =  4

  [10] Projection Cols Count =  0

  [11] Index Key Count = 1

  [12] Work Table Cols:

... snip ...

        - details of 4 columns that make up the worktable

===============

The details for the index may be in the output but I'll need to dig through the output some more.  At the moment since I know the index has a single column then I'm pretty sure it's going to be the 'os' column mentioned in the join ... and the index is not going to include the 'server_id' column mentioned as a SARG ...

[simon_ogden]

Other than in that bit directly, in general you can look at the 'best plan' stage (via set option show long or set option show_best_plan on, search for 'FINAL PLAN').

Find the relevant PopStoreInd operator, look at the 'proj' (projection) for the worktable columns (some may be repeated so look at the distinct ones) and 'pred' (predicate) for the clustered index columns. You can also look at traceflag 201, but this will be a little more cryptic.

In general, under the reformat operator it will apply the filtering lookup predicates at point of scanning the table (ideally using a applicable index), but I think it could be the case that there are occasions when it may read the filtering predicate columns into the worktable along with the join predicates and cluster on both/all.

[Former Member]

Thank you Mark,

I'll try this on my query.

Your sample was very helpfull

Simon

[Former Member]

Thanks Simon,

I'll try this and analyze the output.

Best,

Simon

[Mark_A_Parsons]

Yeah, depends on the query logic, eg, if I change my WHERE clause to the following:

===============

... snip ...

where s.network = c.os and c.server_id > 10

... snip ...

===============

Then the server_id column is not stored in the worktable, but it is used to filter the records scanned from the servers_cb table.