Full tablescans explain plans and indexes

From: Thiko! <biwombi_at_hotmail.com>
Date: 31 Mar 2005 03:53:05 -0800
Message-ID: <1112269985.713096.74640@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 - 05:53:05 CST