Re: Full tablescans explain plans and indexes

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'.

Wrong.

>
> 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

You were referring to large tables... 300k rows is not large since V7.0 was released, or even before that.

And what's wrong with 1 second? Do you really have customers complaining about 1 second response time?!?

> 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.
>

-- 
Regards,
Frank van Bortel