Careful with SQL server Update

Careful with SQL server Update:
The Blog is sharing my experience with the update statement in SQL server. We need to careful when write a update statement in SQL server like a scenarios below.
Initial setup:

create table A
( c1 int,
c2 int
)

create table B
( c1 int,
c2 int
)

insert into A values (101,0)
insert into A values (102,0)
insert into A values (103,0)

insert into B values (101,7)
insert into B values (102,5)
insert into B values (103,4)
insert into B values (103,6)

Tables with value:

select * from A


select * from B




The field in c2 is having Zero for all the fields. We need to update the field c2 in table A from the table B. But we have multiple values in table B for the value 103.


Based on the above scenario, the update statement has to fail. We mostly use the Inner join method of updates in SQL server Like a below statement.

begin tran
update A
set A.c2=B.c2
from A,B
where A.c1=B.c1
The above statement is updating the records. The above method is taking care of the multiple records.





The below method is giving the error message due to multiple records.

begin tran
update A set A.c2
=(select B.c2 from B
where A.c1=B.c1
)
We need to careful when we write a update statements.

Read More

Character of DUAL

Character of DUAL:
DUAL is owned by SYS. SYS owns the data dictionary, therefore DUAL is part of the data dictionary. You are not to modify the data dictionary via SQL ever.
Dual table and its purpose:
Dual is just a convenience table. You don't need to use it, you can use anything you want. The advantage to dual is the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan.
The Dual table structure, field and record details are below.
SQL> desc dual;
Name Null? Type
-------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL> select * from dual;
D
-
X
SQL> select count(*) from dual;
COUNT(*)
----------
1DUAL is owned by SYS:

The below scripts connect to DB as SYSTEM user and try to modify the DUAL table. The insert statement is not able to modify the table which is owned by SYS user.

SQL> conn system/password@oraprc
Connected.

SQL> INSERT INTO DUAL VALUES ('X');
INSERT INTO DUAL VALUES ('X')
*
ERROR at line 1:
ORA-01031: insufficient privileges
We change the connection to SYS user and try the same and it is able to insert the data.
SQL> conn sys/password@oraprc as sysdba
Connected.

SQL> INSERT INTO DUAL VALUES ('X');

1 row created.

SQL> select count(*) from dual;
COUNT(*)
----------
2
Special one row:
The definition has mentioned that DUAL is special one row, one column table. But we can insert the records in that table. So the count got changed in that.

SQL> INSERT INTO DUAL VALUES ('X');
1 row created.

SQL> select count(*) from dual;
COUNT(*)
---------
3

SQL> select * from dual;
D
-
X
The reason is that, the optimizer understands dual is a magic, special 1 row table. It stopped on the select * because there is to be one row in there. It’s just the way it works.

Delete operation on DUAL:
SQL> select count(*) from dual;
COUNT(*)
----------
3
SQL> delete from dual;
1 row deleted.

SQL> select count(*) from dual;
COUNT(*)
----------
2

SQL> delete from dual;
1 row deleted.

SQL> select count(*) from dual;
COUNT(*)
----------
1
The delete statement is deleting single record each time. The reason is that, the DUAL is special one row table.

Checking with FUCTION:

We create the function which return the value “1” and check the same function with DUAL and user created table.

SQL> select count(*) from dual;

COUNT(*)
----------
2

SQL> create or replace function foo return number
2 as
3 x number;
4 begin
5 x:=1;
6 return 1;
7 end;
8 /
Function created.

SQL> create table emp
2 ( num int);

Table created.

SQL> insert into emp values(1);

1 row created.

SQL> insert into emp values(2);

1 row created.

SQL> insert into emp values(3);

1 row created.

