| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Do fast full index scans do physical disk reads?
I resent the self-touting, the email you are probably refering to was a sarcastic one of someone (I believe Mark's) email. I am actually fairly humble for the most part. Although I tend to be quite sarcastic at times and on email it is sometimes hard to see my humor in most everything I say.
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 9/10/01 6:40 PM
Yes it's fun getting Chris, our self-touting genuis. However, the point is not that unique indexes cannot be compressed, but that the prefix length canot be mre that one less than the numbewr of coluymns in the index.
Ian A MacGregor
Stanford Linear Accelerator Center
ian_at_slac.Stanford.edu
-----Original Message-----
Sent: Monday, September 10, 2001 2:55 PM
To: Multiple recipients of list ORACLE-L
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
REBUILD COMPRESS 2 TABLESPACE PEPII_INDEX
*
ERROR at line 2:
ALTER INDEX CASEPUPPY.WOLO_PK
REBUILD COMPRESS 1 TABLESPACE PEPII_INDEX
/
Index altered.
"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
<ian_at_SLAC.Stan cc:
ford.EDU> Subject: RE: Do fast full
index
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
------- ------ -------- ---------- ---------- ---------- ----------
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 ListsReceived on Mon Sep 10 2001 - 17:30:39 CDT
--------------------------------------------------------------------
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: 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).
 |
 |