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

What do you mean by compressing. ...

Anjan

Jared.Still_at_radisys.com wrote:

> Whoa Chris, gotcha on that one. :)
>
> The combination of columns may be unique, but you can certainly compress
> a unique index for significant space savings.
>
> The unique index I used in testing had three columns, ordered most
> selective
> first. By reversing the column order of the index and compressing it,
> the index went from 180 Megs to 60 megs.
>
> The cardinality of the new leading column was 26 in table of 1.5 million
> rows.
>
> Jared
>
>
> Christopher
> Spence To: Multiple recipients of list
>ORACLE-L <ORACLE-L_at_fatcity.com>
> <cspence_at_FuelS cc:
> pot.com> Subject: RE: Do fast full index scans
>do physical disk reads?
> Sent by:
> root_at_fatcity.c
> om
>
>
> 09/10/01 01:20
> PM
> Please respond
> to ORACLE-L
>
>
>
> Yes, there is no point in compressing all columns of a unique index as it
> would result in 0% compression as they are unique.
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
>
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax: (707) 885-2275
>
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
> -----Original Message-----
> Sent: Monday, September 10, 2001 3:55 PM
> To: Multiple recipients of list ORACLE-L
>
> The restriction is on unique indexes.
>
> 1* select column_name from dba_ind_COLUMNS where INDEX_NAME = 'WOLO_PK'
> SQL> /
>
> COLUMN_NAME
> ----------------------------------------------------------------------------
>
> ----
> PERSON_ID
> INSTITUTION_CODE
>
> ALTER INDEX CASEPUPPY.WOLO_PK
> REBUILD COMPRESS 2 TABLESPACE PEPII_INDEX
> /
>
> REBUILD COMPRESS 2 TABLESPACE PEPII_INDEX
> *
> ERROR at line 2:
> ORA-25194: invalid COMPRESS prefix length value
>
> ALTER INDEX CASEPUPPY.WOLO_PK
> REBUILD COMPRESS 1 TABLESPACE PEPII_INDEX
> /
>
> Index altered.
> ----------------------------------------------------------------------------
>
> ----------------------------------------
> This was done on an 8.1.6.3 database. As far as what's done in practice, I
> would hazard accepting the Oracle defaults for prefix length values is the
> most common. The documentation states:
>
> "For unique indexes, the valid range of prefix length values is from 1 to
> the number of key columns minus 1. The default prefix length is the number
> of key columns minus 1.
>
> For nonunique indexes, the valid range of
> prefix length values is from 1 to the number of key columns. The default
> prefix length is the number of
> key columns."
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Monday, September 10, 2001 11:26 AM
> To: Multiple recipients of list ORACLE-L
>
> Actually you can create compressed indexes upto the size of the columns. In
> other words, the last column in a concentated index can be compressed.
> Although most practice does not.
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
>
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax: (707) 885-2275
>
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
> -----Original Message-----
> Sent: Monday, September 10, 2001 1:45 PM
> To: Multiple recipients of list ORACLE-L
>
> You cannot compress single column unique indexes. The rule is: you can
> compress up to n-1 columns of a unique index where n = the number of
> columns
> in the index. A multi-column compressed index should, for maximum effect,
> have as its leading column the one with greatest number of repeated
> values.
> This is in conflict with the rule that states to put the column with the
> highest cardinality first.
>
> Bear in mind compressing an index is not cost free. The CPU will need to
> do
> more work to read the index; however the cost of the work will be less than
> doing a physical I/O.
>
> You can compress all columns of a non-unique index.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Monday, September 10, 2001 5:50 AM
> To: Multiple recipients of list ORACLE-L
>
> 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?
>
> 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:
> 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: Christopher Spence
> INET: cspence_at_FuelSpot.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: Christopher Spence
> INET: cspence_at_FuelSpot.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:
> INET: Jared.Still_at_radisys.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: Anjan Thakuria
  INET: anjan.thakuria_at_eds.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).