通过shell脚本生成查询表数据的sql (r2笔记63天)

在工作中我们需要查询表的数据条数,一般来说就是使用select count(1)或者select count(*)之类的语句。 当然了对于不同的表来说,应该还是可以做一些细分,能够最大程度的提高效率,比如表中含有主键列,尝试走索引扫面可能会被全表扫描效率要高。如果表中的数据太多,而且没有一些相关的约束,可以考虑使用并行来提高等等。 以下就是使用shell脚本所做的查询数据条数的一个例子,看起来有些太不值得了,但是如果数据量很大的情况下这些分析就格外有用了。 比如表customer,数据量不是很大,可以直接走索引来做。

> ksh get_tab_cnt.sh prdappo  customer

************************************************
CUSTOMER               .859375

CUSTOMER
select  /*+  index_ffs(CUSTOMER,CUSTOMER_pk ) parallel_index(CUSTOMER,CUSTOMER_pk,1) */   'CUSTOMER,', count(*) from CUSTOMER  ;

如果表比较大,可以同时开启并行。

************************************************
GREEMENT            10.359375

GREEMENT
select  /*+ index_ffs(GREEMENT,GREEMENT_pk ) parallel_index(GREEMENT,GREEMENT_pk,4) */  'GREEMENT,', count(*) from GREEMENT  ; 

对应的脚本如下:

#!/bin/ksh
#get_tab_cnt_sql
DATE=`/bin/date '+%y%m%d_%H%M%S'`
constr1=$DB_CONN_STR@$SH_DB_SID
constr1=`echo  ${constr1} | tr '[:lower:]' '[:upper:]'`
timestamp=$3
time_vs=""
if [  "${timestamp}" = "" ]
then
        echo " "
        echo "  "
else
        if [ "${timestamp2}" = "" ]
         then
                exit;
        else
                time_vs=" As of  timestamp to_date('${timestamp}','mm/dd/yyyy hh24:mi:ss') "
                 time_vt=" As of timestamp to_date('${timestamp2}','mm/dd/yyyy hh24:mi:ss')  "
        fi
fi
user1=`echo $constr1 | awk -F "/" '{print $1}'`
inst1=`echo $constr1 | awk  -F "@" '{print $2}'`
if [[ -z ${inst1} ]];then
inst1=$ORACLE_SID
constr1=${constr1}@${inst1}
fi
function check_connectivity
{
  Num=`echo show user |  $ORACLE_HOME/bin/sqlplus -s $1| grep -i 'USER ' | wc -l`
  if [ $Num -gt 0  ]
        then
                ## ok - instance is  accessible
               echo  '************************************************'
         else
                ## inst is Inaccessible
                echo  Connection Details: `echo $1 ` is Inaccessible...
                echo  '************************************************'
                 exit;
        fi
}

check_connectivity $constr1
sleep 1;
sleep 1;

mkdir `pwd`/Get_tab_cnt_${user1}_${inst1}_${DATE}
mkdir  `pwd`/Get_tab_cnt_${user1}_${inst1}_${DATE}/List
small2='1'
small='10'
medium='30'
big_par=8
medium_par=4
small_par=2
small2_par=1
COUNT_DIR=`pwd`/Get_tab_cnt_${user1}_${inst1}_${DATE}
exclude_tab=x
sqlplus -s ${constr1} <<EOF
set head off
set  line 100
set pages 50000
set long 10000
col segment_name for  a40
spool ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_size_temp.lst
select  distinct segment_name,to_number(to_char(sum(bytes/1024/1024/1024))) from  dba_segments 
where owner=upper('$1') and segment_name =upper('$2')  group by  segment_name order by 2 desc;
spool off;
spool  ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_with_pk.lst
select table_name   from dba_tables where owner=upper('$1')  and table_name in ( select table_name  from dba_constraints  where owner=upper('$1')  and table_name =upper('$2')  
and constraint_type='P');
spool off;
EOF

################## Getting Big Tables ######################
sed  '/^$/d' ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_size_temp.lst | grep  -v SQL | grep -v select | grep -v ERROR | grep -v ORA- | grep -v '*' >  ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_size.lst

echo ${medium} | awk -v medium=${medium} ' $2 > medium { print $1 } '  ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_size.lst >  ${COUNT_DIR}/List/${user1}_${inst1}_big_tab.lst
echo ${medium} | echo  ${small} | awk -v medium=${medium} -v small=${small} ' $2 < medium &&  $2 > small  { print $1 } '  ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_size.lst >  ${COUNT_DIR}/List/${user1}_${inst1}_medium_tab.lst
echo ${small} | echo  ${small2} | awk -v small=${small} -v small2=${small2} ' $2 < small   && $2 > small2 { print $1 } '  ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_size.lst >  ${COUNT_DIR}/List/${user1}_${inst1}_small_tab.lst
echo ${small2} | awk -v   small2=${small2} ' $2 < small2 { print $1 } '  ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_size.lst >  ${COUNT_DIR}/List/${user1}_${inst1}_small2_tab.lst

###############################( Generating Count Files ( Big )  #############################################
while read  table
do
        is_pk=`grep -i ${table}  ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_with_pk.lst | wc -l `
         is_pk=`echo $is_pk`
        if [ is_pk -ne 0 ]
         then
                echo "select  /*+ index_ffs(${table},${table}_pk )  parallel_index(${table},${table}_pk,${big_par}) */  '${table},', count(*) from  ${table} ${time_vs} ;"  >>  ${COUNT_DIR}/sqls/${user1}_${inst1}_${table}_tab_count.sql
         else
                echo "select  /*+ PARALLEL(${table},${big_par}) */  '${table} ,' , count (1) from  ${table} ${time_vs} ;" >>  ${COUNT_DIR}/sqls/${user1}_${inst1}_${table}_tab_count.sql
        fi
done  < ${COUNT_DIR}/List/${user1}_${inst1}_big_tab.lst

################################ Generating Count Files ( Medium )  #############################################
while read table
do
        #echo " Table Name is : $table "
         is_pk=`grep -i ${table} ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_with_pk.lst  | wc -l `
        is_pk=`echo $is_pk`
        if [ is_pk -ne 0  ]
        then
                echo "select  /*+  index_ffs(${table},${table}_pk )  parallel_index(${table},${table}_pk,${medium_par}) */  '${table},', count(*)  from ${table} ${time_vt} ;"  >>  ${COUNT_DIR}/List/${user1}_${inst1}_medium_tab_count.lst
         else
                echo "select   /*+ PARALLEL(${table},${medium_par}) */  '${table} ,' , count (1) from  ${table} ${time_vs} ;" >>  ${COUNT_DIR}/List/${user1}_${inst1}_medium_tab_count.lst
        fi
done < ${COUNT_DIR}/List/${user1}_${inst1}_medium_tab.lst
################################ Generating Count Files ( Small )  #############################################
while read table
do
        #echo " Table Name is : $table "
         is_pk=`grep -i ${table} ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_with_pk.lst  | wc -l `
        is_pk=`echo $is_pk`
        if [ is_pk -ne 0  ]
        then
                echo "select  /*+  index_ffs(${table},${table}_pk )  parallel_index(${table},${table}_pk,${small_par}) */  '${table},', count(*) from  ${table} ${time_vs} ;"  >>  ${COUNT_DIR}/List/${user1}_${inst1}_small_tab_count.lst
         else
                echo "select   /*+ PARALLEL(${table},${small_par}) */  '${table} ,' , count (1) from  ${table} ${time_vs} ;" >>  ${COUNT_DIR}/List/${user1}_${inst1}_small_tab_count.lst
        fi
done  < ${COUNT_DIR}/List/${user1}_${inst1}_small_tab.lst
################################ Generating Count Files (Very Small )  #############################################
while read table
do
        #echo " Table Name is : $table "
         is_pk=`grep -i ${table} ${COUNT_DIR}/List/${user1}_${inst1}_all_tab_with_pk.lst  | wc -l `
        is_pk=`echo $is_pk`
        if [ is_pk -ne 0  ]
        then
                echo "select  /*+  index_ffs(${table},${table}_pk )  parallel_index(${table},${table}_pk,${small2_par}) */  '${table},', count(*)  from ${table} ${time_vs} ;"  >>  ${COUNT_DIR}/List/${user1}_${inst1}_small2_tab_count.lst
         else
                echo "select   /*+ PARALLEL(${table},${small2_par}) */  '${table} ,' , count (1) from  ${table} ${time_vs} ;" >>  ${COUNT_DIR}/List/${user1}_${inst1}_small2_tab_count.lst
        fi