SQL> insert into emp values(4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

NUM
----------
1
2
3
4
SQL> select foo from dual;

FOO
----------
1
SQL> select foo from emp;
FOO
----------
1
1
1
1
The EMP table is having the 4 records and it is returning the 4 records when do the select on that table. We have checked the same thing with the DUAL table. The count of the DUAL is 2 and it is returning one record.

Read More

Autotrace from SYS - Some things appear to work but don't really

Autotrace from SYS - Some things appear to work but don't really

We use autotrace to get the Execution Plan and Statistics. It appear to work but don't really from SYS user. We see that.

SQL> create table t ( num number(2), name varchar2(10));

Table created.



SQL> insert into t values(1,'A');

1 row created.

SQL> insert into t values(2,'A');

1 row created.

SQL> select * from t;

NUM NAME
---------- ----------
1 A
2 A



SQL> set autotrace on;
SQL> select * from t;

NUM NAME
---------- ----------
1 A
2 A
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
We see the same thing from the SYS user.

SQL> conn sys/password@oraprc as sysdba
Connected.
SQL> set autotrace on;
SQL> select * from system.t;

NUM NAME
---------- ----------
1 A
2 A


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SYSDBA, SYSOPER, "internal" and sys in general shouldn't be used for anything other then admin.

Read More

CHAR Vs VARCHAR

CHAR Vs VARCHAR:

We see the information about the CHAR and VARCHAR in this Blog.

Create table t ( X varchar2(30) , Y char(30));
Insert into t values ('a','a');
The above table is having the two fields X & Y and corresponding data types are varchar2 and char. The CHAR is nothing more than a VARCHAR2 that is blank padded out to the maximum length. That is difference between the column X and Y.
The field X consumes 3 bytes – ( NULL indicator, leading byte length , 1 byte for ‘a’).

The field Y consumes 32 bytes–(NULL indicator, leading byte length, 30 byte for ‘a ’).

Need to consider the below points when we use the CHAR data type:

1. The a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments.

2. Another important reason to avoid CHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. The Below scripts proves that.

SQL@ORA9i> create table t
2 ( char_column char(20),
3 varchar2_column varchar2(20)
4 );

Table created.

SQL@ORA9i> insert into t values ( 'Hello World', 'Hello World' );

1 row created.

SQL@ORA9i> select * from t;

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

SQL@ORA9i> select * from t where char_column = 'Hello World';

CHAR_COLUMN VARCHAR2_COLUMN
------------ --------------------
Hello World Hello World

SQL@ORA9i> select * from t where varchar2_column = 'Hello World';

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

The above result looks like identical but, in fact, some implicit conversion has taken place and the CHAR(11) literal ‘Hello World’ has been promoted to a CHAR(20) and blank padded when compared to the CHAR column. The reason is, ‘Hello World ’ is not the same as ‘Hello World’ without the trailing spaces. We can confirm that these two strings are different.

SQL@ORA9i> select * from t where char_column = varchar2_column;
no rows selected

They are not equal to each other. We would have to either blank pad out the VARCHAR2_COLUMN to be 20 bytes in length or trim the trailing blanks from the CHAR_COLUMN, as follows:

SQL@ORA9i> select * from t where trim(char_column) = varchar2_column;

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World

SQL@ORA9i> select * from t where char_column = rpad( varchar2_column, 20 );

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
The problem arises with applications that use variable length strings when they bind inputs, with the resulting “no data found”


SQL@ORA9i> variable varchar2_bv varchar2(20)
SQL@ORA9i> exec :varchar2_bv := 'Hello World';

PL/SQL procedure successfully completed.

SQL@ORA9i> select * from t where char_column = :varchar2_bv;

no rows selected

SQL@ORA9i> select * from t where varchar2_column = :varchar2_bv;

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
The above search for VARCHAR2 string worked but not for CHAR. The VARCHAR2 bind variable will not be promoted to a CHAR(20) in the same way as a character string literal. At this point, many programmers form the opinion that “bind variables don’t work; we have to use literals.” That would be a very bad decision indeed.

The solution is to bind using a CHAR type:

SQL@ORA9i> variable char_bv char(20)
SQL@ORA9i> exec :char_bv := 'Hello World';

PL/SQL procedure successfully completed.

SQL@ORA9i> select * from t where char_column = :char_bv;

CHAR_COLUMN VARCHAR2_COLUMN
-------------------- -------------------
Hello World Hello World

SQL@ORA9i> select * from t where varchar2_column = :char_bv;

no rows selected
We will be running into this issue constantly if we mix and match CHAR and VARCHAR.

Read More

Performance Improvement – Index scan with NULL condition

Performance Improvement – Index scan with NULL condition

What will be our first step when we start tuning the queries?

The first step is creating the index. But simply creating the index does not improve the performance in all scenarios.

The below scripts are proves that.

SQL> create table t
2 as
3 select object_name name, a.*
4 from all_objects a;

Table created.

SQL> alter table t modify name null;

Table altered.

SQL> create index t_idx on t(name);

Index created.

SQL> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

SQL> variable b1 varchar2(30)
SQL> exec :b1 := 'T'

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> delete from t
2 where name = :b1 or name is null;

2 rows deleted.


Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=49 Card=2 Bytes=48)
1 0 DELETE OF 'T'
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=49 Card=2 Bytes=48)



