Oracle Analytical Question

Index:
Scenario -1 

• Suppose I have a table EMP with three columns (EMP_ID, EMP_NAME, SALARY), and I create a unique composite index on this table's two columns (EMP_ID, EMP_NAME). Now I write a query in which the query's where clause contains two columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'). Which types of index scans use Oracle?

        Answer: Oracle will use INDEX UNIQUE SCAN.-TABLE ACCESS BY INDEX ROWID

• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_ID=100 ). Which types of index scans use Oracle?
  
  
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_NAME='BASIR'). Which types of index scans use Oracle?

      Answer: Oracle will use TABLE ACCESS FULL 

• If I write a query in which the query's where clause contains three columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'  AND SALARY=3000). Which types of index scans use Oracle?

        Answer: Oracle will use INDEX UNIQUE SCAN.- TABLE ACCESS BY INDEX ROWID

Scenario -2

• Suppose I have a table EMP with three columns (EMP_ID, EMP_NAME, SALARY), and I create a unique composite index on this table's two columns (EMP_ID, EMP_NAME). Now I write a query in which the query's where clause contains two columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'). Which types of index scans use Oracle?
        Answer: Oracle will use INDEX RANGE SCAN.
        TABLE ACCESS BY INDEX ROWID BATCHED
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_NAME='BASIR'). Which types of index scans use Oracle?
      Answer: Oracle will use TABLE ACCESS FULL 
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_ID=100 ). Which types of index scans use Oracle?
      Answer: Oracle will use INDEX RANGE SCAN.-
                TABLE ACCESS BY INDEX ROWID BATCHED


Index fast full scan :
If we create primary key constraints or create a unique composite index and specify that particular column as a not null. Then if we write a query about which column is part of the composite index, then it will use-Index fast full scan.
Suppose: I have an EMP Table with three columns (id not null, name, salary)
Create a unique composite Index (ID, Name). 
My query was SELECT ID, NAME FROM EMP. Then it will use Index fast full scan.

SQL Loader & External File:

SQL Loader: SQL loader is a utility provided by Oracle to load data from external files into the Oracle database. This is a command line utility. To do this, first, we need to create database directories. Then need two files one is a data file, and another is a control file. The control file will have information about the name of the source file and metadata information about the source file. For example, The name of the source file, how the source file is formatted, and what should be a target table.

The sample Data File is source_data.txt, and the data format

ID, NAME, ADDRESS, SALARY

1001,Hasan Mahmud,Mirpur-1 Dhaka,100000,

1002,Rasel Hasan Mahmud,Mirpur-1 Dhaka,500000,

1003,Kabir Hasan Mahmud,Mirpur-1 Dhaka,800000,

Control file: control_file.txt

OPTIONS(SKIP=1)

LOAD DATA

INFILE 'SampleFile.txt'

TRUNCATE INTO TABLE EMP_DTL

FIELDs TERMINATED BY ','

(ID, NAME, ADDRESS, SALARY)

SQL Loader Command: Got to Directory Location. Then run this command

sqlldr hrms/hrms@orcl control=control_file.txt

External Table: External table is another way to read information from external files to Oracle Database. For this, first, we need to create directories and put source files here. Another one is to create an external table. This particular file reads data from an external file. Actually, the external table will not store any data. When we call an external table then, it will read data from external sources and show this data.Table Syntex:

CREATE TABLE EMP_DTL_EXTERNAL

(

   ID        NUMBER,

   NAME      VARCHAR2 (100),

   ADDRESS   VARCHAR2 (100),

   SALARY    NUMBER

)

ORGANIZATION EXTERNAL

   (TYPE ORACLE_LOADER

         DEFAULT DIRECTORY DB_BACKUP

            ACCESS PARAMETERS

               (RECORDS DELIMITED BY NEWLINE

 FIELDS TERMINATED BY ',')

         LOCATION ('SampleFile.txt'))

         REJECT LIMIT UNLIMITED;

SELECT * FROM EMP_DTL_EXTERNAL