How to extract Month, Year value from date using Extract Function in Oracle

Extract Function in Oracle

The extract function extracts a value from a date. You can only extract YEAR, MONTH, and DAY from a DATE



Syntax:

  EXTRACT (
{ YEAR
MONTH
DAY
HOUR
MINUTE
SECOND }
{ TIMEZONE_HOUR
TIMEZONE_MINUTE }
{ TIMEZONE_REGION
TIMEZONE_ABBR }
FROM { date_value
interval_value } )


• Works in - Oracle 11g, Oracle 10g, Oracle 9i


Sample:



select extract(YEAR FROM sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)

------------------------

2013

1 row selected.





select extract(MONTH FROM sysdate) from dual;


EXTRACT(MONTHFROMSYSDATE)

-------------------------

5

1 row selected.



select extract(DAY FROM sysdate) from dual;



EXTRACT(DAYFROMSYSDATE)

-----------------------

14

1 row selected.

Read More

Removing Leading Zeros

Removing Leading Zeros:

Sample data:

SQL> SELECT '0000100' AS Num FROM dual;

NUM
-------
0000100

Option 1:

SQL> SELECT To_Number('0000100') AS Num FROM dual;

       NUM
----------
       100

Option 2:

SQL> SELECT LTrim('0000100','0') AS Num FROM dual;

NUM
---
100

Read More

The easy way to Get table and index DDL script

The easy way to Get table and index DDL script:

The dbms_metadata utility helps to display DDL directly from the data dictionary. We see the small example.
Initial setup:SQL> conn scott/tiger;
Connected.
SQL> create table GET_TAB_SCRIPT
2 (
3 c1 varchar2(10),
4 c2 number
5 );

Table created.

SQL> insert into get_tab_script values ('A',10);

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_1 on GET_TAB_SCRIPT (c1);

Index created.
To get table script:

SQL> select dbms_metadata.get_ddl('TABLE','GET_TAB_SCRIPT') from dual;
DBMS_METADATA.GET_DDL('TABLE','GET_TAB_SCRIPT')
------------------------------------------------

CREATE TABLE "SCOTT"."GET_TAB_SCRIPT"
( "C1" VARCHAR2(10),
"C2" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
To get index script:

SQL> select dbms_metadata.get_ddl('INDEX','IDX_1') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX_1')
----------------------------------------

CREATE INDEX "SCOTT"."IDX_1" ON "SCOTT"."GET_TAB_SCRIPT" ("C1")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

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

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

Performance Improvement - Part 2: Hard parse is not bad

Performance Improvement - Part 2: Hard parse is not bad

In previous post, we have seen the soft parsing is doing less operation comparing with hard parsing.

Please have a look on below post before read this one.
http://karthikeyanbaskaran.blogspot.com/2010/07/performance-improvement-part-1-parsing.html

The Hard parse includes the parse, optimize, generation of the plan for the query and soft parse skips optimize and generation of the plan. So we decided that hard parse is not good for application performance.

Some times soft parsing is having performance issue. The below scripts proves that.
SQL@ORA10G> create table t
2 as
3 select case when rownum = 1
4 then 1 else 99 end id, a.*
5 from all_objects a;

Table created.

SQL@ORA10G> create index t_idx on t(id);

Index created.


SQL@ORA10G> analyze table t compute statistics;

Table analyzed.


The WHERE ID=1 condition will return one record and WHERE ID=99 will return all of the rest (about 40,000 records). Also, the optimizer is very aware of this fact, we can definitely see different plans for different inputs, as we are passing the literals in the WHERE clause.

SQL@ORA10G> set autotrace traceonly explain
SQL@ORA10G> select * from t where id = 1;

Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=141)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=141)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)

SQL@ORA10G> select * from t where id = 99;

Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=137 Card=34323 Bytes=4839543)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=137 Card=34323 Bytes=4839543)





From Oracle9i Database Release 1 through Oracle Database 10g Release 2, Oracle Database will wait until the cursor is opened to do the actual optimization of the query—it will wait for the bind variable value to be supplied by the application before figuring out the right way to optimize the query. This is called bind variable peeking, when the optimizer first looks at the bind values and then optimizes the query.


