下面的 SQL 语句 查询时间高达5分钟以上 请那位大哥帮个忙优化一下
小弟我不是做DBA的 是在没办法 请达人们帮帮忙 本人在线等select a.formset_inst_id, a.ext4 as 编号, a.ext11 as 申请单位, a.ext7 as 申请人,
to_char(a.ext27,'yyyy-mm-dd') as 申请日期, b.mname as 设备名称, b.ousername as 原使用人, b.nusername as 现使用人 frommv_formset_inst a
inner join
(
select a.formset_inst_id,a.mname,b.ousername,c.nusername from
(
select a.formset_inst_id,2 as aa, b.value as mname from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id = b.formset_inst_id and b.name='设备名称2'
union select formset_inst_id,1 as aa,ext5 as mname from mv_formset_inst
union select formset_inst_id,3 as aa,ext8 as mname from mv_formset_inst
union select formset_inst_id,4 as aa,ext9 as mname from mv_formset_inst
union select formset_inst_id,5 as aa,ext10 as mname from mv_formset_inst
) a
inner join
(
select a.formset_inst_id,b.name as bb, b.value as ousername from mv_formset_inst a inner join mv_form_data_inst b on
a.formset_inst_id=b.formset_inst_id and b.name like '原使用人%' and substr(b.name,-1) between '1' and '5'
) b on a.formset_inst_id=b.formset_inst_id and a.aa=substr(b.bb,-1)
inner join
(select formset_inst_id,1 as cc,ext17 as nusername,ext18 as nusername,ext19 as nusername,ext20 as nusername,ext21 as nusername, from mv_formset_inst
union select formset_inst_id,2 as cc,ext18 as nusername from mv_formset_inst
union select formset_inst_id,3 as cc,ext19 as nusername from mv_formset_inst
union select formset_inst_id,4 as cc,ext20 as nusername from mv_formset_inst
union select formset_inst_id,5 as cc,ext21 as nusername from mv_formset_inst
) c on a.formset_inst_id=c.formset_inst_id and a.aa=c.cc
) b on a.formset_inst_id=b.formset_inst_id
where a.formset_id=265
小弟我不是做DBA的 是在没办法 请达人们帮帮忙 本人在线等select a.formset_inst_id, a.ext4 as 编号, a.ext11 as 申请单位, a.ext7 as 申请人,
to_char(a.ext27,'yyyy-mm-dd') as 申请日期, b.mname as 设备名称, b.ousername as 原使用人, b.nusername as 现使用人 frommv_formset_inst a
inner join
(
select a.formset_inst_id,a.mname,b.ousername,c.nusername from
(
select a.formset_inst_id,2 as aa, b.value as mname from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id = b.formset_inst_id and b.name='设备名称2'
union select formset_inst_id,1 as aa,ext5 as mname from mv_formset_inst
union select formset_inst_id,3 as aa,ext8 as mname from mv_formset_inst
union select formset_inst_id,4 as aa,ext9 as mname from mv_formset_inst
union select formset_inst_id,5 as aa,ext10 as mname from mv_formset_inst
) a
inner join
(
select a.formset_inst_id,b.name as bb, b.value as ousername from mv_formset_inst a inner join mv_form_data_inst b on
a.formset_inst_id=b.formset_inst_id and b.name like '原使用人%' and substr(b.name,-1) between '1' and '5'
) b on a.formset_inst_id=b.formset_inst_id and a.aa=substr(b.bb,-1)
inner join
(select formset_inst_id,1 as cc,ext17 as nusername,ext18 as nusername,ext19 as nusername,ext20 as nusername,ext21 as nusername, from mv_formset_inst
union select formset_inst_id,2 as cc,ext18 as nusername from mv_formset_inst
union select formset_inst_id,3 as cc,ext19 as nusername from mv_formset_inst
union select formset_inst_id,4 as cc,ext20 as nusername from mv_formset_inst
union select formset_inst_id,5 as cc,ext21 as nusername from mv_formset_inst
) c on a.formset_inst_id=c.formset_inst_id and a.aa=c.cc
) b on a.formset_inst_id=b.formset_inst_id
where a.formset_id=265
你这个语句估计就慢在UNION上了~
时间是短了点 但也在3分钟以上
你最好用select表mv_formset_inst的数据一次或两次,然后在后面加where条件
select a.formset_inst_id, a.ext4 as 编号, a.ext11 as 申请单位, a.ext7 as 申请人,
to_char(a.ext27,'yyyy-mm-dd') as 申请日期, b.mname as 设备名称, b.ousername as 原使用人, b.nusername as 现使用人 from
mv_formset_inst a
inner join
(
select a.formset_inst_id,a.mname,b.ousername,a.nusername from
(
select a.formset_inst_id,2 as aa, b.value as mname,ext18 as nusername from mv_formset_inst a inner join mv_form_data_inst b on a.formset_inst_id = b.formset_inst_id and b.name='设备名称2'
union all select formset_inst_id,1 as aa,ext5 as mname,ext17 as nusername from mv_formset_inst
union all select formset_inst_id,3 as aa,ext8 as mname,ext19 as nusername from mv_formset_inst
union all select formset_inst_id,4 as aa,ext9 as mname,ext20 as nusername from mv_formset_inst
union all select formset_inst_id,5 as aa,ext10 as mname,ext21 as nusername from mv_formset_inst
) a
inner join
(
select a.formset_inst_id,b.name as bb, b.value as ousername from mv_formset_inst a inner join mv_form_data_inst b on
a.formset_inst_id=b.formset_inst_id and b.name like '原使用人%' and substr(b.name,-1) between '1' and '5'
) b on a.formset_inst_id=b.formset_inst_id and a.aa=substr(b.bb,-1)
) b on a.formset_inst_id=b.formset_inst_id
where a.formset_id=265 and b.mname is not null
如果有可能,把你的WHERE 条件放到你的各个数据集里面去~
/* File Name : ComputeYeardate_main.c */#include "ComputeYearDate_inc.h"void main()
{
char cInputDate[MAX_SIZE]; /* user's input */
bool IsLeapYear = FALSE; /* is or not a leapyear */
bool IsNoProblem = FALSE; /* is or not input is no problem */
bool IsContinueInput = FALSE; /* is or not continue input date */
while(1)
{
do
{
memset(cInputDate,MAX_SIZE,'\0');
printf("Example:2008 03 05 or 2008-03-05.\n");
gets(cInputDate);
IsContinueInput = f_IsContinueInput(cInputDate);
if(IsContinueInput == FALSE) /* if use do not want to continue input ,exit. */
{
exit(1);
} IsNoProblem = f_IsNoProblem(cInputDate);
}
while (FALSE == IsNoProblem); /* while the input have problem,input again */ IsLeapYear = f_IsLeapYear(cInputDate); /* rerutn the year is or not a leap year*/
f_Output(cInputDate,IsLeapYear); /* output the result */ }
}/* File Name : ComputeYeardate_def.h */
#ifndef _ComputeYearDate_def_H
#define _ComputeYearDate_def_H#define MAX_SIZE 20 /* max size of array */
#define LEAP_FEB_MONTH 29 /* february of leap year have 29 days */
#define FEB_MONTH 28 /* feb of command year hace 28 days */
#define TOTAL_MONTH 12 /* A year have 12 month */
#define TRUE 1
#define FALSE 0 typedef int INT32 ;
typedef int bool ;#endif _ComputeYearDate_def_H
// File Name : ComputeYearDate_ext.h
#ifndef _ComputeYearDate_ext_H
#define _ComputeYearDate_ext_H
#include ".\ComputeYearDate_def.h"extern bool f_IsContinueInput(char cInputDate[]);
extern bool f_IsNoProblem(char cInputDate[]);
extern bool f_IsStandard(char cInputDate[]);
extern bool f_IsValueRight(char cInputDate[]);
extern bool f_IsLeapYear(char cInputDate[]);
extern void f_Output(char cInputDate[],INT32 iIsLeapYear);
//File Name : ComputeYearDate_inc.h
#ifndef _ComputeYearDate_inc_H
#define _ComputeYearDate_inc_H#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#include "ComputeYearDate_ext.h"
#include "ComputeYearDate_def.h"#endif _ComputeYearDate_inc_H#endif _ComputeYearDate_ext_H
{
if( (tolower(cInputDate[0]) == (INT32)'e')
&& (tolower(cInputDate[1]) == (INT32)'x')
&& (tolower(cInputDate[2]) == (INT32)'i')
&& (tolower(cInputDate[3]) == (INT32)'t') ) /* first,tolower array[0]~array[4],then judge if these words are "exit". */
{
return FALSE;
}
else
return TRUE;
}
bool f_IsNoProblem(char cInputDate[])
{
bool IsStandard = FALSE;
bool IsValueRight = FALSE; IsStandard = f_IsStandard(cInputDate); /* return use's input is or not standard */
if(FALSE == IsStandard) /* Judge user's input is or not no problem. */
{
return FALSE;
}
else
{
IsValueRight = f_IsValueRight(cInputDate); /* return use's input is or not right */
if(FALSE == IsValueRight) /* Judge user's input is or not no problem. */
{
return FALSE;
}
else
{
return TRUE;
}
}
}
bool f_IsStandard(char cInputDate[])
{
INT32 i = 0;
if( ( (cInputDate[4] == '-') || (cInputDate[4] == ' ') )
&&( (cInputDate[7] != '-')||(cInputDate[7] != ' ') )
&& (strlen(cInputDate) == 10) ) /* Judge use's input is or not standard.It only judge the form is or not right. */
{
return TRUE;
}
else
{
printf("Your input is not standard...\n");
printf("(戅弌丗exit.)\n\n");
return FALSE;
}
}
bool f_IsValueRight(char cInputDate[])
{
INT32 iMonth = 0; /* the month value*/
INT32 iDay = 0; /* the day value */
if( ( ((INT32)cInputDate[0] <= 57) && ( (INT32)cInputDate[0] >= 48) )
&& ( ((INT32)cInputDate[1] <= 57) && ( (INT32)cInputDate[1] >= 48))
&& ( ((INT32)cInputDate[2] <= 57) && ( (INT32)cInputDate[2] >= 48))
&& ( ((INT32)cInputDate[3] <= 57) && ( (INT32)cInputDate[3] >= 48))
&& ( ((INT32)cInputDate[5] <= 57) && ( (INT32)cInputDate[5] >= 48))
&& ( ((INT32)cInputDate[6] <= 57) && ( (INT32)cInputDate[6] >= 48))
&& ( ((INT32)cInputDate[8] <= 57) && ( (INT32)cInputDate[8] >= 48))
&& ( ((INT32)cInputDate[9] <= 57) && ( (INT32)cInputDate[9] >= 48)) )
{
iMonth = (((INT32)cInputDate[5] - 48)*10 + ((INT32)cInputDate[6] - 48)); /* take month value */
iDay = (((INT32)cInputDate[8]) - 48)*10 + (((INT32)cInputDate[9]) - 48);/* take day value */ if(12 >= iMonth >= 1
&& 31 >= iDay >= 1)
{
return TRUE; /* month value should <=12 */
/* day value should <=31 */
}
else
{
printf("Your input date is not correct...\n");
printf("(戅弌丗exit.)\n\n");
return FALSE;
}
}
else
{
printf("Your input date is not correct...\n");
printf("(戅弌丗exit.)\n\n");
return FALSE;
}
}
bool f_IsLeapYear(char cInputDate[])
{
INT32 iYear = 0; /* the year value */
iYear=( ((INT32)cInputDate[0]) - 48)*1000 + ( ((INT32)cInputDate[1]) - 48)*100
+ ( ((INT32)cInputDate[2])*10 - 48) + ( (INT32)cInputDate[3] - 48); /* 48 means when a digit from char->int,it should reduce 48 because the digit 0 is 48 in ASCII. */ if( ( (0 == iYear%4) && (0 != iYear%100) )
|| ( (0 == iYear%100) && (0 != iYear%400) ) ) /* The conditon of a year is or not Isbissextileyear */
{
return TRUE;
}
else
{
return FALSE;
}
}
void f_Output(char cInputDate[],INT32 iIsLeapYear)
{
INT32 iYeararray[TOTAL_MONTH] = {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
/* the days of every month */
INT32 iYearsday = 0; /* x month x day is the number x day of the year */
INT32 iMonth = 0; /* month value */
INT32 iDay = 0; /* day value */
INT32 i = 0;
iMonth =( ((INT32)cInputDate[5]-48)*10 ) + ( (INT32)cInputDate[6] - 48 ) - 1;
/* take month value */
iDay = ( ((INT32)cInputDate[8]) - 48 )*10 + ( ((INT32)cInputDate[9]) - 48 );/* take day value */
if(0 == iIsLeapYear) /*compute iYearsday value */
{
for(i = 0;i < iMonth;i ++)
{
iYearsday += iYeararray[i];
}
iYearsday += iDay;
}
else
{
iYeararray[1] = LEAP_FEB_MONTH;
for(i = 0;i < iMonth;i ++)
{
iYearsday += iYeararray[i];
}
iYearsday += iDay;
} printf("%c%c寧%c%c擔惀%c%c%c%c擭揑戞%d揤.\n",
cInputDate[5],cInputDate[6],cInputDate[8],cInputDate[9],cInputDate[0]
,cInputDate[1],cInputDate[2],cInputDate[3],iYearsday); /* output result infomation */
printf("戅弌丗exit.\n");
system("pause"); /* Add this sentence,the .exe can move alone. */
}