[PL/SQL]测试存储过程执行超长S...-TCP_server-基于Solr的地理位置搜索（3）

当前位置: 编程技术>综合

本页文章导读:

▪[PL/SQL]测试存储过程执行超长SQL(使用CLOB变量) 在存储过程执行动态SQL一般有两种方法: 1、EXECUTE IMMEDIATE sql语句. 11g支持 EXECUTE IMMEDIATE CLOB变量. 2、使用DBMS_SQL包 11g的DBMS_SQL.PARSE也已经支持C.........

▪TCP_server #include "common.h" int main (int argc, char *argv[]) { int sock_fd,conn_fd; struct sockaddr_in server_addr,client_addr; socklen_t addrlen = ADDR_SIZE; int wc = -1,rc = -1; char buffer_r[BUFFER_SIZE],buffer_w[BU.........

▪基于Solr的地理位置搜索（3）接上文，本文将继续介绍基于Solr的地理位置搜索的第二种实现方案Cartesian Tiers+GeoHash 从基于Solr的地理位置搜索（2）中可以看到完全基于GeoHash的查询过滤，将完全遍历整个docment文档，从效率.........

[1][PL/SQL]测试存储过程执行超长SQL(使用CLOB变量)

来源: 互联网发布时间: 2013-11-10

在存储过程执行动态SQL一般有两种方法:

1、EXECUTE IMMEDIATE sql语句.

11g支持 EXECUTE IMMEDIATE CLOB变量.

2、使用DBMS_SQL包

11g的DBMS_SQL.PARSE也已经支持CLOB变量

由于存储过程的参数VARCHAR2只能支持4000字符长度传输,在传入动态SQL时候,要么使用LONG、要么使用LOB、要么使用多个VARCHAR2参数(需要在存储过程里拼接后再执行).

1、使用LONG类型传递

由于oracle已经明文建议不要再使用LONG,所以建议不要使用此类型做存储过程参数,实际上oracle很多函数也不支持LONG。

2、使用LOB对象类型

oracle对LOB类型大力推荐,也推出了DBMS_LOB包来辅助LOB对象的各种处理,所以我采用了CLOB类型做测试,结果证明CLOB完全可以处理超级大的SQL.

3、使用多个VARCHAR2参数

目前很多是采用这种方式,在存储里面进行拼接,不过虽然PL/SQL的varchar2变量可以到32762的长度,不过还是没有CLOB长.

下面开始准备使用CLOB变量做存储过程参数进行测试:

在这之前介绍一下SQL_TRACE，11g中sql跟踪的默认目录可以通过命令show parameter USER_DUMP_DEST查看

12:05:38 SYS@orcl> show parameter USER_DUMP_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      f:\app\administrator\diag\rdbm
                                                 s\orcl\orcl\trace

使用命令 alter session set tracefile_identifier=’测试跟踪存储过程' 更改跟踪文件的名称,方便识别

