Oracle SQL Tutorial -7 Creating Other Schema Objects

Melike Duran

5 years ago

Hi ,

In this article I will tell you what is view , how to create . View is the collection of data from multiple tables into a single table and the ability to query by making restrictions from this table .

VIEW

Creates an interface to extract data from one or more tables .

VIEW TYPES

Views are Simple and Complex .
Complex views include join , order by , group by .

CREATING VIEW

CREATING VIEW :

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view [(alias [,alias]..)] AS SUBQUERY [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];

Now let’s create our own view .

CREATE VIEW test as select * from sh.customers;

VIEW FROM DATA

Queries are drawn from the same table as a query .

SELECT * FROM TEST;

CHANGING VIEW

If there is a view that has already been created , let’s change it or make it :

CREATE OR REPLACE VIEW test AS SELECT CUST_FIRST_NAME,CUST_LAST_NAME FROM SH.CUSTOMERS;

VIEW ON DML

We can do DML operations on simple view, but generally DML operations cannot be performed on complex view .
The WITH READ ONLY statement prevents view from being made on DML .
WITH CHECK OPTION allows DML to be controlled .
DMLs can be made in source tables on simple views .
**To disable DML on a view , we create the view with READ ONLY .

SEQUENCE

Because it is shareable , the public key can be used instead of the Primary key .

Sequence can be accessed quickly via cache memory .

CURRVAL : Displays the current value of Sequence .

Use of :

SELECT seq_ornek.CURRVAL FROM SH.CUSTOMERS;

NEXTVAL : It generates the next value and increases the counter , which is given to each user in the ordinary .

Use of :

SELECT SEQ_ORNEK.NEXTVAL FROM SH.CUSTOMERS;

CREATE SEQUENCE seq_ornek INCREMENT BY 1 START WITH 6 MAXVALUE 9999

NOCACHE

NOCYCLE;

In the example above we have shown how to create a sequence .

SEQUENCE CAVITIES

ROLLBACK operations ,
When the system crashes ,
Sequence when used elsewhere
And unnecessarily when the .NEXTVAL command is called .

CHANGING THE SEQUENCE

Cycle ,
Cache ,
Increment by ,
Maxvalue ,
Minvalue
When we want to change the Sequence , we can only change the above values .

ALTER SEQUENCE SEQ_ORNEK INCREMENT BY 2 MAXVALUE 10000

NOCACHE

NOCYCLE;

INDEX

Objects used by Oracle to fetch a row faster .

Reduces disk input / output operations .
They are managed by Oracle .
A table can have more than one index .
They occupy a certain space on the disc .
They work with B Tree logic .

CREATING INDEX

When we define primary key and Unique constraint , the index is created automatically . There may be one or more indexes on the table .

CREATE INDEX ADMIN.NEW_TABLE_IX ON ADMIN.NEW_TABLE(EMPLOYEE_ID);

In the example above , we have shown how to create our index .

Let’s look at the explain plan of our query , which lists the user whose name is melike:

SELECT * FROM ADMIN.NEW_TABLE WHERE FIRST_NAME='melike';

And now let’s index the same column and look at the explanation plan of the above query :

CREATE INDEX ADMIN.NEW_TABLE_FIRSTNAME ON ADMIN.NEW_TABLE (FIRST_NAME);

AFTER INDEX

Let’s look at the explain plan of our query that we just run after creating an index :

SELECT * FROM ADMIN.NEW_TABLE WHERE FIRST_NAME='melike';

CREATE FUNCTION BASED INDEX

In order to use these indexes , we must query them as they were created .

CREATE INDEX ADMIN.NEW_TABLE_ISIM ON ADMIN.NEW_TABLE (UPPER(FIRST_NAME));

SELECT * FROM ADMIN.NEW_TABLE WHERE UPPER(FIRST_NAME)='MELIKE' ;

SYNONYM

They are aliases given to Synonym database objects , allowing the use of abbreviated names . In short , they are expressions that make it easier for other users to reach an object . They can be seen by everyone (because they are created as public) .

CREATE [PUBLIC] SYNONYM synonym FOR object ;

For example , let’s rename the admin.new_table table :

CREATE SYNONYM YENIKISI FOR ADMIN.NEW_TABLE;;

SELECT * FROM YENIKISI;

DROP

With the drop command , many database objects are deleted from the database . If we add the PURGE command to the table , they are deleted without being dumped in the trash . Below we wanted to delete the synonym we just created :

DROP SYNONYM YENIKISI;

See you in my next post.