I did not have my coffee yet, so can someone explain me what is the reason (unless it is pure experiment) for expressions like nvl('9900135',Customer_id) which always results in '9900135'. Another words, condition Customer_id = nvl('9900135',Customer_id) is nothing more but a very twisted way to say Customer_id = '9900135'. So if you want to test optimizer IQ, it obviously did not pass this one. Otherwise it would realize that NVL of a constant is a constant itself and therfore would use index UNIQUE SCAN. However, it is smart enough to know that NVL, in general, can result in its second operand which in this case is NULLABLE Customer_id column. As a result, it can not use UNIQUE SCAN and either decides todo RANGE SCAN (by Branch_id = '664') or FULL SCAN if statistics suggests so (or if it is "because I feel like it" we all know optimizer often has a mind of its own :-) ).

Artist formerly known as SY.
P.S. Another early morning surprize. Someone stole my identity and registered as SY. So if you see some nonsense from SY - it is not me, but if is something smart, yes it is me :-).