9 Replies Latest reply: Sep 17, 2010 4:38 AM by 646155 RSS

    How to create index

    535516
      Hi

      what is index how to define it please suggest me
      and how reduce the performance using indexing

      Thanks

      gita
        • 1. Re: How to create index
          Anand...
          Would recommend you reading

          [http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#PFGRF004]


          Anand
          • 2. Re: How to create index
            SatishKandi
            Gita wrote:
            Hi

            what is index how to define it please suggest me
            and how reduce the performance using indexing
            Did I read that correctly? REDUCE?
            • 3. Re: How to create index
              Gurjas
              First of all refer to wiki would be better for you .. if you think index will reduce the performance.

              http://en.wikipedia.org/wiki/Index_%28database%29
              • 4. create index
                644649
                create index index_name on table_name(column_name)



                CREATE INDEX IDX_CUSTOMER_LOCATION
                on emp (empno, emp_cd)
                • 5. Re: How to create index
                  Anand...
                  Gita wrote:
                  Hi

                  what is index how to define it please suggest me
                  and how reduce the performance using indexing

                  Did I read that correctly? REDUCE?>

                  Good Catch :)

                  Anand
                  • 6. Re: How to create index
                    askraks
                    HI,

                    i am sorry, i think you are new to DBA.
                    Please refer the below link

                    [http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_indexing.htm#ADFNS005|http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_indexing.htm#ADFNS005]

                    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_indexing.htm#ADFNS005
                    Using Indexes in Application Development


                    Kind Regards,
                    Rakesh Jappy
                    • 7. Re: How to create index
                      Anand...
                      HI,
                      i am sorry, i think you are new to DBA.
                      Please refer the below link>

                      Yes, i am :) . Thanks :)

                      Anand
                      • 8. Re: How to create index
                        askraks
                        Opps i wanted to reply Gita :)

                        I am sorry Anad.

                        Kind Regards,
                        Rakesh jayappa
                        • 9. Re: How to create index
                          646155
                          CREATING INDEXES:

                               We can create an index in our own schema if the table or cluster to be indexed is in our schema, we should have the index privilege on the table to be indexed or we have the CREATE ANY INDEX system privilege. To create an index in the another schema we should have the CREATE ANY INDEX system privilege and the owner of the schema should have the sufficient quota on the tablespace or the user must be granted with the UNLIMITED TABLESPACE system privilege.

                          To create an index explicitly use the CREATE INDEX sql statement. Use the following statement to create an index

                          CREATE INDEX idx ON emp(rollno) TABLESPACE users STORAGE ( INITIAL 20K, NEXT 20K, PCTINCREASE 75);

                          here if we do not specify the storage parameters then the default storage options of the specified tablespace are automatically set.

                          CREATE UNIQUE INDEX:

                               Index that we create can be either unique or non unique. To create an unique index on the table use the following statement.

                          CREATE UNIQUE INDEX idx ON emp(rollno) TABLESPACE users;

                          Alternatively we can create the unique index by specify the unique integrity constraint on the desired column. When the unique integrity constraint is specified on a column then automatically unique index is created.

                          CREATING INDEX ASSOCIATE WITH A CONSTRAINT:

                               Oracle database enforces an UNIQUE or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created when the constraint is enabled. We can use an optional USING INDEX clause to exercise control over the index creation. This includes both when a constraint is defined or enabled.
                          We can specify the storage options for an index associated with a constraint (unique or primary key) using the USING INDEX option as follows :

                          CREATE TABLE emp (empno NUMBER PRIMARY KEY ) ENABLE PRIMARY KEY USING INDEX TABLESPACE USERS;

                          here the index is created in the tablespace users and the table is created in the users default permanent tablespace.

                          If we want to have more control over the indexes associated with the UNIQUE or PRIMARY KEY constraints the database also lets us specify an existing index that the database is to use to enforce the constraint or we can specify an CREATE INDEX statement that the database can use to create the index and enforce the constraint. These options are usually specified using the USING INDEX clause.

                          CREATE TABLE a (a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));

                          CREATE TABLE b(b1 INT, b2 INT, CoNSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

                          CREATE TABLE c(c1 INT, c2 INT);
                          CREATE INDEX ci ON c (c1, c2);
                          ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;