Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Extended column statistics for Join order and Join type

Sekar_BLUE4EVERMar 6 2018 — edited Mar 8 2018

I was reading up on extended statistics(although I have never actually used them) and I see that they should be helpful in estimating the cardinality when the query contains multiple columns in WHERE caluse. Is anyone using extended column statistics in their database? Now my main doubt is will extended column statistics help in deciding the join order and join type when there are multiple tables joined together and join type ?

  select a.*,b.*,c.* from a,b,c where a.id=b.id and a.id1=b.id1 and a.id=c.id and b.id2=c.id2

  and a.id4=66 and b.id7=44 and c.id88=88

Would it make sense to gather extended statistics on columns

  table A - id,id1

  table B - id,id1

  table C - id,id2

Will this help in deciding the join order? Is there a way to gather statistics specifically for particular joins if we know a particular set columns will be joined frequently or should we go for a cluster in that case?

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 5 2018
Added on Mar 6 2018
5 comments
1,591 views