In this case, however, depending on which inputs are used to first run the query, the database will either choose a full scan or an index range scan plus table access by index rowid. And in Oracle9i Database Release 1 through Oracle Database 10g Release 2, that is the plan that will be used to execute the SELECT * FROM t WHERE ID = :ID query, regardless of the subsequent bind values, until the query is hard-parsed and optimized again.

SQL@ORA10G> exec :id := 99

PL/SQL procedure successfully completed.

SQL@ORA10G> select * from t where id = :id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=137 Card=20391 Bytes=1814799)

1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=137 Card=20391 Bytes=1814799)




So we started off with ID=99 as the bind, and the optimizer chose a full scan. Therefore, regardless of the bind value, the database will execute a full scan from now on. For example:
SQL@ORA10G> exec :id :=1

PL/SQL procedure successfully completed.

SQL@ORA10G> select * from t where id = :id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=137 Card=20391 Bytes=1814799)

1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=137 Card=20391 Bytes=1814799)



The above result demonstrates that it is not going for the index range scan or table access by index rowid. In this case, the logical I/Os (Consistent gets) are more because of the FULL table scan instead of the index scan.

The result proves the “poorly performing query”.

Read More

Performance Improvement - Part 1: Parsing

Performance Improvement - Part 1: Parsing

In this article, we see how to improve the performance of the SQL queries. Before that we should know about the parsing and types of that.

Parsing:

This is the first step in the processing of any statement in Oracle. Parsing is the process of breaking the submitted statement down into its component parts. Determining what type of statement it is (whether Query, DML, DDL) and performing various checks on it.
The parsing process performs two main functions:

· Syntax Check
· Semantic Analysis

Syntax Check:

The syntax check performs the below operation.

Is the statement a valid one?
Does it make sense given the SQL grammar documented in the SQL Reference Manual? Does it follow all of the rules for SQL?

Semantic Analysis:
The semantic going beyond the syntax and it is performing the below operation.

Is the statement valid in light of the objects in the database (do the tables and columns referenced exist)?
Do you have access to the objects?
Are the proper privileges in place?
Are there ambiguities in the statement?
Soft parsing and hard parsing:
The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. We may be in luck here If it has, we can skip the next two steps in the process, that is

1 .Optimization
2. Row source generation.

If we can skip these next two steps in the process, we have done what is known as a Soft Parse.


If we cannot, if we must do all of the steps, we are performing what is known as a Hard Parse. Hard parse includes the parse, optimize, generation of the plan for the query.
Example for Hard Parse:
The below scripts demonstrate the hard parsing.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> create table t
(
id number(2),
Des varchar2(20)
);

Table created.

insert into t values(1,'AA');
insert into t values(2,'BB');
insert into t values(3,'CC');
insert into t values(4,'DD');
insert into t values(5,'EE');
insert into t values(6,'FF');


SQL> select * from t;

ID DES
--- --------
1 AA
2 BB
3 CC
4 DD
5 EE
6 FF

6 rows selected.


SQL> select SQl_TEXT,executions,hash_value,child_latch,child_address from v$sql where sql_text like 'select * from t where id %';

no rows selected

SQL> select * from t where id = 2;

ID DES
--- --------
2 BB

SQL> select * from t where id = 2;

ID DES
--- --------
2 BB

SQL> select * from t where id = 3;

ID DES
--- --------
3 CC

SQL> select SQl_TEXT,executions,hash_value,child_latch,child_address from v$sql where sql_text like 'select * from t where id %';




The above result is showing that, it is creating the different hash value for “where id = 2” and “where id =3 “. That means it is going for the hard parse for the each time when we change the condition.

Example for Soft parse:

The Execution column is showing 2 for the “where id =2 “. First time it has gone for the hard parse and second time it is gone for the soft parse.

How to make soft parse for “where id =2 “and “where id =3 “. We use the bind variable and it solves the issue.


SQL> variable id number
SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select * from t where id = :id;

ID DES
--- --------
1 AA

SQL> exec :id :=2 ;

PL/SQL procedure successfully completed.

SQL> select * from t where id = :id;

ID DES
--- ------
2 BB

SQL> exec :id :=2 ;

PL/SQL procedure successfully completed.

