通過shell腳本生成查詢表資料的sql

在工作中我們需要查詢表的資料條數，一般來說就是使用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'`

<a href="mailto:constr1=%24DB_CONN_STR@%24SH_DB_SID">constr1=$DB_CONN_STR@$SH_DB_SID</a>

constr1=`echo ${constr1} | tr '[:lower:]' '[:upper:]'`

timestamp=$3

time_vs=""

if [ "${timestamp}" = "" ]

then

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

user1=`echo $constr1 | awk -F "/" '{print $1}'`

inst1=`echo $constr1 | awk -F "@" '{print $2}'`

if [[ -z ${inst1} ]];then

inst1=$ORACLE_SID

function check_connectivity

{

Num=`echo show user | $ORACLE_HOME/bin/sqlplus -s $1| grep -i 'USER ' | wc -l`

if [ $Num -gt 0 ]

## ok - instance is accessible

echo '************************************************'

## inst is Inaccessible

echo Connection Details: `echo $1 ` is Inaccessible...

echo '************************************************'

}

check_connectivity $constr1

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}

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

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 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 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 ${COUNT_DIR}/List/${user1}_${inst1}_small2_tab.lst

###############################( Generating Count Files ( Big ) #############################################

while read 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 ]

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

echo "select /*+ PARALLEL(${table},${big_par}) */ '${table} ,' , count (1) from ${table} ${time_vs} ;" >> ${COUNT_DIR}/sqls/${user1}_${inst1}_${table}_tab_count.sql

done

################################ Generating Count Files ( Medium ) #############################################

#echo " Table Name is : $table "

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

echo "select /*+ PARALLEL(${table},${medium_par}) */ '${table} ,' , count (1) from ${table} ${time_vs} ;" >> ${COUNT_DIR}/List/${user1}_${inst1}_medium_tab_count.lst

################################ Generating Count Files ( Small ) #############################################

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

echo "select /*+ PARALLEL(${table},${small_par}) */ '${table} ,' , count (1) from ${table} ${time_vs} ;" >> ${COUNT_DIR}/List/${user1}_${inst1}_small_tab_count.lst

################################ Generating Count Files (Very Small ) #############################################

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

echo "select /*+ PARALLEL(${table},${small2_par}) */ '${table} ,' , count (1) from ${table} ${time_vs} ;" >> ${COUNT_DIR}/List/${user1}_${inst1}_small2_tab_count.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

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

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

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

get_final_tab_cnt_stats $schema_con

cat ${COUNT_DIR}/${schema_con}_final_tab_cnt_stats.sql

rm -rf ${COUNT_DIR}

通過shell腳本生成查詢表資料的sql

繼續閱讀

Oracle的基本操作

spec檔案詳解

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

HK-2000資料采集儀資料庫操作說明

終端環境之tmux

查找檔案中的字元串

關于SQL語言

SQL語言基礎：常用的資料查詢語句

拒絕使用者登入:/bin/false和/usr/sbin/nologin

Shell程式設計——sort排序、uniq忽略重複、tr替換壓縮删除、cut指定删除字段、正規表達式元字元sort 指令uniq 指令tr 指令cut 指令正規表達式

Linxu常用指令技巧彙總

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

ACS基本配置-權限等級管理

neo4j之cypher使用文檔

sqlServer根據經緯查距離