|
|
|
Let’s make sure the following facts. First, the logical process is to return the daily trade volumes for all small orgs. Second, all shops belong to small orgs can not be more than 150,000 (total shops), say about half at time being. The problem is definitely the not in clause due to data modeling. Assume adding orchid in his table, which probably solves the critical part by directly joining bbb table using index on .orgid/top orgid. Without changing data model, you do want parallel hash join. But no surprise Oracle optimizer gets lost in every step. Why it chooses global index while group by is on partition column trade date? Why it uses nested join on first table aaa and then hopelessly using index on joining bbb table? Now come back to solutions. The natural parallel execution needs his table to be the leading table with each spread on one partition and return the sum volume independently. But hash join wants to build hash table on small data set and then loop his partitions one by one. So it appears a conflict here. If Oracle optimizer is smart enough it should allow to create a materialized view from aaa joining bbb first and then build a leading hash table and then all parallel hash join by loop each his partition one by one. It would be great if working in that way. If not, try other options such as materialized view or global temp table with index. But for personal interest, I would test that creating a joined materialized view for aaa an bbb with order by in with clause (hope it creates an indexing function), or use table function to return a associate array to serve as a indexed table. Certainly there is a lot to learn during this optimization course. |
|
楼主: oracle杀手
IP卡
狗仔卡
楼主
显身卡
发表于 2021-10-11 10:38