The NAME field is having the index but still it is going for the FULL table scan. the index (t_idx) is not getting used here.

The reason is because NAME is nullable, the index on only on name and entirely null keys are NOT entered into b*tree indexes.

SQL> rollback;

Rollback complete.

SQL> drop index t_idx;

Index dropped.

SQL> create index t_idx on t(name,0);

Index created.

SQL> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

SQL> delete from t
2 where name = :b1 or name is null
3 ;

2 rows deleted.


Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=26 Card=2 Bytes=48)
1 0 DELETE OF 'T'
2 1 INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=26 Card=
2 Bytes=48)



The above method is going for the index scan and avoids the table scan.

Read More

Output clause

Output clause:

OUTPUT clause has accesses to inserted and deleted tables just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.


Let us understand OUTPUT clause using example.

Initial setup:

create table #t
(id int,
p_name char(10)
)

insert into #t values ( 1,'AAAAA')
insert into #t values ( 2,'BBBBB')
insert into #t values ( 3,'CCCCC')
insert into #t values ( 4,'DDDDD')
insert into #t values ( 5,'EEEEE')

select * from #t



Scneario1:

Archive the records into #t_arc table when records get deleted in #t table.

Normal solution is that, insert into #t_arc table before delete the records in the #t table based on the conditions.
create table #t_arc
(id int,
p_name char(10)
)

insert into #t_arc
select * from
where id =3

delete from #t
where id =3
The output clause avoids the execution of the two statements.

delete from #t
output deleted.* into #t_arc
where id =3


One of the very good advantages is eliminating the data access from table for each time. The DELETE … OUTPUT statement access the data once and do the operations.

Scneario2:
We see another example when we insert the data into table.
create table #t_audit
(id int,
load_time datetime
)

The below statement loads the audit table when loads the data into #t table.

insert into #t
output inserted.id,getdate() into #t_audit
values ( 6,'FFFFF')

Read More

Performance Improvement - Part 3: Correlated Update tuning

Performance Improvement - Part 3: Correlated Update tuning:

We have seen the small difference in correlated update statement execution in the previous Blog. Please refer below link if need to check it.

http://karthikeyanbaskaran.blogspot.com/2010/06/difference-in-correlated-update.html

The scenario is, need to update the “Address” field in table”Main” from the “Sub” table using the “id” field. The -1 needs to update in all other columns. The below update method solve it in single statement.

update main mset address = nvl((select address from sub s where m.id=s.id),-1);
Correlated update statement process for all the rows in the table “MAIN”. We see how it works when we load the lot records in the table “MAIN” and performance improvement method.


SQL> insert into main
2 select * from main;
............
.......
....
SQL> insert into main
2 select * from main;
Now the table “Main” is having the 1048576 records and table “Sub” is having the 3 records.

SQL> select count(*) from main;

COUNT(*)
---------
1048576

SQL> select count(*) from sub;

COUNT(*)
----------
3
The number of distinct address data is same now and we start our analysis process.

SQL> select address,count(*) from main
2 group by address;

ADDRESS COUNT(*)
---------- ----------
NNNNNNN 1048576
The below update statement works very well for our scenario. But every time it updates for the 1048576 records.

All the time it updates the 1048576 records, even if there are less difference needs to update it in the table “Main” from “Sub”.

