I love automagic things :) so I can leave the table alone....
right now there are all of 7 rows in it
Rachel
- Anjo Kolk <anjo_at_oraperf.com> wrote:
> Given the fact that the table is so small and frequently accessed, it
> will get
> cached 'automagically'. No need to do anything.
>
> Anjo.
>
>
> On Thursday 05 September 2002 23:43, you wrote:
> > Rachel,
> > With a table that small I would consider caching the table to
> > eliminate the io.
> > I do not know if you can cache an IOT but then it should be even
> > faster.
> > Ron
> > ROR
> >
> > >>> wisernet100_at_yahoo.com 09/05/02 04:28PM >>>
> >
> > Cary,
> >
> > in the nick of time.... I have a very small table (4 rows) that
> will
> > be
> > accessed as part of a view. But this view will be accessed a LOT
> > during
> > the day. I hadn't thought to index the table but....
> >
> > now, it's a single column table (just a list of codes to include in
> > the
> > join but I don't want to hard_code them into the view). SO I guess
> > I'll
> > just create it as a IOT, combining index and saving space at the
> same
> > time
> >
> > Rachel
> >
> > --- Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> > > Even when the high-water mark thing isn't a problem, it's
> sometimes
> > > more
> > > efficient to read every row in a table through an index than via
> a
> > > full-table scan.
> > >
> > >
> > >
> > > If you're curious, try this. Create a table with two columns,
> "key"
> > > and
> > > "value", and insert one row with key=1, value='x'. Create an
> index
> >
> > on
> >
> > > "key". Then.
> > >
> > >
> > >
> > > alter session set events '10046 trace name context forever, level
> >
> > 8';
> >
> > > select * from onerow; /* just to make sure it's cached */
> > >
> > > select * from onerow;
> > >
> > > select * from onerow where key=1; /* just to make sure it's
> cached
> > > */
> > >
> > > select * from onerow where key=1;
> > >
> > > exit;
> > >
> > >
> > >
> > > Now look at your trace data. You'll find that the full-table scan
> of
> > > this table is both cheaper and faster through the index.
> > >
> > >
> > >
> > > The age-old advice from many SQL tuning "experts" is badly wrong
> >
> > when
> >
> > > they tell you never to index small tables. For applications that
> > > execute
> > > a lot of small-table queries, the performance impact really adds
> up.
> > >
> > >
> > >
> > > Cary Millsap
> > > Hotsos Enterprises, Ltd.
> > > http://www.hotsos.com
> > >
> > > Upcoming events:
> > > - Hotsos Clinic <http://www.hotsos.com/training/clinic> , Oct 1-3
> >
> > San
> >
> > > Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
> > > - 2003 Hotsos Symposium <http://www.hotsos.com/events/symposium>
> on
> > > OracleR System Performance, Feb 9-12 Dallas
> > > - Next event: Miracle Database Forum <http://www.miracleas.dk> ,
> Sep
> > > 20-22 Middlefart Denmark
> > >
> > > -----Original Message-----
> > > Sent: Thursday, September 05, 2002 12:19 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > >
> > > Not necessarily... Cary's IOUG-A presentation covers this very
> well.
> > > One
> > > scenario is where the high water mark is set artificially high,
> and
> > > there are far more blocks allocated than actually contain data.
> In
> > > this
> > > case, a FTS will be reading far too many empty blocks.
> > >
> > > -----Original Message-----
> > > Sent: Thursday, September 05, 2002 10:19 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > Hello
> > >
> > >
> > >
> > > I think that the amount of records you read is also taken into
> > > account.
> > >
> > > If you run a query that selects ALL the records in the tables
> > >
> > > it is ALWAYS more efficient to do full table scan then to access
> > >
> > > by index.
> > >
> > >
> > >
> > > Yechiel Adar
> > > Mehish
> > >
> > > ----- Original Message -----
> > >
> > >
> > > To: Multiple <mailto:ORACLE-L_at_fatcity.com> recipients of list
> > > ORACLE-L
> > >
> > > Sent: Saturday, August 31, 2002 4:23 PM
> > >
> > >
> > >
> > >
> > > Hi All,
> > >
> > >
> > >
> > > Thanks a lot to you all. At last I got the function-based index
> > > working
> > > properly.
> > >
> > > This is what I noticed :-
> > >
> > > Have to alter session/system for :-
> > >
> > > + alter session set QUERY_REWRITE_ENABLED=TRUE;
> > > + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> > > + alter session set optimizer_mode=FIRST_ROWS;
> > >
> > >
> > >
> > > And
> > >
> > > + can't use IS NULL & IS NOT NULL clause.
> > >
> > > + can't use Like operator.
> > >
> > >
> > >
> > > Regards,
> > >
> > > Marul.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > ----- Original Message -----
> > >
> > >
> > > To: Multiple <mailto:ORACLE-L_at_fatcity.com> recipients of list
> > > ORACLE-L
>
=== message truncated ===
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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).
Received on Thu Sep 05 2002 - 21:18:24 CDT
