通过IP定位区域的SQL优化思路(r10笔记第10天)

    昨天中午吃饭的时候，突然手机收到一条报警信息，提示数据库的负载突然提高了。对于一个高配，稳定，核心的系统来说，出现这么一个报警会立刻引起关注。SQL_FULLTEXT COLUMN_ID COLUMN_NAME                    DATA_TYPE       DATA_LENGTH NULLABLE  SQL> select STRIPTOINT('124.115.229.74') from dual;SELECT IP_ID,COUNTRY,PROVINCE,CAPITAL FROM tlbb.SWD_IP2COUNTY WHERE 208797012 BETWEEN IP2NUM_LEFT_LINE AND IP2NUM_RIGHT_LINE IP_LEFT_LINE    IP_RIGHT_LINE   IP2NUM_LEFT_LINE IP2NUM_RIGHT_LINE COUNTRY    PROVINCE   CAPITAL Snap Id      Snap Time      Sessions Curs/SessALTER SESSION SET EVENTS='10053 trace name context forever, level 1';Final query after transformations:******* UNPARSED QUERY IS *******Access path analysis for SWD_IP2COUNTY  Best:: AccessPath: IndexRangeCard: 1114.40  Bytes: 0SQL> SELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2COUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=3030728598StatisticsSELECT "SWD_IP2COUNTY"."IP_ID" "IP_ID","SWD_IP2COUNTY"."COUNTRY" "COUNTRY","SWD_IP2COUNTY"."PROVINCE" "PROVINCE","SWD_IP2COUNTY"."CAPITAL" "CAPITAL" FROM "TEST"."SWD_IP2COUNTY" "SWD_IP2COUNTY" WHERE "SWD_IP2COUNTY"."IP2NUM_LEFT_LINE"<=3030728598and rownum=1 ;Statistics          3  consistent gets

这一点上还得感谢兔子的建议，看了下老白总结的案例就是这样的改进，都是套路，哈哈。

对于性能的比对情况如下，DB time有了很多改进。