02 2014 档案
摘要：SQL> select count(*),ID from test_2 group by id; COUNT(*) ID---------- ---------- 131072 1 1179648 2select count(*) from test_2 where...
阅读全文
摘要：20条记录一页,扫描第2页就需要访问40条记录。SQL> select * from ( select * from ( select /*+ index_desc(a idx_page_3) */ a.*,rownum rn from pagea where object_id >1000 ...
阅读全文
摘要：select * from (select * from (select a.*,rownum rn from page a where object_id >1000 and owner='SYS' order by object_id desc) where rownum=20;现在加个H...
阅读全文
摘要：条件有rownum的时候出现扫描表，到前n行停止
阅读全文
摘要：技巧2：union 代替or的情况当SQL语句中，or 条件上面有一个为子查询，并且子查询上的表与源表不同，这个时候就可以用union代替or或者你发现执行计划中的 filter 有 or 并且 or 后面跟上子查询(EXISTS...)的时候就要注意，比如：2 - filter("T"."LRR_...
阅读全文
摘要：示例如下(请自己动手实验)：create table test1 as select * from dba_objects;create table test2 as select * from dba_objects;create index idx1 on test1(object_id);cr...
阅读全文
摘要：SQL> create table t1(id1 char(2),id2 char(2),id3 char(2));Table created.SQL> desc t1 Name Null? Type ---------------------------------------...
阅读全文
摘要：SQL> set linesize 200SQL> set pagesize 200SQL> set autot traceSQL> select distinct department_name from hr.departments dept, hr.employees emp where ...
阅读全文
摘要：SQL> create table t as select * from dba_objects; Table created. SQL> create index idx_t on t(object_id); Index created. SQL> BEGIN 2 ...
阅读全文
摘要：SQL> create table t as select * from dba_objects; Table created. SQL> create index idx_t on t(object_id); Index created. SQL> BEGIN 2 ...
阅读全文
摘要：SQL> show parameter pgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------pga_aggregate_target big integer ...
阅读全文
摘要：ipcs:-q Writes information about active message queues-o Writes the following usage information: * Number of messages on queue * Total number of bytes...
阅读全文
摘要：select ename,sal,sum(sal) over (partition by ename order by sal,empno) as running_totalfrom emp1order by 2按ename 汇总salover()开窗函数和聚合函数的不同之处是对于每个组返回多行，而...
阅读全文
摘要：SQL> set linesize 200SQL> select * from (select a.* ,row_number() over( order by empno) rn from emp a)where rn=1 2 3 ; EMPNO ENAME JOB MGR HIREDATE S...
阅读全文
摘要：latch是一种锁,用来实现对Oracle所有共享数据结构的串行化访问。共享池就是这样一个例子,这是系统全局区中一个庞大的共享数据结构,Oracle正是在这里存储已解析,已编译的SQL。修改这个共享内存结构时,必须注意一次只允许一个进程访问。(如果两个进程或线程试图同时更新内存中的相同的数据结构,这...
阅读全文
摘要：BUFFER (SORT)DescriptionPerforms a memory sort on a row source CREATE TABLE t1 (c01 NUMBER); CREATE INDEX T1_C1 ON T1(C01);CREATE TABLE t2 (c02 NUMBE...
阅读全文