I love automagic things :) so I can leave the table alone.... right now there are all of 7 rows in it
Rachel --- Anjo Kolk <[EMAIL PROTECTED]> 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 > > > > >>> [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
