In article <38E49E26.1AAC939E_at_Unforgetable.com>,
K Stahl <BlueSax_at_Unforgetable.com> wrote:
> Sean wrote:
> >
> > Here is the scenario I can not figure out which is best. I want to
> > create a PL/SQL stored procedure/pkg for performance gains. I have a
> > table named foobar with columns A B C and D (for lack of a better
> > example):
> >
> > foobar
> > ------------
> > A
> > B
> > C
> > D
> >
> > I want to be able to execute the procedure passing in parameters
> > telling the procedure WHICH columns to return like if I wanted just
two
> > columns (say A and C) vs three columns (A C and D).
> >
> > I could create a SQL statement and use the Dynamic SQL package, but
is
> > there a better way to do this. In C++, you can pass parameters in
> > (like &H000 & H0001) and find out what the user was specifying.
> >
> > Is there a slick way to do this?
> > Thanks for you help,
>
> I think this is something that a lot of programmers don't understand.
If
> you have a table with four columns, there is no performance gain by
only
> selecting one column because the engine still has to do as much work
as if
> you requested all four columns.
>
Actually -- there can be a HUGE difference. The engine might very well
come up with different query plans depending on the columns you pick.
Say you have a table T ( x, y, z ) and there is a index on T(x,y) and
you:
select * from T where x = 5 and y > 10;
That *will* do lots more IO's then:
select x, y from T where x = 5 and y > 10;
The first query will typically do an index range scan followed by a
table access by ROWID.
The second will never do the table access by rowid, it'll just use the
index.
there are many other cases similar to the above where the optimizer
will choose a fast full scan of an index over a full scan of a table --
depending on the columns you choose.
It is important that a program select out AS MANY columns as it needs --
but as few as it really wants. It can affect many things. Don't
start coding "select * from T" everywhere -- just get the ones you want.
If you add in other things like row chaining (what if Z was a big LONG
column or if Z was a set of columns, not a single a column....)....
If you consider the amount of network traffic you could reduce....
If you consider the amount of ram you are not using....
> What I would probably do in this type of situation is have a user-
defined
> recordset anchored on the table/view if it only involves a single
> table/view or on the cursor if the cursor is a join. When rows are
fetched
> from the cursor the values would be placed in this recordset and then
I'd
> programatically determine which columns I want to use at any discrete
point
> in my program. The big advantage to this is that if the algorithm
changes
> in the future it will be easier to maintain the program.
>
--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Apr 01 2000 - 07:54:41 CST
