MIN , MAX and INDEX Access in Oracle.

All Entries in Index are stored as sorted either in ascending or descending order.
If we need MIN or MAX for any column being indexed, its should be easier for oracle to fetch same as both would be at index end points.

Lets understand it better and how it is access to end point within index is implemented with an example.

CREATE TABLE IDX_MIN_MAX
AS
SELECT ROWNUM RN , 'EATSPACE' COL1 FROM DUAL CONNECT BY ROWNUM < 1000000;

CREATE INDEX IDX_RN ON IDX_MIN_MAX(RN);

We have an index created on RN column, let now query to get MIN or MAX in same statement and check underlying execution plan.

SELECT MIN(RN),MAX(RN) FROM IDX_MIN_MAX;

min_max_index_1

Table full scan is perform to read MIN and MAX data and total memory buffer access to get desired data is 2628.
As we are querying MIN and MAX in same statement, oracle is not able to utilize Index data though it is sorted and required data that are at Extreme ends.
If we need Extreme end point for any indexed columns i.e. MIN and MAX for a indexed column, querying it within same SQL statement scope will impact performance.

Oracle provide “INDEX FULL SCAN (MIN/MAX)” to read extreme end point of indexed data.
It only read extreme ends point of index either MIN or MAX and procure desired result for us.

Lets Rewrite above statement with segregation of MIN and MAX in two different SQL statement scope.

SET AUTOTRACE ON;
SELECT (SELECT MIN(RN) FROM IDX_MIN_MAX) MIN_RN ,
(SELECT MAX(RN) FROM IDX_MIN_MAX) MAX_RN FROM DUAL;

we are querying MIN and MAX in two different SQL statement scope.
lets check underlying execution plan for same.

min_max_index_2

Elapsed time for SQL execution is minimal and total memory buffer access to get desired output reduced to only 3.

#Learning,
If we need indexed column extreme end points, query both in different SQL statement scope to take advantage of INDEX FULL SCAN (MIN/MAX) access path.
Also if we frequently querying MIN or MAX for a column then having an index on same would give good performance gain.

Advertisements
Posted in Oracle Internal, Oracle Performance | Tagged , , , , | Leave a comment

#SQLSERVERLearning : String Concatenation for NULL and Empty String in Oracle and SQL Server

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

Most of times during our day to day development, we work with Concatenation of string data type in database.
Its important to understand how it works with Empty String ” and NULL while concatenating in Oracle and SQL Server.

Very interesting blog with Quiz on Aggregation and Concat with Empty String and NULL and help me to explore difference between NULL and Empty String between Oracle and SQL Server.
Click to Explore

Lets first start with Oracle and try to understand how it treat NULL and Empty String.

ORACLE ::
WITH DATA AS
(
SELECT 'A' AS COL1 FROM DUAL UNION ALL
SELECT NULL AS COL1 FROM DUAL UNION ALL
SELECT '' AS COL1 FROM DUAL
)
SELECT * FROM DATA;

concat_blog

As we can see for Empty String also Oracle project it as NULL.
Oracle Treat Empty String as NULL, So ideally in oracle Empty String and NULL are treated Same.!

With NULL we are aware, NULL with any other expression force output to be NULL.
SELECT NULL + 1 , NULL -1 , NULL *1 , NULL /2 FROM DUAL;
Output for each operator will be NULL.

But it differ on how it is treated with in Concat operations in oracle.With concate in oracle, NULL does not cause output to be NULL.
|| operator will project operand that is not null, concat with NULL or Empty String will not cause make it NULL.

SELECT 'A' || '1' , 'A' || '' , 'A' || NULL , NULL || '' FROM DUAL
'A'||'1' 'A'||'' 'A'||NULL NULL||''
-------- ------- --------- --------
A1 A A NULL

With Concat in oracle, output will be NULL only when all operand is NULL or empty string.
As a best practise for concat with NULL, it is always advisory to use NVL function to replace NULL with Empty String ”.

