I have below table on Oracle19c(I am an oracle newbie). 4 million rows are inserted into the table daily and for now this table have 40 column and 240 million rows.
I usually search the table with user_id and MyTimestamp columns filter query and it takes 10 minutes to return the answer.
Example:
select * from table where user_id=123581 and MyTimestamp between 1657640396 and 1657777396
Note: Duplicate values are stored in the user_id and MyTimestamp columns.
I want partition monthly on MyTimestamp and index on user_id but which global or local indexes is suitable for indexing and how do I do it?
----------------------------------------------------------------------------------------------------
| id | MyTimestamp | Name | user_id ...
----------------------------------------------------------------------------------------------------
| 0 | 1657640396 | John | 123581 ...
| 1 | 1657638832 | Tom | 168525 ...
| 2 | 1657640265 | Tom | 168525 ...
| 3 | 1657640292 | John | 123581 ...
| 4 | 1657640005 | Jack | 896545 ...
--------------------------------------------------------------------------------------------------
>Solution :
If the majority of your queries contain the partition key, then better create LOCAL indexes:
CREATE INDEX index_name ON table_name (MyTimestamp, user_id) LOCAL;
Local indexes are smaller (i.e. the index partition) and thus faster and you don’t have to rebuild the index when you drop an outdated partition.