Re: Full tablescans explain plans and indexes

Pllllease take a look at
http://asktom.oracle.com/pls/ask/f?p=4950:8:17977441398581393250::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:37453890985588

Daniel

"Thiko!" <biwombi_at_hotmail.com> wrote in message news:<1112269985.713096.74640_at_l41g2000cwc.googlegroups.com>...
> 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.
Received on Thu Mar 31 2005 - 15:06:30 CST