Full tablescan eventhough join is on indexed field [message #479588] |
Mon, 18 October 2010 08:08 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Full tablescan eventhough join is on indexed field [message #479594 is a reply to message #479588] |
Mon, 18 October 2010 08:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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.
|
|
|
Re: Full tablescan eventhough join is on indexed field [message #479621 is a reply to message #479594] |
Mon, 18 October 2010 09:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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,
|
|
|
|
|
|
|
Re: Full tablescan eventhough join is on indexed field [message #479652 is a reply to message #479632] |
Mon, 18 October 2010 10:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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,
|
|
|
|
Re: Full tablescan eventhough join is on indexed field [message #479739 is a reply to message #479588] |
Tue, 19 October 2010 02:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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.
|
|
|