OraFAQ Forum: Performance Tuning » Full tablescan eventhough join is on indexed field

Home » RDBMS Server » Performance Tuning » Full tablescan eventhough join is on indexed field (10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Full tablescan eventhough join is on indexed field [message #479588]

Mon, 18 October 2010 08:08

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Hi,
I am posting the below 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)
                   OR (PEIA.PROJECT_ID = 7290
                       AND PEIA.TASK_ID IN
                              (187096,
                               187097,
                               187098,
                               187099,
                               187100,
                               187101,
                               187102,
                               187103,
                               187104,
                               187105)))
             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_EXPENDITURE_ITEMS_ALL PEIA
          INNER JOIN (SELECT *
                        FROM PA_EXPENDITURES_ALL
                       WHERE EXPENDITURE_ENDING_DATE > to_date( '29-MAR-2009', 'DD-MON-YYYY')) PEA
             ON PEIA.EXPENDITURE_ID = PEA.EXPENDITURE_ID
    WHERE PEIA.SYSTEM_LINKAGE_FUNCTION = 'ST';

The explain is below:

SELECT STATEMENT  ALL_ROWSCost: 48,287  Bytes: 18,428,818  Cardinality: 297,239  		
	3 HASH JOIN  Cost: 48,287  Bytes: 18,428,818  Cardinality: 297,239  	
		1 TABLE ACCESS FULL TABLE PA.PA_EXPENDITURES_ALL Cost: 2,964  Bytes: 3,506,094  Cardinality: 194,783  
		2 TABLE ACCESS FULL TABLE PA.PA_EXPENDITURE_ITEMS_ALL Cost: 43,425  Bytes: 26,637,468  Cardinality: 605,397

Can anyone suggest what can be done to avoid full table scan?

Thanks
Anand

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479594 is a reply to message #479588]

Mon, 18 October 2010 08:46

cookiemonster
Messages: 13973
Registered: September 2008
Location: Rainy Manchester

Senior Member

How many rows in each table?
How many rows does the query return?
What indexes are present on the tables?

And can you use the following method in future to get the explain plan:

SQL> explain plan for select 1 from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1546270724

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

8 rows selected.

SQL>

It's easier to read.

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479621 is a reply to message #479594]

Mon, 18 October 2010 09:44

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

pa_expenditures_all has 565399 rows
pa_expenditure_items_all has 2426174 rows
The query returns 1014986 rows.

PA_EXPENDITURE_ITEMS_ALL

INDEX_NAME	         COLUMN
PA_EXPENDITURE_ITEMS_ALL_N27	PROJECT_ID
PA_EXPENDITURE_ITEMS_N2	        TASK_ID
PA_EXPENDITURE_ITEMS_N2	        BILL_HOLD_FLAG
PA_EXPENDITURE_ITEMS_N2	        PROJECT_ID
PA_EXPENDITURE_ITEMS_N25	TASK_ID
PA_EXPENDITURE_ITEMS_N25	COST_DISTRIBUTED_FLAG
PA_EXPENDITURE_ITEMS_N25	SYSTEM_LINKAGE_FUNCTION
PA_EXPENDITURES_ITEMS_N18	BURDEN_SUM_DEST_RUN_ID
PA_EXPENDITURE_ITEMS_N1	        EXPENDITURE_ID
PA_EXPENDITURE_ITEMS_N1	        EXPENDITURE_ITEM_DATE
PA_EXPENDITURE_ITEMS_N10	TRANSACTION_SOURCE
PA_EXPENDITURE_ITEMS_N10	ORIG_TRANSACTION_REFERENCE
PA_EXPENDITURE_ITEMS_N12	REV_IND_COMPILED_SET_ID
PA_EXPENDITURE_ITEMS_N13	INV_IND_COMPILED_SET_ID
PA_EXPENDITURE_ITEMS_N14	SOURCE_EXPENDITURE_ITEM_ID
PA_EXPENDITURE_ITEMS_N16	REQUEST_ID
PA_EXPENDITURE_ITEMS_N16	COST_DISTRIBUTED_FLAG
PA_EXPENDITURE_ITEMS_N17	TASK_ID
PA_EXPENDITURE_ITEMS_N17	REVENUE_DISTRIBUTED_FLAG
PA_EXPENDITURE_ITEMS_N18	EXPENDITURE_TYPE
PA_EXPENDITURE_ITEMS_N18	SYSTEM_LINKAGE_FUNCTION
PA_EXPENDITURE_ITEMS_N19	DENORM_ID
PA_EXPENDITURE_ITEMS_ALL_N27	ADJUSTMENT_TYPE
PA_EXPENDITURE_ITEMS_N9	        TASK_ID
PA_EXPENDITURE_ITEMS_N9	        EXPENDITURE_ITEM_DATE
PA_EXPENDITURE_ITEMS_N20	CC_BL_DISTRIBUTED_CODE
PA_EXPENDITURE_ITEMS_N21	CC_CROSS_CHARGE_CODE
PA_EXPENDITURE_ITEMS_N22	RECVR_ORG_ID
PA_EXPENDITURE_ITEMS_N22	CC_IC_PROCESSED_CODE
PA_EXPENDITURE_ITEMS_N22	CC_CROSS_CHARGE_CODE
PA_EXPENDITURE_ITEMS_N22	EXPENDITURE_ITEM_DATE
PA_EXPENDITURE_ITEMS_N22	TASK_ID
PA_EXPENDITURE_ITEMS_N23	TP_IND_COMPILED_SET_ID
PA_EXPENDITURE_ITEMS_N3	        COST_DISTRIBUTED_FLAG
PA_EXPENDITURE_ITEMS_N4	        REVENUE_DISTRIBUTED_FLAG
PA_EXPENDITURE_ITEMS_N5	        ADJUSTED_EXPENDITURE_ITEM_ID
PA_EXPENDITURE_ITEMS_N6	        TRANSFERRED_FROM_EXP_ITEM_ID
PA_EXPENDITURE_ITEMS_N7	        REQUEST_ID
PA_EXPENDITURE_ITEMS_N7	        REVENUE_DISTRIBUTED_FLAG
PA_EXPENDITURE_ITEMS_U1	        EXPENDITURE_ITEM_ID
PA_EXPENDITURE_ITEMS_N11	COST_IND_COMPILED_SET_ID
PA_EXPENDITURE_ITEMS_N15	COST_BURDEN_DISTRIBUTED_FLAG
PA_EXPENDITURE_ITEMS_N8	        PROJECT_ID
PA_EXPENDITURE_ITEMS_N8	        EVENT_NUM
PA_EXPENDITURE_ITEMS_N8	        EVENT_TASK_ID
PA_EXPENDITURE_ITEMS_N24	EXPENDITURE_ITEM_DATE

