Re: how do columns affect one another (theory question)?

yes, except in certain conditions. Here are the ones I can think of:

1. You have an index that has all 3 of those columns and Oracle chooses to read the index instead of the table.
2. You have some columns that are stored "out of line". For example LOBs can be stored out of line. So if you had 17 columns where 14 are out of line LOBS and 3 are not and you are retrieving those 3 that are not lobs then you would get 3 columns and 14 columns of refs. (much smaller than the lob data) in the buffer cache.

It would be a lot of work and probably not very efficient if Oracle just stored the requested data in the cache (3 columns instead of 17 in this example) because then it would have to keep track of the mismatch between the database block and the block in cache. In Oracle the block in cache has the same structure as the one on disk.

Jim

"Marc E" <marc_e_at_cablespeed.com> wrote in message news:ufqr1el0pil48c_at_corp.supernews.com...
> Sybrand,
> So if I have a table with 20 columns, and I only want 3 of those columns
> for a given query, I'm still actually fetching the data from the other 17
> columns into buffer cache? Is that what you're saying?
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:06iofu43h4ckkpc15ad8as2nradpcl6j2t_at_4ax.com...
> > On Mon, 3 Jun 2002 20:39:32 -0700, "Marc E" <marc_e_at_cablespeed.com>
> > wrote:
> >
> > >Hi all,
> > > I have a database theory question. Let's say I have two columns:
> > >MyInteger and MyBlob.
> > >
> > >The table contains 100,000 rows. If I run a select statement against
just
> > >the MyInteger column, with no reference to any other table, do the
> contents
> > >of the MyBlob column affect the performance of my query?
> > >
> > >Put another way, if I have 2 MB of MyInteger Data, and 1000 GB of
MyBlob
> > >data, and I just want to get at some of the MyInteger data, does that
> 1000
> > >GB of MyBlob data have any impact on my query?
> > >
> > >Thanks for input.
> > >
> > >Marc
> > >
> >
> > If you select one column of a record, the whole record is fetched in
> > buffer cache. In your case it depends whether the BLOBs are stored
> > inline or in a separate blob segment.
> > However, I have been working with an application where there was a
> > long in a table. The long was needed in only one program, but
> > logically belonged to that table. The long was also on average 40k,
> > the rest a few measly bytes. Now you guess what happened.
> > We created a separate table with the primary key of the original table
> > and the long.
> >
> > Hth
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>
Received on Tue Jun 04 2002 - 21:41:43 CDT