You can translate the question and the replies:

Dropping and Loading Oracle Table Indexes Using Denodo and/or the Denodo Scheduler

I update an Oracle table using Denodo code executed via a Denodo Scheduler job, i would like to know if it is possible to drop the table indexes before the table is loaded and then load the indexes to the table after the table is loaded using Denodo and/or the Denodo Scheduler.
user
12-01-2022 15:57:56 -0500

1 Answer

Hi, As far as I understand, you have an Oracle table that is created as a base view in Denodo and loading the data to this base view (IDU operations) through Scheduler jobs. Generally, the [**indexes**](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_derived_views/advanced_configuration_of_views/indexes_of_views) in the base view of the Denodo Platform represents the indexes already defined in the underlying database and it is not possible to create/drop index directly from Denodo. For your scenario, I would suggest you create two stored procedures in the underlying source (Oracle), * **Stored Procedure 1** - To drop the index. * **Stored Procedure 2** - To recreate the index. Then, create the **base view** in the Denodo on top of the stored procedures in Oracle data source. For further information, refer to the following documents, * [**Importing Graphically Stored Procedures from a Database**](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#importing-graphically-stored-procedures-from-a-database) * [**Creating Base Views from SQL Queries**](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries) In order to drop the index before loading and recreate the index after the table is loaded, I would try this by creating 3 [**Denodo Scheduler Jobs**](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/general_structure_of_a_job) and configuring the [**dependencies**](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/dependencies_among_jobs) accordingly if required, * **Job 1**: To remove the index by leveraging the base view created from the stored procedure. * **Job 2**: To load the table. * **Job 3**: To recreate the index by leveraging the base view created from the stored procedure. Hope this helps!
Denodo Team
13-01-2022 04:31:20 -0500
You must sign in to add an answer. If you do not have an account, you can register here