An index is a performance-tuning method of allowing faster retrieval of records.An index creates an entry for each value that appears in the indexed columns. In this example, we've created an index on the supplier table called supplier_idx.It consists of only one field - the supplier_name field.

SUB_ITEM_CD, NULL,1,0) ---------- - ------------------------------- 1010 a 0 1011 a 0 1012 a 0 1010 b 0 1010 c 0 1011 d 1 6 rows selected. the cbo does OK though with the user written one: select count(*) from t1, t2 cbo where t1.object_id = cbo.object_id(+) and cbo.object_id IS NULL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.08 0.10 0 91 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.08 0.11 0 91 0 1 but I find the not in to be infinitely easier to code and alot more "informational" (clear,easy to understand) Hi Tom, I don't have any statistics to provide you but could you look at query A and query B and advise which would be more efficient to write. Hello Tom, I would like to ask you for one our bad sql which uses "not in" for remote site.

[email protected] select t1.item_cd 2 from t1, (select distinct sub_item_cd from t2 ) t2 3 where t1.sub_item_cd = t2.sub_item_cd(+) 4 group by t1.item_cd 5 having max(decode( t2.sub_item_cd, NULL, 1, 0 )) = 0 6 / ITEM_CD ---------- 1010 1012 [email protected] Tom, Your points are very helping, however, in case of having 20 Mill rows in outer table and Mill in inner table none is working, both are too slow. Query A) select * from contr_prod_price cpp, where cpp.contr in ( select contr from contr where co = 'E') and cpp.prod_cd = 'PX' and not exists (select 'x' from contr_prod_prc_low_lvl_ind cpli where cpp.contr = cpli.contr and cpp.contr_frmla = cpli.contr_frmla and cpp.contr_lvl = cpli.contr_lvl union select 'x' from contr_prod_prc_low_lvl_dir cpld where cpp.contr = cpld.contr and cpp.contr_frmla = cpld.contr_frmla and cpp.contr_lvl = cpld.contr_lvl ) Query B) select * from contr_prod_price cpp, where cpp.contr in ( select contr from contr where co = 'E') and cpp.prod_cd = 'PX' and not exists (select 'x' from contr_prod_prc_low_lvl_ind cpli where cpp.contr = cpli.contr and cpp.contr_frmla = cpli.contr_frmla and cpp.contr_lvl = cpli.contr_lvl ) union select * from contr_prod_price cpp where cpp.contr in ( select contr from contr where co = 'E') and cpp.prod_cd = 'PX' and not exists (select 'x' from contr_prod_prc_low_lvl_dir cpld where cpp.contr = cpld.contr and cpp.contr_frmla = cpld.contr_frmla and cpp.contr_lvl = cpld.contr_lvl ) Thanks well, given that they do not return the same results "in general" (the 2cnd will do a distinct on the result set).... I would probably go with the first, but I would benchmark them (since I had both) select * from external_documents --(Primary Key: NAME) NAME ---------------------- corp-bro2cvs_Automatic Undo 5 rows selected ------------------------------------------------------------- SQL select * from external_document_links; --(All the 4 columns are part of the primary key) FORM DOCUMENT_NAME DOCUMENT_VERSION EXTERNAL_DOCUMENT_NAME ---------- ----------------------- ----------------------------- form_1 document_1 1 corp-bro2form_1 document_1 1 form_2 document_2 1 Automatic Undo form_2 document_1 1 PO po_4000542_2_69 1 PO po_4000542_2_69 1 Automatic Undo form_3 document_1 1 form_1 document_2 1 PO po_4000542_2_69 1 PO po_4000542_2_69 1 corp-bro2PO po_4000542_2_69 1 cvs_11 rows selected. DOCUMENT_NAME='document_1') So I get the right output as follows :- -------------------------------------------------------------------------------- NAME P ------------------------------ - Automatic Undo N Y N corp-bro2Y cvs_N 5 rows selected ---------------------------------------------------------------- Can you please tell me if there is any other way in which this query can be re-written, i.e without using the 'NOT IN' clause. For some sites we have slow network and the package takes about 10min. Do you have any suggestion for us how can we improve performance of the bad sql? SET AUTOTRACE TRACEONLY -- insert into [email protected] select PROINUM1, PROINUM2, PROAPPL, --lc_host, PROSTAT, upper(PROPROG), PROMESS, PRODATE, upper(PROUSER), PROTYPE from r4.promon where prodate might try from promon a, (select distinct upper(batch) batch from [email protected] ) b where upper(a.proprog) = b.batch(+) and b.batch is null and .....

