| 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?
Rarely would you need to compress an index.
Compression of an index is perfect for a case as follows:
Table 1
Col1 Col2 Col3
45 100 10000 25 124 34423 04 124 24643252 06 100 23423052 01 100 3242422 08 123 4252525 02 123 324234
If there was an index on Col2, Col3, compress 1 would help a lot in efficiency.
One thing about compressiong, although it keeps your physical io / logical io down, it increases your cpu usage slightly. More importantly, you slightly increase the chance of locking blocks as more rows are stored in the index.
"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 8: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
------- ------ -------- ---------- ---------- ---------- ----------
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: 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).Received on Mon Sep 10 2001 - 07:53:52 CDT
 |
 |