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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers 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).
