Re: Do fast full index scans do physical disk reads?

On Monday 10 September 2001 05:50, Cherie_Machler_at_gelco.com wrote:
> Ian,
>
> I'll look at compressing the index. Does that only work on unique indexes
> or can you
> do it on non-unique multi-column indexes as well?

You can compress unique and non-unique indexes. You may get better compression from non-unique indexes, but this is really dependent on your data in any case.

I've shrunk 180 meg indexes down to 60 meg with it.

Don't expect your indexes to be faster though. The benefit of compression is chiefly that Oracle will be able to cache more index blocks when compressed, greatly speeding queries when so.

If your index is read from disk though, you won't see much of a difference in many cases.

Jared

> Thanks,
>
> Cherie
>
>
>
> "MacGregor,
> Ian A." To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com> <ian_at_SLAC.Stan cc:
> ford.EDU> Subject: RE: Do fast full
> index scans do physical disk reads? Sent by:
> root_at_fatcity.c
> om
>
>
> 09/07/01 03:26
> PM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> The advantage of the fast full index scan is that it should read fewer
> blocks than the full table scan. Index compression may help reduce the
> number of blocks read even further. A unique index mist be at least two
> columns wide to benefit from compression.
>
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Friday, September 07, 2001 5:20 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Ian,
>
> The last one I looked at it was cached, I guess. I could purposely cache
> the
> table (and index) if it was small, though.
>
> I'm confused though. Isn't the whole benefit of the fast, full index scan
> that you
> don't have to go against the table, thereby avoiding those physical reads?
>
> Or, in the case where the index isn't cached, is the benefit that you don't
> have
> to read all of the columns in the table that aren't part of the index?
>
> Thanks for your reply,
>
> Cherie
>
>
>
> "MacGregor,
>
> Ian A." To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com>
> <ian_at_SLAC.Stan cc:
>
> ford.EDU> Subject: RE: Do fast full
> index scans do physical disk reads?
> Sent by:
>
> root_at_fatcity.c
>
> om
>
>
>
> 09/07/01 01:05
>
> AM
>
> Please respond
>
> to ORACLE-L
>
>
>
>
>
>
>
> There is no rule that says an index will be cache. Yes physical reads are
> being done.
> If the unique index is composed of more than one column look into
> compressing it.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
>
>
> -----Original Message-----
> Sent: Thursday, September 06, 2001 1:51 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> I am confused by the output from tkprof below. An fast full index
> scan is being performed. However, from the statistics, it looks as
> thought 649 physical disk reads are being performed. Is that actually
> the case? Are physical disk reads being done?
>
> Thanks,
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
> ***************************************************************************
>*****
>
>
>
> Select SD.KS_OBJECTID as CONCEPTID
> From kbowner.KS_SHORTDESCRIPTION SD
> Where SD.KS_DESCRIPTIONTYPE = 'CPTNAME' And
> UPPER(SD.KS_DESCRIPTIONTEXT) = ''
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.03 0.03 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 0.30 0.30 649 649 4
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 0.33 0.33 649 649 4
> 0
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 INDEX FAST FULL SCAN (object id 5286)
>
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'SYS_C001069' (UNIQUE)
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Cherie_Machler_at_gelco.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> 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).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Cherie_Machler_at_gelco.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> 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).

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