RE: Is this a performance issue?

From: Glenn Travis <Glenn.Travis_at_wcom.com>
Date: Tue, 25 Jul 2000 15:12:05 -0400
Message-Id: <10569.112922@fatcity.com>

This is a multi-part message in MIME format.

--Boundary_(ID_BTNMXmSeNvK82Y0qz2dECw)
Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7bit

RE: Is this a performance issue?I guess I'm crossing dbms's again. This is a feature that is available in Ingres, and it allows you to create an index and specify index columns, but it also allows you to specify additional columns to carry as data columns (not part of the index, but stored with the index structure). I made the term 'dangling' up for lack of a better word, but you get my drift.

The point is that if you are constantly selecting SSN, FirstName, LastName from an employee table which has 30 columns and an index on SSN, then you could carry ('dangle') FirstName and LastName with the index and avoid the joinback to the table data. If the extra columns are few enough and small enough, you could get some real benefits in queries.

While Oracle or Informix may not have the same feature to carry extra columns as data in the index, you could still include them as part of the index, and it would server the same purpose. You just need to be careful with primary/foreign key issues, data model issues, constraints and so on. :disclaimer end.

just my .02.
and LIST, please correct me as necessary...   -----Original Message-----
  From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Siva_Chintalapati
  Sent: Tuesday, July 25, 2000 3:34 PM
  To: Multiple recipients of list ORACLE-L   Subject: RE: Is this a performance issue?

What are dangling columns.

Regards
Siva

From: Glenn Travis[SMTP:Glenn.Travis_at_wcom.com] Reply To: ORACLE-L_at_fatcity.com Sent: Tuesday, July 25, 2000 10:27 PM

    To:     Multiple recipients of list ORACLE-L
    Subject:        RE: Is this a performance issue?

Unless all columns can be satisfied with an index (but you already knew that, right?).

If you have are selecting 5 columns, and say, three of them make up an index, you may want to consider storing the other two in the index as dangling columns. Depending on your table size, joins to other tables, performance needs, this could be a real win...

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
> Kamesh.J.Mantri_at_citicorp.com
> Sent: Tuesday, July 25, 2000 5:24 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Is this a performance issue?
>
>
> Actually this is not a performance issue bcos when Oracle fetches
> rows for a
> select it does in with implicit cursor
> which goes row by row not column by column. Even if we mention
> a single col
> it fetches the whole row.
>
> Gurus correct me I am wrong..
>
>
> Kamesh.
>
> -----Original Message-----
> Sent: Monday,July 24, 2000 9:07 PM
> To: ORACLE-L
> Cc: schanna
>
>
> Gurus,
>
> I'm wondering if it's a performance issue if i select all the
> columns from
> a table even if you don't require.
>
> Let say I have a table which has about 50 columns in it and i need to
get

> values for only 5-10 columns. So, instead of selecting only these
columns

> I select *.
>
> Any idea if that screws up my performance?
>
>
>
> Santhosh Babu
>
> --
> Author: Channa, Santhosh, SITS
> INET: schanna_at_att.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Author:
> INET: Kamesh.J.Mantri_at_citicorp.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

    --
    Author: Glenn Travis
      INET: Glenn.Travis_at_wcom.com

    Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
    San Diego, California        -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--Boundary_(ID_BTNMXmSeNvK82Y0qz2dECw)
Content-type: text/html; charset=iso-8859-1 Content-transfer-encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: Is this a performance issue?</TITLE>
<META content=3D"text/html; charset=3Dwindows-1252" =

http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2919.6307" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =

class=3D870010219-25072000>I=20
guess I'm crossing dbms's again.  This is a feature that is = available in=20
Ingres, and it allows you to create an index and specify index columns, = but it=20
also allows you to specify additional columns to carry as data columns = (not part=20
of the index, but stored with the index structure).  I made the = term=20
'dangling' up for lack of a better word, but you get my=20 drift. </DIV>
<DIV><SPAN=20 class=3D870010219-25072000> </DIV> <DIV>The=20
point is that if you are constantly selecting SSN, FirstName, LastName = from an=20
employee table which has 30 columns and an index on SSN, then you could = carry=20
('dangle') FirstName and LastName with the index and avoid the joinback = to the=20
table data.  If the extra columns are few enough and small enough, = you=20
could get some real benefits in queries.</DIV> <DIV><SPAN=20 class=3D870010219-25072000> </DIV> <DIV>While=20
Oracle or Informix may not have the same feature to carry extra columns = as data=20
in the index, you could still include them as part of the index, and it = would=20
server the same purpose.  You just need to be careful with = primary/foreign=20
key issues, data model issues, constraints and so on.  :disclaimer=20 end.</DIV>
<DIV><SPAN=20 class=3D870010219-25072000> </DIV> <DIV>just=20
my .02.</DIV>
<DIV>and=20
LIST, please correct me as necessary...</DIV> <BLOCKQUOTE=20
style=3D"BORDER-LEFT: #0000ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: = 5px">
 <DIV class=3DOutlookMessageHeader>-----Original Message----- From: root_at_fatcity.com=20 [mailto:root_at_fatcity.com]On Behalf Of = Siva_Chintalapati Sent:=20
 Tuesday, July 25, 2000 3:34 PM To: Multiple recipients of = list=20
 ORACLE-L Subject: RE: Is this a performance=20 issue? </DIV>
 What are dangling = columns.=20

 Regards = <FONT=20
 color=3D#0000ff face=3DArial size=3D2>Siva <UL>
 ---------- = <FONT=20

