A.PAYMENT_TYPE IN ('55')
00 45304503
01 63454748
02 123116203
03 760951
04 745957
05 1917303
06 442206
07 3124481
08 34567
1 6
10 464908
11 56955192
55 596007
select payment_type, count(*) from table1 group by payment_type;
--36.766s
select count(*) from table1 where payment_type not in ('10', '11', '55');
--Plan
--SELECT STATEMENT ALL_ROWSCost: 51,365 Bytes: 3 Cardinality: 1
-- 3 SORT AGGREGATE Bytes: 3 Cardinality: 1
-- 2 PARTITION RANGE ALL Cost: 51,365 Bytes: 238,824,735 Cardinality: 79,608,245 Partition #: 2 Partitions accessed #1 - #208
-- 1 INDEX STORAGE FAST FULL SCAN INDEX user1.table1_I7 Cost: 51,365 Bytes: 238,824,735 Cardinality: 79,608,245 Partition #: 2 Partitions accessed #1 - #208
--13.81s
select /*+ FULL(a)*/ count(*) from table1 a where payment_type not in ('10', '11', '55');
--Plan
--SELECT STATEMENT ALL_ROWSCost: 433,385 Bytes: 3 Cardinality: 1
-- 3 SORT AGGREGATE Bytes: 3 Cardinality: 1
-- 2 PARTITION RANGE ALL Cost: 433,385 Bytes: 238,824,735 Cardinality: 79,608,245 Partition #: 2 Partitions accessed #1 - #208
-- 1 TABLE ACCESS STORAGE FULL TABLE user1.table1 Cost: 433,385 Bytes: 238,824,735 Cardinality: 79,608,245 Partition #: 2 Partitions accessed #1 - #208
select count(*) from table1 where payment_type in ('02');
--Plan
--SELECT STATEMENT ALL_ROWSCost: 51,365 Bytes: 3 Cardinality: 1
-- 3 SORT AGGREGATE Bytes: 3 Cardinality: 1
-- 2 PARTITION RANGE ALL Cost: 51,365 Bytes: 238,824,735 Cardinality: 79,608,245 Partition #: 2 Partitions accessed #1 - #208
-- 1 INDEX STORAGE FAST FULL SCAN INDEX user1.table1_I7 Cost: 51,365 Bytes: 238,824,735 Cardinality: 79,608,245 Partition #: 2 Partitions accessed #1 - #208
--9s
select /*+ FULL(a)*/ count(*) from table1 a where payment_type in ('02');