STRATEGY & CREATIVITY

SAP HANA In Memory Database.

PORTFOLIO

We pride ourselves on bringing a good technology and effective knowledge to every perfect one.

  • SAP HANA - SQL Explain Plans

    SQL explain plans are used to generate detail explanation of SQL statements. They are used to evaluate execution plan that SAP HANA database follows to execute the SQL statements.
    The results of explain plan are stored into EXPLAIN_PLAN_TABLE for evaluation. To use Explain Plan, passed SQL query must be a data manipulation language (DML).

    Common DML Statements

    • SELECT − retrieve data from the a database
    • INSERT − insert data into a table
    • UPDATE − updates existing data within a table
    SQL Explain Plans cannot be used with DDL and DCL SQL statements.

    EXPLAIN PLAN TABLE in database

    EXPLAIN PLAN_TABLE in database consists of multiple columns. Few common column names − OPERATOR_NAME, OPERATOR_ID, PARENT_OPERATOR_ID, LEVEL and POSITION, etc.
    COLUMN SEARCH value tells the starting position of column engine operators.
    ROW SEARCH value tells the starting position of row engine operators.

    To create an EXPLAIN PLAN STATEMENT for a SQL query

    EXPLAIN PLAN SET STATEMENT_NAME = ‘statement_name’ FOR <SQL DML statement>
    

    To see values in EXPLAIN PLAN TABLE

    SELECT Operator_Name, Operator_ID
    FROM explain_plan_table
    WHERE statement_name = 'statement_name';

    To delete a statement in EXPLAIN PLAN TABLE

    DELETE FROM explain_plan_table WHERE statement_name = 'TPC-H Q10';
  • SAP HANA - SQL Synonym

    SQL Synonyms is an alias for a table or a Schema object in a database. They are used to protect client applications from the changes made to name or location of an object.
    Synonyms permit applications to function irrespective of user who owns the table and which database holds the table or object.
    Create Synonym statement is used create a Synonym for a table, view, package, procedure, objects, etc.

    Example

    There is a table Customer of efashion, located on a Server1. To access this from Server2, a client application would have to use name as Server1.efashion.Customer. Now we change the location of Customer table the client application would have to be modified to reflect the change.
    To address these we can create a synonym of Customer table Cust_Table on Server2 for the table on Server1. So now client application has to use the single-part name Cust_Table to reference this table. Now, if the location of this table changes, you will have to modify the synonym to point to the new location of the table.
    As there is no ALTER SYNONYM statement, you have to drop the synonym Cust_Table and then re-create the synonym with the same name and point the synonym to the new location of Customer table.

    Public Synonyms

    Public Synonyms are owned by PUBLIC schema in a database. Public synonyms can be referenced by all users in the database. They are created by the application owner for the tables and other objects such as procedures and packages so the users of the application can see the objects.

    Syntax

    CREATE PUBLIC SYNONYM Cust_table for efashion.Customer;
    
    To create a PUBLIC Synonym, you have to use keyword PUBLIC as shown.

    Private Synonyms

    Private Synonyms are used in a database schema to hide the true name of a table, procedure, view or any other database object.
    Private synonyms can be referenced only by the schema that owns the table or object.

    Syntax

    CREATE SYNONYM Cust_table FOR efashion.Customer;
    

    Drop a Synonym

    Synonyms can be dropped using DROP Synonym command. If you are dropping a public Synonym, you have to use the keyword public in the drop statement.

    Syntax

    DROP PUBLIC Synonym Cust_table;
    DROP Synonym Cust_table;
  • SAP HANA - SQL Triggers

    Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −
    • A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
    • A database definition (DDL) statement (CREATE, ALTER, or DROP).
    • A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
    Triggers could be defined on the table, view, schema, or database with which the event is associated.

    Benefits of Triggers

    Triggers can be written for the following purposes −
    • Generating some derived column values automatically
    • Enforcing referential integrity
    • Event logging and storing information on table access
    • Auditing
    • Synchronous replication of tables
    • Imposing security authorizations
    • Preventing invalid transactions
  • SAP HANA - SQL Sequences

    A sequence is a set of integers 1, 2, 3, that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them.

    Using AUTO_INCREMENT column

    The simplest way in MySQL to use sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care.

    Example

    Try out the following example. This will create table and after that it will insert few rows in this table where it is not required to give record ID because it is auto-incremented by MySQL.
    mysql> CREATE TABLE INSECT
       -> (
       -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
       -> PRIMARY KEY (id),
       -> name VARCHAR(30) NOT NULL, # type of insect
       -> date DATE NOT NULL, # date collected
       -> origin VARCHAR(30) NOT NULL # where collected
    );
    
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
       -> (NULL,'housefly','2001-09-10','kitchen'),
       -> (NULL,'millipede','2001-09-10','driveway'),
       -> (NULL,'grasshopper','2001-09-10','front yard');
     
    Query OK, 3 rows affected (0.02 sec)
    Records: 3 Duplicates: 0 Warnings: 0
    mysql> SELECT * FROM INSECT ORDER BY id;
    +----+-------------+------------+------------+
    | id | name        | date       | origin     |
    +----+-------------+------------+------------+
    | 1  | housefly    | 2001-09-10 | kitchen    |
    | 2  | millipede   | 2001-09-10 | driveway   |
    | 3  | grasshopper | 2001-09-10 | front yard |
    +----+-------------+------------+------------+
    3 rows in set (0.00 sec)
    

    Obtain AUTO_INCREMENT Values

    LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how to issue SQL statements. Otherwise, PERL and PHP scripts provide exclusive functions to retrieve auto-incremented value of last record.

    PERL Example

    Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle −
    $dbh->do ("INSERT INTO INSECT (name,date,origin)
    VALUES('moth','2001-09-14','windowsill')");
    my $seq = $dbh->{mysql_insertid};

    PHP Example

    After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling mysql_insert_id( ) −
    mysql_query ("INSERT INTO INSECT (name,date,origin)
    VALUES('moth','2001-09-14','windowsill')", $conn_id);
    $seq = mysql_insert_id ($conn_id);

    Renumbering an Existing Sequence

    There may be a case when you have deleted many records from a table and you want to re-sequence all the records. This can be done by using a simple trick but you should be very careful to do so if your table is having join, with other table.
    If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again. The following example shows how to renumber the id values in the insect table using this technique −
    mysql> ALTER TABLE INSECT DROP id;
    mysql> ALTER TABLE insect
       -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
       -> ADD PRIMARY KEY (id);

    Starting a Sequence at a Particular Value

    By default, MySQL will start sequence from 1 but you can specify any other number as well at the time of table creation. Following is the example where MySQL will start sequence from 100.
    mysql> CREATE TABLE INSECT
       -> (
       -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
       -> PRIMARY KEY (id),
       -> name VARCHAR(30) NOT NULL, # type of insect
       -> date DATE NOT NULL, # date collected
       -> origin VARCHAR(30) NOT NULL # where collected
    );
    Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE.
  • SAP HANA - SQL Stored Procedures

    A procedure allows you to group the SQL statement into a single block. Stored Procedures are used to achieve certain result across applications. The set of SQL statements and the logic that is used to perform some specific task are stored in SQL Stored Procedures. These stored procedures are executed by applications to perform that task.
    Stored Procedures can return data in the form of output parameters (integer or character) or a cursor variable. It can also result in set of Select statements, which are used by other Stored Procedures.
    Stored Procedures are also used for performance optimization as it contains series of SQL statements and results from one set of statement determines next set of statements to be executed. Stored procedures prevent users to see the complexity and details of tables in a database. As Stored procedures contain certain business logic, so users need to execute or call the procedure name.
    No need to keep reissuing the individual statements but can refer to the database procedure.

    Sample Statement to Create Procedures

    Create procedure prc_name (in inp integer, out opt "EFASION"."ARTICLE_LOOKUP")
    as
    begin
    opt = select * from "EFASION"."ARTICLE_LOOKUP" where article_id = :inp ;
    end;
  • SAP HANA - SQL Expressions

    An Expression is used to evaluate a clause to return values. There are different SQL expressions that can be used in HANA −
    • Case Expressions
    • Function Expressions
    • Aggregate Expressions
    • Subqueries in Expressions

    Case Expression

    This is used to pass multiple conditions in a SQL expression. It allows the use of IF-ELSE-THEN logic without using procedures in SQL statements.

    Example

    SELECT COUNT( CASE WHEN sal < 2000 THEN 1 ELSE NULL END ) count1,
    COUNT( CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE NULL END ) count2,
    COUNT( CASE WHEN sal > 4000 THEN 1 ELSE NULL END ) count3 FROM emp;
    This statement will return count1, count2, count3 with integer value as per passed condition.

    Function Expressions

    Function expressions involve SQL inbuilt functions to be used in Expressions.

    Aggregate Expressions

    Aggregate functions are used to perform complex calculations like Sum, Percentage, Min, Max, Count, Mode, Median, etc. Aggregate Expression uses Aggregate functions to calculate single value from multiple values.
    Aggregate Functions − Sum, Count, Minimum, Maximum. These are applied on measure values (facts) and It is always associated with a dimension.
    Common aggregate functions include −
    • Average ()
    • Count ()
    • Maximum ()
    • Median ()
    • Minimum ()
    • Mode ()
    • Sum ()

    Subqueries in Expressions

    A subquery as an expression is a Select statement. When it is used in an expression, it returns a zero or a single value.
    A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
    Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.
    There are a few rules that subqueries must follow −
    • Subqueries must be enclosed within parentheses.
    • A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
    • An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
    • Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
    • The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
    • A subquery cannot be immediately enclosed in a set function.
    • The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.

    Subqueries with the SELECT Statement

    Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

    Example

    SELECT * FROM CUSTOMERS
    WHERE ID IN (SELECT ID
    FROM CUSTOMERS
    WHERE SALARY > 4500) ;
    +----+----------+-----+---------+----------+
    | ID | NAME     | AGE | ADDRESS | SALARY   |
    +----+----------+-----+---------+----------+
    | 4  | Chaitali | 25  | Mumbai  | 6500.00  |
    | 5  | Hardik   | 27  | Bhopal  | 8500.00  |
    | 7  | Muffy    | 24  | Indore  | 10000.00 |
    +----+----------+-----+---------+----------+
  • CONTENT

    We are providing SAP HANA Administration Materials.

    CONTACT US

    For enquiries you can contact us in several different ways. Contact details are below.

    VENKAT SAP HANA

    • Street :Hyderabad
    • Person :Venkat
    • Country :INDIA
    • Email :venkatsaptraining@gmail.com

    For any Query,Please leave message

    Learn SAP HANA Be with latest update.