1、oracle的索引有以下几类:
B-树索引
B-树簇索引
散列簇索引
全局和局部索引
逆转键索引
位图索引
基于函数的索引
域索引
2、第二种会好一点,但区别极微
3、create global temporary tablename(column list)
on commit preserve rows; --提交保留数据 会话临时表
on commit delete rows; --提交删除数据 事务临时表
临时表是相对于会话的,别的会话看不到该会话的数据。
B-树索引
B-树簇索引
散列簇索引
全局和局部索引
逆转键索引
位图索引
基于函数的索引
域索引
2、第二种会好一点,但区别极微
3、create global temporary tablename(column list)
on commit preserve rows; --提交保留数据 会话临时表
on commit delete rows; --提交删除数据 事务临时表
临时表是相对于会话的,别的会话看不到该会话的数据。
SQL> set serverout on
SQL> set autotrace on
SQL> set autotrace traceonly
SQL> select a.user_id,a.user_name,a.last_update_date from fnd_user a order by 1,2,3;483 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=450 Bytes=855
0) 1 0 SORT (ORDER BY) (Cost=8 Card=450 Bytes=8550)
2 1 TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=450 Bytes
=8550)Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
25 consistent gets
0 physical reads
0 redo size
13912 bytes sent via SQL*Net to client
2420 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
483 rows processedSQL> select a.user_id,a.user_name,a.last_update_date from fnd_user a order by a.user_id,a.user_name,
a.last_update_date;483 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=450 Bytes=855
0) 1 0 SORT (ORDER BY) (Cost=8 Card=450 Bytes=8550)
2 1 TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=450 Bytes
=8550)Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
25 consistent gets
0 physical reads
0 redo size
14217 bytes sent via SQL*Net to client
2420 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
483 rows processedSQL> --从执行计划来看是一样的. 所不同的是,只是多传递了几个字节的数据,
表自然还存在了。只是在你的这个session中插入的数据会被删掉。
p:primary key约束
r:reference key约束
o:with read only, on a view
u:unique约束
v:with check option, on a view