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

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Fri, 07 Sep 2001 06:28:47 -0700
Message-ID: <F001.003865CE.20010907050611@fatcity.com>

Ussually the leaf blocks will cause physical reads, but generally the root and branch blocks stay in the cache. But it is very possibly for them all to be in the cache and it is also just as possible for none of them to be in the cache.

Oracle favor's caching index blocks, but doesn't mean index scans will not cause physical reads.

"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: Thursday, September 06, 2001 4: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: 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 Fri Sep 07 2001 - 08:28:47 CDT