| Optimizer in 8 not the same way as in 7: help please. [message #366895] |
Mon, 24 May 1999 09:26 |
Gianluca cecchi
Messages: 1
Registered: May 1999
|
Junior Member |
|
|
Hello,
my name is Gianluca.
Here the situation:
We have a dwh, with one fact table of 90Gb.
Then there are dimension tbls for products (300.000 rcs), mkt (300
recs),
periods (104 recs). On fact tbl there are 3 bitmap indexes (on periods,
mkt and prd
keys). In queries we have joins for these 3 fields. We have the best
result if "bitmap and" is used on all 3 idxs. We have to use front end
appl and in 7 we created view on fact containing STAR hint. In 8 without
partitioning (on periods) all was again ok.
Partitioning the table, degenerates many queries in full tab
scan or use of only period and mkt bitmap idxs. So we used AND_EQUAL but
for many queries is not enough. For what statistics are concerned we had
compute on mkt and per tables in 7. In 8, after arising of problems, we
added compute on prd col of prd tab but, especially for queries that
involves in conditions with many items of mkt and/or prd it goes not
well.
How can we do to solve? Trying to analyze one partition and doing the
query on it doesn't go well. Thanks for your attention.
With native sql we can solve the problem using
the 2 hints "ORDERED USE_NL(facts)", but I can't
implement this for 2 reasons: a) the appl doesn't
put the fact table as the last in from clause
2) using the view doesn't function with this hints.
The hint above should function as the STAR, or not?
If I reord the query and use star, it doesn't go
well.
Thanks in advance and excuse me for the many words, but I wanted to
explain
clearly the problem.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
