Обсуждение: list admin note:Fwd: RE: two sums in one query

this guy is still polluting this list:

----------  Forwarded Message  ----------

Subject: RE: [SQL] two sums in one query
Date: Friday 08 Jul 2005 11:33 am
From: AntiSpam UOL <marcion.jms.sspam@uol.com.br>
To: lawgon <lawgon@thenilgiris.com>

<table border=0 cellpadding=1 cellspacing=0 width=580 height=240>    <tr bgcolor=#18396B>    <td height=20 colspan=3><a
href="http://antispam.uol.com.br"style="font:bold10px verdana; padding-left:5px;text-decoration:none;
color:white;">ANTISPAMUOL »TIRA-TEIMA</a></td> </tr><tr>    <td colspan=3>        <table border=0 cellpadding=0
cellspacing=0>           <tr>                <td width=10 rowspan=2> </td>                <td><font
size=1> </font><br>                   <font size=2 face=verdana>Olá,<br><br>                    Você enviou uma
mensagempara <b>marcion.jms@uol.com.br</b><br>                    Para que sua mensagem seja encaminhada, por favor,
<ahref="http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2
RcMRVAMqAqRQr%2BDFbnjriSBKRVpApCuYtIfJSjMZrzX24WJDqyl7oj4Zc0t49l5sMpU
V%2Bpou%0AvW59walwIaK82PYACcj3x9IPrAYt4yTtrn1Dkcf019y1Q7ILcRd6ZQQIp33
k%2BeNJwLSwFj4xIw%3D%3D" target=_blank><b>clique aqui</b></a><br><br></font>                </td>                <td
width=10rowspan=2> </td>            </tr>            <tr>                <td>                    <font size=2
face=verdana>Estaconfirmação é necessária porque<b>marcion.jms@uol.com.br</b> usa o Antispam UOL, um programa
queeliminamensagens enviadas por robôs, como pornografia, propaganda ecorrentes.<br><br></font> <font size=1
style=font-size:12pxface=arialcolor=#2C719D><b>As próximas mensagens enviadas paramarcion.jms@uol.com.br não precisarão
serconfirmadas*.</b><br></font><font size=1face=tahoma,arial,verdana>*Caso você receba outro pedido deconfirmação, por
favor,peça para marcion.jms@uol.com.br incluí-loem sua lista de autorizados.<br><br></font> <table
cellpadding=3cellspacing=0border=0 bgcolor=dddddd width=100%><tr><td><fontsize=1 face=verdana><b>Atenção!</b> Se você
nãoconseguir clicar noatalho acima, acesse este
endereço:<br>http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2RcMRVA
MqAqRQr%2BDFbnjriSBKRVpApCuYtIfJSjMZrzX24WJDqyl7oj4Zc0t49l5sMpUV%2Bpo
u%0AvW59walwIaK82PYACcj3x9IPrAYt4yTtrn1Dkcf019y1Q7ILcRd6ZQQIp33k%2BeN
JwLSwFj4xIw%3D%3D</td></tr></table> </td>            </tr>        </table>    </td></tr><tr><td colspan=3
align=center><hrwidth="573" size="1"noshade></td></tr> <tr>    <td colspan=3>        <table border=0 cellpadding=0
cellspacing=0>           <tr>                <td width=10 rowspan=2> </td>                <td><font size=1
style=font-size:6px> </font><br>                   <font size=2 face=verdana>Hi,<br><br>                    You´ve
justsent a message to <b>marcion.jms@uol.com.br</b><br>                    In order to confirm the sent message, please
<ahref="http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2
RcMRVAMqAqRQr%2BDFbnjriSBKRVpApCuYtIfJSjMZrzX24WJDqyl7oj4Zc0t49l5sMpU
V%2Bpou%0AvW59walwIaK82PYACcj3x9IPrAYt4yTtrn1Dkcf019y1Q7ILcRd6ZQQIp33
k%2BeNJwLSwFj4xIw%3D%3D" target=_blank><b>click here</b></a><br><br></font>                </td>                <td
width=10rowspan=2> </td>            </tr>            <tr>                <td>                    <font size=2
face=verdana>Thisconfirmation is necessarybecause <b>marcion.jms@uol.com.br</b> uses Antispam UOL, a servicethat avoids
unwantedmessages like advertising, pornography,viruses, and spams.<br><br></font> <font size=1
style=font-size:12pxface=arialcolor=#2C719D><b>Other messages sent tomarcion.jms@uol.com.br won't need to be
confirmed*.</b></font><br><fontsize=1 face=tahoma,arial,verdana>*If you receive anotherconfirmation request, please ask
marcion.jms@uol.com.brto includeyou in his/her authorized e-mail list.<br><br></font> <tablecellpadding=3 cellspacing=0
border=0bgcolor=ddddddwidth=100%><tr><td><font size=1 face=verdana><b>Warning!</b> If thelink doesn´t work, please copy
theaddress below and paste it onyour browser:<br>http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2RcMRVA 
MqAqRQr%2BDFbnjriSBKRVpApCuYtIfJSjMZrzX24WJDqyl7oj4Zc0t49l5sMpUV%2Bpo
u%0AvW59walwIaK82PYACcj3x9IPrAYt4yTtrn1Dkcf019y1Q7ILcRd6ZQQIp33k%2BeN
JwLSwFj4xIw%3D%3D</td></tr></table> </td>
            </tr>        </table><br>    </td></tr><tr><td colspan=3 height=20 bgcolor=#2C719D
