Forum Stats

  • 3,751,464 Users
  • 2,250,366 Discussions
  • 7,867,433 Comments

Discussions

index

948403
948403 Member Posts: 6
edited Jul 12, 2012 1:40AM in SQL & PL/SQL
create index ts_portfolio_dm.instrument_ref_dim_indx1 on ts_portfolio_dm.instrument_reference_dim(instrument_type_dvd, revenue_source_dvd, pricing_dt, instrument_dim_key);

when I execute this command the process is taking too long to create an index .is there any additional coding that can help create index fast.
Tagged:

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    945400 wrote:
    create index ts_portfolio_dm.instrument_ref_dim_indx1 on ts_portfolio_dm.instrument_reference_dim(instrument_type_dvd, revenue_source_dvd, pricing_dt, instrument_dim_key);

    when I execute this command the process is taking too long to create an index .is there any additional coding that can help create index fast.
    The time taken to create an index depends largely on the the volume of data in the table and number of columns being indexed.
  • fjfranken
    fjfranken Member Posts: 1,268 Silver Badge
    Hi

    You could add the "NOLOGGING" option.
    This might speed it up a bit, because no redo logging is written for the index-creation. But it is only a small improvement

    Your statement would then look like:
    CREATE INDEX ts_portfolio_dm.instrument_ref_dim_indx1
       ON ts_portfolio_dm.instrument_reference_dim (instrument_type_dvd
    ,                                               revenue_source_dvd
    ,                                               pricing_dt
    ,                                               instrument_dim_key) nologging ;
    Maybe also add the PARALLEL clause to force parallel execution. But for the remainder I agree with the other reply. It is simply the amount of data that makes the time here
  • 948403
    948403 Member Posts: 6
    Hi ,

    thanks for the reply .

    could you please give me the statement that contains both

    1.parllel creation 2.no logging.

    Is there any further improvements that can be done on this .
    The table is very huge and lot of procedures use this .Hence the reson we are not able to allow the statement to execute for more time .
    cheers,
    P
This discussion has been closed.