使用sys.dbms_system.set_sql_trace_in_session(...)来进行会话中的SQL跟踪,它有3个参数(SID,SERIAL#,SQL_TRACE)，所以需要查询到当前会话的SID及SERIAL#值.我这里使用下面的SQL查询到这2个值

  select distinct b.sid, b.SERIAL#
    from v$mystat a, v$session b
   where a.sid = b.sid;

至此我将在存储过程中进行SQL的跟踪.

存储过程如下:

create or replace procedure p_TestClob
(
  parray in CLOB
 ,POUT   out SYS_REFCURSOR
) as
  -- 测试存储过程参数为CLOB的情况
  v_sql  CLOB;
  v_sid    number;
  v_SERIAL number;

  TYPE F IS TABLE OF clob INDEX BY BINARY_INTEGER;--定义CLOB对象数组
  V_P   F;
  V_SEP VARCHAR2(2) := '^';
  rf    sys_refcursor;
  V_CURSOR NUMBER DEFAULT DBMS_SQL.OPEN_CURSOR;
  V_RES    NUMBER;
begin
  dbms_lob.createtemporary(v_sql, true); --初始化CLOB
  --sql跟踪
  execute immediate 'alter session set tracefile_identifier=''测试跟踪存储过程' ||
                    fn_getname || ''' ';
  select distinct b.sid, b.SERIAL#
    into v_sid, v_SERIAL
    from v$mystat a, v$session b
   where a.sid = b.sid;

  sys.dbms_system.set_sql_trace_in_session(v_sid, v_SERIAL, true);
  --分离字段
  SELECT * BULK COLLECT INTO V_P FROM TABLE(SPLITCLOB(PARRAY, V_SEP));
  v_sql := 'SELECT ''' || v_p(1);
  dbms_output.put_line('字段1长度:' || dbms_lob.getlength(v_p(1)));
  if v_p.count > 1
  then
    for x in 2 .. v_p.count - 1
    loop
      DBMS_LOB.append(V_SQL, ''' as t' || to_char(x - 1) || ',   ''');
      DBMS_LOB.append(V_SQL, V_P(x));
      end loop;
  end if;
  dbms_output.put_line('总长度为:' || dbms_lob.getlength(v_sql));
  DBMS_LOB.append(V_SQL, ''' AS TT FROM DUAL ');
  DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);--解析SQL
  V_RES := DBMS_SQL.EXECUTE(V_CURSOR);--执行SQL
  POUT  := DBMS_SQL.TO_REFCURSOR(V_CURSOR);--转换为REF游标

  dbms_output.put_line('成功了!!!');
  sys.dbms_system.set_SQL_TRACE_in_session(v_sid, v_SERIAL, false);--关闭SQL跟踪
exception
  when others then
    dbms_output.put_line('失败了!!!');
    sys.dbms_system.set_SQL_TRACE_in_session(v_sid, v_SERIAL, false);--关闭SQL跟踪
end p_TestClob;

其中SPLITCLOB函数和fn_getname函数见本博客

http://blog.csdn.net/edcvf3/article/details/8050978一文

测试存储过程为:

declare
  v_r  sys_refcursor;
  v_i  integer;
  v_cb clob := empty_clob();
begin
  dbms_lob.createtemporary(v_cb, true);--初始化V_CB
  for j in 1 .. 10000
  loop
    for i in 1 .. 100
    loop
      dbms_lob.append(v_cb, '999985' || i);
      --v_cb := v_cb || 'TEST_CLOB' || i;
    end loop;
    v_cb := v_cb || '^';--^为字段分隔符
    for i in 1 .. 100
    loop
      dbms_lob.append(v_cb, '00234567' || i);
    end loop;
  end loop;
  v_i := dbms_lob.getlength(v_cb);
  --debug
  dbms_output.put_line('长度:' || v_i);
  p_TestClob(v_cb, v_r);
end;

只要改变for后面的循环次数即可生成超级大的SQL语句.

我不断增大循环次数,当增加到如上10000*200次循环的时候,

执行结果为:

长度:17850000

字段1长度:792
总长度为:17987894
成功了!!!

花费时间:928.422 seconds

继续增大,仍然可以,只是时间需要的更长了.

呵呵,VARCHAR2变量没这么强大吧

可以发现测试的sql是这样的：SELECT ‘字段1’ as t1,‘字段2’ as t2,‘字段3’ as t3...‘最后字段' as tt from dual

当单个字段(如字段1)的长度超过4000的时候,会出现错误,猜想是因为在SQL里VARCHAR2只能支持4000字符长度,测试的结果如下:

长度:7568
字段1长度:4003
总长度为:4011

失败了!!!

查看SQL_TRACE文件发现如下错误:

PARSE ERROR #4:len=4011 dep=1 uid=84 oct=3 lid=84 tim=32007611307 err=1704

可以确定是在DBMS_SQL.PARSE解析SQL语句的时候出错的.

让我们调小一点，再测试发现

长度:7565
字段1长度:4000
总长度为:4008

成功了!!!

可见单个字段只能到4000的长度.

--------------------------------------------

至此可以体现CLOB变量的强大.加上ORACLE提供的DBMS_LOB包,我们就可以在PL/SQL编程中很方便的处理更长更大的变量了.

作者：edcvf3 发表于2013-1-9 12:31:56 原文链接

阅读：46 评论：0 查看评论

[2]TCP_server

来源: 互联网发布时间: 2013-11-10

#include "common.h"

int main (int argc, char *argv[])
{
int sock_fd,conn_fd;
struct sockaddr_in server_addr,client_addr;
socklen_t addrlen = ADDR_SIZE;
int wc = -1,rc = -1;
char buffer_r[BUFFER_SIZE],buffer_w[BUFFER_SIZE];
int i = 1;

sock_fd = socket(AF_INET,SOCK_STREAM,0);
if(sock_fd == -1)
  Err_sys("Server socket:")

bzero(&server_addr,ADDR_SIZE);
server_addr.sin_family = AF_INET;
server_addr.sin_port = htons(Server_port);
server_addr.sin_addr.s_addr = htonl(INADDR_ANY);

setsockopt(sock_fd,SOL_SOCKET,SO_REUSEADDR,(void *)&i,sizeof(i));

if(bind(sock_fd,(struct sockaddr *)&server_addr,addrlen) == -1)
  Err_sys("Server bind:")

if(listen(sock_fd,2) == -1)
  Err_sys("Server listen:")
conn_fd = accept(sock_fd,(struct sockaddr *)&client_addr,&addrlen);
if(conn_fd == -1)
  Err_sys("Server accept:")

else
  printf("++++++++++++Accept Success++++++++++++++\n");

printf("Connect client [ip]:%s [port]:%d\n",inet_ntoa(client_addr.sin_addr),ntohs(client_addr.sin_port));

while(RUNNING)
{
  memset(buffer_r,0,BUFFER_SIZE);

  rc = recv(conn_fd,buffer_r,BUFFER_SIZE,0);
  if(rc <= 0)
   Err_sys("Server recv:")
  printf("[Server recv]:%s\n",buffer_r);

  memset(buffer_w,0,BUFFER_SIZE);
  printf("[Server send]:");
  fflush(stdout);
  fgets(buffer_w,BUFFER_SIZE,stdin);
  wc = send(conn_fd,buffer_w,BUFFER_SIZE,0);
  if(wc <= 0)
   Err_sys("Server send:")
} //while

shutdown(conn_fd,SHUT_RDWR);
close(sock_fd);

return 0;
}

作者：qianzhaotu 发表于2013-1-9 13:10:02 原文链接

阅读：0 评论：0 查看评论

[3]基于Solr的地理位置搜索（3）

来源: 发布时间: 2013-11-10

接上文，本文将继续介绍基于Solr的地理位置搜索的第二种实现方案Cartesian Tiers+GeoHash

从基于Solr的地理位置搜索（2）中可以看到完全基于GeoHash的查询过滤，将完全遍历整个docment文档，从效率上来看并不太合适，所以结合笛卡尔层后，能有效缩减少过滤范围，从性能上能很大程度的提高。

构建索引阶段：

String geoHash = GeoHashUtils.encode(latitude, longitude);
      docment.addField("geohash", geoHash);
      //Cartesian Tiers
      int tier = START_TIER;//开始构建索引的层数
      //Create a bunch of tiers, each deeper level has more precision
//将一条记录的经纬度对应全部笛卡尔层的tierBoxId作为域值构建索引
      for (CartesianTierPlotter plotter : plotters) {
        docment.addField("tier_" + tier , plotter.getTierBoxId(latitude, longitude));
        tier++;
      }

看到这里大家肯定明白了。越相近的经纬度在同层肯定会在同一个网格中，所以他们存储的tierBoxId就会是一样。那么查询的时候通过经纬度对应层的tierBoxId,也就能找到相同层域的docId，但是如果给定的的查询范围大，可能需要将若干层的所属网格的docId都查到。

整个查询过程是先通过笛卡尔层将若干个网格涉及的DocList存入bitSet，如下代码所示：

public DocIdSet getDocIdSet(final IndexReader reader) throws IOException {
    final FixedBitSet bits = new FixedBitSet(reader.maxDoc());
final TermDocs termDocs = reader.termDocs();
//需要查询的若干层网格的boxIdList,当然至此已经过滤掉不需要查询层的boxIdList
    final List<Double> area = shape.getArea();
    int sz = area.size();
    final Term term = new Term(fieldName);//
    // iterate through each boxid
    for (int i =0; i< sz; i++) {
      double boxId = area.get(i).doubleValue();
termDocs.seek(term.createTerm(NumericUtils.doubleToPrefixCoded(boxId)));
      // iterate through all documents
      // which have this boxId
//遍历所有包含给定boxId的docList,并将其放入bitset
      while (termDocs.next()) {
        bits.set(termDocs.doc());
      }
    }
    return bits;
  }

介绍完笛卡尔层的计算后，接下来介绍笛卡尔层过滤后返还的bitset如何和geoHash结合，从实现上讲其实很简单，就是将通过笛卡尔层过滤的数据结果集合依次遍历计算其与查询给定的经纬度坐标的球面距离，同时将该计算距离和查询指定范围距离进行比较，如果大于给定距离，则将当前记录继续过滤掉，那么最终剩下的数据结果集合，将是满足查询条件的地理位置结果集合。具体实现流程见如下代码：

//将笛卡尔层的Filter作为Geohash的Filter参数传递进去，形成一个过滤链
 filter = distanceFilter = new GeoHashDistanceFilter(cartesianFilter, lat, lng, miles, geoHashFieldPrefix);

再看GeoHashDistanceFilter中最核心的方法getDocIdSet():

 public DocIdSet getDocIdSet(IndexReader reader) throws IOException {
      //在这里使用到了Lucene的FieldCache来作为缓存，实际上缓存了一个以docId为下标，base32编码为值的数组
    final String[] geoHashValues = FieldCache.DEFAULT.getStrings(reader, geoHashField);
    final int docBase = nextDocBase;
    nextDocBase += reader.maxDoc();
    return new FilteredDocIdSet(startingFilter.getDocIdSet(reader)) {
      @Override
      public boolean match(int doc) {
        //通过笛卡尔层的过滤后的doc直接找到对应的base32编码
        String geoHash = geoHashValues[doc];
        //通过解码将base32还原成经纬度坐标
        double[] coords = GeoHashUtils.decode(geoHash);
        double x = coords[0];
        double y = coords[1];
        Double cachedDistance = distanceLookupCache.get(geoHash);
        double d;
        if (cachedDistance != null) {
          d = cachedDistance.doubleValue();
        } else {
           //计算2个经纬度坐标的距离
          d = DistanceUtils.getDistanceMi(lat, lng, x, y);
          distanceLookupCache.put(geoHash, d);
        }
       //小于给定查询距离的的docid放入缓存,以供下次使用，同时返回True代表当前docId是满足条件的记录
        if (d < distance){
          distances.put(doc+docBase, d);
          return true;
        } else {
          return false;
        }
      }
    };

从上述分析中大家应该可以想到采用笛卡尔层 Filter结合GoHash Filter的实现方案，在计算规模上会比单独使用GeoHash少了很多，而在查询性能也会有更优异的表现。

最后附上一个本地Demo的查询实例：

用geofilter查找给定经纬度500km内的的数据

http://localhost:8983/solr/select/?q=*:*&fq={!geofilt pt=30.15,-79.85 sfield=tier d=500}

已有 0 人发表留言，猛击->>这里<<-参与讨论

ITeye推荐

—软件人才免语言低担保赴美带薪读研！—