Inmemory table on Exadata and indexes

From: Sanjay Mishra <"Sanjay>
Date: Wed, 11 Apr 2018 01:14:37 +0000 (UTC)
Message-ID: <>

I had strange issue and not sure if I am doing wrongly as I had populated table in memory and using 200G for In Memory. Table is Compressed on Exadata with size of 30G. It has only three column  Eckacct( scnid number(20), Sqrno Number(10), BoxId nuber (20)) and it is compress for query low Tables is completely populated in Memory Table has index (Eckacct_idx1 (scnid,boxid)Partiioned with Boxid  and Another index eckacct_idx2(boxid)

Running Queryselect distinct sqrno from exkacct where boxId in (around 100 values); Tables has aorund 10Mill plus Records

It is using Inlist Iterator ==> Index Range Scan ==> eckacct_idx2  ==Elapsed: 00:00:00:18 I made this index invisible and ran again It is then using Inlist Iterator ==> Partion Hash Iterator ==> Index Eckacct_idx1  ==Elapsed: 00:00:00:12

If I also made this index invisible so as to use In memory data, It never returned the data and had to cancel the query after few min  Can someone point as why it is not using Oracle inmemory as this query is called 10K time in 10min and traget is reduce the time to meet the SLA. Query is supposed to be using inmemory or if I missing anything Thanks for your help and suggestionSanjay

Received on Wed Apr 11 2018 - 03:14:37 CEST

Original text of this message