Creating Other Schema Objects

Hi ,

In this article I will tell you what is view , how to create .

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 .

VIEW FROM DATA

  • Queries are drawn from the same table as a query .

CHANGING VIEW

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

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 :

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

Use of :

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 .

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 .

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:

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

AFTER INDEX

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

CREATE FUNCTION BASED INDEX

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

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 :

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 :

See you in my next post.