SQL basic index, flashback, temporary table (XVIII)
Create an index:
Automatic
-create a 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 index
A function-based index is an expression-based index.
Index expressions consist 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 the index from the data dictionary:
Drop index index
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.
Restore the data in the table and related indexes and constraints.
The table can be restored based on a point in time or a system change number (SCN).
Repair tools for unexpected modifications of the table:
-the table is restored to an earlier point in time
-advantages: ease of use, availability, fast execution
-execution in place (Is performed in place)
Syntax:
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
Temporary watch
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 catalog for an external table
Create a DIRECTORY object that corresponds to the directory 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
(,)
Organization external
(type
Default directory
Access parameters
(.))
Location ('')
Reject limit [0 | | unlimited]
Use the ORACLE_LOADER driver to create external tables
Create table oldemp (
Fname char (25), 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
Query external tables
Select * from oldemp
Use the ORACLE_DATAPUMP driver to create external tables:
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
