Performance Improvement – Index scan with NULL condition

Performance Improvement – Index scan with NULL conditionWhat 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 t2 as3 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...

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...

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...

Read More