SQL SERVER
In SQL Server NULL and Empty String are treated different.

sql_concat

As we can see in output projected, Empty Sring is not displayed as NULL.
It is just Empty.

Let see how Concat(+) behave in SQL Server, when we have Empty String or NULL as operand in
sql_concat-1jpg

Observations in SQL Server:
1. Concat with any NULL operand is always NULL and depend on Setting of “CONCAT_NULL_YIEDS_NULL”
2. Concat of all Empty String Operand is Empty String.
2. Concat with Empty String ” is NON Empty String operand.

With SQL Server, we have SET option “CONCAT_NULL_YIEDS_NULL” that influence concat with NULL behavior.

SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'SQL' + NULL;
GO

OUTPUT : SQL

So Concat with NULL or Empty String differ a lot in both database.
Hope information share was valuable, please share with others.

Posted in Oracle Internal, SQL Server Learning | Tagged , , , , , , | 2 Comments

#SQLSERVERLearning : CASE Statement Insight

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

#TGIF, had decided to publish at least one blog weekly and preferably on Friday!

Today will post about CASE statement in SQL server and how it differ with comparison in Oracle.
CASE is widely used for expression comparison with varying WHEN conditions and desired THEN for respective conditions.
IF nothing match we can use a default ELSE expression.

CASE help to compare different type of condition other then equality as compared to DECODE and get us desire output.
Whenever we compare any expression, it’s key factor to manage data type of operator for comparision.
Today will explore CASE expression with different data type and understand how it’s behave in Oracle and SQL server.

Lets start with below CASE statement,

SELECT CASE WHEN 1=1 THEN 1
WHEN '2' = 2 THEN '2'
WHEN GETDATE() = GETDATE() THEN GETDATE()
ELSE NULL
END

In above case statement, you will observe below thing

1. Data type of each expression in WHEN is different. (NUMBER – VARCHAR – DATETIME)
2. Return type for each expression is different.
3. It will match only for first WHEN expression, remaining WHEN expression and not reachable.

Now let see what the output for same.
Output :: 1900-01-02 00:00:00.000

Ideally at first thought, we would think output would be 1, as it match for first WHEN expression or some exception for data type mismatch due to different result expression.
But output is of DATE datatype and value is somewhat as of now irrelevant.

Now let route to Oracle Database and see how it behave for such different data type expression.

SELECT CASE WHEN 1=1 THEN 1
WHEN '2' = '2' THEN '2'
WHEN SYSDATE = SYSDATE THEN SYSDATE
ELSE NULL
END FROM DUAL;
Output :ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

In Oracle it is mandate for each result expression to be for same data type, implicit conversion is not impose.

Let understand, how SQL server behave with varying data type in CASE Expression.

1. Data type for Return expression is decided based on “Data Type Precedence”.
When we have expression that combine different data type, as in our case NUMBER , VARCHAR and DATE.It convert data type of lower precedence to higher precedence IFF valid.
For CASE example mentioned, expected return data type will be “DATE”

So in CASE statement examples, final output base on match was after implicit conversion of first match result expression (1) to DATE.
i.e. SELECT CAST(1 as DATETIME)
Output :: 1900-01-02 00:00:00.000

2.
Now let see what would be happen when second expression is match.
SELECT CASE WHEN 1!=1 THEN 1
WHEN '2' = 2 THEN '2'
WHEN GETDATE() = GETDATE() THEN GETDATE()
ELSE NULL
END
Output :: Conversion failed when converting date and/or time from character string.

Now we got an exception as we are trying to convert match result expression character data type to Date, which is not valid and hence not permitted.

Ideally it is always good practice to have similar datatype for each condition within any expression.
It will avoid any conversion failure at run-time.

Please share any feedback if any.!

Posted in SQL Server Learning | Tagged , , , , | Leave a comment

Change Date in Oracle using FIXED_DATE

In our previous project for testing purpose, we required to change Sysdate in database.
Changing date is useful for many functional testing to go back date and perform functional compliance operations.

