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.

copying clustered index during select into

4 posts in General Discussion Last posting was on 2011-02-08 18:07:42.0Z
sebastian Posted on 2011-02-07 02:12:08.0Z
Sender: 60e0.4d4f52fd.1804289383@sybase.com
From: sebastian
Newsgroups: sybase.public.ase.general
Subject: copying clustered index during select into
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d4f54f8.611f.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Feb 2011 18:12:08 -0800
X-Trace: forums-1-dub 1297044728 10.22.241.41 (6 Feb 2011 18:12:08 -0800)
X-Original-Trace: 6 Feb 2011 18:12:08 -0800, 10.22.241.41
Lines: 3
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29927
Article PK: 79156

I think there should be an option to copy a clustered index
during a simple select into command. This should save time
recreating the clustered index.


Sherlock, Kevin [TeamSybase] Posted on 2011-02-07 19:11:37.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4d4f54f8.611f.1681692777@sybase.com>
Subject: Re: copying clustered index during select into
Lines: 12
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.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d5043e9$1@forums-1-dub>
Date: 7 Feb 2011 11:11:37 -0800
X-Trace: forums-1-dub 1297105897 10.22.241.152 (7 Feb 2011 11:11:37 -0800)
X-Original-Trace: 7 Feb 2011 11:11:37 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29930
Article PK: 79159

I don't think there would be any time saved. A clustered index contains
page pointers and can't really be "copied" per se, it would have to be built
from scratch. If there were such a feature as "copy clustered index", it
would take just as much time to execute as "select into"/"create clustered
index"

<sebastian> wrote in message news:4d4f54f8.611f.1681692777@sybase.com...
>I think there should be an option to copy a clustered index
> during a simple select into command. This should save time
> recreating the clustered index.


Bret Halford Posted on 2011-02-08 16:47:04.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: copying clustered index during select into
References: <4d4f54f8.611f.1681692777@sybase.com> <4d5043e9$1@forums-1-dub>
In-Reply-To: <4d5043e9$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: <4d517388$1@forums-1-dub>
Date: 8 Feb 2011 08:47:04 -0800
X-Trace: forums-1-dub 1297183624 10.22.241.152 (8 Feb 2011 08:47:04 -0800)
X-Original-Trace: 8 Feb 2011 08:47:04 -0800, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29933
Article PK: 79160

There is actually some possibility here.

The REORG REBUILD command (and ALTER TABLE) do something
like this - if the table has a clustered index, the rows
are essentially selected into a new table ordered by the
clustered index, then the clustered index can be rebuilt
using the "with sorted data" option which means the
data layer doesn't have to be moved around, just the index
tree has to be built up.

Extending the idea to a command like SELECT INTO .... WITH
CLUSTERED_INDEX, the existing technique could be extended
pretty easily - but would only apply to selects from a single
table that had a clustered index. That seems rather limiting.

A more flexible way to do that would be to have the index based
on an ORDER BY clause. The rows would be sorted by the select
into process, then the index could be built up using the
sorted_data option. Syntax something like

SELECT col_list INTO table
FROM select_statement
ORDER BY order_by_list
WITH CREATE [unique] CLUSTERED_INDEX
[with allow_dup_row, etc]

On 2/7/2011 12:11 PM, Sherlock, Kevin [TeamSybase] wrote:
> I don't think there would be any time saved. A clustered index contains
> page pointers and can't really be "copied" per se, it would have to be built
> from scratch. If there were such a feature as "copy clustered index", it
> would take just as much time to execute as "select into"/"create clustered
> index"
>
> <sebastian> wrote in message news:4d4f54f8.611f.1681692777@sybase.com...
>> I think there should be an option to copy a clustered index
>> during a simple select into command. This should save time
>> recreating the clustered index.
>
>


Sherlock, Kevin [TeamSybase] Posted on 2011-02-08 18:07:42.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4d4f54f8.611f.1681692777@sybase.com> <4d5043e9$1@forums-1-dub> <4d517388$1@forums-1-dub>
Subject: Re: copying clustered index during select into
Lines: 76
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d51866e$1@forums-1-dub>
Date: 8 Feb 2011 10:07:42 -0800
X-Trace: forums-1-dub 1297188462 10.22.241.152 (8 Feb 2011 10:07:42 -0800)
X-Original-Trace: 8 Feb 2011 10:07:42 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29935
Article PK: 79164

Ok, but no faster than an already supported method:

>
select *
into newtable
from mytable
order by col1,col2

>
create clustered index newindex on newtable(col1,col2) with sorted data
>

which was kinda my point. The OP envisions the capability of an index tree
being copied, thereby "saving time", which isn't really possible unless the
feature offered a "physical copy" of the pages/extents, tracking the mapping
of old->new along the way, and modifying the copied index tree accordingly.
But, just thinking about that, it would be fraught with tons of
restrictions, so much that it wouldn't be worth having the engineering time
spent on it.\

Bottom line is that index trees need to be "built" AFTER the data page chain
is established because they are heavily linked to the physical layout of the
data page chain being indexed (of course). The most useful (maybe)
enhancement here would be to extend "select into" to allow ASE to
automatically reconstruct the index (or indexes) from the source table for
you, much like reorg rebuild (again, with restrictions to go with all of
this).

"Bret Halford" <bret@sybase.com> wrote in message
news:4d517388$1@forums-1-dub...
> There is actually some possibility here.
>
> The REORG REBUILD command (and ALTER TABLE) do something
> like this - if the table has a clustered index, the rows
> are essentially selected into a new table ordered by the
> clustered index, then the clustered index can be rebuilt
> using the "with sorted data" option which means the
> data layer doesn't have to be moved around, just the index
> tree has to be built up.
>
> Extending the idea to a command like SELECT INTO .... WITH
> CLUSTERED_INDEX, the existing technique could be extended
> pretty easily - but would only apply to selects from a single
> table that had a clustered index. That seems rather limiting.
>
> A more flexible way to do that would be to have the index based
> on an ORDER BY clause. The rows would be sorted by the select
> into process, then the index could be built up using the
> sorted_data option. Syntax something like
>
> SELECT col_list INTO table
> FROM select_statement
> ORDER BY order_by_list
> WITH CREATE [unique] CLUSTERED_INDEX
> [with allow_dup_row, etc]
>
>
>
>
> On 2/7/2011 12:11 PM, Sherlock, Kevin [TeamSybase] wrote:
>> I don't think there would be any time saved. A clustered index contains
>> page pointers and can't really be "copied" per se, it would have to be
>> built
>> from scratch. If there were such a feature as "copy clustered index", it
>> would take just as much time to execute as "select into"/"create
>> clustered
>> index"
>>
>> <sebastian> wrote in message news:4d4f54f8.611f.1681692777@sybase.com...
>>> I think there should be an option to copy a clustered index
>>> during a simple select into command. This should save time
>>> recreating the clustered index.
>>
>>
>