Sorting Order by GETSORTED

Question

In my sybase 12, when I look at the showplan of a select distinct query, I see GETSORTED in the showplan's output.

I've searched through the entire internet (YES!) and I can not seem to find a link that tells in what order does GETSORTED actually sort the data?

I have reasons to believe that it does the sorting on the size of the data in column for different rows, and for rows of same length, it does alphabetical.

Execution plan:

W (5):     STEP 1
   W (6):         The type of query is INSERT.
   W (7):         The update mode is direct.
   W (8):         Worktable1 created, in allpages locking mode, for DISTINCT.
   W (9): 
   W (10):         FROM TABLE
   W (11):             tablename
   W (12):         Nested iteration.
   W (13):         Table Scan.
   W (14):         Forward scan.
   W (15):         Positioning at start of table.
   W (16):         Using I/O Size 2 Kbytes for data pages.
   W (17):         With LRU Buffer Replacement Strategy for data pages.
   W (18):         TO TABLE
   W (19):             Worktable1.
   W (20): 
   W (21):     STEP 2
   W (22):         The type of query is SELECT.
   W (23):         This step involves sorting.
   W (24): 
   W (25):         FROM TABLE
   W (26):             Worktable1.
   W (27):         Using GETSORTED
   W (28):         Table Scan.
   W (29):         Forward scan.
   W (30):         Positioning at start of table.
   W (31):         Using I/O Size 2 Kbytes for data pages.
   W (32):         With MRU Buffer Replacement Strategy for data pages.

Query:

select distinct col_1 from tab_1

I am in the middle of migration of an application from Sybase 12 to Sybase 15. I see differences in the ordering of the data and that further causes differences downstream. I can not use an ORDER BY because I don't want to touch the existing application. If I can figure out what GETSORTED is doing, I can probably do the same order by on my new dataserver.

Answer 1

What you see in your execution plan is Sybase performing a DISTINCT operator.

When you do a SELECT DISTINCT in Sybase it first builds a Worktable (as you can see in step 1 of your execution plan) and then it selects sorted data removing duplicates from that worktable (which is what you see in step 2). This is documented

The sort algorithm doesn't appear to be disclosed so if the Sybase developers, with all their wisdom, introduced an optimization where they use data length to determine if values are duplicates, that could have the effect you describe as:

I have reasons to believe that it does the sorting on the size of the data in column for different rows, and for rows of same length, it does a alphabetical

The moral of the story is:

If you want your data sorted add an ORDER BY clause

As to your question

in what order does GETSORTED actually sorts the data?

In the order the algorithm can remove duplicates most efficiently.