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 > > > > Sent: Saturday, August 31, 2002 6:33 PM > > > > > > > > > > Hi Naveen, > > > > Thanks a lot for the efforts you are putting in for me for such a > > simple > > problem, but unfortunately, for me all the tips and tricks are not > > solving the problem. > > > > Now these are my current statistics :- > > > > > > > > + alter session set QUERY_REWRITE_ENABLED=TRUE; > > + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; > > + alter session set optimizer_mode=FIRST_ROWS; > > + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1; > > > > > > > > This procedure writes 180,000 records in employeees table > > > > + execute bulk_insert > > > > > > > > Analyzing table and rebuilding index (though its not necessary) > > > > + analyze table employees compute statistics; > > > > + alter index upper_ix rebuild; > > > > Making autotrace on > > > > + set autotrace traceonly explain > > > > > > > > Fired the query: > > > > SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL > > ORDER BY UPPER(last_name); > > Elapsed: 00:00:00.00 > > > > > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001 > > Bytes=2 > > 0005) > > > > > > > > 1 0 SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005) > > 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001 > > By > > === 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: Anjo Kolk 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).