SQL> select * from t where id = :id;

ID DES
--- -------
2 BB


SQL> select SQl_TEXT,executions,hash_value,child_latch,child_address from v$sql where sql_text like 'select * from t where id %';

It is not creating the different plan for each time when we use the bind variable. The hash value vale has created for the script “where id = :id “ and the value of the variable (:id) is changing each time. Refer the “Execution field, It is showing 3 for the different values.
Hard parsing is very CPU intensive. If our applications we want a very high performance then our queries to be Soft Parsed (to be able to skip the optimize/generate phases)

If we have to Hard Parse a large percentage of our queries, our system will function slowly and in some cases not at all.

Now we have a strong proof for the soft parsing and that improve the performance. But soft parsing is not always good and hard parsing is not always bad. We see that in the next Blog.

Read More

Reusable scripts - Part1

Find the letter occurrences in the statement:

The below query is finding the number of occurrence letter “a” in the string.

select length('Find a letter in a string') - length(replace('Find a letter in a string', 'a','')) as "Number of occurrence" from dual ;

Number of occurrence
--------------------
2


Remove the alphabetic letters in the given string:

declare
l_start number;
len number;
i number;
p_str varchar2(1000) := 'aa123yksjds45';
p_str1 varchar2(1000);
res varchar2(1000);
begin
i:=0;
len :=length(p_str);
WHILE i <= len
loop
len := ASCII(substr(p_str,i,1));
IF len > 47 AND len < 58 then
i:= i+1;
else
p_str := replace(p_str,substr(p_str,i,1),'');
END IF;
end loop;
dbms_output.put_line(p_str);
end;

Result:

12345

Divide the given comma separated words:

declare v_comma_position number;
v_len number;
p_comma_val varchar2(1000) := 'value1,value2,value3,value4,value5'; p_comma_tmp varchar2(1000);
v_result varchar2(1000);
begin p_comma_val := p_comma_val ',';
v_len :=length(p_comma_val);
WHILE v_len >= 0
loop
v_comma_position := instr(p_comma_val,',')+1;
p_comma_tmp := '';v_result := '';
p_comma_tmp := p_comma_val;
p_comma_val:='';
v_result := substr(p_comma_tmp,1,v_comma_position-2);
p_comma_val := substr(p_comma_tmp,v_comma_position,v_len);
v_len := v_len - v_comma_position;
dbms_output.put_line( v_result);
end loop;
end;

Result:

value1
value2
value3
value4
value5

Read More

Difference in Correlated Update

Difference in Correlated Update :

I came across the Correlated update statement with below format. It updates the address filed in the “Main” table and takes it from the “sub” table. The NVL function has given outside the sub query.

Outer NVL Query:

update main m
set address = nvl((select address from sub s where m.id=s.id),-1);

We can give the NVL function with the sub query (specifically for that column). Then what is the difference between “Outer NVL Query” and “Inner NVL Query”.
Inner NVL Query:
update main m
set address = (select nvl(address,-1) from sub s where m.id=s.id)
Initial setup:
This step creates the tables and populates it.
create table main ( id number, Address varchar2(10));

insert into main values (1,'NNNNNNN');
insert into main values (2,'NNNNNNN');
insert into main values (3,'NNNNNNN');
insert into main values (4,'NNNNNNN');

create table sub ( id number, Address varchar2(10));

insert into sub values (1,'A');
insert into sub values (2,null);
insert into sub values (4,'C');

select * from main;

select * from sub

Inner NVL Query:

update main m
set address = (select nvl(address,-1) from sub s where m.id=s.id);
It is applying the NVL function if NULL value comes from the “SUB” table. It is putting the NULL value in the “Main” table if it does not find the match in the “Sub” table.

We think that the outer NVL query works in the same way. But there is some difference.
Outer NVL Query:
update main m
set address = nvl((select address from sub s where m.id=s.id),-1)
We can see the difference in below result. It is applying the NVL function in the both the situation.

This is small Difference in the Correlated Update.

Read More

Get rows just I want