PA_EXPENDITURES_ALL

INDEX_NAME	    COLUMN
PA_EXPENDITURES_ALL_N9	EXPENDITURE_GROUP
PA_EXPENDITURES_ALL_N9	INCURRED_BY_ORGANIZATION_ID
PA_EXPENDITURES_N8	TRANSFER_STATUS_CODE
PA_EXPENDITURES_N1	INCURRED_BY_PERSON_ID
PA_EXPENDITURES_N1	EXPENDITURE_ENDING_DATE
PA_EXPENDITURES_N2	INCURRED_BY_ORGANIZATION_ID
PA_EXPENDITURES_N3	EXPENDITURE_GROUP
PA_EXPENDITURES_N4	EXPENDITURE_STATUS_CODE
PA_EXPENDITURES_N5	ENTERED_BY_PERSON_ID
PA_EXPENDITURES_N6	REQUEST_ID
PA_EXPENDITURES_N7	ATTRIBUTE1
PA_EXPENDITURES_U1	EXPENDITURE_ID

Regds,

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479624 is a reply to message #479621]

Mon, 18 October 2010 09:48

cookiemonster
Messages: 13973
Registered: September 2008
Location: Rainy Manchester

Senior Member

Judging by the row counts I expect that a full table scan is the most efficient way of doing this.

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479627 is a reply to message #479621]

Mon, 18 October 2010 09:52

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Are the stats up to date?
>> The query returns 1014986 rows.
Above is way different from what you have posted earlier.
I would post the plan with dbms_xplan.
From the plan, an FTS with hash join seems to be appropriate.

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479629 is a reply to message #479627]

Mon, 18 October 2010 09:54

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Yes Stats are upto date.

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479632 is a reply to message #479629]

Mon, 18 October 2010 09:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT COUNT(*) FROM FROM PA_EXPENDITURES_ALL WHERE EXPENDITURE_ENDING_DATE > to_date( '29-MAR-2009', 'DD-MON-YYYY'));

SELECT COUNT(*) FROM PA_EXPENDITURE_ITEMS_ALL PEIA WHERE PEIA.SYSTEM_LINKAGE_FUNCTION = 'ST';

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479652 is a reply to message #479632]

Mon, 18 October 2010 10:43

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

SELECT COUNT(0) FROM PA_EXPENDITURES_ALL WHERE EXPENDITURE_ENDING_DATE > to_date( '29-MAR-2009', 'DD-MON-YYYY');
--396406 rows

SELECT COUNT(0) FROM PA_EXPENDITURE_ITEMS_ALL PEIA WHERE PEIA.SYSTEM_LINKAGE_FUNCTION = 'ST';
--2424041

Sorry, the join query actually returns 1013921 rows. I did not update correct number in my previous update.

Regds,

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479656 is a reply to message #479652]

Mon, 18 October 2010 10:52

cookiemonster
Messages: 13973
Registered: September 2008
Location: Rainy Manchester

Senior Member

So you're joining 70% of the rows in one table to 99% of the rows in another.
Indexes will not speed that up, full table scan is what you want.

Report message to a moderator

Re: Full tablescan eventhough join is on indexed field [message #479739 is a reply to message #479588]

Tue, 19 October 2010 02:49

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

Why do you want to avoid full scans?Is this query slow?
Full scans are not always evil,indexes are not always good.
As cookiemonster has pointed out,
your query belongs to category of week selectivity (396406/565399)=70% for table PA_EXPENDITURES_ALL and (2424041/2426174)~100%,indexes might not help.

Report message to a moderator

Previous Topic:	QUERY OPTIMIZATION
Next Topic:	AWR analysis

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 04 09:08:31 CST 2025