Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why FULL TABLE SCAN ?

Re: Why FULL TABLE SCAN ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 12 Jun 2004 10:14:17 +0000 (UTC)
Message-ID: <cael1p$3be$1@titan.btinternet.com>

There area a couple of articles of mine
on www.dbazine.com that might help.
They give a basic introduction to how
the optimizer does its calculations

Note, to start with, that the delete statement is going to delete 151,172 rows (according to Oracle's estimates). That's probably quite a lot of block gets (up to a maximum in the region of 151,172, and Oracle counts each block get as an I/O request) when using the indexed access path, whereas the tablescan is going to take only 923 I/O requests.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message
news:40c9cbad$1_at_olaf.komtel.net...

> Hi folks,
>
> i have a problem with the way Oracle executes one of my SQL statements
on
> a partitioned table. It uses a full table access even though in my point
of
> view the CBO could perfectly well use an index to accelerate things. But
it
> doesnt. So I am going to post my statement and the schema information
about
> the table and index here, hoping that someone may find the time to go
> through it and tell my why my index is not being used and how I can speed
> things up.
>
> 2. Execution Plan created by Oracle:
> ****************************
> COST ALL ROWS (Optimizer: CHOOSE)
> total cost:923
> 1. HARTMANN_SCHEMGEN_ARCHIVERTEST.SG_TracedNodes_p TABLE ACCESS [FULL]
> (estimated cost: 923, estimated rows returned: 151.172, estimated KB
> returned: 3.247,836
> 2. DELETE
> 3. DELETE STATEMENT (estimated cost: 923, estimated rows returned:
151.172,
> estimated KB returned: 3.247,836
Received on Sat Jun 12 2004 - 05:14:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US