PL/SQL
文章平均质量分 50
Kevin_ora
这个作者很懒,什么都没留下…
展开
-
One simple health check for oracle with sql
There are some sqls which is used for check the oracle database's health condition.------numbers of processselect count(*) process_num from v$process;select SESSIONS_MAX,SESSIONS_CURRENT,SESSIONS_HI原创 2013-09-03 12:59:50 · 1277 阅读 · 1 评论 -
select count(column_name) form table_name or select count(*) from table_Name
select count(*) from table_nameselect count(column_name) form table_nameTo calculate a table's row number, most programer think 'select count(*) from table_name' is not a patch on ' select c原创 2013-10-30 14:18:44 · 1572 阅读 · 0 评论 -
One sql performance tuning
Original sql:select * from ((select count(c.customer_id) day30 from tls_customer c where c.is_active = '0' and c.birth IN (SELECT to_char(SYSDATE - 1 + rownum, 'mm原创 2014-01-06 18:18:38 · 931 阅读 · 0 评论 -
oracle top n sentences
Oracle has some sentences for top n version oracle 10g 11gTOP-NSELECT * FROM ( SELECT name, id FROM test ORDER BY id DESC) WHERE rownum <= 5;SELECT * FROM ( SELECT name, id FROM原创 2014-03-21 13:49:01 · 815 阅读 · 0 评论 -
one sql to calculate the schema's table capacity
SELECT owner, table_name, TRUNC(SUM(bytes)/(1024*1024)) MBFROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type = 'TABLE' UNION ALL SELECT i.ta原创 2014-04-11 14:50:24 · 956 阅读 · 0 评论 -
in exists 使用
两个写法可以达到相同的结果1原创 2014-06-30 17:38:15 · 571 阅读 · 0 评论 -
not exists 事例
今天一个开发问到一个not exists问题,小小做个实验实验1SQL> select * from emp e1 where not exists (select 1 from emp e2 where e1.mgr = e2.empno); EMPNO ENAME JOB MGR HIREDATE原创 2014-06-30 17:02:35 · 888 阅读 · 0 评论 -
sql 插入多个表数据
insert more than one rowseg.insert allwhen column into tab1when column > condition theninto tab2elseinto tab 3 select from tab原创 2015-02-08 22:30:39 · 1683 阅读 · 0 评论 -
ROW_NUMBER() OVER()
ROW_NUMBER()OVER() 是oracle SQL分析函数,主要是用来对要查询的数据分组排序使用。 使用方法 ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2) 对列col1分组,col2排序操作。 例子:SQL> SELECT 2 ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY原创 2017-03-21 16:33:34 · 476 阅读 · 0 评论