Mục đích: Demo cho các bạn full chậm hơn index, nên dùng index phải rất chuẩn (3-5% giá trị trả về thôi nhé).
1. Kiểm tra phân bố dữ liệu trường payment_type
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;
2. Câu lệnh 1
2.1. Trường hợp dùng index (viết thông thường)
--Do quét index nên mất: 36.766s
select count(*) from table1 where payment_type not in ('10', '11', '55');
--Plan
--SELECT STATEMENT ALL_ROWS Cost: 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
2.2. Trường hợp ép hint FULL
--Trong khi đó ép hint chạy FULL chỉ mất: 13.81s, mặc dù cost 433,385, lớn hơn cost dùng index, nhưng vẫn nhanh hơn
select /*+ FULL(a)*/ count(*) from table1 a where payment_type not in ('10', '11', '55');
--Plan
--SELECT STATEMENT ALL_ROWS Cost: 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
3.Câu lệnh 2
-- 50s dùng index
select count(*) from table1 where payment_type in ('02');
--Plan
--SELECT STATEMENT ALL_ROWS Cost: 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 dùng FULL
select /*+ FULL(a)*/ count(*) from table1 a where payment_type in ('02');
