Tables & External Tables

Q1. What are the various constraints that can be applied to a Table?

Answer. NULL, NOT NULL, CHECK, DEFAULT, Primary Key & Foreign Key.

Q2. What is the purpose of a CLUSTER?

Answer. Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, Oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.

Q3. What is Global Temporary Tables (GTT)?

Answer. Applications often use some form of temporary data store for processes that are too complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. From Oracle 8i onward, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables. The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction.

The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1 NUMBER,
  column2 NUMBER) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1 NUMBER,
  column2 NUMBER) ON COMMIT PRESERVE ROWS;

Following are the features of Temporary Table:

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no effect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.

Q4. Can dual table be deleted, dropped or altered or updated or inserted?

Answer. Yes, but not advisable.

Q5. If another table same as dual is created would it act like dual?

Answer. Yes, provided it has only one record in it.

Q6. What do you mean by External Tables?

Answer. External table is the table created in Oracle but its organization is external, i.e., the data is not available in the database, the data is instead stored in some external file.

Example:

CREATE OR REPLACE DIRECTORY ext_tab_data AS '/data';
  
CREATE TABLE employees_ext
(
  emp_code   VARCHAR2(5),
  Emp_name   VARCHAR2(100),
  birth_date VARCHAR2(15)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      emp_code CHAR(5),
      emp_name CHAR(100),
      birth_date CHAR(15)
    )
  )
  LOCATION ('EmpDetails1.txt', 'EmpDetails2.txt')  
 )
 PARALLEL 5
 REJECT LIMIT UNLIMITED;
  
 SELECT *
   FROM employees_ext
  ORDER BY emp_code; 

Following table depicts the contents of the file like "EmpDetails1.txt"

EMP_CODE	EMP_NAME	BIRTH_DATE
E01	Amish Rana	11-Jan-1973
E02	Ancy Joce	15-Sep-1973
E03	Reena Tandon	23-Nov-1990
E04	Bilal Ahmed	03-Mar-1993
E05	Sony Ali	08-May-1974
E06	John Dmelo	20-Aug-1980
E07	Lata Nair	30-Jul-1976

Q7. Can a table have invisible columns?

Answer. Yes, from Oracle 12c tables can have invisible columns & column visibility can be altered as shown below.

ALTER TABLE accounts MODIFY full_name INVISIBLE;
ALTER TABLE accounts MODIFY full_name VISIBLE;

Q8. What is Partition?

Answer. Maintenance of large tables and indexes is very time and resource consuming and so is their data retrieval. Partitioning of tables and indexes can improve performance and maintenance in several ways.

Following are the types of partitioning:
•    Range Partitioning Tables
 CREATE TABLE invoices
 (invoice_no    NUMBER NOT NULL,
  invoice_date  DATE   NOT NULL,
  comments      VARCHAR2(500))
 PARTITION BY RANGE (invoice_date)
 (PARTITION invoices_q1 VALUES LESS THAN 
    (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION invoices_q2 VALUES LESS THAN
    (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION invoices_q3 VALUES LESS THAN
    (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION invoices_q4 VALUES LESS THAN
    (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

•    Hash Partitioning Tables
 CREATE TABLE invoices
 (invoice_no    NUMBER NOT NULL,
  invoice_date  DATE   NOT NULL,
  comments      VARCHAR2(500))
 PARTITION BY HASH (invoice_no)
 PARTITIONS 4
 STORE IN (user1, user2, user3, user4);

•    Composite Partitioning Tables
 CREATE TABLE invoices
 (invoice_no    NUMBER NOT NULL,
  invoice_date  DATE   NOT NULL,
  comments      VARCHAR2(500))
 PARTITION BY RANGE (invoice_date)
 SUBPARTITION BY HASH (invoice_no)
 SUBPARTITIONS 8
 (PARTITION invoices_q1 VALUES LESS THAN 
    (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
  PARTITION invoices_q2 VALUES LESS THAN 
    (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
  PARTITION invoices_q3 VALUES LESS THAN
    (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
  PARTITION invoices_q4 VALUES LESS THAN 
    (TO_DATE('01/01/2002', 'DD/MM/YYYY'));

Q9. What is Truncate Partition?

Answer. If the partition contains data and global indexes, then use one of the following methods to truncate the table partition.

Option 1– This method is most useful if the partition that you are truncating has good amount of data in the table. But rebuilding indexes is going to take time if there is huge data for which indexes are to be rebuild.

ALTER TABLE invoices TRUNCATE PARTITION invoices_q1;
ALTER INDEX invoices_pk REBUILD;

Option 2– The 1st statement will avoid the generation of logs and make the process faster. The delete statement here updates any global indexes & fires triggers. Then proceed with truncation. But this will still be slower for partitions containing large amount of data.

ALTER TABLE invoices MODIFY PARTITION invoices_q1 NOLOGGING;
DELETE FROM invoices PARTITION (invoices_q1);
ALTER TABLE invoices TRUNCATE PARTITION invoices_q1;

Option 3– Specifying UPDATE INDEXES in the ALTER TABLE statement causes the global index to be truncated at the time the partition is truncated.

ALTER TABLE invoices TRUNCATE PARTITION invoices_q1 UPDATE INDEXES;

Q10. Explain Generated clause.

Answer. Oracle 12c introduced a new clause called Generated used to create identity column. Following syntax is to be used with Create Table command.

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

GENERATED ALWAYS: create a value for the identity column. Attempt to insert a value into the identity column will cause an error.

GENERATED BY DEFAULT: creates a value for the identity column if you provide no value. If you provide a value, Oracle will insert that value into the identity column. Oracle will issue an error if you insert a NULL value into the identity column.

GENERATED BY DEFAULT ON NULL: creates a value for the identity column if you provide a NULL value or no value at all.

Following are the other options for the identity column.

START WITH initial_value specifies the default initial value which can be 1.
INCREMENT BY internval_value defines the interval between generated values which can be 1.
CACHE defines number of values that Oracle should generate beforehand to improve the performance.

Examples:

CREATE TABLE identity_demo1(
id NUMBER GENERATED ALWAYS AS IDENTITY,
description VARCHAR2(100) NOT NULL);

CREATE TABLE identity_demo2(
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
description VARCHAR2(100) not null);

CREATE TABLE identity_demo(
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
description VARCHAR2(100) not null);

CREATE TABLE identity_demo(
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 101 INCREMENT BY 2,
description VARCHAR2(100) not null); 

Q11. What do you mean by Logical Delete & Physical Delete?

Answer. Logical Delete & Physical Delete refers to column deletion from the table. Prior to Oracle 8i, it was necessary to drop the entire table and rebuild it, if you wanted to drop a column of a table.

Logical Delete – Dropping column from large tables can be very time & resource consuming. To overcome this issue logical delete can be helpful.

ALTER TABLE table_name SET UNUSED (column_name1, column_name2)

After this the columns will no longer be visible to the user. Later when time permits these columns can be physically deleted using the following.

ALTER TABLE table_name DROP UNUSED COLUMNS;

Physical Delete – To physically drop a column you can use one of the following syntax.

ALTER TABLE table_name DROP (column_name1, column_name2);

Note: Dropping a column from a table will cause all unused columns in that table to be dropped at the same time.

Q12. What are LOBs?

Answer. Large Objects (LOBs) are set of datatypes that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

BLOB (Binary Large Object) stores any kind of data in binary format, used for data such as image, audio, and video.

CLOB (Character Large Object) stores large strings or documents.

NCLOB (National Character Set Large Object) stores large strings or documents in the National Character Set.

BFILE (External Binary File) is not stored in the database instead it is stored in host OS’s file system, but accessible from database tables. BFILEs are used to store static data such as images, that does not need to be manipulated in applications.

Any kind of operating system file can be stored in a BFILE. For example, you can store character data in a BFILE and then load the BFILE data into a CLOB specifying the character set upon loading.

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.