CODEWITHSUNDEEP
oracle-databaseplsql
refresh
refresh

Reputation: 1329

PL/SQL Creating a table with indexes

I have the following PL/SQL code:

    DROP TABLE TAB_PARAM;

BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE TAB_PARAM
  (
     TABLE_OWNER  VARCHAR2(30) NOT NULL,
     TABLE_NAME   VARCHAR2(30) NOT NULL,
     COLUMN_NAME  VARCHAR2(30) NOT NULL,
     PATTERN VARCHAR2(1024),
     TYPE_METHODE VARCHAR2(30) NOT NULL,
     SEPARATEUR VARCHAR2(20),
     ID VARCHAR2(30),
    CONSTRAINT PK_TAB_PARAM PRIMARY KEY (TABLE_OWNER,TABLE_NAME,COLUMN_NAME) USING INDEX TABLESPACE IND_PARC_256M NOLOGGING
  )
  TABLESPACE TAB_PARC_256M NOLOGGING NOCACHE NOMONITORING NOPARALLEL';

commit;
END;
/

I don't understand the part :

CONSTRAINT PK_TAB_PARAM PRIMARY KEY (TABLE_OWNER,TABLE_NAME,COLUMN_NAME) USING INDEX TABLESPACE IND_PARC_256M NOLOGGING

Nor the part:

TABLESPACE TAB_PARC_256M NOLOGGING NOCACHE NOMONITORING NOPARALLEL';

I know that it is setting the ID as primary key of TAB_PARAM but then I do not get the index part. Can anyone help me understand this code please?

Upvotes: 0

Views: 8061

Answers (3)

Moudiz
Moudiz

Reputation: 7377

Edit. from oracle doc

Oracle Database uses an existing index if it contains a unique set of values before enforcing the primary key constraint. The existing index can be defined as unique or nonunique. When a DML operation is performed, the primary key constraint is enforced using this existing index.

If there already index hitting the pk columns , oracle will used it, else then Oracle Database generates a unique index.

so you can create a primary key in oracle without an index if there was already index for that columns , specifying an index is for performance issue. The purpose of an index in a table is to 'read' the data faster.

From the oracle document

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows.

As for the TABLESPACE, its where the database object exists, so when you create a table you specify in which tablespace you want it to exists. Read more here oracle document

As for NOLOGGING NOCACHE NOMONITORING, so the table not to be logged in redo logs or cached, also related to performance issue.

Upvotes: 1

Frank
Frank

Reputation: 881

Sometimes you can create a table in a disposable way , for example you need to read data via sqlloader from file and insert in table , after that you will select all records from table . This operation doesnt need indexes and doesnt need primary key . Anyway it is always good to create primary key when you create a table and Oracle will create index for you . You can create more indexes whenever you want since the table was created . Now suppose you wanna update the table using where condition on a field who is not a primary key , it could be a better decision create an index on that field . In addition when you create indexes or table , you can associate tablespaces (created before) and could be good practice separate tablespaces for tables and indexes . All those operations are DDL Statements . In Oracle you can check datafiles , tables and indexes with those queries select * from dba_data_files; select * from dba_tables; select * from dba_indexes;

Upvotes: 2

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

For primary key oracle implicitly creates unique index in table's tablespace. This part USING INDEX TABLESPACE allow us to indicate tablespace for this implicitly index.

NOLOGGING - data is modified with minimal logging (to mark new extents invalid and to record dictionary changes).

NOCACHE - Oracle doesn't store blocks in the buffer cache.

NOPARALLEL - Parallel execution is not allowed on this table.(Default value)

NOMONITORING - Disable statistics collection. Now is deprecated. There is other mechanism to collect statistic

Upvotes: 2

Related Questions