self join problem

[062895]

I have a query which joins the table to itself - this bring the cost too high because all the time it brings full table scan for the self join - the number of the records to pull are not more then 20% of the table.Even if I put a hint a specific index the optimizer do not want to use it. How can I tune this query?
Thanks a lot.
MJ
qry:
SELECT aud.GROUP_ID change_value_1, aud.project_id change_value_2,
aud.GROUP_ID
|| '-'
|| aud.project_id
|| ' '
|| aud.group_desc item_modified,
x.total_points original_value, aud.total_points new_value,
aud.inf_app_user record_modified_by,
INITCAP (aud.inf_client_info) client_package_information,
aud.inf_module application, NULL record_created_on,
aud.inf_date_change record_modified_on,
DECODE (aud.inf_action_type,
'INSERT', 'TOTAL POINTS ADDED',
'DELETE', 'TOTAL POINTS REMOVED',
'TOTAL POINTS MODIFIED'
) modification_type
FROM inf_sls_group_inventory aud, inf_sls_group_inventory x
WHERE aud.inf_action_type = 'UPDATE'
AND x.GROUP_ID = aud.GROUP_ID
AND x.project_id = aud.project_id
AND x.total_points != aud.total_points
AND x.inf_action_type IN ('INSERT', 'UPDATE')
AND x.inf_id IN (
SELECT MAX (y.inf_id)
FROM inf_sls_group_inventory y
WHERE y.GROUP_ID = aud.GROUP_ID
AND y.project_id = aud.project_id
AND y.inf_id < aud.inf_id)
AND aud.inf_date_change BETWEEN TO_DATE ('20050201', 'YYYYMMDD')
AND TO_DATE ('20050228', 'YYYYMMDD') + .99999
expl plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 787.524754295954
FILTER
HASH JOIN 1 99 740 TABLE ACCESS FULLPAUDIT.INF_SLS_GROUP_INVENTORY2 K162 K 364
TABLE ACCESS FULLPAUDIT.INF_SLS_GROUP_INVENTORY98 K2 M 364
SORT AGGREGATE 1 11
TABLE ACCESS BY INDEX ROWID PAUDIT.INF_SLS_GROUP_INVENTORY 3 33 47.5247542959539
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP CONVERSION FROM ROWIDS
INDEX RANGE SCANPAUDIT.INF_PK_SLS_GRP_INV_IDX 1 K 3
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCANPAUDIT.INF_SLS_GRP_INV_PK 1 K 4

 

[LKBrwnDBA]

Do you have an index on aud.inf_action_type?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[062895]

Sure, on this one, also on all join fields - inf_id, group_id and one on Project_id. But you are right - the x.inf_action_type is the one to make the full table scan.
How can I prevent the full scan for the x.action_type aud.actiontype uses the index.
Thanks a lot,
MJ

 

[062895]

WHat kind of hint I could use to supress the fulltable scan?
If I hint the indexes the table scan is gone, but the cost rises up 10 times...

 

[carp]

How many rows are in the table and how many rows are returned by your query?

 

[062895]

there are about 1.5 mln rows but the total returns are about 20% - I thought about that if there's more then 50% retunrs the full scan will be cheaper and it'll be used.
The optimizer behaves like there's more then 50% back...

 

[cmmrfrds]

If you have Oracle 9 and above, then have you tried an inline view instead of the sub query.

 

[062895]

The join is not too bad when joining the indexed secondary field(x.inv_id = aud.inv_id) - it does use the index, but on the point where you put criteria for INF_Action_type in Update, Insert for the second table - x - it full scan again. It has an index and it uses it for AUD table, but not for x. I tried to change <> delete
instead "IN ('INSERT', 'UPDATE')" or inline view, hint the index on inf_action_type - the same...

SELECT
aud.inv_id change_value_1, aud.inv_id change_value_2,
audit_inventory_reports_pkg.get_audit_inventory_desc
(aud.inv_id)
item_modified,
x.trust_xfr_code original_value, aud.trust_xfr_code new_value,
aud.inf_app_user record_modified_by,
INITCAP (aud.inf_client_info) client_package_information,
aud.inf_module application, NULL record_created_on,
aud.inf_date_change record_modified_on,
DECODE (aud.inf_action_type,
'INSERT', 'TRUST TRANSFER ADDED',
'DELETE', 'TRUST TRANSFER REMOVED',
'TRUST TRANSFER MODIFIED'
) modification_type
FROM inf_p_inventory aud, inf_p_inventory x
WHERE aud.inf_action_type = 'UPDATE'^^^^^^^^^^^^^^^^^^^^^^^^^GOOD ONE
AND x.inv_id = aud.inv_id^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^GOOD ONE
AND x.trust_xfr_code != aud.trust_xfr_code
AND x.inf_action_type IN ('INSERT', 'UPDATE')^^^^^^^^^^^^^^^^^^PROBLEM
AND x.inf_id IN (SELECT MAX (y.inf_id)
FROM inf_p_inventory y
WHERE y.inv_id = aud.inv_id AND y.inf_id < aud.inf_id)
AND aud.inf_date_change BETWEEN TO_DATE ('20050201', 'YYYYMMDD')
AND TO_DATE ('20050228', 'YYYYMMDD') + .99999