The Oracle latest version optimizers are working too smart. The comparison with oracle 9i and higher viersions.
I have a Query with an ORDER BY clause that returns 10,000 rows, but I'm restricting it to 500 rows with ROWNUM. Does the Oracle database store 10,000 rows in memory or open with 500 rows? For example:
SELECT empno, empnmFROM ( SELECT empno, empnm FROM employee ORDER BY empno )WHERE ROWNUM < 500;

Generally, the Oracle database does not store an entire result "in memory." Instead, the database tries to answer the query and return the first row before it gets the last row.
If the database attempted to store the result set in memory, we would never be able to ask a query like give more that one billion rows. Oracle generally answers the query on the fly whenever possible. In the above case—if EMPNO is indexed and EMPNO is NOT NULL—the optimizer will read the index to process the ORDER BY and stop after reading 500 rows.

Oracle 11g Result:

SQL_ORA11G> create table emp as select object_id empno, object_name ename from all_objects;
Table created.
SQL_ORA11G> select count(*) from emp;
COUNT(*)
----------
68503

SQL_ORA11G> create index emp_idx on emp(empno);
Index created.

SQL_ORA11G> analyze table emp compute statistics;
Table analyzed.

SQL_ORA11G> analyze index emp_idx compute statistics;
Index analyzed.

SQL_ORA11G> set autotrace traceonly
SQL_ORA11G> select empno, ename from ( select empno, ename from emp order by empno ) where rownum < 500;
Execution Plan
----------------------------------------------------------
Plan hash value: 2549860052
----------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------
0 SELECT STATEMENT 499 14970 6 (0) 00:00:01
* 1 COUNT STOPKEY
2 VIEW 499 14970 6 (0) 00:00:01
3 TABLE ACCESS BY INDEX ROWID EMP 68503 1873K 6 (0) 00:00:01
4 INDEX FULL SCAN EMP_IDX 499 3 (0) 00:00:01
---------------------------------------------------------
Oracle 9i Result:

The same script is working differently in the Oracle 9i. it is taking all the records and it is not stopping after it reaches 500. Oracle 9i reads all the data and filtering the 500 records.
SQL> create table emp as select object_id empno, object_name ename from all_objects;
Table created.

SQL> select count(*) from emp;
COUNT(*)
----------
29125

SQL> create index emp_idx on emp(empno);
Index created.

SQL> analyze table emp compute statistics;
Table analyzed.

SQL> analyze index emp_idx compute statistics;
Index analyzed.

SQL> set autotrace traceonly
SQL> select empno, ename from ( select empno, ename from emp order by empno ) where rownum < 500

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=194 Card=499 Bytes=873750) 1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=194 Card=29125 Bytes=873750)
3 2 SORT (ORDER BY STOPKEY) (Cost=194 Card=29125 Bytes=786375)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=29125 Bytes=786375)

We try to retrieve the data from table with out order by. So it will not use any index and it goes for the FULL table scan.

Oracle 9i:
In Oracle 9i, it reads the 29125 records and giving the 499 records to outside.
SQL> select empno, ename from emp where rownum < 500;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=499 Bytes=786375)
1 0 COUNT (STOPKEY) 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=29125 Bytes=786375)

Oracle 11g:
In Oracle 11g, it is going for the FULL table scan and it reads only 499 records but the actual count of the table is 68503. it is giving rows just I want.

SQL_ORA11G> select empno, ename from emp where rownum < 500;

Execution Plan
----------------------------------------------------
Plan hash value: 1973284518
----------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------
0 SELECT STATEMENT 499 13972 3 (0) 00:00:01
* 1 COUNT STOPKEY
2 TABLE ACCESS FULL EMP 499 13972 3 (0) 00:00:01
----------------------------------------------------

Read More

Count with View in Oracle 9i vs 11g

Oracle 9i Result:
SQL> drop table t;
Table dropped.

SQL> create table t (c number);
Table created.

SQL> create view v1
as select count(1) count from t;
View created.

SQL> create view v2
as select count(*) count from t;
View created.

SQL> select object_name, object_type, status from user_objects
where (object_name in ('V1','V2'))
and object_type = 'VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------- --------
V2 VIEW VALID
V1 VIEW VALID

Add another column in the table.
SQL> alter table t add ( c2 number);
Table altered.

