Forum Stats

  • 3,852,695 Users
  • 2,264,128 Discussions
  • 7,905,120 Comments

Discussions

procedure to create indexes on all columns --- let the flames begin

Jason_S
Jason_S Member Posts: 102 Blue Ribbon
edited Jan 28, 2014 2:53PM in SQL & PL/SQL

Yes you read it correctly.

Is it possible to create a procedure that will create an index on every column in a table without specifying the column names?

     exec index_all_cols('tbl_name', 'parallel');

You ask why? There is no reason that I can provide that will quench the flames coming my way. But, it is ultimately because (1) I am tired of using excel to generate create index statements, and (2) I want speed at the cost of everything else, (3) I don't like using a mouse ... I know, let the hate begin.

Jason

Oracle 10g

... at least I didn't ask to index every column in every table in my schema!

Tagged:

Best Answer

  • Jason_S
    Jason_S Member Posts: 102 Blue Ribbon
    Answer ✓

    Got it working !!!!

    CREATE OR REPLACE PROCEDURE index_all_cols (pTABLENAME IN varchar2)

    IS

        CURSOR c1 IS SELECT distinct COLUMN_NAME FROM USER_TAB_COLS WHERE TABLE_NAME = pTABLENAME;

    BEGIN

        FOR c IN c1 LOOP

            BEGIN EXECUTE IMMEDIATE 'create index X_'||c.COLUMN_NAME||' ON '||pTABLENAME||' ('||c.COLUMN_NAME||') logging parallel';

    --              EXCEPTION WHEN others THEN NULL;

                  END;

        END LOOP;

        COMMIT;

    END index_all_cols;

    /

    ... and not even one flame

Answers

  • Jason_S
    Jason_S Member Posts: 102 Blue Ribbon
    Answer ✓

    Got it working !!!!

    CREATE OR REPLACE PROCEDURE index_all_cols (pTABLENAME IN varchar2)

    IS

        CURSOR c1 IS SELECT distinct COLUMN_NAME FROM USER_TAB_COLS WHERE TABLE_NAME = pTABLENAME;

    BEGIN

        FOR c IN c1 LOOP

            BEGIN EXECUTE IMMEDIATE 'create index X_'||c.COLUMN_NAME||' ON '||pTABLENAME||' ('||c.COLUMN_NAME||') logging parallel';

    --              EXCEPTION WHEN others THEN NULL;

                  END;

        END LOOP;

        COMMIT;

    END index_all_cols;

    /

    ... and not even one flame

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    wouldn't it be more appropriate to index every possible combination of columns to get all concatenated index that could be of interest...?

    You know that indexes have a massive impact on DML and that they are not helpful if you don't have the necessary limiting conditions?

    Technically you could use a pl/sql block with a loop over user_tab_cols that constructs an execute immediate for every row. I will not add code here - cause I think the operation is not a particularly good idea. It would be less dangerous to generate the create statements by a select 'create index xxx on ' || table_name || '.' || column_name || ');' from user_tab_cols where ... - copy the result to your editor and delete all the results that are useless.

    But I am not able to imagine a situation where an exec index_all_cols() could be helpful at all...

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Jan 28, 2014 3:18PM

    I hope this piece of code will not get somewhere into production: even if you should know what you do others could just copy and use it without further considerations.

    Besides: what do you want to commit (given that 'create index' is DDL and contains an implicit commit)? What shall the strange "error handling" do?

This discussion has been closed.