Re: A hint on indexes to avoid table access full

Hi all.
My futher reply below...

> alex.malgaroli_at_gmail.com wrote:
> > Hi all.
> > I excuse myself for this topic that has probably been answered many
> > times, but I have a complex query and I can't figure how to make it
> > without getting a TABLE ACCESS FULL.
> > [...]
> > Thanks in Advance.
>
> Assuming you have 9i or above the above is not an explain plan. Well
> at least not one you should be using.

Yes, sorry. Oracle 9iR2. Windows.

> Instead of whatever ancient script you are running ... run this:
>
> SELECT * FROM TABLE(dbms_xplan.display);

ok. Will memorize that.

> Obviously Oracle has decided the cost of using your indexes is higher
> than not using them. In addition to running proper EXPLAIN PLANS post
> the DDL for the indexes and verify the validity of statistics gathered
> using DBMS_STATS.

Actually, as long I'm not a DBA... most of this "statistics", "dbms_stats"... sound familiar to me but I don't know how to enable them. DDL was a basic "create index...". Using your suggested statement above I got a result that left me thinking. A table in the result showed that the two access full-ed tables seem small (200K or so) and even to a quick and dirty byte count (estimating size based on their descs and row count) that size is correct.
Maybe you're right and the two tables are so small that fits in memory and so Oracle does not need to make any disk access. I wonder if this does happen because this is the development environment where the database has a very low load. I only wanted to be sure that on production environment where the db will be shared with other applications, that table access could not result in performance decrease or unnecessary high db load.
(Aside: no, I'm not one that believe table access full is always bad. It's just that an old colleague of mine had a very long headache for a problem like this, and he had to do some strange magic his query (something like select /* + ... something */, can't remember well), I wanted to be sure I hadn't missed something. )

Thanks everyone. Received on Thu Aug 24 2006 - 10:53:21 CDT