ORACLE

ORACLE SQL优化 - ORACLE执行计划

Last login: Mon Feb  8 14:13:19 2010 from 192.168.1.249

 

ipnet

-bash-3.00$ 

-bash-3.00$ ipnet

-bash: ipnet: command not found

-bash-3.00$ su - oracle

Password: 

eastcsu: incorrect password

-bash-3.00$ eastcom

-bash: eastcom: command not found

-bash-3.00$ su - oracle

Password: 

[oracle@ipnet-td2 ~]$ 

 

[oracle@ipnet-td2 ~]$ sqlplus /nolog

 

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 8 14:14:08 2010

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

SQL> su^H^H^H

SP2-0042: unknown command " - rest of line ignored.

SQL> conn ipnet/ipnet1234@ORAC^H^H^H

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

 

 

SQL> ^[[A^H               " - rest of line ignored.

SQL> 042: unknown command "

SQL> conn ipnet/ipnet1234@ORCL 

Connected.

SQL> set timn^H

SP2-0158: unknown SET option "tim"

SQL> set timing on

SQL> set autot traceonly

SQL> select v.NAME_CN from vendor v, vendordevicetype vt, devicetype t where t.name = vt.device_type_name and v.name = vt.vendor_name and t.name_cn = '防火墙';

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 497066359

 

--------------------------------------------------------------------------------

------------------

 

| Id  | Operation                     | Name             | Rows  | Bytes | Cost

(%CPU)| Time     |

 

--------------------------------------------------------------------------------

------------------

 

|   0 | SELECT STATEMENT              |                  |     5 |   205 |     7

   (0)| 00:00:01 |

 

|   1 |  NESTED LOOPS                 |                  |     5 |   205 |     7

   (0)| 00:00:01 |

 

|   2 |   NESTED LOOPS                |                  |     5 |   140 |     4

   (0)| 00:00:01 |

 

|*  3 |    TABLE ACCESS FULL          | DEVICETYPE       |     1 |    14 |     3

   (0)| 00:00:01 |

 

|   4 |    TABLE ACCESS BY INDEX ROWID| VENDORDEVICETYPE |     5 |    70 |     1

   (0)| 00:00:01 |

 

|*  5 |     INDEX RANGE SCAN          | N907275051_IDX   |     5 |       |     0

   (0)| 00:00:01 |

 

|   6 |   TABLE ACCESS BY INDEX ROWID | VENDOR           |     1 |    13 |     1

   (0)| 00:00:01 |

 

|*  7 |    INDEX UNIQUE SCAN          | NAME_4_1         |     1 |       |     0

   (0)| 00:00:01 |

 

--------------------------------------------------------------------------------

------------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("T"."NAME_CN"='防火墙')

   5 - access("T"."NAME"="VT"."DEVICE_TYPE_NAME")

   7 - access("V"."NAME"="VT"."VENDOR_NAME")

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         18  consistent gets

          0  physical reads

          0  redo size

        466  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          3  rows processed

 

SQL> 

 

 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jxluoix/archive/2010/02/08/5298961.aspx

 

 

Oracle 查询 删除 重复记录

 

select t.*, t.rowid from test_select_delete_dump_rd t;


-- 查询表中存在重复记录的所有记录
select t.*, t.rowid from test_select_delete_dump_rd t
    where
    name in (
        select name from test_select_delete_dump_rd
            group by name
            having count(name) > 1
    );


-- 查询表中存在重复记录的多余记录，
-- rowid最小的记录被认为是保留记录
select * from (
    select t.*, t.rowid  from test_select_delete_dump_rd t
        where
        name in (
            select name from test_select_delete_dump_rd
                group by name
                having count(name) > 1
        )
) t2
    where
    t2.rowid not in (
        select min(t1.rowid) from test_select_delete_dump_rd t1
            group by name
            having count(name) > 1
    );

   
-- 查询表中存在重复记录的多余记录，
-- rowid最大的记录被认为是保留记录
select * from (
    select t.*, t.rowid  from test_select_delete_dump_rd t
        where
        name in (
            select name from test_select_delete_dump_rd
                group by name
                having count(name) > 1
        )
) t2
    where
    t2.rowid not in (
        select max(t1.rowid) from test_select_delete_dump_rd t1
            group by name
            having count(name) > 1
    );

/**
delete from test_select_delete_dump_rd
    where
    name in (
        select name from test_select_delete_dump_rd
            group by name
            having count(name) > 1
    )
    and
    rowid not in (
        select min(name) from test_select_delete_dump_rd
            group by name
            having count(name) > 1
    );
*/

 

 

 

 

 

 

 

dense_rank, rank：

 

 

select * from (
    select deptno, ename, sal,
        dense_rank() over(
            partition by deptno
            order by sal desc
        ) dr
    from emp
)
where dr <= 3
order by deptno, sal desc;

 

 

 

 

 

select * from (
    select deptno, ename, sal,
        rank() over(
            partition by deptno
            order by sal desc
        ) dr
    from emp
)
where dr <= 3
order by deptno, sal desc;