Oracle provide FIXED_DATE, system level parameter for changing date.
Please note, these parameter is at System level, hence its impact is across session i.e. System level.

Lets suppose, fixed date is not use and we have current real-time date setup as below.
SELECT SYSDATE , CURRENT_DATE , SYSTIMESTAMP FROM DUAL;

SYSDATE CURRENT_DATE SYSTIMESTAMP
----------- ------------ ---------------------------------------
12-DEC-2016 12-DEC-2016 12-12-16 12:58:09.295000000 PM +05:30

We will change date to backdated one, for instance 05-Jan-2015.

ALTER SYSTEM SET FIXED_DATE='05-JAN-2015';

Using FIXED_DATE parameter, we had set reset Sysdate to 05-Jan-2015.

Now let re run above query to check Sysdate, Current Date and Systimestamp information.

SYSDATE CURRENT_DATE SYSTIMESTAMP
----------- ------------ ---------------------------------------
05-JAN-2015 12-DEC-2016 12-12-16 01:02:56.743000000 PM +05:30

Only SYSDATE change using FIXED_DATE parameter, CURRENT_DATE and SYSTIMESTAMP shows AS OF Date/Time information.
It it important to understand, it will impact only sysdate at database level.

Now let reset date to a constant date with time component.

ALTER SYSTEM SET FIXED_DATE='05-JAN-2015 10:12:11';

If NLS_DATE_FORMAT is not set properly, it would give below exceptions.

SQL Error: ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02078: invalid setting for ALTER SYSTEM FIXED_DATE
02097. 00000 - "parameter cannot be modified because specified value is invalid"

Reset NLS_DATE_FORMAT and let try again.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SYSTEM SET FIXED_DATE='05-JAN-2015 10:12:11';

Now it will successful and now time component is also FIXED.
Let recheck Different date function values.

SYSDATE CURRENT_DATE SYSTIMESTAMP
---------------------- ---------------------- ---------------------------------------
05-JAN-2015 10:12:11 12-DEC-2016 13:10:00 12-12-16 01:10:00.419000000 PM +05:30

Now we have SYSDATE with Time component as FIXED, it wont change!.
Time component will be FIXED, it wont changed!

CURRENT_DATE and SYSTIMESTAMP has no impact with FIXED_DATE Parameter.

When we are done with our testing, we need to reset FIXED_DATE to get actual real time date information.
We can reset using “NONE”

ALTER SYSTEM SET FIXED_DATE=NONE;

SYSDATE CURRENT_DATE SYSTIMESTAMP
---------------------- ---------------------- ---------------------------------------
12-DEC-2016 13:13:58 12-DEC-2016 13:13:58 12-12-16 01:13:58.636000000 PM +05:30

It reset to real time date informations.

Posted in Helper SQL | Tagged , , , | Leave a comment

Interesting Database SQL Concept Question, Give it a try??

#TGIF
Today’s will try to walk you through a Database concept questions and hopefully you will learn something new. (Obviously if you are not aware of it.!)

Suppose we have below scenarios,
In Database Session 1 :
–> we are creating a table
–> adding a row
–> Drop a table that does not exists

In Database session 2 :
–> Query newly created table in session 1 and check row count.

Database Session 1 :

DROP TABLE TAB;
CREATE TABLE TAB
AS
SELECT 1 COL1 FROM DUAL;
INSERT INTO TAB VALUES(2); --inserting a values into table.


DROP TABLE TABLE_DOES_NOT_EXITS; --Dropping a table that does not exists in database.

We are dropping a table which does not exists in Database. Hence Drop command would failed with below exception.
SQL Error: ORA-00942: table or view does not exist

Database Session 2 :
In Another session we are trying to query table TAB and check total number of rows.
select count(*) from TAB;
Please note we have not perform any commit in Database Session 1.

So the questions is When we Query Select count(*) from TAB; in Database Session 2 what will be row count.

Please update your answer within below Polls.

Will update my answer on same with a reason.

