Tag: Oracle

oracle 导入导出  使用expdp/impdp
我的版本11gR2   版本号 11.2.0.1.0    同事的是11gr1 需要指定版本号11.1.0.6.0

我装的oracle是11gR2  同事装的是11gR1  用expdp导出  impdp导入  提示版本不兼容错误
经查看oracle文档  看到了 这个参数version  最后加oracle版本号 version=11.1.0.6.0   成功导入

Sql代码
–创建dump文件目录
create directory dump_test as ‘D:\test\dump’
–授权读写权限给你要操作的用户
grant read, write on directory dump_test to
username

–查询是否存在自己创建的文件目录
select * from dba_directories;

select  * from da.user_tables

–注意低版本是不能导入高版本 比如我的数据库是11gR2 对方是11gr1 需要指定版本号11.1.0.6.0
expdp username/password directory=dump_test dumpfile=data.dmp  version=11.1.0.6.0
–默认导在刚才创建的目录 ‘D:\test\dump’

–导入文件
impdp username/password directory=dump_test dumpfile=data.dmp

这篇文章通过例子说明分页查询使用的NESTED LOOP操作，在分页查询翻到最后几页时的性能问题：
SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;
表已创建。
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;
表已创建。
SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);
表已更改。
SQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)
2 REFERENCES T(USERNAME);
表已更改。
SQL> CREATE INDEX IND_T1_OWNER ON T1(NAME);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’)
PL/SQL 过程已成功完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T1’)
PL/SQL 过程已成功完成。
SQL> SET AUTOT TRACE
SQL> SELECT /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20 Bytes=1200)
1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF ‘PK_T’ (UNIQUE)

Statistics
———————————————————-
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
574 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 )
12 WHERE RN BETWEEN 11 AND 20;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)
1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T’ (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)

Statistics
———————————————————-
0 recursive calls
0 db block gets
8586 consistent gets
8052 physical reads
0 redo size
574 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
在分页查询的前几页，NESTED LOOP操作比HASH JOIN操作效率高得多。
SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM T, T1 WHERE USERNAME = OWNER;
COUNT(*)
———-
96985
SQL> SELECT USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 )
12 WHERE RN BETWEEN 96971 AND 96980;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)
1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T’ (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)

Statistics
———————————————————-
0 recursive calls
0 db block gets
8586 consistent gets
8068 physical reads
0 redo size
571 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
对于最后几页，采用HASH JOIN的方式，执行效率几乎没有任何改变，而采用NESTED LOOP方式，则效率严重下降，而且远远低于HASH JOIN的方式。
SQL> SELECT /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 96980
12 )
13 WHERE RN >= 96971;
已选择10行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96980 Bytes=5818800)
1 0 VIEW (Cost=97811 Card=96980 Bytes=5818800)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF ‘T1’ (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF ‘PK_T’ (UNIQUE)

Statistics
———————————————————-
0 recursive calls
0 db block gets
105566 consistent gets
8068 physical reads
0 redo size
571 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
分页查询一般情况下，很少会翻到最后一篇，如果只是偶尔碰到这种情况，对系统性能不会有很大的影响，但是如果经常碰到这种情况，在设计分页查询时应该给予足够的考虑。