Oracle SQL Tutorial -7 Creating Other Schema Objects

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.