Re: Oracle 7.1 Enhancements

mem8321_at_u.cc.utah.edu (Mark Miller) writes:
: I agree with the rules of ANSI SQL for > and <. It is <> that I have a hard
: time with.
: Select * from employee where commission <> 400.00
: should return all the employees that do not have a commission of $400.00,
: NOT all employees who have a commission that is not 400.00 as defined
: by ANSI SQL.
:
: A better example: You want to query all the employees that may live out
: of Nebraska.
:
: You have to use
: Select * from employee where nvl(state,'ZZ') <> 'NB';
: instead of
: Select * from employee where state <> 'NB';
:

If employees is a large table, and state is indexed, the above query can give VERY POOR performance, as the nvl around state will disable the index, and the above will perform a full tablescan.

Better, would proboubly be:
select * from employee where state <> 'NB' or state is NULL;

--
regards,
Carl
+-----------------------------------------------------------------------------+
Carl Gohringer, 
Oracle UK, The Oracle Centre, The Ring, Bracknell, Berkshire, England, RG121BW
Internet   : cgohring_at_uk.oracle.com    
+-----------------------------------------------------------------------------+