DataBase NoteBook

SAP HANA

2013-08-06T22:39:00.003-07:00

SAP HANA:

SAP HANA converges database and application platform capabilities
in-memory to transform transactions, analytics, text analysis, predictive and spatial processing so businesses can operate in real-time.

The Below Articles will helps for HANA learning.

HANA Start up and Initial setup

HANA Database Schemas and Database Tables

Look here for more Article .............

HANA Database Schemas and Database Tables

2013-08-06T22:36:00.000-07:00

HANA Database Schemas and Database Tables

The below artifacts are major part of the SAP HANA Database.

Schemas

Tables

SQL views

Sequences

Procedures and Etc….

Schema:

The schema contains the logical group of Database objects and it is the mandatory database object in the HANA and all database objects below to schema. So below establish to develop any database artifacts we need to create schema.

Data base objects are like a

Tables

Views

Procedures

Sequences

…

Steps to create Schema:

Go to Project Explorer - > Right click -> New -> File and it brings the pop up

Provide the schema name with the extension of the “.hdbschema” and Click Finish Button. It creates new window in HANA management studio to provide the syntax to provide schema.

Issue the command schema_name = “WORKSHOPA_00”;

Save the File.

Right click on the schema name and issue the Commit and Activate it.

HANA Start up and Initial setup

2013-08-06T05:37:00.000-07:00

HANA Start up and Initial setup:

The below topics provide the knowledge of the important objects and create HANA artifacts.

HANA Management studio

The SAP HANA studio has 3 major tabs to contain and create a HANA Objects.

Those are,

Project Explorer

SAP HANA Repositories

SAP HANA systems or Navigator (in older version)

The below Diagram shows that.

Steps to create a new System Connection:

Right click in the SAP HANA system and click the Add systems

It brings up the Pop up and provides the Server Details and Credentials in this window.

Now it has been connected to the system and it shows the Catalogs,Content and security Folders.

Repository Workspace Creation:

The below topic shows the step to create a Repository Workspace which keeps all objects in the local system.

Steps:

Go to SAP HANA Rep Tab - > Click New Repository Work space.

Fill the below Details in the Pop up which has come:

1. Select SAP HANA system which we have created in the previous step.

2. Provide the Name of the Workspace in the “Workspace Name:” Text box.

3. Provide the directory path in the “Workspace Root:” Text Box.

The Workspace name will be a separate the folder in the Workspace Root. The workspace name is specific to local copy and no need to same as Server details or other developer workspace name.

New Project Creation:

The below topic shows the steps to create a New project in HANA.

Go to Projects Tab in the SAP HANA Management studio - > Right Click & select “ Project”

All the SAP Projects are under the SAP HANA Development folder. - > Select "Xs Project" and Click Next

1. Provide the Project Name in the text box.

2. By Default the Location will be default workspace location and needs to change it to the workspace which we have created.

3. So remove the check mark in “Use Default Location” and Browse to Path where we have created the workspace.

Click “Finish” create a Project and we have a project now.

The next will be connecting the project with the Server.

Right click on the Project - > select Team - > share project

It brings up a folder and select the Repository workspace and Click on “Finish”

SSIS: Connect to Oracle - Cannot retrieve the column code page info from the OLE DB Provider

2013-05-20T00:02:00.003-07:00

SSIS: Connect to Oracle - Cannot retrieve the column code page info from the OLE DB Provider

I have tried to access data in Oracle from SSIS via OLE DB and the component reported the following warnings:

"Cannot retrieve the column code page info from the OLE DB Provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used."

The fix is simple. Set AlwaysUseDefaultCodePage=TRUE on the offending OLE DB Source component and it works fine.

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

2013-05-14T00:09:00.002-07:00

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.

Get month value from Date in SQL server

2013-05-13T00:13:00.001-07:00

Get month value from Date in SQL server:

Sample:

select month(getdate()) as month_num, datename(month,getdate()) as month_name

month_num                 month_name

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

5                          May

select month(getdate()) as month_num , datename(month,'1-jan-2013') as month_name

month_num                    month_name

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

5                            January

Error message 402: The data types ntext and varchar are incompatible in the equal to operator.

2013-05-12T22:55:00.000-07:00

where clause with ntext in SQL server:

I have a column of type ntext in SQL server how can I evaluate it in a WHERE clause.

Structure of the Table:

sp_help Priorities

Coulumn_name           Type

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

PriorityId                      int

ProjectId                     int

Priority                         nvarchar

Description                   ntext

Sample :

select * from Priorities

where Description = 'High'

The error message for the above Query:

Msg 402, Level 16, State 1, Line 2

The data types ntext and varchar are incompatible in the equal to operator.

Solution:

select * from Priorities
where cast(Description as nvarchar(max)) = 'High'

INSERT Statement

2013-05-09T03:05:00.000-07:00

INSERT Statement

The SQL INSERT statement allows you to insert a single record or multiple records into a table.

Syntax:

INSERT INTO table

(column-1, column-2, ... column-n)

VALUES

(value-1, value-2, ... value-n);

INSERT Statement - Using VALUES keyword example

