*That last observation means that if the inputs and data sizes for this
query are typical then a nested loop join using a perfect index into the
partitioned table might have to decompress 415 (query high) to find the
415 relevant rows - which would be a tiny amount of I/O and CPU compared to
the current load . It's not quite that nice, though, because with the best
local index it looks as if you'd have to probe all 181 partitions of the
index for each of the 34 driving rows (and you might decide that you don't
want to create the index). *
It's very much up to you to work out what you consider to be the perfect
index - but from what you've said so far on this thread the combination of
predicates you've indicated and your preference for local indexes does
suggest a locial index on (anum,did) or (dd, anum). I note, though, that
the original statement you sent us included a predicate "i_dt between :1
and :2", which also telling us that the table was partitioned on pt_dt but
no partition elimination was occurring. Is "i_dt" actually the partitioning
"pt_dt" or is is another column that might need to be in the index.
I suggest you extract a few partitions of data from your table and do a few
experiments to give you some idea of how Oracle will use the index you've
suggested with the code you've supplied. The key detail is that you don't
want Oracle to visit the table unless it absolutely has to.
* - makes me wonder whether you've been caught in the "double-decompression
trap some of the time, meaning the decompressed data to be sent back to the
server it larger than the 1MB limit so the CU is decompressed at the cell,
then sent compressed anyway and decompressed again at the datbase. The
session stats would give you a clue about that. *
I'd have to check one of the Exadata books (Kerry Osborne et. al.) for your
version of Oracle on Exadata, but I think the following stats highlight the
symptom:
cell CUs sent
compressed
866,940.00
cell physical IO bytes sent directly to DB node to balance CPU
15,739,502,952.00
I'm not sure how Oracle is counting (or possibly double counting) because I
would have expected query high CUs to be close to 32KB each so sending
866,940 of them to the database server for decompression should have meant
something like 26GB sent to DB node; however there is some consistency
visible in other numbers viz:
EHCC Turbo Scan CUs Decompressed 1,212,123.00 =
cell CUs sent compressed 866,940.00 + cell CUs sent uncompressed
345,183.00
Regards
Jonathan Lewis
Regards
Jonathan Lewis
On Sat, 23 Jan 2021 at 15:00, Lok P <loknath.73@xxxxxxxxx> wrote:
Thank You Jonathan.Regarding the index , as we are really avoiding any global index because of
