And if you adopt the 9i ASSM model for segment space, then not indexing small tables can hurt you even more...
Which brings me to my hypothesis: "If you do not index small tables, then there is no such thing as a small table" Comments anyone? Cheers Connor --- 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 > tes=20005) > > > > Any clues what is happening? Should I insert more > records in the table. > > > > TIA, > > Marul. > > > > > > > > > > > > > > ----- Original Message ----- > > > To: Multiple <mailto:[EMAIL PROTECTED]> > recipients of list ORACLE-L > > Sent: Saturday, August 31, 2002 4:58 PM > > > > > See the table's size is very small. Till it atleast > 2 times the value of > DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it > will not use index. > > > > Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to > one. > > > > Insert lots of values in the table. You can make a > procedure to insert > random characters into the table, and then put it in > a big loop. Analyze > table and thn run the same query. > > > > It should work > > > > naveen > > > -----Original Message----- > Sent: Saturday, August 31, 2002 4:03 PM > To: Multiple recipients of list ORACLE-L > > Thanks a lot Naveen, > > > > Even after executing the following the execution > plan shows full table > scan :- > > > > + alter session set QUERY_REWRITE_ENABLED=TRUE; > + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; > + alter session set optimizer_mode=FIRST_ROWS; > > + Insert into employees values('A'); > > + Insert into employees values('B'); > > + analyze table employees compute statistics; > > + > > select last_name > FROM employees WHERE UPPER(last_name) IS NOT NULL > ORDER BY UPPER(last_name); 2 3 > Elapsed: 00:00:00.00 > > > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=FIRST_ROWS > (Cost=3 Card=2 Bytes=2 > ) > > > > 1 0 SORT (ORDER BY) (Cost=3 Card=2 Bytes=2) > 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' > (Cost=1 Card=2 Bytes= > 2) > > > > Even after using the hint no change in the plan :- > > + select /* INDEX employees(upper_ix) */ last_name > FROM employees WHERE > UPPER(last_name) IS NOT NULL; > > > > Please tell me what else should I do to make this > query use the index > which is created. > > > > > > TIA, > > Marul. > > > > ----- Original Message ----- > > > To: Multiple <mailto:[EMAIL PROTECTED]> > recipients of list ORACLE-L > > Sent: Saturday, August 31, 2002 3:03 PM > > > > > Marul, > > > > 1. you don't have table analyzed in which case Rule > based optimizer will > be used. CBO is used if atleast one of the tables in > the query is > ANALYZED > > 2. There is no data in your table. Optimizer goes > for a full tablescan > if it thinks that it will be moer advisable to do a > full table scan. > e.g. You will not use the INDEX if your book has > only one page. > > > > The decision of going for a full tablescan is based > on DB_BLOCK_SIZE * > DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much > data Oracle fetches > at one time. If your entire table can be fetched in > atleast 2 fetches, > full table scan will be done instead of INDEX scan, > to avoid doubling of > work. > > > > > > Naveen > > -----Original Message----- > Sent: Saturday, August 31, 2002 2:18 PM > To: Multiple recipients of list ORACLE-L > > Hi, > > > > Can you please help me out in solving this weird > problem of > funcation-based index not being used when I query > the table. > > This is the comand I fired and the result it > returned me. > > > > 1. SQL> create table employees (last_name > varchar2(20)); > > Table created. > > > > 2. SQL> CREATE INDEX upper_ix ON employees > (UPPER(last_name)); > > Index created. > > > > Made the autotrace on and than:- > > > > 3. SELECT last_name FROM employees WHERE > UPPER(last_name) IS NOT NULL > ORDER BY UPPER(last_name); > > no rows selected. > > > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE > 1 0 SORT (ORDER BY) > 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' > > > > > > I fired without order by clause also but no use. > > > > Now can any body please let tell me why this Oracle > is having a full > scan of the employee table. > > > > TIA, > > Marul. > > > > > > > > > > > ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
