RelationalDBDesign
SiteMap
Advanced Tuning
SQL Tuning
Perform Tuning
Tuning Instance
Tuning Basics
External Environment
ANSI SQL Extensions
«Prev
Oracle SQL Tuning
Tuning Oracle SQL
Extension ANSI SQL
SQL Tuning Tool Set
Executing Oracle SQL
Explain Plan Utility
SQL Optimizer Modes
Rule Based Optimizer
Ranking Scheme
Cost Based Optimizer
SQL Tuning Hints
Oracle SQL
ANSI SQL Extensions
Built in Functions
Oracle null Value BIF
Decode Function
Grouping Rollup bifs
Supplied Extensions
Extensions Conclusion
SQL Tuning Tools
DBA SQL Roles
Creating SQL Standards
Developers Write SQL
Inside Stored Procedures
SQL Tuning Tools
Explain Plan Utility
SQL Declarative Language
Oracle Plan Table
Explain Plan Statement
Full Table Scan
Diagnose Table Joins
Non Correlated Subqueries
Index Range Scans
Analyze Explain Plan
SQL Explain Plan Conclusion
SQL Optimizer Modes
Optimizer Modes
Default Optimizer Mode
SQL Optimizer Modes
Rule Based Optimizer
Using the null value BIF with Oracle SQL
1) Perform outer join using the + operator. An outer join will return rows in dba_data files, even if the values are missing. So, what about the missing values from dba_free_space?
2) As you see from the query, we are querying dba_free_space.bytes, and we know that because there is no entry in dba_free_space for full data files, that this value must be zero.
3) We have enclosed all queries with the NVL clause, telling Oracle to replace null values with a numeric zero.
4)Here is another way to look at the problem. When you join two tables, some rows from the first table will not have a match in the second table
5) Because of this, your result will have missing values.
6) You can set these to zero instead by specifying SELECT NVL(column name, 0). This says, "If this is NULL, replace with zero."