Joining of varchar with number column for huge volumn table

Question

I have to equate the value of organization_id(number) of table msc_system_items with the the value of lookup meaning (varchar2).

i used the to_char(msi.organization_id =(select meaning from fnd_lookup_values ........) but this is taking more time.

Any suggestions?

Maybe the long time is due to the nested query, more than to the type conversion. Please post some sample data to explain the logic you need, together with your table structure, including relevant indexes. Also, please post the execution plan for your query — Aleksej, Jan 31 '18 at 15:22
please note that the msc_system_item has huge amount of records. — Vikash, Jan 31 '18 at 15:46
select distinct inventory_item_id from apps.msc_system_items msi where plan_id = -1 and sr_instance_id = 1 and exists(some condition) --upto here the query runs fast --today i added this below query as we need to check that the 5 orgs mentioned in lookup should not exists in msi table: and msi.organization_id not in (select meaning FROM apps.fnd_lookup_values and condition) after this it is taking more time — Vikash, Jan 31 '18 at 15:52
"huge amount of records" means different things to different people. How many is "huge"? Is it 100.000, 1 million, 500 million, 1 trillion etc. — HoneyBadger, Jan 31 '18 at 16:04
Again, what is your table structure? Do you have indexes on that columns? And what is the plan of that query? It's really hard to tune a query without knowing anything — Aleksej, Jan 31 '18 at 16:04

score 0 · Answer 1 · answered Jan 31 '18 at 17:13

instead of using NOT IN:

msi.organization_id not in (select meaning FROM apps.fnd_lookup_values and condition)

try to use NOT EXISTS:

not exists (select meaning 
            FROM apps.fnd_lookup_values 
            WHERE meaning = msi.organization_id and condition)

1 Answers1