done  < ${COUNT_DIR}/List/${user1}_${inst1}_small2_tab.lst
rm  ${COUNT_DIR}/List/*temp.lst

#################### get table list from schemas  ##########################
touch  ${COUNT_DIR}/${user1}_${inst1}_final_tab_cnt_stats.sql
schema_con=${user1}_${inst1}
function  get_final_tab_cnt_stats
{
schema_type=$1
if [ -s   ${COUNT_DIR}/List/${schema_con}_big_tab_count.lst ];then
cat  ${COUNT_DIR}/List/${schema_con}_big_tab_count.lst >>  ${COUNT_DIR}/${schema_con}_final_tab_cnt_stats.sql
fi
if  [ -s  ${COUNT_DIR}/List/${schema_con}_medium_tab_count.lst ];then
cat  ${COUNT_DIR}/List/${schema_con}_medium_tab_count.lst >>  ${COUNT_DIR}/${schema_con}_final_tab_cnt_stats.sql
fi
if [ -s  ${COUNT_DIR}/List/${schema_con}_small2_tab_count.lst ];then
cat  ${COUNT_DIR}/List/${schema_con}_small2_tab_count.lst >>  ${COUNT_DIR}/${schema_con}_final_tab_cnt_stats.sql
fi
if [ -s  ${COUNT_DIR}/List/${schema_con}_small_tab_count.lst ];then
cat  ${COUNT_DIR}/List/${schema_con}_small_tab_count.lst >>  ${COUNT_DIR}/${schema_con}_final_tab_cnt_stats.sql
fi
}
get_final_tab_cnt_stats $schema_con
cat ${COUNT_DIR}/${schema_con}_final_tab_cnt_stats.sql 
rm -rf  ${COUNT_DIR}

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-08-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JAVA后端开发

结合swagger,实现shiro权限资源的导入功能

做shiro权限控制时,shiro的权限RequiresPermissions都写在Controller的方法内,如果做动态的权限管理时,每个shiro的权限资...

32240
来自专栏文渊之博

用于重新编译的工具和命令

1.SQL Prifiler:捕捉事件类型为SP和T-SQL的事件(Starting、Stmtcompleted、Recompile、Completed、Cac...

22890
来自专栏乐沙弥的世界

FORALL 之 SAVE EXCEPTIONS 子句应用一例

     对于大批量的DML操作中出现的错误,除了使用DML error logging特性来记录在DML期间出现的错误之外,使用批量SQL语句FORALL的S...

7610
来自专栏杨建荣的学习笔记

原来Oracle也不喜欢“蜀黍"(r6笔记第54天)

今天在部署一个脚本的时候,碰到了一个奇怪的问题,脚本运行过程中报了一个ora错误 ORA-01756: quoted string not properly t...

29150
来自专栏数据库新发现

字符集问题的初步探讨(二)

原文发表于itpub技术丛书《Oracle数据库DBA专题技术精粹》,未经许可,严禁转载本文.

14120
来自专栏数据库新发现

字符集问题的初步探讨(五)

http://www.eygle.com/special/NLS_CHARACTER_SET_05.htm

21630
来自专栏乐沙弥的世界

使用datapump 导出导入同义词(export and import synonym using datapump)

      对于同义词的备份我们有多种方式来实现,如直接通过脚本生成同义词的创建脚本,或者使用dbms_metadata.get_ddl来提取同义词的定义脚本。...

13430
来自专栏耕耘实录

记一次生产环境MySQL数据库的备份与还原

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢

12340
来自专栏菩提树下的杨过

mybatis 使用经验小结

一、多数据源问题 主要思路是把dataSource、sqlSesstionFactory、MapperScannerConfigurer在配置中区分开,各Map...

30060
来自专栏恰童鞋骚年

《T-SQL查询》读书笔记Part 1.逻辑查询处理知多少

  T-SQL是ANSI和ISO SQL标准的MS SQL扩展,其正式名称为Transact-SQL,但一般程序员都称其为T-SQL。

9740

扫码关注云+社区

领取腾讯云代金券