Indexing NULL table column values for fast SQL performance

Note: Starting in Oracle 11g, there is new "create index" syntax that allows NULL values to be included in the index:

create index
    emp_ename_idx
on
   emp 
   (ename asc, 1)
;

Here, the "1" tells Oracle that to index on NULL values within the tables.

---

One problem with pre 11g databases (see above) is having the optional ability to index on a NULL column.  By default, relational databases ignore NULL values (because the relational model says that NULL means "not present").  Hence, Oracle indexes will not include NULL values. 

For example, this index definition would not index on "open positions", new employee positions that are stored with a NULL employee name:

create index
    emp_ename_idx
on
   emp 
   (ename)
;

Whenever a SQL query asks for the open position employee slots "where ename is NULL", there will be no index entries for NULLS in emp_name_idx and Oracle would perform an unnecessary large-table full-table scan.

Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6) 
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=6)

To get around the optimization of SQL queries that choose NULL column values, we can create a function-based index using the null value built-in SQL function to index only on the NULL columns. 

Note that the "null value" (NVL) function replaces NULL values with the character string "null', a real value that can participate in an index:

 

-- create an FBI on ename column with NULL values
create index
    emp_null_ename_idx
on
   emp 
   (nvl(ename,'null'));

 

analyze index emp_null_ename_idx compute statistics;
 

You can also do this techniques with NULL numeric values.  This syntax replaces NULL values with a zero:
 

-- create an FBI on emp_nbr column with NULL values
create index
    emp_null_emp_nbr_idx
on
   emp 
   (nvl(ename,o));

 

analyze index emp_null_ename_idx compute statistics;

Now we can use the index and greatly improve the speed of any queries that require access to the NULL columns.  Note that we must make one of two changes:

    1- Add a hint to force the index

   2 - Change the WHERE predicate to match the function

Here is an example of using an index on NULL column values:

-- insert a NULL row
insert into emp (empno) values (999);

set autotrace traceonly explain;

-- test the index access (change predicate to use FBI)
select /*+ index(emp_null_ename_idx) */
   ename
from
   emp e
where
   nvl(ename,'null') = 'null'
;

Continue Reading

SQL Server SP Call With Out Parameter

Returning stored procedure parameter values to a calling stored procedure in SQL SERVER

Overview

In a previous topic we discussed how to pass parameters into a stored procedure, but another option is to pass parameter values back out from a stored procedure.  One option for this may be that you call another stored procedure that does not return any data, but returns parameter values to be used by the calling stored procedure.

Explanation

Setting up output paramters for a stored procedure is basically the same as setting up input parameters, the only difference is that you use the OUTPUT clause after the parameter name to specify that it should return a value.  The output clause can be specified by either using the keyword "OUTPUT" or just "OUT".

Simple Output

CREATE PROCEDURE uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT

AS

SELECT @AddressCount = count(*)

FROM AdventureWorks.Person.Address

WHERE City = @City

Or it can be done this way:

CREATE PROCEDURE uspGetAddressCount @City nvarchar(30), @AddressCount int OUT

AS

SELECT @AddressCount = count(*)

FROM AdventureWorks.Person.Address

WHERE City = @City

To call this stored procedure we would execute it as follows.  First we are going to declare a variable, execute the stored procedure and then select the returned valued.

DECLARE @AddressCount int

EXEC uspGetAddressCount @City = 'Calgary', @AddressCount = @AddressCount OUTPUT

SELECT @AddressCount

This can also be done as follows, where the stored procedure parameter names are not passed.

DECLARE @AddressCount int

EXEC uspGetAddressCount 'Calgary', @AddressCount OUTPUT

SELECT @AddressCount

Continue Reading

Oracle TRUNC function

Let's look at some Oracle TRUNC function examples and explore how to use the TRUNC function in Oracle/PLSQL.

For example:

TRUNC(TO_DATE('22-AUG-03'), 'YEAR')  Result: '01-JAN-03'    TRUNC(TO_DATE('22-AUG-03'), 'Q')  Result: '01-JUL-03'    TRUNC(TO_DATE('22-AUG-03'), 'MONTH')  Result: '01-AUG-03'    TRUNC(TO_DATE('22-AUG-03'), 'DDD')  Result: '22-AUG-03'    TRUNC(TO_DATE('22-AUG-03'), 'DAY')  Result: '17-AUG-03'

Continue Reading

SQL SERVER – Find Last Day of Any Month – Current Previous Next

Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.

I have answered this question twice before here:

SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Query to Find First and Last Day of Current Month

Today, we will see the same solution again. Please use the method you find appropriate to your requirement.

Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000

LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000

LastDay_NextMonth
———————–
2007-09-30 23:59:59.000

If you want to find last day of month of any day specified use following script.
--Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth
———————–
2007-08-31 23:59:59.000

Continue Reading

MySQL’s generic “errno 150” message

MySQL's generic "errno 150" message "means that a foreign key constraint was not correctly formed." As you probably already know if you are reading this page, the generic "errno: 150" error message is really unhelpful. However:

You can get the actual error message by running SHOW ENGINE INNODB STATUS; and then looking for LATEST FOREIGN KEY ERROR in the output.

For example, this attempt to create a foreign key constraint:

CREATE TABLE t1  (id INTEGER);    CREATE TABLE t2  (t1_id INTEGER,   CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));

fails with the error Can't create table 'test.t2' (errno: 150). That doesn't tell anyone anything useful other than that it's a foreign key problem. But run SHOW ENGINE INNODB STATUS; and it will say:

------------------------  LATEST FOREIGN KEY ERROR  ------------------------  130811 23:36:38 Error in foreign key constraint of table test/t2:  FOREIGN KEY (t1_id) REFERENCES t1 (id)):  Cannot find an index in the referenced table where the  referenced columns appear as the first columns, or column types  in the table and the referenced table do not match for constraint.

Continue Reading

Things to remember about Update Strategy .

Mapping with Update Strategy and Bulk mode as load type will run in normal mode as below:

Mapping with Update Strategy and Treat rows as Insert will run in bulk mode by disabling Update Strategy Transformation as below:

Continue Reading