Home » RDBMS Server » Performance Tuning » full tablescan though indexes exist
full tablescan though indexes exist [message #312855] Wed, 09 April 2008 23:15 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
I am running the following query:

SELECT PEA.INCURRED_BY_PERSON_ID AS PERSON_ID, PEA.EXPENDITURE_ENDING_DATE AS WEEK_END_DATE, 
CASE 
  WHEN PEIA.PROJECT_ID IN(496,497,499,502,507) THEN 'V' 
  ELSE PEIA.BILLABLE_FLAG 
  END AS BILLABLE_FLAG, 
NVL(PEIA.QUANTITY,0) AS QUANTITY, 
NVL(PEIA.TRANSACTION_SOURCE, 'EXPENDITURE BATCH') AS TRANSACTION_SOURCE 
FROM
PA_EXPENDITURES_ALL PEA 
INNER JOIN PA_EXPENDITURE_ITEMS_ALL PEIA ON PEA.EXPENDITURE_ID=PEIA.EXPENDITURE_ID 
INNER JOIN APPS.DISC2_GL_WORKING_DAYS period ON  PEIA.EXPENDITURE_ITEM_DATE BETWEEN period.START_DATE AND period.END_DATE;



In this query, PA_EXPENDITURES_ALL, PA_EXPENDITURE_ITEMS_ALL are TABLES and DISC2_GL_WORKING_DAYS is a VIEW.


Indexes exists in the following columns:

PA_EXPENDITURES_ALL.EXPENDITURE_ID
PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ID
PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_DATE

Still I get a Full Table Scan in the explain plan.

Kindly guide me as I am new to Perf Tuning area.

Explain Plan :
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		2 K	 	8285  	 	      	             	 
  HASH JOIN		2 K	161 K	8285  	 	      	             	 
    NESTED LOOPS		2 K	127 K	8007  	 	      	             	 
      VIEW	APPS.DISC2_GL_WORKING_DAYS	1  	20  	52  	 	      	             	 
        SORT GROUP BY		1  	41  	52  	 	      	             	 
          NESTED LOOPS		1  	41  	5  	 	      	             	 
            VIEW	APPS.DISC_GL_MONTH	1  	33  	4  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	GL.GL_PERIODS	1  	54  	4  	 	      	             	 
                INDEX RANGE SCAN	GL.GL_PERIODS_N1	9  	 	1  	 	      	             	 
                  SORT AGGREGATE		1  	18  	 	 	      	             	 
                    VIEW	APPS.DISC_GL_YEAR	1  	18  	2  	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	GL.GL_PERIODS	1  	46  	2  	 	      	             	 
                        INDEX RANGE SCAN	GL.GL_PERIODS_N1	2  	 	1  	 	      	             	 
            INDEX RANGE SCAN	APPS.DISC_WEEK_U1	1  	8  	1  	 	      	             	 
      TABLE ACCESS FULL	PA.PA_EXPENDITURE_ITEMS_ALL	2 K	87 K	7955  	 	      	             	 
    TABLE ACCESS FULL	PA.PA_EXPENDITURES_ALL	99 K	1 M	273  	 	      	             	 




If you see there is a TABLE ACCESS FULL on PA_EXPENDITURES_ALL & PA_EXPENDITURE_ITEMS_ALL

Can you explain why? I thought creation of appropriate indexes can avoid Full Table Scan. Please correct me if I am wrong.

Thanks
Sandi
Re: full tablescan though indexes exist [message #312921 is a reply to message #312855] Thu, 10 April 2008 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/100345/312263/102589/#msg_312263

Regards
Michel
Re: full tablescan though indexes exist [message #312989 is a reply to message #312921] Thu, 10 April 2008 05:15 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thanks Michel,
Can you tell me how to look the tuning area in Explain Plan?

Does it mean higher the cost, more you should tune the SQL?

Sandi
Re: full tablescan though indexes exist [message #313151 is a reply to message #312989] Thu, 10 April 2008 22:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How many rows are there in the VIEW when you SELECT COUNT(*) from it directly?

Oracle thinks only 1 row will be returned, and that is your problem.

I don't think the FULL scans are NECESSARILY a problem. Since you have only join predicates (no filter predicates), you will be attempting to join all of the rows in these two tables.

Indexes are only good when you want to access SOME of the rows.

Because of the issue with the view (Oracle thinks 1 row), it chooses a NESTED LOOPS join at the third line of your plan. This means that for each row returned from the view, Oracle will perform a FULL scan of PA.PA_EXPENDITURE_ITEMS_ALL.

This is FINE if the view returns only one row, but is TRAGIC if it returns more.

If you TELL Oracle how many rows will come from the view, it should do a better job. Try a CARDINALITY hint:
SELECT /*+ CARDINALITY(period 100)*/ ....

Replace the "100" with roughly however many rows are in the view. This is kind of academic; so long as it is above 1 or 2, it shouldn't perform a NESTED LOOPS.

What you are looking for is to get a MERGE join to the view, and a HASH join to the other table.

Ross Leishman
Re: full tablescan though indexes exist [message #313379 is a reply to message #313151] Fri, 11 April 2008 09:58 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Why did you include this custom view in the first place? I can not see where it is actually used in the statement.

If you mean to limit the result rows based on the contents of this view, you should perhaps use a where exist clause instead of a join.
Previous Topic: regarding update on million of records
Next Topic: 'DISTINCT' vs 'GROUP by'
Goto Forum:
  


Current Time: Sun Dec 04 13:02:01 CST 2016

Total time taken to generate the page: 0.11124 seconds