Results 1 to 2 of 2

Thread: Performance

  1. #1
    Join Date
    Oct 2005
    Posts
    39

    Unanswered: Performance

    Hello Experts,

    I am a bit confused about the Index of the below mentioned table. Not getting the concept. The table TEMP_PPL_WART is not having any index on it.

    If I collect database stats it's taking more time.

    The below mentioned query is taking around 5 seconds in 35 executions.

    DELETE FROM TEMP_PPL_WART ;

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 35 0.00 0.00 0 0 0 0
    Execute 35 0.15 4.81 4631 7490 210 0
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 70 0.15 4.81 4631 7490 210 0

    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 100

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 DELETE TEMP_PPL_WART
    1 TABLE ACCESS FULL TEMP_PPL_WART

    Should I create an Index on this table to reduce the time..?? If yes then, on what column. Structure is pasted below :

    SQL> desc TEMP_PPL_WART
    Name Null? Typ
    ----------------------------------------- -------- ----------------------------
    PB_ID NOT NULL NUMBER(8)
    SAT NOT NULL NUMBER(1)
    WART NOT NULL VARCHAR2(5)
    WART_PF NOT NULL VARCHAR2(8)
    T1 VARCHAR2(6)
    T2 VARCHAR2(6)
    ABSOLUTE_TIME DATE
    RELATIVE_TIME NUMBER(4)
    RELATIVE_TIME_EXTRA_SECOND NUMBER(2)
    VMAX NUMBER
    LADE NUMBER
    ROW_ID NUMBER

    Will appreciate any suggestion/help.

    Regards, Raj

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    When you are doing a full table delete, how is an index going to help? If you want a quick way to empty the table use

    TRUNCATE TABLE TEMP_PPL_WART;

    Please be aware this statement can NOT be rolled back and it will commit any outstanding transactions, but it will work in only a couple of seconds no matter how big the table is.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •