Can I drop the partitioned index then recreate it?

Posted on 2007-10-17
Last Modified: 2008-01-09
If one partitioned table has one partitioned index, can I drop the partitioned index then recreate it?
The user said in Oracle it's quite different to create partitioned index before or after importing data. He said the query would run fast when creating partitioned index after importing data. Is it true?  
Question by:luyan
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    <If one partitioned table has one partitioned index, can I drop the partitioned index then recreate it?

    if you have to load o lot of data, ie the full table data, it will be eventually be faster to drop the index before, and recreate if afterwards.
    now, do you really load data from many/all partitions of that table?

    LVL 21

    Accepted Solution

    I agree and Your client is right as index ,any part-d or not interfers with dml performed in importing the data so You'd better to drop the index ,to run the imort and then recreate the index.To be certain You're on the same page,thre's also a disable option for the index,so You could disable it first then import and enable
    LVL 18

    Expert Comment

    with a partitioned table, you can simply "drop index <index_name>" to remove the index.
    creating an index is also no different from a normal create index statement.  it will result in a locally partitioned index with teh partitioning aligned to the partitions of the parent table.
    LVL 28

    Expert Comment

    syntax is :
    create index my_partition_idx on my_par_table ( part_column_name ) local;

    This is to create a partitioned index whose partitioning will be same as that of the table.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Oracle date format issue 36 80
    Oracle SQL Nested Select 3 50
    SAP and ODBC links 2 66
    Generate HTML charts from SQL 4 38
    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now