SQL base index, Flashback, temp table (18)

To create an index:

Automatic

– Create PRIMARY KEY

– Create a UNIQUE KEY

Manual

–create INDEX Statement

–create TABLE Statement

Create index in CREATE TABLE statement

CREATE TABLE New_emp (employee_id number (6) primary key using index

(CREATE INDEX EMP_ID_IDX on

New_emp (employee_id)),

First_Name varchar2 (20),

Last_Name VARCHAR2 (25));

Select Index_name, table_name from user_indexes WHERE table_name = ' new_emp ';

Function-based indexing

• A function-based index is an expression-based index

• An index expression is composed of columns, constants, SQL functions, and user-defined functions.

Create INDEX UPPER_DEPT_NAME_IDX on DEPT2 (Upper (Department_name));

SELECT * from Dept2 where upper (department_name) = ' SALES ';

Delete Index

Use the DROP INDEX command to remove an index from the data dictionary:

Drop index Index;

To remove the UPPER_DEPT_NAME_IDX index from the data dictionary:

Drop index UPPER_DEPT_NAME_IDX;

To delete an index, you must be the owner of the index or have DROP any index permission

drop table Dept80 Purge;

FLASHBACK TABLE Statement

• A single statement can be restored to a specified point in time.

• Recovers data from tables and related indexes and constraints.

• You can restore a table based on a point in time or a system change number (SCN).

Repair tool for which the table was modified unexpectedly:

– Table reverts to an earlier point in time

– Benefits: Ease of use, availability, fast execution

– Execution in position (is performed on place)

Grammar:

Flashback Table[schema.] table[,

[Schema.] Table] ...

to {timestamp | scn} expr

[{enable | disable} triggers];

Example:

drop table emp2;

Select Original_name, operation, Droptime from RecycleBin;

Flashback table EMP2 to before drop;

Temp table

Create a temporary table

Create global temporary table cart on commit delete rows;

Create global temporary table Today_sales

On commit preserve rows as

SELECT * FROM Orders

where order_date = Sysdate;

External table

Create a directory for an external table

Create directory objects that correspond to directories on the file system where the external data source resides.

Create or Replace directory Emp_diras '/.../emp_dir ';

Grant Read on directory Emp_dir to Ora_21;

Create an external table

CREATE TABLE <table_name>

(<col_name> <datatype>)

Organization External

(Type <access_driver_type>

Default directory <directory_name>

Access parameters

(...) )

Location (' <location_specifier> ')

Reject Limit [0 | <number> | unlimited];

Creating an external table using the Oracle_loader driver

CREATE TABLE Oldemp (

fname char (+), lname char (25))

Organization External

(Type Oracle_loader

Default directory Emp_dir

Access parameters

(Records delimited by newline

Nobadfile

Nologfile

Fields terminated by ', '

(fname position (1:20) Char,

LName position (22:41) char))

Location (' Emp.dat '))

Parallel 5

Reject limit 200;

Querying external tables

SELECT * FROM Oldemp

To create an external table using the Oracle_datapump driver:

CREATE TABLE Emp_ext

(employee_id, first_name, last_name)

Organization External

(

Type oracle_datapump

Default directory Emp_dir

Location

(' Emp1.exp ', ' emp2.exp ')

)

Parallel

As

Select employee_id, first_name, last_name

From employees;

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1910401

SQL base index, Flashback, temp table (18)