Thanks a lot for voting, please find Voting results as of today(12th Dec 2016).We can also View result in poll itself.
poll_result

Correct answer for SQL concept questions is “TWO”.

Let reiterate with steps in DB Session 1, to understand it better.

Step 1 :
DROP TABLE TAB;
Table does not exists, hence it would give exceptions.

Step 2 :
CREATE TABLE TAB
AS
SELECT 1 COL1 FROM DUAL;

We are using CTAS (Create table as ) for creating new table TAB with a row.
Create table is Auto Commit statement, hence table will have one row and it persist for each and every session for database.
So after these statement, every session in database will show record count as One.

Step 3 :
INSERT INTO TAB VALUES(2);

We are inserting a record in newly created table and no Commit statement is executed.
Hence newly inserted rows won’t be visible in any other session other then current one.

Step 4 :
DROP TABLE TABLE_DOES_NOT_EXITS;
These steps is very Tricky!

We all know it will failed as table we are trying to drop does not exists in DB.
But Statement we are trying to execute is an Data Definition Language (DDL – CREATE/ALTER/DROP/TRUNCATE).
For any DDL in oracle, We perform “Implicit Two Commit“. One before executing Statement and one after executing statement.
So though Statement had failed, but First Implicit commit before executing failed statement cause data to get commit within session.
Hence though we did not have any explicit commit in Database session 1, but first implicit commit of DDL cause data to Commit and visible for each and every session in database.

Key Learning :
DDL operate as Two phase commit statement, one before executing or parsing other after successful execution.

Hope it was interesting learning, please share it with others.!

Posted in Helper SQL, Oracle Internal | Tagged , , , , , | 4 Comments

Multiplication across rows and Analytical Functions.

Oracle or SQL Server provide Aggregation functions for MIN/MAX/COUNT/SUM, but suppose a scenario when we want multiplication of rows across a partition or complete table.
Database does not provide any buildin function to achieve same.

Suppose we have below expectations, we need multiplication of a column data within a partition column scope and increase as partition scope increase.
As Date increase multiplier scope also increase.
To understand it better, please check below expected output

ITEM DATE_TIME MULTIPLER EXPECTED_OUTPUT
----- --------- ---------- -----------------
ITEM1 14-NOV-16 1 1
ITEM1 21-NOV-16 2 2 --> 1*2
ITEM1 28-NOV-16 3 6 --> 1*2*3
ITEM1 05-DEC-16 4 24 --> 1*2*3*4
ITEM1 12-DEC-16 5 120 --> 1*2*3*4*5
ITEM1 19-DEC-16 6 720 --> 1*2*3*4*5*6
ITEM1 26-DEC-16 7 5040 --> 1*2*3*4*5*6*7
ITEM1 02-JAN-17 8 40320 --> 1*2*3*4*5*6*7*8

ITEM2 14-NOV-16 9 9
ITEM2 21-NOV-16 10 90
ITEM2 28-NOV-16 11 990
ITEM2 05-DEC-16 12 11880
ITEM2 12-DEC-16 13 154440
ITEM2 19-DEC-16 14 2162160
ITEM2 26-DEC-16 15 32432400
ITEM2 02-JAN-17 16 518918400

As Date increase for a Item, Multiplication scope also increase based on ascending date.
For E.g. for Item1 with date 02-JAN-2017, expected output should be multiplication of all rows data within Item1 scope.

Before moving towards solution to achieve same, lets explore some mathematical function Oracle or SQL Server provides.

1. Exponential function : EXP
It return e raised to Nth Power, i.e. “e^N”

SELECT 'Exponential Raised to Natural log e (2.71828183)' AS EXPLANATION , ROUND(EXP(2),2) FROM DUAL;


EXPLANATION ROUND(EXP(2),2)
----------------------------------------------------------------------
Exponential Raised to Natural log e (2.71828183) 7.39