SQL> select object_name, object_type, status
2 from user_objects
3 where (object_name in ('V1','V2'))
4 and object_type = 'VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------ -------------
V2 VIEW INVALID
V1 VIEW INVALID

Both the views are Invalid in Oracle 9i environment.
Oracle 11g Result:
SQL> drop table t;
Table dropped.

SQL> create table t (c number);
Table created.

SQL> create view v1
2 as
3 select count(1) count from t;
View created.

SQL> create view v2
2 as
3 select count(*) count from t;
View created.

SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- -------------- -------------
V1 VIEW VALID
V2 VIEW VALID

Add another column in the table.

SQL> alter table t add(c2 number);
Table altered.

SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
------------ --------------- -------------
V1 VIEW INVALID
V2 VIEW VALID


The V1 became Invalid. but V2 is valid

Read More

Array Size Effects

The array size is the number of rows fetched (or sent, in the case of inserts, updates, and deletes) by the server at a time. It can have a dramatic effect on performance.
SQL> drop table t;

Table dropped.

SQL> create table t as select * from all_objects;

Table created.

SQL> select count(*) from t;
COUNT (*)
----------
29120
SQL> set autotrace traceonly statistics;
SQL> set arraysize 2
SQL> select * from t;

29120 rows selected.

Statistics
----------------------------------------------------------
14779 consistent gets

Note how one half of 29120 (rows fetched) is very close to 14779 , the number of consistent gets. Every row we fetched from the server actually caused it to send two rows back. So, for every two rows of data, we needed to do a logical I/O to get the data. Oracle got a block, took two rows from it, and sent it to SQL*Plus. Then SQL*Plus asked for the next two rows, and Oracle got thatblock again or got the next block, if we had already fetched the data, and returned the next two rows, and so on.
Next, let’s increase the array size:
SQL> set arraysize 5
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
6152 consistent gets

Now, 29120 divided by 5 is about 5824, and that would be the least amount of consistent gets we would be able to achieve (the actual observed number of consistent gets is slightly higher).

All that means is sometimes in order to get two rows, we needed to get two blocks: we got the last row from one block and the first row from the next block.
Let’s increase the array size again:

SQL> set arraysize 10
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
3271 consistent gets

SQL> set arraysize 25
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
1551 consistent gets

SQL> set arraysize 100
SQL> select * from t;

29120 rows selected.


Statistics
----------------------
688 consistent gets

SQL> set arraysize 500
SQL> select * from t;

29120 rows selected.


Statistics
------------------
460 consistent gets
............
As you can see, as the array size goes up, the number of consistent gets goes down. So, does that mean you should set your array size to 5,000, as in this last test? Absolutely not. If you notice, the overall number of consistent gets has not dropped dramatically between array sizes of 100 and 5,000.

It would be better to have more of a stream of information flowing: Ask for 100 rows, get 100 rows, ask for 100, process 100, and so on. That way, both the client and server are more or less continuously processing data, rather than the processing occurring in small bursts.

Read More

Can we create table name and column name with case sensitive in oracle?

The table name and Column names CAN BE case sensitive in oracle, but are not by default. Bydefault, any way you create a table, upper case, lower case, mixed case, doesn't matter; everything will be forced to UPPER CASE. If you want it not to be UPPER CASE, you need to create the column with quotes around the name of the table or column and the case for the column that you specify will be kept.

The below script explains that very well.

The first one we see the table name with the case sensitive.
SQL> create table "Test" ( num number);

Table created.
We select the records from the table test with out any quotes. It is telling us that the table is not available.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "Test";
no rows selected
The above query with the quotes is showing result. It proves that we can create a table with case sensitive.

Now we see the column name with the case sensitive.
SQL> create table t
2 as
3 select decode(mod(rownum,2),0,'M','F') as "gender",
4 all_objects.*
5 from all_objects
6 /

Table created.


SQL> create index t_idx on t (Gender,object_id);
create index t_idx on t (Gender,object_id)
*
ERROR at line 1:
ORA-00904: "GENDER": invalid identifier


SQL> desc t;
Name Null? Type
-------------- -------- ----------------------------
gender VARCHAR2(1)
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
...........
........
....

Read More

Index skip scan

