External Table and the whole story

Oracle External Tables is introduced in Oracle 9i. External tables are used to load data in to database. This is similar to oracle SQLLDR mechanism to load data.

In Oracle 10g, using external tables, we can also unload data.
There are the following scenarios where we cant use Oracle external tables.
1. If the source need to be loaded via network. External table requires the source file need to be in the database server. If not we need to use SQLLDR to load data.
2. LOBs cant be loaded using external tables

The following are the advantages of external tables over SQLLDR.
1. External tables open up the entire SQL set of functionality to data loading21
2. The ability to use complex WHERE conditions to selectivity load data. SQLLDR has WHEN clause but there are limitations like only = and 'AND' can be used. We cant use 'OR' or
other relational operator.
3. The ability to MERGE data.
4. Easier multiple inserts using INSERT.

The external table synax would be as below
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'D:\srikar\oracle'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EMP2"
(
"EMPNO" NUMBER,
"EMPNAME" VARCHAR2(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'empdata.bad'
LOGFILE 'logg.txt_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY ",",
"EMPNAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'empdata.csv'
)
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO EMP2
(
EMPNO,
EMPNAME
)
SELECT
"EMPNO",
"EMPNAME"
FROM "SYS_SQLLDR_X_EXT_EMP2"

The above code is obtained from the control file in the command line with below command

D:\srikar\oracle>sqlldr scott/abcd@orcl control=emp_ctl.txt log=logg.txt external_table=generate_only

That produces the whole DDL in the logg.txt file
Here, 'external_table' takes 3 parameters
NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED).
'GENERATE_ONLY' creates the code as if it is executed (It does not execute the code)
'EXECUTE' executes the code hence loads the data.

If we look at the create external table script,

CREATE TABLE "SYS_SQLLDR_X_EXT_EMP2"
(
"EMPNO" NUMBER,
"EMPNAME" VARCHAR2(2)
)
ORGANIZATION external --> this is to make the table as external
(
TYPE oracle_loader
--> driver to load data,'DATA_PUMP' is used to unload data in 10g
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --> directory name created
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'empdata.bad'
LOGFILE 'logg.txt_xt'
READSIZE 1048576 --> Buffer size to load the input file if we use shared memory - --the size is taken from SGA else from PGA
FIELDS TERMINATED BY "," LDRTRIM --LDRTRIM is loader trim, we can also use ltrim, -- rtrim, lrtrim
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY ",",
"EMPNAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'empdata.csv'
)
)REJECT LIMIT UNLIMITED

If we explicitly create load data, first of all we need to create directory
the syntax as below

create directory dd as 'D:\srikar'
The oracle software is the one which loads/writes data. So the oracle software must have permission ('read' in windows, 'read and execute' in UNIX) to read the directory. 'Write' permission on the directory where we save 'log', 'bad' and 'discard' file

Unindexed foreign keys

Unindexed foreign keys makes the child table get locked and also it makes the performance down.
There is a very beautiful article written by Tom kyte on Unindexed foreign kyes.
The link is here

and if you are not able to access, the content goes here
-------------------------------------------------------------------------------
Unindexed Foreign Keys

Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

To read about this issue, please see the Concepts Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys.

The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.

Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.

So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:


SQL> @unindex

STAT TABLE_NAME COLUMNS COLUMNS
---- ------------------------------ -------------------- --------------------
**** APPLICATION_INSTANCES AI_APP_CODE
ok EMP DEPTNO DEPTNO



The **** in the first row shows me that I have an unindexed foreign key in the table APPLICATION_INSTANCES. The ok in the second row shows me I have a table EMP with an indexed foreign key.
------------------------------------------------------------------------

The script that Tom is used can be found here to find the unindexed foreign keys

select table_name,
constraint_name,
cname1 || nvl2(cname2, ',' || cname2, null) ||
nvl2(cname3, ',' || cname3, null) ||
nvl2(cname4, ',' || cname4, null) ||
nvl2(cname5, ',' || cname5, null) ||
nvl2(cname6, ',' || cname6, null) ||
nvl2(cname7, ',' || cname7, null) ||
nvl2(cname8, ',' || cname8, null) columns
from (select b.table_name,
b.constraint_name,
max(decode(position, 1, column_name, null)) cname1,
max(decode(position, 2, column_name, null)) cname2,
max(decode(position, 3, column_name, null)) cname3,
max(decode(position, 4, column_name, null)) cname4,
max(decode(position, 5, column_name, null)) cname5,
max(decode(position, 6, column_name, null)) cname6,
max(decode(position, 7, column_name, null)) cname7,
max(decode(position, 8, column_name, null)) cname8,
count(*) col_cnt
from (select substr(table_name, 1, 30) table_name,
substr(constraint_name, 1, 30) constraint_name,
substr(column_name, 1, 30) column_name,
position
from user_cons_columns) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4, cname5,
cname6, cname7, cname8)
and i.column_position <= cons.col_cnt
group by i.index_name)


Thanks Tom for all the help

Select privileges on SYS schema views and objects

Here, I explain about 'select_catalog_role' and 'select any dictionary' privileges and the difference between them.

SQL> show user;
User is "scott"

SQL> select * from dba_objects where rownum<=1;
select * from dba_objects where rownum<=1
ORA-00942: table or view does not exist

SQL> select * from v$session where rownum<=1;
select * from v$session where rownum<=1
ORA-00942: table or view does not exist
Above, SCOTT was not able to access sys schema views (v$session, dba_objects)

SQL> conn system/YYYY@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system

SQL> show user
User is "system"

SQL> grant select_catalog_role to scott;
Grant succeeded

[from scott schema]
SQL> select count(*) from v$session;
COUNT(*)
----------
44

SQL> select count(*) from dba_objects;

COUNT(*)
----------
72517

Now, scott is able to access sys schema views.

So, select_catalog_role grants user to select sys schema views (only views, no other objects like table)

[from scott schema]
SQL> select count(*) from sys.OBJ$;
select count(*) from OBJ$
ORA-00942: table or view does not exist

scott is not able to access sys schema objects

[from system schema]
SQL> revoke select_catalog_role from scott;
Revoke succeeded

SQL> grant select any dictionary to scott;
Grant succeeded

[from scott schema]
SQL> select count(*) from v$session;
COUNT(*)
----------
45

SQL> select count(*) from sys.obj$;

COUNT(*)
----------
73425

So, SCOTT is able to select rows from sys schema views and tables

So, the conclusion is,

'select_catalog_role' provides access to all SYS schema views only
'select any dictinaly' provides access to all SYS schema objects (views and tables etc..)