To monitor the performance of views, we can use two tools
1) Explain the plan
2) To administration control.
It is a table to maintain details of views that are used to evaluate the execution plan that the SAP HANA DB follows to excursive the SQL statements. The result of the evaluation is stored in the EXPLAIN – PLAN - table view for later user examination
The SQL statement must be a data manipulation statement. Thus a schema definition language stunt can not be used with the EXPLAIN PLAN command
We can obtain a SQL plan from EXPLAIN – PLAN – TABLE view. All users shy the view. Here is an example of reading an SQL plan from the view.
Inclined to build a profession as SAP HANA Developer? Then here is the blog post on, explore "SAP HANA Training"
DELETE FROM Explain – plan – table WHERE statement name = ‘TPc – H Q10 Explain Plan set statement – NAME = ‘TPC – H Q10’ for
SELECT TOP 20
C- Cust keys
C- name
Sum (1 – enfolded price * (1-1___ discount) AS revenues
C- acct bal,
N – Name,
C – address,
C – phone,
C – connect
FROM
Customer,
Orders,
Line item,
Nation
WHERE
C_Cust key = O_custkey
AND 0_ORDINATE <> = ‘1993 – 10 – 101’
AND 0_ordinate < - ADD _Months (‘1993-10-01’3)
GROUP by
C _ cust key:
C_name,
C_ acetbol,
C_Phone
N_name
C_address,
C-connect
ORDER By
We venue DESC;
SELECT operator _name, operator – details, table – name FROM EXPLAON – plan – table
WHERE statement – name = ‘TPc – H QSO’;
This table contains
Statement _ name
Operator _name
Operator _ details
Schema – Name
Table _name
Table – Type
Table – Size
Out put –Size
Subtee – cost
operator _ ID
Parent – operator – ID
Level
position
Host
Port
Timestamp
connection _ ID
Execution – Engine
To run this console. Go to the HANA install directory and execute SAP admin. SH
It is used to trace fro loading/analysis performance & provides landscapes, tables, views, …….. etc
Procedure click on plan execution (tab) – click on start (to tray)
Loading / analytic performer
And for every trace system will create.TPT in HANA DB working directory
It gives overviews of all plans.
| Checkout: SAP HANA Tutorial |
SQL syntax: CREATE SCHEMA <Schema – name > [owned by <User-name>]
Description: The Create schema statement creates a schema in the current database
Example: CREATE SCHENA my – schema owned by the system;
SET SCHEMA
SQL schema syntax: SET SCHEMA <SCHEMA- NAME>
Parameters: <schema_name> :: = string literal which specie files schema name
Description: We can change the current schema of the session. The current schema is used when database objects names such as table names are not prefixed with the schema name.
CREATE Table: CREATE[<table-tyupe>] TABLE <table-name> <table-contents – socrce.>[ <table_create_option_list>] [WITH PARAMETERS ( < parameter – key – value – list)]
Example: Create column table abc. (schema name) company (table) (int primary key, chp – Name varchar 2 (20))
This is for creating column table
EX:- To create row table abc. Company ((MP number int primary key, CMP – Name varchar (20))
Import:- To import from specified directory ;
SQL Syntax: IMPORT <TABLE – NAME – LIST> as < import _format> format <path> [with <options – name-list] [threads <number – of – threads]
<Table_name_list> :: = ,table – name>, …. | ALL
<import – format> :: = BINARY | csv
<option – name – list> :: = <option – name>,….
<option – name> :: = REPLACE | CATALOG ONLY
<path> :: = ‘full –path
<number-of – threads> :: > integrator to indicate the no. of threads
SQL Syntax
Export <table – name-list> AS <Export – format> INTO <path>
[with <option- name- list>] Threads <number-of-thereads>]
<table – name-list>:: = <table – name>,……. | All
<Export – format> :: = BINARY | CSV
<OPTION – NAME- LIST> :: = OPTION – NAME>,…….
<OPTION – NAME> :: = REPLACE | CATALOG ONLY | SCRABBLE [BY <PASSWORD>]
<Path>:: = A string literal to specify the path
<number-of-threads>:: = Integer to specify the number the threads
The SAP HANA column store engine has been optimized for the most frequent pattern of OLAP greenies in the form of single block SPJG (select, project, join & group by). This selection lists high – cost features that are best avoided when formulating SQL – queries to bobbin the best performance from the column engine
Slower query Select * From T
WHERE TO _DATA (Date_string, “yyyymmdd’) = CURRENI_DATE)
Faster Query Select * from T
Where to – DATE (date-string) = current date;
Implicit type casting:
SAP HANA could perform type casts implicitly even if the user did not explicitly specify a type cast operation.
For Eg: if there is a comparison between a VARCHAR value and a DATE value, the database system performs an implicit typecast operation to convert the
VARCHAR Value into a DATE value. Implicit type casting is done from lower–precedence types to higher procedure types.
We can find type precedence rules in the SAP HANA SQL reference.
Slower Query SELECT * FROM
WHERE date – string <Current – Date:
Faster Query Select* from
Where date – string <to_char(current – date, ‘yyyymmdd’);
In other words, the column (not natively support join) engine supports only engines natively
EX:
Slower query:
Select H year, M. month, SUM (T.ship-amount) From T JOIN M No T.Ship – Date BETWEEN H. first – date and M last – date Group By M.Year, M months
Faster Query:
Select M.Yerar, M.Month, SUM (T. ship – amount) From T Join M ON Extract (Year from t-ship _date)= M.year and Extract (a month from T.Ship – date)= M.month group by M.year, m. month
Filter predicate inside outer Join predicate
This is used for inside outer join predicates and they cannot be sifted below the Join operation
Example
Slower Query Select * from
Left joins on T.a = s.a and T.b=1;
Faster Query Select - * from Left joins on T.a = S.a and t.b = s.one;
DDL for faster query Alter tables
Add (one integer Generated always As 1);
It is a reporting tool to generate different flowers
After modelling views & tables we need to provide them as sconces to BO,
So, we need to integrate SAP BO, & SAP HANA with JDBC & DDBC connection
In BO flow
SAP (HANA) DB (Views & tables)
In BD we need to connect semantic layers first and designed a structure based on reports (a structure is nothing but the structure of the table we need to join them
After joining the semantic layers, we need to create reports
NOTE:- For HANA we can create only crystal reports web, explorer, and dashboards only
↓
Scheduling
↓
Deployment
↓
Publishing
↓
customization
↓
Migration
For in-depth knowledge on SAP HANA, click on below
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
