Thử nghiệm index chạy chậm hơn quét FULL trong Oracle Database

 A.PAYMENT_TYPE NOT IN ('10', '11', '55');

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');

KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm