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.

Bridge Tables

3 posts in General Discussion Last posting was on 2010-11-12 01:07:15.0Z
RGS Posted on 2010-11-11 22:53:21.0Z
Sender: 6b4b.4cd820ba.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Bridge Tables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cdc73e1.97e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Nov 2010 14:53:21 -0800
X-Trace: forums-1-dub 1289516001 10.22.241.41 (11 Nov 2010 14:53:21 -0800)
X-Original-Trace: 11 Nov 2010 14:53:21 -0800, 10.22.241.41
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29700
Article PK: 78928

Hi!

I am using ASE 15.0.3

I need to do a join between two table:

TableA Is a medium table (1 million of records)
TableB Is a huge table (100 millions od records)

TableB has a clustered index by field: date

The query between tables A and B has a SARG by TableB.date

e.d.

TableB.date = @w_date

Some pleople said me that is a good idea to create a "bridge
table" for save the query result of TableB:

select * into bridge_table from TableB where date = @w_date

And then do the join with the new table:

select * from TableA, bridge_table where.....

Is this method faster than do the join direcly between
TableA and TableB? Why reason? What If the index is
nonclustered?

Thanks!


Robert Densmore Posted on 2010-11-12 00:40:11.0Z
From: Robert Densmore <bdensmore@austin.rr.ignore.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Bridge Tables
Message-ID: <7u2pd6td8d4r3cs0addbm9drcq624d7uqr@4ax.com>
References: <4cdc73e1.97e.1681692777@sybase.com>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 11 Nov 2010 16:40:11 -0800
X-Trace: forums-1-dub 1289522411 10.22.241.152 (11 Nov 2010 16:40:11 -0800)
X-Original-Trace: 11 Nov 2010 16:40:11 -0800, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29701
Article PK: 78930

You will need to provide more information, such as the exact query and
showplan output, but, generally speaking, I would say that it's
unnecessary to create the "bridge table" as you indicate.

I don't know what other SARGs you have, but it appears the query
should start with tableB using the clustered index and join to tableA
for the rows meeting the SARG on TableB.date.

Bob

On 11 Nov 2010 14:53:21 -0800, RGS wrote:

>Hi!
>
>I am using ASE 15.0.3
>
>I need to do a join between two table:
>
>TableA Is a medium table (1 million of records)
>TableB Is a huge table (100 millions od records)
>
>TableB has a clustered index by field: date
>
>The query between tables A and B has a SARG by TableB.date
>
>e.d.
>
>TableB.date = @w_date
>
>Some pleople said me that is a good idea to create a "bridge
>table" for save the query result of TableB:
>
>select * into bridge_table from TableB where date = @w_date
>
>And then do the join with the new table:
>
>select * from TableA, bridge_table where.....
>
>Is this method faster than do the join direcly between
>TableA and TableB? Why reason? What If the index is
>nonclustered?
>
>Thanks!


IQRules Posted on 2010-11-12 01:07:15.0Z
From: IQRules <IQRules@noname.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Bridge Tables
References: <4cdc73e1.97e.1681692777@sybase.com>
In-Reply-To: <4cdc73e1.97e.1681692777@sybase.com>
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: <4cdc9343$1@forums-1-dub>
Date: 11 Nov 2010 17:07:15 -0800
X-Trace: forums-1-dub 1289524035 10.22.241.152 (11 Nov 2010 17:07:15 -0800)
X-Original-Trace: 11 Nov 2010 17:07:15 -0800, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29702
Article PK: 78931

I can think about lots of drawbacks on big tables:
1. hard to recover if data corrupt
2. hard to sync up if using Sybase replication

15 comes with semantic partition, it might help.
But is it easier maybe split the data into multiple DBs and create a
view to join them later.

On 11/11/2010 5:53 PM, RGS wrote:
> Hi!
>
> I am using ASE 15.0.3
>
> I need to do a join between two table:
>
> TableA Is a medium table (1 million of records)
> TableB Is a huge table (100 millions od records)
>
> TableB has a clustered index by field: date
>
> The query between tables A and B has a SARG by TableB.date
>
> e.d.
>
> TableB.date = @w_date
>
> Some pleople said me that is a good idea to create a "bridge
> table" for save the query result of TableB:
>
> select * into bridge_table from TableB where date = @w_date
>
> And then do the join with the new table:
>
> select * from TableA, bridge_table where.....
>
> Is this method faster than do the join direcly between
> TableA and TableB? Why reason? What If the index is
> nonclustered?
>
> Thanks!