Partitioned Sales_data Table But Engine Run Is Slower (Doc ID 1331482.1)

Goal

Qn1:

Customer is having huge SALES_DATA table.
They had partitioned sales_data table on the column sales_date and each partition is for one month. They used the DBMS_REDEFINITION package according to <Document 472449.1> .
However, the engine run takes more time then before the partitioning.

They first created the indexes for the partitioned table same as before. When the results were negative they instead recreated the indexes as partitioned indexes. But the result was not good.


Qn2:

As the locations were more stables, customer thought the best would be to go for partitions on locations.
But for a location level , he should of add a new field in sales_data tabe and he did not wanted to add more columns there. He created patitioned of sales_data table by location_id column. However, after setting PartitionColumnLoc parameter to 'Location_id' , the engine failed with:

ERROR CREATE TABLE TempTable35 ( ITEM_ID NUMBER (10) NOT NULL, LOCATION_ID NUMBER (10) NOT NULL, SALES_DATE DATE NOT NULL,FORE_2 NUMBER (20,10) NOT NULL, REGIME_CHANGE NUMBER (20,10) NOT NULL, OUTLIER NUMBER (20,10) NOT NULL, SIG_SQR NUMBER NOT NULL,LOCATION_ID NUMBER (20,10) NOT NULL, SYNCRO_SIG NUMBER (1) NOT NULL)

12:28:26:217 ERROR - ErrorDescription: ORA-00957: duplicate column name

Would you please let me know if there is a restriction in partitioning on location_id column or if he should not set up PartitionColumnLoc parameter to 'Location_id' ?


Qn3:

Customer has partitioned the SALES_DATA table using the column location_id. He compared the engine run time after partitioning using regular indexes and the run time after partitioning with partitioned indexes.

After partitioning and creating indexes he executed the following:
SQL> exec dbms_stats.gather_table_stats('<his user>','SALES_DATA', cascade => TRUE);

So it seems to work better with regular indexes, but it only saved 35 minutes. Do you think that it is the best they could do? Is there anything else they should try?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.