SQL> set timing on
SQL> set autotrace traceonly
SQL> update main m
2 set address = nvl((select address from sub s where m.id=s.id),-1);

1048576 rows updated.

Elapsed: 00:01:44.04
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'MAIN'
2 1 TABLE ACCESS (FULL) OF 'MAIN'
3 1 TABLE ACCESS (FULL) OF 'SUB'

Statistics
----------------------------------------------------------
805 recursive calls
1073721 db block gets
2659 consistent gets
2124 physical reads
252412780 redo size
618 bytes sent via SQL*Net to client
582 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1048576 rows processed

The statement took 01:44 minutes to complete and it generated the 252412780 bytes. We roll back the results.


SQL> rollback;

Rollback complete.

Elapsed: 00:02:26.09
Performance improved script:

The WHERE condition has added to update statement and it updates the records which is difference comparing with Table “Main” and “Sub”.

SQL> update main m
2 set address = nvl((select address from sub s where m.id=s.id),-1)
3 where address is null
4 or address <> nvl((select address from sub s where m.id=s.id),-1);

1048576 rows updated.

Elapsed: 00:01:42.05

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'MAIN'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'MAIN'
4 2 TABLE ACCESS (FULL) OF 'SUB'
5 1 TABLE ACCESS (FULL) OF 'SUB'

Statistics
----------------------------------------------------------
536 recursive calls
1073630 db block gets
2611 consistent gets
1963 physical reads
252411468 redo size
627 bytes sent via SQL*Net to client
672 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1048576 rows processed

The statement took 01:42 minutes to complete and it generated the 252411468 bytes. There is no big difference when it updates all the records.

SQL> select address,count(*) from main
2 group by address;

ADDRESS COUNT(*)
---------- ----------
-1 524288
A 262144
C 262144

Elapsed: 00:00:01.03
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
We change the some set of records to check the execution time.

SQL> update sub set address='M' where id =4;

1 row updated.

Elapsed: 00:00:00.02
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> set autotrace traceonly
SQL>
SQL> update main m
2 set address = nvl((select address from sub s where m.id=s.id),-1);
1048576 rows updated.

Elapsed: 00:01:51.00

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'MAIN'
2 1 TABLE ACCESS (FULL) OF 'MAIN'
3 1 TABLE ACCESS (FULL) OF 'SUB'




Statistics
----------------------------------------------------------
624 recursive calls
1072662 db block gets
4953 consistent gets
2313 physical reads
248380252 redo size
629 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1048576 rows processed


The statement took 01:51minutes to complete and it generated the 248380252 bytes.


SQL> set autotrace off;
SQL> select address,count(*) from main
2 group by address;

ADDRESS COUNT(*)
---------- ----------
-1 524288
A 262144
M 262144

Elapsed: 00:00:00.09

Rollback the result.

SQL> rollback;

Rollback complete.

Elapsed: 00:04:40.08
SQL> select address,count(*) from main
2 group by address;

ADDRESS COUNT(*)
---------- ----------
-1 524288
A 262144
C 262144

Performance improved script:

SQL> set autotrace traceonly

SQL> update main m
2 set address = nvl((select address from sub s where m.id=s.id),-1)
3 where address is null
4 or address <> nvl((select address from sub s where m.id=s.id),-1);

262144 rows updated.

Elapsed: 00:00:45.04

Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'MAIN'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'MAIN'
4 2 TABLE ACCESS (FULL) OF 'SUB'
5 1 TABLE ACCESS (FULL) OF 'SUB'




Statistics
----------------------------------------------------------
176 recursive calls
268198 db block gets
2421 consistent gets
2054 physical reads
62104336 redo size
628 bytes sent via SQL*Net to client
672 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
262144 rows processed

The statement took 00:45.04 seconds to complete and it generated the 62104336 bytes.

SQL> set autotrace off;
SQL> select 248380252-62104336 redo_diff from dual;

REDO_DIFF
----------
186275916
The second update statement is avoiding the 186275916 bytes of the redo. So it is saving space in the UNDO and space in archive folders.
This method gives the good performance if it updates some part records in table.

Read More