Of course in my quick typing that is rubbish because I left out the from
clauses.
select /* test */ count(*) from
(
select mb.gds_id from f1 mb where (mb.gds_id = '124') and mb.month_id between
'2502' and '2513'
union all
select mb.gds_id from f1 mb where (mb.gds_id = '126') and mb.month_id between
'2502' and '2513'
)
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mark W. Farnham
Sent: Thursday, June 09, 2016 2:02 PM
To: contact@xxxxxxxx; jessica.masson85@xxxxxxxxx
Cc: 'ORACLE-L'
Subject: RE: SQL Query tuning - Index stats
And just to humor a dinosaur, maybe try this:
select /* test */ count(*) From F1 MB where
(MB.GDS_ID IN ('124') OR MB.GDS_ID IN ('126')) AND MB.MONTH_ID
BETWEEN '2502' AND '2513'
select /* test */ count(*) from
(
select mb.gds_id where (mb.gds_id = '124') and mb.month_id between '2502' and
'2513'
union all
select mb.gds_id where (mb.gds_id = '126') and mb.month_id between '2502' and
'2513'
)
and if your types happen to be numeric, try your original query and my revision
with numeric predicates.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Stefan Koehler
Sent: Thursday, June 09, 2016 1:29 PM
To: jessica.masson85@xxxxxxxxx
Cc: ORACLE-L
Subject: Re: SQL Query tuning - Index stats
Hey Jessica,
ok, but this execution plan makes way more sense :)
(1) Shouldn't the NUM_ROWS for both the indexes be 2063134584 as there are no
rows with NULL value and these are bitmap indexes ?
(2) If so, then why Oracle is not collecting the correct number. Even the
fresh stats on the indexes return the same NUM_ROWS.
(4) How can this issue be fixed?
Jessica Mason <jessica.masson85@xxxxxxxxx> hat am 9. Juni 2016 um 18:21--
geschrieben:
Hi Stefan,
The execution plan was captured using the following statement -
select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS
LAST')) ;
Here is the output with 'ALLSTATS ALL' option. The E-rows and A-rows are
still way off :
Thanks
JM.