<<<<< while i'm here I think of another solution I don't know that will work or not... what if I create one table as staging area load the data index free and another table with index created on them...

as already said above, let your loading get over and then you have to create indexes. As you may know if the indexes are already created before loading, then there will be overhead for the database to maintain the indexes.

thanks for replay... well loading is the daily operation as I get around 8000+ files each day around 20GB and I have to load them everyday.... and index I have to speed up the queries so I have to have both at the same time...if I have to drop indexes and load the data everyday and then recreate them will create huge backlogs...

and if I wait for the load to finish that will take forever as there is 9000 files and I don't know till which file is loaded and maybe 6000 is loaded and if I wait for another 3000 to load it will take months as I mentioned earlier at this pace which is 1 file every half an hour 3000 files will be months....

may be if your tables are having constraints etc then that would add to further overhead. Disabling them before loading & enabling them after loading may help to speed up but you need to aware of the consequences etc by doing that.

thanks for replay... what could be the consequences if I do that...removing/disabling the indexes ... I don't have any constraint ... and also this is my daily operation any other suggestion would be highly appreciate to avoid such things...

Why did it become slow out of a sudden ?
were any new indexes created recently after which you are seeing slowness ?
Did the data volumes increase ?
Does your table has a lot of DML ( Insert , update , delete ) happening everyday or only inserts happen for loading data ?

I am thinking that indexes should have been there from day one if nothing has changed recently.

no the problem arises when I created index.... the indexes where not there... actually these data was in postgress before and I recently migrated them to oracle for more option and controll... I never had this issue in postgress even when index the loading was normal... but then I start loading them here in oracle and couple days was fine and things get this much slow when I created indexes...

now I removed the indexes and the loading is normal.... so since this is daily operation for me I at the end of the day I need the index to be present to speed up the queries... so if I do (alter index indexname unsable) is that the same as drop index indexname?

since it's my daily operation i'm thinking of automating the deleting index and load the data and then create index again... this will create timing issue that index will take... I have to see if index will take more time or loading or queries without index then decide... that is what it came in my mind... suggestion will be appreciate it...

table will be renewed every month... so for each month it will be different table... and this table is partitioned by date so to 28-31 partition based on the month like for feb there is 28+1 partition for max... the tablespace for table and index are different from each other and they are in different disk I did this to optimize the performance ....

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

>>but is "alter index indexname unsable" is the same as "drop index indexnam" in terms of:

From what I've read, making it unusable and a rebuild can take advantage of some of what is already there.

Dropping it causes a complete rebuild.

You will need to test to see which one is actually better for your specific situation.

I think a lot would have to do with the type of index: Is it global or local to the partition? You might just be able to work with the individual partition index if it local but I've not done a lot with partitioning.

>>are they doing the same practice of doping or altering index every time they load?

I don't know. I've not been around that many DSS databases to have first hand knowledge.

but is "alter index indexname unsable" is the same as "drop index indexnam" ?

As said above, these are different slightly with respect to the time they take and I too believe in your case - you can first try to make it unusable and then rebuild it to see if that helps.

dropping and then recreating may take longer in theory but if the unusable/rebuild does not help - give it a go to test the drop/recreate as well so you get a fair idea of what it takes and would could be the best to suit your requirements.

so with all these result I assume I have go with dropping and re-creating index...

while i'm here I think of another solution I don't know that will work or not... what if I create one table as staging area load the data index free and another table with index created on them... once I load the data then insert it with sql statement into the indexed table... so my question is will this be the same time consuming?

I don't see what problem loading into a staging table first will solve.

It is the index on the main table that is slowing things down. Eventually the data will have to be loaded in the main table and the indexes updated.

Have you looked into local or partitioned indexes? As I posted above, I've not done a lot with partitioning but know a little. If you create local indexes on the partitions then you only need to rebuild the index on the partitions that changed.

<<<<< while i'm here I think of another solution I don't know that will work or not... what if I create one table as staging area load the data index free and another table with index created on them... once I load the data then insert it with sql statement into the indexed table... so my question is will this be the same time consuming? >>>>> ---> This will be of no help to you and you would end up with the same time while loading data from your staging to main table as your main table would still have those indexes and they needed to be updated/maintained along with the data uploads.

Thanks

0

Featured Post

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Cursors in Oracle:
A cursor is used to process individual rows returned by database system for a query.
In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…