We are using the B-Tree index and our predicate does not use the leading edge of index, in this case we might have a table T with an index on T(x,y). we query SELECT * from T WHERE x= 8.

The optimizer will allow to use the index since our predicate did not involve the column X. the optimizer would notice that it did not have to go to the table to get either X or Y. they are in the index. So it may very well option for the fast full index scan.

See below link for more information:http://karthikeyanbaskaran.blogspot.com/2010/02/index-is-not-getting-used.html

The is another case whereby the index T(x,y) could be used by the CBO is during an index skip scan. The skip scan works well if and only if the leading edge of the index has very few distinct values and the optimizer understand that. Example, consider an idex on (GENDER, EMPNO) where GENDER has the values M and F.


SQL> create table t
2 as
3 select decode(mod(rownum,2),0,'M','F') as gender,
4 all_objects.*
5 from all_objects;

Table created.

SQL> create index t_idx on t(gender,object_id);

Index created.

SQL> begin
dbms_stats.gather_table_stats('SYSTEM','T');
end;
/

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from t where object_id = 41;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=94)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=1 Bytes= 94)
2 1 INDEX (SKIP SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1 )
The INDEX SKIP scan step tells us that Oracle is going to skip throughout the index, looking for the points where GENDER changes values and read down the tree from there.

Now we increase the count of the distinct value.
SQL> set autotrace off;
SQL> update t set gender = chr(mod(rownum,256));

29119 rows updated.

SQL> begin
2 dbms_stats.gather_table_stats('SYSTEM','T');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from t where object_id = 41;

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

Read More

Index is not getting used

We are using the B-Tree index and our predicate does not use the leading edge of index, in this case we might have a table T with an index on T(x,y). we query SELECT * from T WHERE x= 8 and see how the optimizer executes.

We see this in the below example.
create table test (x number, y number);
insert into test values ( 4,5);
insert into test values (1,3);
insert into test values (7,5);
insert into test values (3,6);

SQL> select * from test;

X Y
----- ----
4 5 1 3
7 5
3 6

SQL> create index test_idx on test(x,y);

Index created.

SQL> begin
2 dbms_stats.gather_table_stats('SYSTEM','TEST');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from test where y=5;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=1 Bytes=6)
The above query is going for the FULL table access since we have an index but the Y is not a leading edge of the index.


SQL> select * from test where x=3;

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

Now we try with both the conditions. The below example is having both X and Y in the where clause.

SQL> select * from test where x=3 and y=5;

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

Still it is not going for the index scan. The reason is that, the table is having the small number of records (4 records). The optimizer checks the best plan and goes for the FULL table access. This is proving us the FULL table access is not always bad.

“FULL table access is not always bad and indexes are not always good.”

We increase the records in the table.
SQL> set autotrace off;
SQL> insert into test select * from test;
8 rows created.

SQL> /
16 rows created.
....
...
..
.
SQL> /
16384 rows created.

SQL> commit;
Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats('SYSTEM','TEST');
3end;
/
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from test where x=3;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=8192 Bytes=49152)

1 0 INDEX (FAST FULL SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=4 Card=8192 Bytes=49152)
The optimizer will allow to use the index since our predicate did not involve the column X. the optimizer would notice that it did not have to go to the table to get either X or Y. they are in the index. So it may very well option for the fast full index scan. The index is smaller typically smaller than the underlying table. (This access path is always available with CBO only).
SQL> select * from test where y=6;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=10923 Bytes=65538)

1 0 INDEX (FAST FULL SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=4 Card=10923 Bytes=65538)

Now we try with both the conditions. The below example is having both X and Y in the where clause.
SQL> select * from test where x=3 and y=6;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2731 Bytes=16386)
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=3 Card=2731 Bytes=16386)
The explain plans shows that, it is going for the index scan.

Read More

Comparing the Contents of Two Tables

Comparing the Contents of Two Tables:
I have two tables named M and N. They have identical columns and have the same number of rows via select count(*) from M and from N. However, the content in one of the rows is different, as shown in the following query:

SQL> select * from M where C1=1;
C1 C2 C3
-- ------------ ----
1 AAAAAAAAAAAA 100

SQL> select * from N where C1=1;