Sample:

insert into customer (customer_id,customer_name)
values (101,'CustName');

SQL INSERT Statement - Using sub-selects

Sample:

insert into customer (customer_id,customer_name)
select cust_id,first_name from customer_temp;

CREATE TABLE Statement

2013-05-06T00:00:00.004-07:00

The SQL CREATE TABLE statement allows you to create and define a table. Each column must have a data type. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

The syntax for the SQL CREATE TABLE statement is:

CREATE TABLE table_name

(
column1 datatype null/not null,
column2 datatype null/not null,
...
);

For Example

CREATE TABLE customer
(
customer_id number(10) not null,
customer_name varchar2(50) null,
address varchar2(50),
city varchar2(50),
state varchar2(25),
zip_code varchar2(10)
);

SQL Statement Types

2013-05-05T23:43:00.000-07:00

SQL Statement Types:

1. DDL – Data Definition Language.

2. DML – Data Manipulation Language.

3. DCL – Data Control languages

DDL Statements:

• Create – it used create objects like Tables,Views and etc…

• Alter – It used to alter the Structure of the Obejects like Tables, Views and etc…

• Truncate – It removes all the records in table.

• Grant – Provides / Assigns Privileges

DML Statements:

• Select – Retrieve data from tables.

• Insert – It helps to insert data in to table.

• Update – It helps to Modify the Existing data in table.

• Delete – It helps to Remove data from table.

DCL Statements:

• Commit – It will ends the current Transaction & making the changes to permanent.

• Savepoint – It will identify a point or Num of transaction in Set of Transactions to which we can later roll back.

• Rollback- It will undo / remove all the changes made by Current transaction.

SQL Introduction

2013-05-05T12:30:00.000-07:00

SQL Introduction

SQL stands for Structured Query Language and it is generally referred to as SEQUEL. SQL was developed by IBM in the 1970s.

The American National standard Institute (ANSI) published first SQL stands in 1986 and released updates in 1992 & 1999, Known as SQL 92 and SQL99.

SQL is Simple & powerful language to Create, Access and Manipulate data and object structure in the data base.

The Below Topics will help us to learn SQL.

To Learn More Concepts:

Removing Leading Zeros

2012-05-01T05:20:00.000-07:00

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

Change Password of SA Login Using Management Studio - SQL SERVER

2011-10-14T03:33:00.000-07:00

Change Password of SA Login Using Management Studio - SQL SERVER

Step1: Login into SQL Server using Windows Authentication.

Step2: In Object Explorer, open Security folder, open Logins folder. Right Click on SA account and go to Properties.

Step3: Change SA password, and confirm it. Click OK.

Make sure to restart the SQL Server and all its services and test new password by log into system using SA login and new password.

User Defined Functions - Informatica

2011-09-19T02:01:00.000-07:00

User Defined Functions - Informatica

Informatica User Defined Functions are similar to Built-in Functions, where these functions need to be created once and execute multiple times

Transformation Logic implemented without User Defined Functions:

Validation “IIF( ISNULL(INPUT),’TRUE’,’FALSE’)” is being performed in multiple ports.

The disadvantage with this approach is any changes to this validation need to be done in all the ports.

This can be addressed by creating a User Defined function and have the logic incorporated there.

Steps to Create User Defined Functions
Step 1 : Right-click on the User-Defined Functions folder in a repository folder in the Designer.
Click on “New”

Step 2: In Editor add the transformation logic / validation that needs to be performed.
Click ok and validate the UDF.

The easy way to Get table and index DDL script

2011-02-21T08:23:00.000-08:00

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"

Order by based on the parameter passed in SQL server:

2011-02-12T05:48:00.000-08:00

Order by based on the parameter passed in SQL server:
Dynamically ordering a result set based on the parameter passed is a powerful technique for ordering your data. The below example shows that and see the issues which we faces.

Initial setup:
create table dept
(
deptid int,
deptname varchar(10)
)

insert into dept values (1,'Sales')
insert into dept values (4,'Production')
insert into dept values (3,'fiance')
insert into dept values (2,'HR')
The following SQL script shows how you might try to use a CASE expression to dynamically order a result set:

DECLARE @OrderByOption int
SET @OrderByOption = 1 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE
WHEN @OrderByOption = 1 THEN deptid
WHEN @OrderByOption = 2 THEN deptname
END

deptid deptname
----------- ----------
1 Sales
2 HR
3 fiance
4 Production

(4 row(s) affected)

The script works well if the value of @OrderByOption is set to 1. We change the value as 2 and try.

DECLARE @OrderByOption int
SET @OrderByOption = 2 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE
WHEN @OrderByOption = 1 THEN deptid
WHEN @OrderByOption = 2 THEN deptname
END
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'Sales' to data type int.
Conceptually, the query offers the ability to order by either the deptid column or the deptname column based on the current value of @OrderByOption. The above statement attempts to order by deptname, but produces the error, " Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'Sales' to data type int "

Why the query works when the value for @OrderByOption is set to 1 but doesn't work when the value is set to 2, we need to recognize that the two THEN conditions of the CASE statement reference expressions of different data types. In this case, SQL Server implicitly converts the data type for the entire CASE expression to the data type in the THEN clause that has the highest order of data-type precedence.