face=3D"MS Sans Serif" size=3D1>From:   <FONT=20 face=3D"MS Sans Serif" size=3D1>Glenn = Travis[SMTP:Glenn.Travis_at_wcom.com]=20

Reply To:=20       ORACLE-L_at_fatcity.com <FONT face=3D"MS Sans = Serif"=20

size=3D1>Sent:   Tuesday,=20

July 25, 2000 10:27 PM To:     <FONT face=3D"MS Sans = Serif"=20

size=3D1>Multiple recipients of list ORACLE-L <FONT=20 face=3D"MS Sans Serif" size=3D1>Subject:=20        RE:=20

Is this a performance issue? Unless all columns can be satisfied = with an index=20

(but you already knew that,=20 right?). 
If you have are selecting 5 columns, = and say,=20

three of them make up an index, you may=20

want to consider storing the other two in the index as = <FONT=20

face=3DArial size=3D2>dangling columns.  Depending on your = table size,=20

joins to other tables, performance needs,=20

this could be a real win... > -----Original = Message----- <FONT=20

face=3DArial size=3D2>> From: root_at_fatcity.com [<FONT = color=3D#0000ff=20

face=3DArial size=3D2><A=20
 =
href=3D"mailto:root_at_fatcity.com">mailto:root_at_fatcity.com</A><F= ONT=20
 face=3DArial size=3D2>]On Behalf Of >=20

Kamesh.J.Mantri_at_citicorp.com > Sent:=20

Tuesday, July 25, 2000 5:24 AM > To:=20

Multiple recipients of list ORACLE-L >=20

Subject: RE: Is this a performance issue? <FONT = face=3DArial=20

size=3D2>> > = <FONT=20

face=3DArial size=3D2>> Actually this is not a performance issue = bcos when=20

Oracle fetches > rows for = a=20

> select it does in with implicit =

cursor > which goes row = by  row=20

not column by column.  Even if we mention <FONT = face=3DArial=20

size=3D2>> a single col > it fetches=20

the whole row. > = <FONT=20

face=3DArial size=3D2>> Gurus correct me I am wrong.. = <FONT=20

face=3DArial size=3D2>> >=20

> Kamesh. <FONT = face=3DArial=20

size=3D2>> > = -----Original=20

Message----- > Sent: = Monday,July 24,=20

2000 9:07 PM > To: = ORACLE-L=20

> Cc: schanna <FONT = face=3DArial=20

size=3D2>> > = <FONT=20

face=3DArial size=3D2>> Gurus, >=20

>  I'm wondering if it's a = performance=20

issue if i select all the > columns=20

from >  a table even = if you don't=20

require. > = <FONT face=3DArial=20

size=3D2>>  Let say I have a table which has about 50 = columns in it=20

and i need to get >  = values for=20

only 5-10 columns. So, instead of selecting only these = columns=20

>  I select *. = <FONT=20

face=3DArial size=3D2>> >  Any=20

idea if that screws up my performance? <FONT face=3DArial =

size=3D2>> > = <FONT=20

face=3DArial size=3D2>> > =20

Santhosh Babu > = <FONT=20

face=3DArial size=3D2>> -- > Author:=20

Channa, Santhosh, SITS >  =20

INET: schanna_at_att.com >=20

> Fat City Network = Services   =20

(858) 538-5051  FAX: (858) 538-5051 > San Diego, = California       =20
Public Internet access / Mailing Lists >=20 = --------------------------------------------------------------------</FON= T>=20 > To REMOVE yourself from this = mailing list,=20 send an E-Mail message > = to:=20 ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and = in=20 > the message BODY, include a = line=20 containing: UNSUB ORACLE-L > (or the=20 name of mailing list you want to be removed from).  You = may=20 > also send the HELP command for = other=20 information (like subscribing). > > -- = <FONT=20 face=3DArial size=3D2>> Author: >   INET: Kamesh.J.Mantri_at_citicorp.com = <FONT=20 face=3DArial size=3D2>> > Fat City=20 Network Services    -- (858) 538-5051  FAX: = (858)=20 538-5051 > San Diego,=20 California        -- Public = Internet=20 access / Mailing Lists >=20 = --------------------------------------------------------------------</FON= T>=20 > To REMOVE yourself from this = mailing list,=20 send an E-Mail message > = to:=20 ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and = in=20 > the message BODY, include a = line=20 containing: UNSUB ORACLE-L > (or the=20 name of mailing list you want to be removed from).  You = may=20 > also send the HELP command for = other=20 information (like subscribing). > -- Author:=20 Glenn Travis   INET:=20 Glenn.Travis_at_wcom.com Fat City Network = Services    --=20 (858) 538-5051  FAX: (858) 538-5051 San Diego, = California        --=20 Public Internet access / Mailing Lists <FONT face=3DArial =

size=3D2>----------------------------------------------------------------=

----=20

To REMOVE yourself from this mailing = list, send=20

an E-Mail message to:=20 ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and = in=20

the message BODY, include a line = Received on Tue Jul 25 2000 - 14:12:05 CDT