align=center><fontface=verdanacolor=ffffff size=1>Use o <ahref="http://antispam.uol.com.br"><b><font
color=ffffffsize=1>AntiSpamUOL</font></b></a> e proteja sua caixapostal</font></td></tr> </table> 
<table width=580 cellpadding=2 cellspacing=0 border=0>
<tr><td align=center><font size=1 face=arial>
<font size=1 face=arial><SCRIPT>RdFhCfCggWeb
='1996-';</SCRIPT><SCRIPTsrc="http://indice.uol.com.br/anouol.js"></SCRIPT></font></td></tr></table>

-------------------------------------------------------

--
regards
kg

http://www.livejournal.com/users/lawgon
tally ho! http://avsap.org.in
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!

I have what I call a "dictionary" table which supports a "master table". 

This dictionary table is designed to hold generic data : 
 CREATE TABLE sup_data (link_id uniqueidentifier, field_name varchar(255),
field_data text) WITH OIDS; ... 

It works well when you're digging into it to pull the supplementary information
for a small number of rows in the master table. It uses an index on the
link_id, and can jump right to the few pages for the supplemental data. That
was the design. 

Now "the powers that be" want to do some aggreate inquiries on subsets of the
generic data, based on many rows from the master table. This doesn't work so
well... Its having to pull many pages to create the result set to aggreate on. 

If I could cluster the generic data to match the clustering on the "master
table" it would reduce the number of pulled pages considerably and the speedup
would make it work well.

I'm trying to avoid replicating the column and index used to cluster the main
table in this dictionary table.

Is it even possible to cluster a table based on the clustering scheme (which is
not the link_id ...) from the master table? 

Can you gurus think of a better strategy? :) (Please??) :)

CG


    
____________________________________________________
Sell on Yahoo! Auctions – no fees. Bid on great items.  
http://auctions.yahoo.com/

> Is it even possible to cluster a table based on the clustering scheme  
> (which is
> not the link_id ...) from the master table?
>
> Can you gurus think of a better strategy? :) (Please??) :)
You can create a functional index on a function which returns the desired  
order by looking in the main table, cluster it, then drop the index...

Why would you then drop the index? Performance and storage issues? I imagine
that I would cluster the table at regular intervals to maintain the ordering,
so I'd need to to keep the index around, yes?

--- PFC <lists@boutiquenumerique.com> wrote:

> 
> 
> > Is it even possible to cluster a table based on the clustering scheme  
> > (which is
> > not the link_id ...) from the master table?
> >
> > Can you gurus think of a better strategy? :) (Please??) :)
> 
>     You can create a functional index on a function which returns the desired  
> order by looking in the main table, cluster it, then drop the index...
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 


    
____________________________________________________
Sell on Yahoo! Auctions – no fees. Bid on great items.  
http://auctions.yahoo.com/