Is there a way to avoid the subquery being executed for retrieval of every row from the outer query. on my pc, to full scan a 1,000,000 row table (a copy of all_objects -- the WIDTH * HEIGHT of a table is relevant -- not just the height. ------------------------------------------------------------- The input to the query will be external_document_links.form,external_document_links.document_name and external_document_links.document_version. DOCUMENT_NAME='document_1' union select name ,'N' from external_documents where name not in (select NAME from external_documents a,external_document_links b where a.name=b. (and consider removing the need to upper things like that all of the time by standardizing on upper or lower case for those fields....) Thats a pretty intresting thread. if you understand an outer join -- basically every row in T1 will be in the result set, regardless of whether there was a 'mate' in t2 to be joined to.

I have 1,000,000 tables that consume lots less space then a 10,000 row one) takes about 7 seconds. As long as you are using the CBO, and the tables are analyzed, it will use a hash anti join and be very fast. I have following observations : Query: SELECT ind_id, lst_ord_ss, dec_sc_is, lst_ord_is, ss_group, dec_sc_ss, list_pri, cont_lvl, site_upd_dt, source_cd, sd_custtyp, site_id, ss_subgrp FROM multi_mv WHERE country_cd = :sys_b_00 AND dsf_index IN (:sys_b_01, :sys_b_02) AND mail_cont IN (:sys_b_03, :sys_b_04) AND mail_site = :sys_b_05 AND ss_group IN (:sys_b_06, :sys_b_07, :sys_b_08, :sys_b_09, :sys_b_10) AND NOT ind_id IN (SELECT DISTINCT ind_id FROM ssw.promo WHERE drop_cd = :sys_b_11) 1. NL's SELECT STATEMENT Cost = 193604 FILTER TABLE ACCESS FULL MULTI_MV TABLE ACCESS BY INDEX ROWID PROMO INDEX RANGE SCAN PROM_DROP_CD 2. Based on this criteria I need to retrive documents linked to the above 3 columns with a flag as 'Y' and rest of the documents from external_documents table with the flag as 'N' I have written a query which goes like this :- SQL select NAME,'Y' PRESENT from external_documents a,external_document_links b where a.name=b. Tom would you please through some light on query: select count(*) from t1, t2 cbo where t1.object_id = cbo.object_id(+) and cbo.object_id IS NULL We are (outer) joing the two tables on the basis of object_id and on the other hand we are saying "cbo.object_id IS NULL". So, t1 will be retrieved from, and if there is a row(s) in t2 for that object_id -- the join will happen (and t2.object_id will be NOT NULL of course since we did the join).

Adding "ORDER BY int_id"into above Query changes the plan to seems like MERGE_AJ SELECT STATEMENT Cost = 118609 TABLE ACCESS BY INDEX ROWID MULTI_MV INDEX FULL SCAN MV_MULTI_IND_ID TABLE ACCESS BY INDEX ROWID PROMO INDEX RANGE SCAN PROM_DROP_CD 3. Table 1: ID parent ID col1 col2 col3 Table1: Primary key: ID Table2 Module ID process Date Parent ID data Col1 data Col2 source_column source_fg Table2 Primary key: Module, Id, process Date Table 1 is a superset of all Ids that Table 2 has. Else, the column values for t2 will be set to NULL and the row from t2 will be output.

Changing "NOT IN" to "NOT EXISTS" SELECT STATEMENT Cost = 198944 SORT ORDER BY HASH JOIN ANTI TABLE ACCESS FULL MULTI_MV TABLE ACCESS FULL PROMO Why is the CBO choosing Anti join only in case of NOT EXISTS ? I'll guess (in light of no creates, no knowledge of indexes, no dbms_stats.set_table_stats to let me know how big/how many rows things are, etc etc etc) Is ind_id in ssw.promo NULLABLE and if so, what happens when you say: AND NOT ind_id IN (SELECT DISTINCT ind_id FROM ssw.promo WHERE drop_cd = :sys_b_11 and IND_ID IS NOT NULL ) instead (you do know of course that NOT EXISTS and NOT IN are *not* interchangeable, they'll give different results! Parent Ids for Ids in Table1 and Table2 would be same for same Id. consider: [email protected] select * from t1,t2 where t1.x = t2.x(+); X X ---------- ---------- 1 1 2 the outer join says "all rows in t1 will be output, no matter what".Internally the Anti Hash Join retrives the full row or only the join column ? Now, I need to pick those Ids from Table1 that are - not in Table2 - have same Parent ID from Table2. So, all we need to do is keep the rows where t2.x (cbo.object_id) is NULL -- those are the rows in t1 such that there was no row in T2 to join to -- just like a not in I thought that this should not be processed as correlated: ...However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value.To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows: If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.Hi Tom, Can you pls explain the diff between IN and EXISTS and NOT IN and NOT EXISTS. NOT IN can be just as efficient as NOT EXISTS -- many orders of magnitude BETTER even -- if an "anti-join" can be used (if the subquery is known to not return nulls) select * from dual where NOT( dummy not in (NULL) ); no rows selected (you would think one of the two queries would return a row -- but there is a third state for a boolean expression in sql -- "I don't know what the answer is") Your solution is very useful but i have one query i have two tables first table with item_cd, sub_item_cd (both the columns are in primary key) second table with sub_item_cd (no primary key) i want to filter only those records from first table where all sub_item_cd exists in second table ex. There are many bugs related to this issue (2739068-2700474-3085399-2829723) and in one of them I found the workaround. create index t2_idx on t2(object_id); Index created.Because I have read that EXISTS will work better than IN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). P select count(*) from emp T1 2 where not exists ( select null from emp T2 where t2= t1.empno ); COUNT(*) ---------- 9 Ahh, but now there are 9 people who are not managers. rows of first table item_cd sub_item_cd 1010 A 1010 B 1010 C 1011 A 1011 D 1012 A rows of second table sub_item_cd A A A B B C B C i need output as item_cd 1010 1012 how can I get it in one select ? thanks in advance select t1.item_cd, t1.sub_item_cd, decode( t2.sub_item_cd, NULL, 1, 0 ) 2 from t1, (select distinct sub_item_cd from t2 ) t2 3 where t1.sub_item_cd = t2.sub_item_cd(+) 4 / ITEM_CD S DECODE(T2. select count(*) from t1 rbo where object_id not in ( select object_id from t2 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 8.63 8.53 129066 197295 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 8.64 8.53 129066 197295 0 1 select count(*) from t1 rbo where NOT EXISTS (select null from t2 where t2.object_id = rbo.object_id ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.08 0.13 83 10075 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.09 0.13 83 10075 0 1 select count(*) from t1, t2 rbo where t1.object_id = rbo.object_id(+) and rbo.object_id IS NULL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.08 0.11 72 5087 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.09 0.11 72 5087 0 1 so, it looks "good" right? but, analyze and select count(*) from t1 cbo where object_id not in ( select object_id from t2 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.04 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.06 0.08 0 91 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.06 0.12 0 91 0 1 hmmm, rbo not so good -- "user written hash anti join under rbo" not so good... seems you want every row from external_documents, and if that document has a mate in external_document_links -- show Y else show N select name, nvl( (select 'Y' from external_document_links A where = b.external_document_name and = :bind1 and b.document_version = :bind2 and b.document_name = :bind3 ), 'N' ) present from external_documents; probably works, but I couldn't really run it or anything.