首先,不同sql的写法对执行的性能可能会有较大的差异,要不为什么说80%的性能问题出现在sql一级
执行explain plan或者set autotrace on查看执行计划就能对数据的执行情况清楚了
看看select的语法就应该清楚了where到底能不能放到group by 后面了
SELECT
[ DISTINCT | ALL ]
{ *
| { [ schema. ]{ table | view | snapshot } .*
| expr [ [ AS ] c_alias ] }
[, { [ schema. ]{ table | view | snapshot } .*
| expr [ [ AS ] c_alias ] } ] ...
FROM
{ [ schema. ]
{ table [ PARTITION ( partition_name ) | @dblink ]
| [ view | snapshot ] [ @dblink ] }
[ t_alias ]
| [ THE ] ( subquery )
[ t_alias ]
| TABLE ( nested_table_column )
[ t_alias ] }
[, { [ schema. ]
{ table [ PARTITION ( partition_name ) | @dblink ]
| [ view | snapshot ] [ @dblink ] }
[ t_alias ]
| [ THE ] ( subquery )
[ t_alias ]
| TABLE ( nested_table_column ) } ] ...
[ WHERE condition ]
[ [ START WITH condition ] CONNECT BY condition
| GROUP BY expr [, expr] ...
[ HAVING CONDITION ] ] ...
[ { UNION
| UNION ALL
| INTERSECT
| MINUS } SELECT command ]
[ ORDER BY { expr | position | c_alias } [ ASC | DESC ]
[, { expr | position | c_alias } [ ASC | DESC ] ] ...
| FOR UPDATE
[ OF [ [ schema. ]{ table. | view. } ] column
[, [ [schema.]{table. | view.} ] column] ...] [NOWAIT]
建议:许多东西多试试就知道了
执行explain plan或者set autotrace on查看执行计划就能对数据的执行情况清楚了
看看select的语法就应该清楚了where到底能不能放到group by 后面了
SELECT
[ DISTINCT | ALL ]
{ *
| { [ schema. ]{ table | view | snapshot } .*
| expr [ [ AS ] c_alias ] }
[, { [ schema. ]{ table | view | snapshot } .*
| expr [ [ AS ] c_alias ] } ] ...
FROM
{ [ schema. ]
{ table [ PARTITION ( partition_name ) | @dblink ]
| [ view | snapshot ] [ @dblink ] }
[ t_alias ]
| [ THE ] ( subquery )
[ t_alias ]
| TABLE ( nested_table_column )
[ t_alias ] }
[, { [ schema. ]
{ table [ PARTITION ( partition_name ) | @dblink ]
| [ view | snapshot ] [ @dblink ] }
[ t_alias ]
| [ THE ] ( subquery )
[ t_alias ]
| TABLE ( nested_table_column ) } ] ...
[ WHERE condition ]
[ [ START WITH condition ] CONNECT BY condition
| GROUP BY expr [, expr] ...
[ HAVING CONDITION ] ] ...
[ { UNION
| UNION ALL
| INTERSECT
| MINUS } SELECT command ]
[ ORDER BY { expr | position | c_alias } [ ASC | DESC ]
[, { expr | position | c_alias } [ ASC | DESC ] ] ...
| FOR UPDATE
[ OF [ [ schema. ]{ table. | view. } ] column
[, [ [schema.]{table. | view.} ] column] ...] [NOWAIT]
建议:许多东西多试试就知道了
解决方案 »
- oracle clob 字段,ora-06502:PL/SQL:数字或值错误
- oracle10g 临时表里的记录怎么加锁
- 请教这样的函数应该怎么写,求计算公式数值在51-100范围内返回1 ,101-150返回1.5,151-200返回2。。。依次类推
- 关于随机函数的问题!急!急!
- 请问oracle9i的最新版本是什么啊
- 简单触发器问题,执行就出错,编译没问题
- 数据库链接(oracle9i或者8i链接oracle7.3)
- select a into b from 表 --a 为 NULL 既然不能执行??
- 请教oracle最大会话数和进程数可以设到多少?????
- 寻求有关提高SQL执行效率的文章或书籍
- 启动数据库使出现IO异常是怎么回事啊?
- 关于SQL语句报错的问题!高手请来帮忙!解决马上给分
这个不好
这样
where nameId > 0 and nameId < 10000
SELECT
FROM
WHERE
GROUPBY
ORDEREDBY
顺序是固定好了的?不许更改?而
SELECT
FROM
ORDEREDBY
WHERE
GROUPBY
是错的?
SELECT
FROM
WHERE
GROUPBY
HAVING
ORDEREDBY
顺序不能调换?
另外,它的执行流程是怎样的?
http://www.eygle.com/faq/AutoTrace.htm