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 <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
>
> 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:ORACLE-L_at_fatcity.com>
> 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:ORACLE-L_at_fatcity.com>
> 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: hamcdc_at_yahoo.co.uk
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 - 18:15:35 CDT