CODEWITHSUNDEEP
oracle-databaseoracle11goracle12c
fledgling
fledgling

Reputation: 1051

Are there any alternative to cursor in Oracle for performance improvements?

I have the below query which truncates specific tables in a specific schema. Is there a better option to do this without cursor? This query takes a a few minutes to execute as the schema is big. 

DECLARE TYPE cur_typ IS REF CURSOR;
c           cur_typ;
qry_str   VARCHAR2(1000);
table_name    VARCHAR2(50);    
BEGIN
qry_str := \'SELECT TABLE_NAME FROM USER_ALL_TABLES
    WHERE (TABLE_NAME like \'\'STG_%\'\'
      OR TABLE_NAME like \'\'S_%\'\' )\';

OPEN c FOR qry_str;
LOOP
    FETCH c INTO table_name;
    EXIT WHEN c%NOTFOUND;
    EXECUTE IMMEDIATE \'TRUNCATE TABLE \' || table_name; 
END LOOP;
CLOSE c;

Do any of you have any faster alternative? Thanks for the help.

Upvotes: 0

Views: 3684

Answers (3)

Husqvik
Husqvik

Reputation: 5809

If there is more than handful of tables, TRUNCATE is the bigger issue than the query. It's DDL, pretty heavy statement.

Upvotes: 3

swstephe
swstephe

Reputation: 1910

Have you tried an inline cursor?

 BEGIN
    FOR row in (
        SELECT TABLE_NAME FROM USER_ALL_TABLES
         WHERE TABLE_NAME like 'STG_%'
            OR TABLE_NAME like 'S_%'
    ) LOOP
        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || row.table_name; 
    END LOOP;
END;

That doesn't get rid of the cursor, just hides it under the covers, but is more readable. If you are seeing performance problems, try tuning the query.

Upvotes: 2

baklarz2048
baklarz2048

Reputation: 10938

You can use FORALL. You can also store tables you want to truncate in separate table and do not query dictionary table.

Upvotes: -2

Related Questions