I wont believe you if you say that you are an administrator or HANA developer in fact even if you are even closely related to database and you have not heard of SCHEMAS.
If you don't know about schemas in database refer this Database Schema , but Let me quote the part I want to drop your focus to from the mentioned page.
"A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data."
Physical Database Schema − This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage.
Logical Database Schema − This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.
Let's dive in back in HANA and Schemas ,
SCHEMA = NAMESPACE
Don't get confused but schema is a namespace, it is way to group objects together by addressing them as one group name i.e. schema.
What are different objects that are stored in this group ?
Tables : Basic container to store database [In catalog you con find this under TABLES view]
View and Sequences (A customizable number generator that comes under SEQUENCE View)
Now understand this , Schemas do not have any security feature .
Every user is assigned to a schema which is default schema for that user , which is named after user account name. Whenever a user tries to access database object and no schema is provided then the user checks in default schema as fallback.
Order of Object name Resolution (Schema that will be used ) :
Provided Schema -> Current Schema -> Public Schema
Whenever the match is done in one of the step , no more further evaluation is done it is expected by the developer to understand that and have a clear idea of ambiguity.
Understand it this way , Suppose you "SELECT * from table1"
Table1 will be first searched in Provided schema , if found their no more check are done and the value is used but if not then in that case Current schema will be checked and at last Public Schema. Whenever we the object is found no more evaluations are performed.
If schema name is provided in full qualified object name no further step is evaluated.
here we have provided the full qualified object.
A useful technique for a database developer i.e. to map commonly shared objects into an application user schema via synonym.
As stated above developer should take care of ambiguity when understanding this we need to understand that object names only need to be unique within a given schema but it also makes sense that different schema have objects of same name, But at time developer may also require to check the schema that is used and table that is used for that we use EXPLAIN PLAN.
So try to understand using the above picture ,
1. We have Table 1 in both Schema 1 and Schema 2 , that is fine but we cannot have two tables with same name in any of the Schema.
2. When we need to access table this is how it is done ,
Access table1 of schema1 ---> select * from schema1.table1
Access table1 of schema2 ---> select * from schema2.table1
Access table1 without specifying schema ---> select * from table1 , At this point we can check t1 is used from which schema by using EXPLAIN PLAN
EXPLAIN PLAN FOR
select * from table1
Database decide which schema should be using , using a set of protocol which was discussed above.
SET SCHEMA : We also have a option to SET SCHEMA <SCHEMA_NAME> to set the schema that needs to be used.
Dynamic way to affect many single objects : SELECT privilege's for a schema is assigned to a user (or a user role) when new objects are created in the schema , The user will automatically have SELECT privilege's for the new Objects that are created in that schema.
Object type view provides the SCHEMA_NAME column to indicate the schema that the respective object belongs to.
It is not possible to change the schema of any object or change the schema object owner once the object is created.
How to Change the Schema / Schema owner to do that objects need to be recreated in the target schema by user account that should be the owner of the objects. Another way of doing this is with the EXPORT command or the catalog objects export
Assistant in SAP HANA Studio , the import objects with the RENAME SCHEMA option.
Note : But for doing this user needs the IMPORT privilege's assigned. Worth mentioning that though navigator in HANA Studio shows the schemas and objects it is not easy to copy/move the objects between schemas or users.
References:-
SAP HANA ADMIN GUIDE
Comments
Post a Comment