C1 C2 C3
--- ------------ --------
1 AAAAAAAAAAAB 100

The only difference is the last character in column C2. It is an A in table M and a B in table N. I would like to write SQL to compare or see if tables M and N are in sync with respect to their content rather than the number of rows, but I don't know how to do it.
OK, we'll do the specific solution to this problem with columns C1, C2, and C3, and then we'll see how to generalize this to any number of columns. The first and immediate answer we go to was this:

(select 'A', a.* from M
MINUS
select 'A', b.* from N)
UNION ALL
(select 'B', b.* from b
MINUS
select 'B', a.* from a);

That is, just take M minus N (which gives us everything in M that's not in N) and add to that (UNION ALL) the result of N minus M. In fact, that is correct, but it has a couple of drawbacks:
• The query requires four full table scans.
• If a row is duplicated in M, then MINUS will "de-dup" it silently (and do the same with N).
So, this solution would be slow and also hide information from us. There is a better way, however, that uses just two full scans and GROUP BY. Consider these values in A and B:
create table a
(
c1 int,
c2 varchar(3),
c3 varchar(3)
);


create table b
(
c1 int,
c2 varchar(3),
c3 varchar(3)
);

insert into a values(1,'x','y');
insert into a values(2,'xx','y');
insert into a values(3,'x','y');

insert into b values(1,'x','y');
insert into b values(2,'x','y');
insert into b values(3,'x','yy');

select * from a;

C1 C2 C3
---- -- --
1 x y
2 xx y
3 x y

select * from b;
C1 C2 C3
--- -- --
1 x y
2 x y
3 x yy

The first rows are the same, but the second and third rows differ. This is how we can find them:

select c1, c2, c3,
count(src1) CNT1,
count(src2) CNT2
from
( select a.*,
1 src1,
to_number(null) src2
from a
union all
select b.*,
to_number(null) src1,
2 src2
from b
)
group by c1,c2,c3
having count(src1) <> count(src2);C1 C2 C3 CNT1 CNT2
--- -- -- ---- ----
2 x y 0 1
2 xx y 1 0
3 x y 1 0
3 x yy 0 1




Now, because COUNT() returns a count of the non-null values of —we expect that after grouping by all of the columns in the table—we would have two equal counts (because COUNT(src1) counts the number of records in table A that have those values and COUNT(src2) does the same for table BCNT1 and CNT2, that would have told us that table A has this row twice but table B has it three times (which is something the MINUS and UNION ALL operators above would not be able to do

The below having the script in SQL Server:

http://karthikeyanbaskaransqlserver.blogspot.com/2010/02/comparing-contents-of-two-tables.html

Read More

Index scan gives the sorted record ?

Index scan gives the sorted record.
SQL> column plan_plus_exp format a100
SQL> set trimspool on
SQL> create table t nologging as select * from dba_objects;

Table created.

SQL> set time on
SQL> set timing on
SQL> create index t_idx on t(owner,object_id) nologging compute statistics;

Index created.

Elapsed: 00:00:00.03
SQL> select count(*) from t;
COUNT(*)
----------
29515
Elapsed: 00:00:00.00

SQL Query 1:
SQL> set autotrace on
18:07:31 SQL> select rownum' 'owner' 'object_id from t where owner='SYS' and rownum < 11;

optimizer="CHOOSE" cost="5" card="10" bytes="18581)" cost="5" card="1093" bytes="18581)"

SQL Query 2:
SQL> select * from (select rownum' 'owner' 'object_id from t
2 where owner='SYS'
3 order by owner,object_id ) where rownum < 11;

optimizer="CHOOSE" cost="5" card="10" bytes="63394)" cost="5" card="1093" bytes="63394)" cost="5" card="1093" bytes="18581)"
The first SQL and second SQL are also giving the same result with same statistics. First SQL may not work as expected in future if I drop index obviously it will not.
But RULES OF THE GAME:
Rule #1: if you want, expect, or need sorted data, there is exactly one way to achieve that in a relational database.

"You must use order by"

Rule #2: please re-read #1 until you believe it.

The first query says "give me a random set of 10 rows for the SYS owner"

The second says "give me 10 rows for the sys owner starting at the smallest object_id in sorted order"

Read More