IT Tutorial

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

VIEW TYPES

CREATING VIEW

Now let’s create our own view .

CREATE VIEW test as select * from sh.customers;

VIEW FROM DATA

SELECT * FROM TEST;

CHANGING VIEW

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

VIEW ON DML

SEQUENCE

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

Sequence can be accessed quickly via cache memory .

Use of :

SELECT seq_ornek.CURRVAL FROM SH.CUSTOMERS;

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

CHANGING THE SEQUENCE

ALTER SEQUENCE SEQ_ORNEK INCREMENT BY 2 MAXVALUE 10000

NOCACHE

NOCYCLE;

INDEX

Objects used by Oracle to fetch a row faster .

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 .

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

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

AFTER INDEX

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

CREATE FUNCTION BASED INDEX

 

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 ;

 

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.

 534 views last month,  3 views today

Exit mobile version