首先,不同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]
建议:许多东西多试试就知道了
这个不好
这样
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