Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why FULL TABLE SCAN ?
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...Received on Sat Jun 12 2004 - 05:14:17 CDT
> 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