2. Natural Log function : (Oracle :: LN // SQL SERVER : LOG)
It return natural log of input values.

SELECT 'Natural log with base as e (2.71828183) ' AS EXPLANATION , ROUND(LN(7.39),2) FROM DUAL;


EXPLANATION ROUND(LN(7.39),2)
---------------------------------------- ------------------
Natural log with base as e (2.71828183) 2

Log is exponential factor with base as e.
i.e. e^2 := 7.39
Log of 7.39 with base as e := 2.

Reason to explore above mathematical function is to get our multiplication factor using log and exp formula.
e^x * e^y := e^(x+y)
i.e. log(x*y) := log(x) + log(y) (natural log)

example ::
for 10*10 we will use logarithm and exponential as below.
log(10) + log (10) := 2.302 + 2.302 := 4.604
later exp(4.604) := (2.71828183)^4.604 := 100 approximate.

For more information, please refer below link.

Multiplying Numbers Using Log Tables

After a bit of mathematics, lets see database code to achieve multiplication of data.

SELECT A.*,
ROUND(EXP(SUM(LN(ABS(A.MULTIPLER))) OVER (PARTITION BY ITEMNAME ORDER BY DATE_TIME)),2) EXPECTED_OUTPUT
FROM MULTI_ANALYTICAL A;

Basically we are using SUM of LOG and later using EXP with rounding function.

Using SUM of LN and later applying EXP, our expected output is multiplication of data.
And its beauty increase with help of analytical function.

multi

To explore more on Analytical function check here. and here

Posted in Helper SQL, Oracle Internal, SQL Server Learning | Tagged , , , , , | 1 Comment

#SQLSERVERLearning : Identity Column Insight.

All my blogs with #SQLSERVERLearning are intended to cover all stuff identified as the difference between Oracle and SQL Server.
Hopefully, it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access #SQLSERVER Learning..

Identity column helps to have a column with auto increment counter based on SEED/StartWith and Increment value on each request.
Identity column was part of SQL Server since 2008 release and a genuine need was for oracle developer to have same.
Finally in oracle 12c release, Identity column and sequence as default were part of new Feature.

Ideally comparison of Identity column behaviour in oracle and SQL server can be debatable, as both have different implementation logic.
But for understanding point of views, lets walk through Identity column implementations and its behaviour on different actions.

Lets start with field work by understanding how identity works in SQL Server.

CREATE TABLE TAB_IDENTITY
(
EMP_ID INT IDENTITY(1,1), --Identity column that will start with 1 and always increment by 1.
EMP_NAME VARCHAR(1000),
DATE_JOIN DATE DEFAULT GETDATE()
);
check table propery having identity column.
SP_HELP 'TAB_IDENTITY';

sql_identity

Emp_id is identity column having Not Null constraint implicitly added by SQL Server.
By Specifying not NULL and Identity, we would need to always adhere to column value from identity itself, we can’t insert custom values.
Below insert with insert EMP_ID data as per identity properties.
Insert Into Tab_Identity(emp_name,Date_Join) Values('ENAME1', GETDATE()-4);

–> Custom Data in Identity column.
when we try to explicitly provide a value for emp_id, it would give an exception.

Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(2,'ENAME2', GETDATE()-3);
Error message ::
Cannot insert explicit value for identity column in table 'tab_identity' when IDENTITY_INSERT is set to OFF.

Now lets try to Set IDENTITY_INSERT ON;
set IDENTITY_INSERT identitydb.dbo.Tab_Identity ON;

It will allows insertion of custom identity values to be inserted.

Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(2,'ENAME2', GETDATE()-3);
Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(10,'ENAME2', GETDATE()-3);

By adding custom values greater then Current Seed, i.e by updating Emp_id from 2 to 10 we also updated identity SEED values.
SQL Server provide DBCC CHECKIDENT to check identity column property within a table.

DBCC CHECKIDENT ('TAB_IDENTITY');
Checking identity information: current identity value '10', current column value '10'.

–>Truncate impact on identity
let see what happen when we try to truncate table having identity column.

TRUNCATE TABLE TAB_IDENTITY;
DBCC CHECKIDENT ('TAB_IDENTITY');
Checking identity information: current identity value 'NULL', current column value 'NULL'.

Truncate cause reset of identity column and it will altogether start with initial seed.

–>Drop impact on identity
Reason to include was its behavious in oracle when objects still exists in Recycle bin.

DROP TABLE TAB_IDENTITY;
DBCC CHECKIDENT ('TAB_IDENTITY');
output::
Cannot find a table or object with the name "TAB_IDENTITY". Check the system catalog.

As table is drop, corresponding identity columns is also drop.

–>Number of identity column in a table
Any table in oracle or be in SQL server can have only one identity column.

If in case we want any other column to get increment based on some SEED and Increment factor, we can use Sequence as Default.
CREATE SEQUENCE SEQ_IDENTITY
START WITH 1
INCREMENT BY 1;

CREATE TABLE TAB_IDENTITY_SEQ
(
EMP_ID INT IDENTITY(1,1),
ROW_NO INT DEFAULT NEXT VALUE FOR SEQ_IDENTITY,
);

CONSTRAINT_TYPE CONSTRAINT_KEYS
--------------------------- -------------------------------
DEFAULT ON COLUMN ROW_NO (NEXT VALUE FOR [SEQ_IDENTITY])

With Oracle 12c, we can also have a custom sequence next val as default for column values.

–>Insert for a table having only Identity columns
Suppose a Scenario in which we have a table with only 1 column which is identity column.
With traditional insert syntax, we can’t insert values within such tables.

CREATE TABLE T_IDENTITY
(
EMP_ID INT IDENTITY(1,1)
);

INSERT T_IDENTITY DEFAULT VALUES; -- using default values we can cause identity value to get populated.
(1 ROW(S) AFFECTED)

DBCC CHECKIDENT ('T_IDENTITY');
CHECKING IDENTITY INFORMATION: CURRENT IDENTITY VALUE '1', CURRENT COLUMN VALUE '1'.

–>Change SEED value for a Identity Column
DBCC CHECKIDENT provide an option to change Seed value for identity column.
With identity column we can only change current SEED value but Increment factor once set can’t be change.

Lets take above table for current example.
DBCC CHECKIDENT ('T_IDENTITY', RESEED , -10000);
INSERT T_IDENTITY DEFAULT VALUES;
SELECT * FROM T_IDENTITY

EMP_ID
-----------
1
-9999

Posted in SQL Server Learning | Tagged , , , , | 1 Comment

Oracle 12c : Default values on NULL.

In Oracle, DEFAULT provide an option to have any default expression when column data is not specified during data manipulations.
During Table creation or using Alter we can provide DEFAULT value for column.

While data manipulation we can use “default” keyword to use default value specified as part of table structure.

But suppose a scenario , when we want a default value for all NULL data getting inserted.
Pre 12c,no option was provided to have any Default value for NULL Data.

let’s walk through sample code to understand it better,

CREATE TABLE T_DEFAULT_ON_NULL
(
EMP_ID NUMBER ,
EMP_NAME VARCHAR2(1000),
DATE_JOIN DATE
);
Table T_DEFAULT_ON_NULL can have emp_id as null and currently for handling NULL data we are using NVL function.
To handle NULL data we use NVL function to replace NULL data with our expected default "-1".

INSERT INTO T_DEFAULT_ON_NULL(EMP_ID, EMP_NAME, DATE_JOIN) VALUES (NVL(NULL,-1), 'NAME1',SYSDATE-10);
INSERT INTO T_DEFAULT_ON_NULL (EMP_ID, EMP_NAME, DATE_JOIN) VALUES (0,'NAME2',SYSDATE-9);
INSERT INTO T_DEFAULT_ON_NULL VALUES (1, 'NAME3',SYSDATE-8);
INSERT INTO T_DEFAULT_ON_NULL VALUES (2, 'NAME4',SYSDATE-7);
INSERT INTO T_DEFAULT_ON_NULL VALUES (3, 'NAME5',SYSDATE-6);

With 12c Default on NULL option, we will alter table or implement on table creation to use it.

ALTER TABLE T_DEFAULT_ON_NULL
MODIFY (EMP_ID NUMBER DEFAULT ON NULL -1);

we can explicitly insert any data as needed and whenever column data is null it will get overwrite with default value -1.

All below insert statement are legit.

INSERT INTO T_DEFAULT_ON_NULL VALUES (DEFAULT, 'NAME1',SYSDATE-10); --using default keyword
INSERT INTO T_DEFAULT_ON_NULL VALUES (NULL, 'NAME1',SYSDATE-10);--using NULL
INSERT INTO T_DEFAULT_ON_NULL(EMP_NAME, DATE_JOIN) VALUES ('NAME1',SYSDATE-10);

If you can check our table creation scripts, for column emp_id we did not specify NULL or NOT NULL constraint.
With Default on NULL, Oracle explicitly enforced NOT NULL constraint as after default on null we won’t have any Nullable data.

In 12c new columns are added are data dictionary to check DEFAULT_ON_NULL property.

SELECT COLUMN_NAME , NULLABLE , DEFAULT_ON_NULL , DEFAULT_LENGTH, DATA_DEFAULT
FROM USER_TAB_COLS
WHERE TABLE_NAME LIKE 'T_DEFAULT_ON_NULL';

default_on_null

Posted in Oracle 12c New Features | Tagged , , | Leave a comment

Aggregate Function , Distinct and NULL Values in Oracle.

While working on SQL Server with queries having aggregation got a warning message.
“Warning: Null value is eliminated by an aggregate or other SET operation.”

So thought let write up something on Aggregation and NULL Values for Tradition RDBMS for basic understanding.
Aggregation is key operations for any analytics or summarize information.
Our traditional RDBMS has NULL to mark any information Undefined or Unknown.

Both Aggregation and NULL are related in a way for different standard aggregation method oracle provides (MIN/MAX/COUNT/AVG/SUM).
Lets walk through a Query example and will discuss more on same.

WITH DATA1 AS
(
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 2 COL1 FROM DUAL
)
SELECT 'NON - DISTINCT' AS INFO, COUNT(*) "CNT_*", COUNT(1) CNT_ROW, COUNT(COL1) CNT_COLUMN , COUNT(DISTINCT COL1) CNT_DISTINCT,
COUNT(NVL2(COL1, NULL ,1)) CNT_NULL,
AVG(COL1) AVG, SUM(COL1) SUM, MIN(COL1) MIN , MAX(COL1) MAX FROM DATA1
UNION ALL
SELECT 'DISTINCT' AS INFO, COUNT(*), COUNT(DISTINCT 1) CNT_ROW, COUNT(COL1) CNT_COLUMN , COUNT(DISTINCT COL1) CNT_DISTINCT,
COUNT(NVL2(COL1, NULL ,1)) CNT_NULL,
AVG(DISTINCT COL1) AVG, SUM(DISTINCT COL1) SUM, MIN(DISTINCT COL1) MIN , MAX(DISTINCT COL1) MAX FROM DATA1
;

We have total 6 Rows, with 2 as NULL and 2 distinct values.
Let see output for different aggregation functions and understand it better.

Output ::
aggregate

Key Findings:
1. Count(*) and Count(1) (Or any constant expression like count(‘a’) count(23123)) are similar and are use to count total rows return by a SQL.
COUNT function without any table column as input expression are use to count total number of rows return by query.

2. Count (Column) and Count(Distinct Column)
Will Ignore NULL Values for counting.Output values will be based on count of all Non Null-able values within that column.

3. Count(Distinct *)
Distinct * is not allowed, it would give an Exception.
Distinct within aggregation only works for any constant or column expression.

4. Count(NULL) will always be 0.
Expression as NULL can’t be evaluated as it is always Not Defined and hence output for same would be always Zero.

WITH DATA1 AS
(
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 2 COL1 FROM DUAL
)
SELECT COUNT(NULL) FROM DATA1;
output :
COUNT(NULL)
-----------
0

5.COUNT(DISTINCT 1)
Distinct on any constant expression will always give output as 1, as we don’t have any scope for non distinct values.

6.AVG/SUM/MIN/MAX will ignore NULL values for any evaluations.
In Avg while consideration of denominator all NULL values are ignore.
So basically it is
AVG ( (SUM of all Non NULL Column data)/(Count of Column values) i.e. AVG(SUM(COL1)/COUNT(COL1))

7. AVG(DISTINCT COL1) and AVG(COL1)
When we add Distinct with column expression for AVG function, it turn out to be
AVG ( (SUM of all non null and distinct column data) / (distinct count) i.e. AVG(SUM(DISTINCT COL1)/COUNT(DISTINCT COL1))

Distinct is impose for both SUM and Count of denominator.

8. How to Count NULL for a column.
We can use below expression to count total number of nulls for a column.
COUNT(NVL2(COL1, NULL ,1))

Posted in Oracle Internal | Tagged , , , | Leave a comment

#SQLSERVERLearning : Exception on Invalid/Incorrect Hints?

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All findings or Observations with SQL Server for any Oracle guy can be accessed here.

Ideally, don’t encourage the use of Hints in any database, provided we dint have any other alternative.
Hint helps us to play with execution plan for testing by influencing Optimizer to use, provided query options (Join method, Access path, Index usage, pushing predicate….).

In today’s blog will discuss, what happen when we specify incorrect/invalid hints in Oracle and SQL server.
Does database give any exception or warning, that provided hints are incorrect and cannot be enforced or it simply ignores it.

So what exactly Hint is? Whether its an option for Oracle Optimizer to consider or an order to follow.

The hint is a Command.!
If Hint is Valid, Authentic as per SQL Compliance, the Optimizer is bound to follow it.
But there are many cases when Hint can be Invalid.

Will talk through an example, when we would try to apply Invalid Hint and check how Oracle and SQL Server behave.

Let’s start with Oracle, we will use standard Emp and Dept tables for demo.
Before starting with the demo will like to talk a bit about Hash Join.

Hash join is only use to join two tables based on Equality expression.It can’t evaluate >= or <= or any other non equality expression.
hence if we try to push hash join for below query it will be invalid.

SELECT /*+ USE_HASH(DEPT)*/EMP.*,DEPT.*
FROM EMP , DEPT
WHERE EMP.DEPTNO <= DEPT.DEPTNO;

We are trying to join based on Expression other than equality and hence it would require either Nested loop or Merge join to evaluate queries.

For above SQL, Oracle uses below Plan.
-----------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59 |
| 1 | MERGE JOIN | | 59 |
| 2 | SORT JOIN | | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 7 |
| 4 | INDEX FULL SCAN | PK_DEPT | 7 |
|* 5 | SORT JOIN | | 14 |
| 6 | TABLE ACCESS FULL | EMP | 14 |
-----------------------------------------------------------------

In oracle, when we try to Run above statement, it would run successfully without giving any warning or errors.
We would need to cross verify whether hints are applied or not by checking underlying plan.
For Checking whether there was any issue to apply mention hint, we would need to enable 10053 trace and check Dumping hints section to verify whether any error was reported.

Let see how SQL Server behaves for the same case.

SELECT * FROM HUMANRESOURCES.DEPARTMENT D , HUMANRESOURCES.EMPLOYEE E
WHERE D.MODIFIEDDATE <= E.MODIFIEDDATE OPTION (HASH JOIN);

In SQL Server, we apply hint using OPTION Clause.
When we try to run above statement, we will get an exception message from the SQL Server Engine.

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

hint

SQL Server provides a way of throwing back some error, when we try to provide any Invalid hints.
It won’t execute statement with some other valid joins methods, instead try to Force mention hint.

Posted in Oracle Internal, SQL Server Learning | Tagged , , , , | Leave a comment