Hi Tom ,

I have two queries as mentioned below .
When I run the first query the result are as expected .
But when I run the second query , it goes on executing without displaying any results . I want to know why it is go on executing like an infinite loop .

Note Query 1 is almost same as Query 2 , the only difference I made is marked as underline in below queries
I have also pasted the explain plan of both the queries below .
( I really don't know how to introduce space and also don't know how to paste any diagrams here in the site so please bear with explain plans )

Please note that the subquery used in Query 2 always result in the same value as mentioned in Query 1 .i.e 1234

Query 1

SELECT /*+ INDEX(eventdiscount,eventdiscount_pk) INDEX( elementdiscount,elementdiscount_pk) INDEX(product,product_pk)
INDEX(price,price_pk) */
ed.REVENUE_CODE_ID,
ed.DISCOUNT_DESC,
ted.TARIFF_ID,
ed.EVENT_DISCOUNT_ID,
t.TARIFF_NAME
FROM eventdiscount ed, elementdiscount ted, price t ,productpricedetails cptd , product chp
WHERE ed.EVENT_DISCOUNT_ID= ted.EVENT_DISCOUNT_ID
AND ed.CATALOGUE_CHANGE_ID= ted.CATALOGUE_CHANGE_ID
AND ted.TARIFF_ID = t.TARIFF_ID
AND ted.CATALOGUE_CHANGE_ID= t.CATALOGUE_CHANGE_ID
AND cptd.TARIFF_ID = t.TARIFF_ID
AND cptd.CUSTOMER_REF =chp.CUSTOMER_REF
AND cptd.PRODUCT_SEQ = chp.PRODUCT_SEQ
AND ted.product_id = chp.product_id
AND cptd.CUSTOMER_REF ='BPPRU_T'
AND cptd.START_DAT <= TO_DATE(20160101,'YYYYMMDD')
AND (cptd.END_DAT is null OR cptd.end_dat >=TO_DATE(trim(20160131),'YYYYMMDD'))
AND cptd.PRODUCT_SEQ = 1
AND t.CATALOGUE_CHANGE_ID = 1234
AND ted.START_DAT <= TO_DATE(20160101,'YYYYMMDD')
AND (ted.END_DAT is null OR ted.end_dat >=TO_DATE(trim(20160131),'YYYYMMDD'))
AND ed.DISCOUNT_NAME = 'HUB-10Gbit/s';

Query 2

SELECT /*+ INDEX(eventdiscount,eventdiscount_pk) INDEX( elementdiscount,elementdiscount_pk) INDEX(product,product_pk)
INDEX(price,price_pk) */
ed.REVENUE_CODE_ID,
ed.DISCOUNT_DESC,
ted.TARIFF_ID,
ed.EVENT_DISCOUNT_ID,
t.TARIFF_NAME
FROM eventdiscount ed, elementdiscount ted, price t ,productpricedetails cptd , product chp
WHERE ed.EVENT_DISCOUNT_ID= ted.EVENT_DISCOUNT_ID
AND ed.CATALOGUE_CHANGE_ID= ted.CATALOGUE_CHANGE_ID
AND ted.TARIFF_ID = t.TARIFF_ID
AND ted.CATALOGUE_CHANGE_ID= t.CATALOGUE_CHANGE_ID
AND cptd.TARIFF_ID = t.TARIFF_ID
AND cptd.CUSTOMER_REF =chp.CUSTOMER_REF
AND cptd.PRODUCT_SEQ = chp.PRODUCT_SEQ
AND ted.product_id = chp.product_id
AND cptd.CUSTOMER_REF ='BPPRU_T'
AND cptd.START_DAT <= TO_DATE(20160101,'YYYYMMDD')
AND (cptd.END_DAT is null OR cptd.end_dat >=TO_DATE(trim(20160131),'YYYYMMDD'))
AND cptd.PRODUCT_SEQ = 1
AND t.CATALOGUE_CHANGE_ID in ( select distinct(catalogue_change_id) from eventdiscount where DISCOUNT_NAME = 'HUB-10Gbit/s' )
AND ted.START_DAT <= TO_DATE(20160101,'YYYYMMDD')
AND (ted.END_DAT is null OR ted.end_dat >=TO_DATE(trim(20160131),'YYYYMMDD'))
AND ed.DISCOUNT_NAME = 'HUB-10Gbit/s';


Explain Plan of Query 1

SELECT STATEMENT, GOAL = RULE
TABLE ACCESS BY INDEX ROWID ELEMENTDISCOUNT
NESTED LOOPS
NESTED LOOPS
MERGE JOIN CARTESIAN
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID PRODUCT
INDEX UNIQUE SCAN PRODUCT_PK
TABLE ACCESS BY INDEX ROWID CATALOGUECHANGE
INDEX UNIQUE SCAN CATALOGUECHANGE_PK
TABLE ACCESS BY INDEX ROWID CATALOGUECHANGE
INDEX UNIQUE SCAN CATALOGUECHANGE_PK
TABLE ACCESS BY INDEX ROWID PRODUCTPRICEDETAILS
INDEX RANGE SCAN PRODUCTPRICEDETAILS_PK
BUFFER SORT
TABLE ACCESS BY INDEX ROWID EVENTDISCOUNT
INDEX RANGE SCAN EVENTDISCOUNT_PK
TABLE ACCESS BY INDEX ROWID PRICE
INDEX RANGE SCAN PRICE_AK1
INDEX RANGE SCAN ELEMENTDISCOUNT_PK

Explain Plan of Query 2

SELECT STATEMENT, GOAL = ALL_ROWS
NESTED LOOPS SEMI
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
MERGE JOIN CARTESIAN
TABLE ACCESS BY INDEX ROWID ELEMENTDISCOUNT
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID CUSTHASPRODUCT
INDEX UNIQUE SCAN CUSTHASPRODUCT_PK
TABLE ACCESS FULL CATALOGUECHANGE
INDEX RANGE SCAN ELEMENTDISCOUNT_PK
BUFFER SORT
TABLE ACCESS BY INDEX ROWID PRODUCTPRICEDETAILS
INDEX RANGE SCAN PRODUCTPRICEDETAILS_PK
TABLE ACCESS BY INDEX ROWID PRICE
INDEX RANGE SCAN PRICE_AK1
TABLE ACCESS BY INDEX ROWID EVENTDISCOUNT
INDEX UNIQUE SCAN EVENTDISCOUNT_PK
TABLE ACCESS BY INDEX ROWID CATALOGUECHANGE
INDEX UNIQUE SCAN CATALOGUECHANGE_PK
TABLE ACCESS BY INDEX ROWID EVENTDISCOUNT
INDEX RANGE SCAN EVENTDISCOUNT_PK


Thanks & Regards
Ravi