Toad World® Forums

Creating index on partitioned table from wizard


#1

Hi!

One thing, when you create index from wizard on partitioned table, the partition key column is set in column list as default, but why? Is there any reason of this situation?

This col can’t be removed in wizard…

Regards Piter


#2

Off hand Piter, I’m not sure if this is deliberate or not…
I’m looking into it, and will get back to you shortly.

Jaime


#3

In partitioned table local index looks like , few separate indexes on each partition. Local Index speeds up searching in local partition, so oracle choose partition using partition key (in explain Plan: single partittion scans), not using index field equal to partition key. Indexes with key cols included are bigger (in partitioned indexes you can’t use compression),and going this way, slower . Sometimes hint RULE speeds up searching on big partitioned table (with correct statistics of course), in this way i’m using that practise: very large (sub)partitioned tables with indexes (has no statistics), all applications using hints (with indexes) to access table. Why? because there was some situations like : when table has stats collected , optimisator choose index path, but single subpartition Full scan was much faster!

In other way when you use this field in partitioned indexes, you should in subpartitioned too, and then index should have at least two cols specified (col1 - partition_key,col2 subpartition_key).

In my opinion this column in wizard shouldn’t be placed.


#4

Piter, can you give us one screenshot to localize the issue you are talking about. Thanks, Andrew


#5

Sorry Andrew, I forgot attach this files in my first post…

  1. Table on which i try to create local index
    2.Index wizard afther opening, col data_d is default and you can’t remove them from definition

Regards Piter
table.jpeg


#6

Sorry Andrew, I forgot attach this files in my first post…

  1. Table on which i try to create local index
    2.Index wizard afther opening, col data_d is default and you can’t remove them from definition

Regards Piter
wizard4thistable.jpeg


#7

Thanks for screenshots. They were helpful. Now, we can understand the issue, but none of us can give you some definite answer right now. We need to discuss this in more details among ourselves, but we come back to you later.

Rgds,
Andrew


#8

Hi Piter,

I will look into this for you and will get back to you later tonight.

Thanks and regards,
Bruce


#9

Hi Piter,

Sorry for a late reply, the Community was down for sometimes.

Can you please assist us here with more info. Can you please provide us some script (objects definition) which you might have…We have gone through your notes and revisit our code many times, we believe/understand that for local index (partitioned), the parttitioned column is for display purposes and required in the index definition.

Much appreciated if you can please take us through this thread in more details .

Many thanks Piter.

Regards,
Bruce


#10

Hi Bruce!
There is many aspects of index usage in partitioned tables, depends of density of data in each partition. My only one suggestion is to add posibility to delete key_col entry from creation wizard window, because usage of this col in index is optional.

Regads Piter


#11

Hi Piter,

Thanks for your note. I will discuss with the our domain expert and will revisit the Index VOE again and see how we could we handle the local index in partitioned tables.

We will keep you posted with the changes so you can check it out.

Thanks and regards,
Bruce