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 <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
>
=== 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: wisernet100_at_yahoo.com
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 - 15:28:20 CDT