In this example, the CASE statement has two possible values that follow a THEN clause: deptid, which is an integer data type, and deptname, which is a varchar data type. The integer data type has a higher precedence than the varchar data type, so SQL Server attempts to cast the deptname expression as an integer if you try to order by that column. Such a conversion isn't allowed, so SQL Server generates the above error.
We can work around this problem by using multiple CASE statements, as the following example shows:

DECLARE @OrderByOption int
SET @OrderByOption = 2 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE WHEN @OrderByOption = 1 THEN deptid END,
CASE WHEN @OrderByOption = 2 THEN deptname END
deptid deptname
----------- ----------
3 fiance
2 HR
4 Production
1 Sales

Schema Binding in SQL server

2011-01-05T05:47:00.000-08:00

Schema Binding :
Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.

Notice two important points while creating a view with SCHEMABINDING OPTION:
1. The objects should be referred to by their owner names (two part name - [ownername.objectname] ).
2. SELECT * is not permitted.

We see that using below scripts.
create table schema_binding_table
(
id int,
msg varchar(10)
)

insert into schema_binding_table values
(1,'msg1'),
(2,'msg2'),
(3,'msg3')

select * from schema_binding_table

id msg
----------- ----------
1 msg1
2 msg2
3 msg3

(3 row(s) affected)

Chance for erros while creating view with schema binding:
The below error messgae clearly mention that SELECT * is not permitted whille creating the view with schema binding.
CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT * FROM schema_binding_table

Msg 1054, Level 15, State 6, Procedure schema_binding_VIEW, Line 2
Syntax '*' is not allowed in schema-bound objects.

Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view.

CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT id,msg FROM schema_binding_table

Msg 4512, Level 16, State 3, Procedure schema_binding_VIEW, Line 2
Cannot schema bind view 'schema_binding_VIEW' because name 'schema_binding_table' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Successful syntax:
CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT id,msg FROM dbo.schema_binding_table

Command(s) completed successfully.
We know that without schema binding there was no issue to alter or drop the base table without any type of warning or error. Now if we try to alter the table structure or drop the table, we are going to get this error message.

1. Drop table:

drop table schema_binding_table
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'schema_binding_table' because it is being referenced by object 'schema_binding_VIEW'.
2. Alter table:alter table schema_binding_table alter column msg varchar(20)
Msg 5074, Level 16, State 1, Line 1
The object 'schema_binding_VIEW' is dependent on column 'msg'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN msg failed because one or more objects access this column.

Simulating ORACLE ROWNUM in SQL Server

2010-12-30T22:00:00.000-08:00

Simulating ORACLE ROWNUM in SQL Server:
Here is the approach you can use to generate a pseudo row number in SQL Server.

The table format and data:

select * from A

c1 c2
-----
101 0
102 0
103 0

(3 row(s) affected)

Approach 1:
select rank=count(*),a.c1,a.c2 from A a ,A b
where a.c1+a.c2 > = b.c1+b.c2
group by a.c1,a.c2

rank c1 c2
-----------
1 101 0
2 102 0
3 103 0

(3 row(s) affected)

Approach 2 :
select rank() OVER (ORDER BY a.c1,a.c2) as rank,a.c1,a.c2
from A a
order by rank

rank c1 c2
--------------------
1 101 0
2 102 0
3 103 0

(3 row(s) affected)

Finally very very simple method:
drop table #T
Select IDENTITY(int, 1,1) id, * INTO #T from A
Select * from #T

id c1 c2
-----------
1 101 0
2 102 0
3 103 0

(3 row(s) affected)

EXECUTE AS and REVERT:

2010-12-29T21:54:00.001-08:00

EXECUTE AS and REVERT:
The EXECUTE AS and the REVERT are used to change the execution logins. We see the example to change the connection details and coming back to old one.

The client has connected using the “Test” user and “EXECUTE AS” statement changes the Login connection as “Demo” user. The second result set shows that.

The REVERT command switches the execution context back to the Test user.

Explanation about the keywords:
EXECUTE AS Clause - helps us to define the execution context.

REVERT - Switches the execution context back to the caller of the last EXECUTE AS statement

SUSER_NAME() - Returns the login identification name of the user.

USER_NAME() - Returns a database user name.

Error message is: The directory name is invalid in SQL Server

2010-12-08T05:33:00.000-08:00

Error Message: in SQL server
An error occurred while executing batch. Error message is: The directory name is invalid.

Reason:
This error will come when your disk space is very low or if the directory is protected.

Solution:check your disk space or try connecting the SQL server from some other machine.

Careful with SQL server Update

2010-11-07T20:48:00.000-08:00

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.

Character of DUAL

2010-10-03T07:09:00.000-07:00

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.

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

2010-09-24T08:10:00.000-07:00

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.

CHAR Vs VARCHAR

2010-09-04T07:10:00.000-07:00

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.

Performance Improvement – Index scan with NULL condition

2010-08-16T23:33:00.000-07:00

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.