Re: Full tablescans explain plans and indexes

From: <fitzjarrell_at_cox.net>
Date: 31 Mar 2005 05:17:42 -0800
Message-ID: <1112275062.822280.189850@g14g2000cwa.googlegroups.com>

Thiko! wrote:
> Hi
>
> Im trying to eliminiate full tablescans from 'top' sql querries on
our
> database system. I was led to believe that full table scans on very
> large tables was a big 'nono'.
>
> However, I'm finding that running Query 1 below only takes approx. 1
> second to execute when performaing a full table scan of table
> LAYOUTITEM with 292736 rows in it. After forcing the query to use
> indexe PK's of the tables involved it is taking up to 10 seconds
> longer! This is with using database hints. I don't "get it" as I
> thought index scans on large tables was the way forward ;)
>
> Please, if anyone has any advice please 'hint'.
>
> LayoutItem = 292736 rows
> LayoutGridItem = 4658 rows
> LayoutFormat = 163 rows
>
>
> Many thanks.
>
> Thiko!
>
>
> -- Query 1
>
> SELECT LayoutItem.*, LayoutGridItem.name GridName,
> LayoutFormat.usesStory, LayoutFormat.Name
> FormatName
> FROM LayoutItem, LayoutGridItem, LayoutFormat
> WHERE LayoutItem.LayoutGridItemID=LayoutGridItem.LayoutGridItemID
> AND LayoutItem.layoutFormatID=LayoutFormat.layoutFormatID (+) AND
> LayoutItem.LayoutID=21789
> ORDER BY rank ASC;
>
>
> ID PARENT_ID POSITION
> LPAD('',2*(LEVEL-1))||OPERATION||'('||OPTIONS||')'||OBJECT_NAME
> ---------- ---------- ----------
>

---

> 0 SELECT STATEMENT ()
> 1 0 1 SORT (ORDER BY)
> 2 1 1 NESTED LOOPS ()
> 3 2 1 NESTED LOOPS (OUTER)
> 4 3 1 TABLE ACCESS (FULL)
> LAYOUTITEM
> 5 3 2 TABLE ACCESS (BY INDEX
ROWID)
> LAYOUTFORMAT
> 6 5 1 INDEX (UNIQUE SCAN)
> PK_LAYOUTFORMAT_LAYOUTFORMATID
> 7 2 2 TABLE ACCESS (BY INDEX ROWID)
> LAYOUTGRIDITEM
> 8 7 1 INDEX (UNIQUE SCAN)
> PK_LAYOUTGRIDITEM_LYUTGRDTEMD
>
> 9 rows selected.
>
>
>
>
> SELECT /*+ index(LayoutGridItem PK_LAYOUTGRIDITEM_LYUTGRDTEMD)
> index(LayoutItem
> LAYOUTITEM_LAYOUTGRDITMID_IDX) */ LayoutItem.*, LayoutGridItem.name
> GridName, LayoutFormat.usesStory,
> LayoutFormat.Name FormatName
> FROM LayoutItem, LayoutGridItem, LayoutFormat
> WHERE LayoutItem.LayoutGridItemID=LayoutGridItem.LayoutGridItemID
AND
> LayoutItem.layoutFormatID=LayoutFormat.layoutFormatID (+) AND
> LayoutItem.LayoutID=21789
> ORDER BY rank ASC;
>
> ID PARENT_ID POSITION
> LPAD('',2*(LEVEL-1))||OPERATION||'('||OPTIONS||')'||OBJECT_NAME
> ---------- ---------- ----------
>

---

> 0 26645 SELECT STATEMENT ()
> 1 0 1 SORT (ORDER BY)
> 2 1 1 HASH JOIN ()
> 3 2 1 TABLE ACCESS (BY INDEX ROWID)
> LAYOUTGRIDITEM
> 4 3 1 INDEX (FULL SCAN)
> PK_LAYOUTGRIDITEM_LYUTGRDTEMD
> 5 2 2 HASH JOIN (OUTER)
> 6 5 1 TABLE ACCESS (BY INDEX
ROWID)
> LAYOUTITEM
> 7 6 1 INDEX (FULL SCAN)
> PK_LAYOUTITEM_LAYOUTITEMID
> 8 5 2 TABLE ACCESS (FULL)
> LAYOUTFORMAT
>
> 9 rows selected.

No Oracle version number, no operating system information. How many times must we ask for such information before people start posting it as habit?

Without the above mentioned data it's difficult to diagnose exactly what the 'issue' is. In some cases the presenece of an index isn't enough; current statistics must be kept, and, in some cases, histograms are necesary for proper optimiser decisions. You provide no insight as to the nature of your data except for the number of rows present in each involved table. And, as stated previously, you provide no Oracle release number nor operating system information; this makes it impossible to determine whether this is errant behaviour, requiring a patchset from Oracle, or your data, requiring better statistics and/or histograms.

Please provide the requested information so we can better answer your inquiry.

David Fitzjarrell Received on Thu Mar 31 2005 - 07:17:42 CST