表分析与索引分析

`表分析与索引分析
analyze table HSC_REC_PRODUCT2004 compute statistics
analyze index PK_HSC_REC_PRODUCT2004 compute statistics
--同时分析表与索引
EXEC dbms_stats.gather_table_stats('FSHSC','HSC_REC_PRODUCT2004',CASCADE=>TRUE) ;

/
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@172.20.23.118:1521:oracle"; // 连接数据库的URL
test/test@oracle
create table test(
userid number(15) not null,
username char(20),
userpwd char(20),
sex char(2),
age number(5),
intrest varchar(2000),
changedate date,
role char(2) default 0,
primary key (userid,username)
);
/
create index test_username on test(username);
create index test_change on test(to_date(changedate,'yyyy-mm-dd hh24:mi:ss'));

truncate table test;
set timing on;

alter table test nologing on;
CREATE or REPLACE PROCEDURE inserttest
as
userid number(7):=5000000;
begin
For i In 1 .. 5000000 Loop
Insert Into test
Select Lpad(ToChar(userid), 7, '0'), 'test'||Lpad(ToChar(userid), 7, '0'), 'test'||Lpad(To_Char(userid), 7, '0'),
decode(Mod(i,2),0,'0',1,'1'),21 , 'adklfs',
sysdate,decode((Mod(i,2)),0,'0',1,'1')
from dual;
userid:=userid+1;
If Mod(i,10000)=0 then
Commit;
End If;
End Loop;
end;
/
exec inserttest;
commit;

alter session enable parallel dml; //开启并发模式
Alter Table test Parallel; //开启表的并发模式
Alter Table test NOLOGGING; //关闭操作日志:
Alter Table test noParallel; //关闭并发
Alter Table test LOGGING; //打开操作日志

select /+ parallel(4) / count(*) from test;

analyze table test compute statistics; --完成分析，比较慢，

select /+ parallel(4)/ * from test where username ='test_5000094';

select from test where username ='test_5000094';
select /+ INDEX(test,test_username)/ from test where username ='test_5000094';

select count(*) from test where CHANGEDATE>to_date('2014-09-28 09:38:11','yyyy-mm-dd hh24:mi:ss') and CHANGEDATE<to_date('2014-09-28 09:38:13','yyyy-mm-dd hh24:mi:ss');

ALTER INDEX test_change UNUSABLE;
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

select count(*) from test where CHANGEDATE>to_date('2014-09-28 09:38:11','yyyy-mm-dd hh24:mi:ss') and CHANGEDATE<to_date('2014-09-28 09:38:13','yyyy-mm-dd hh24:mi:ss');

ALTER INDEX test_change DISABLE | ENABLE;
select /+INDEX(test,test_change)/count(*) from test where CHANGEDATE>to_date('2014-09-28 09:38:11','yyyy-mm-dd hh24:mi:ss') and CHANGEDATE<to_date('2014-09-28 09:38:13','yyyy-mm-dd hh24:mi:ss');

alter index test_username rebuild; /使用索引生效,重建索引/
ALTER INDEX test_username REBUILD ONLINE;

表分析
:
analyze table test estimate statistics;--抽样分析，比较快，推荐

analyze table test compute statistics; --完成分析，比较慢，
ANALYZE TABLE 表名 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
函数索引可临时生效,生效
ALTER INDEX indexname DISABLE | ENABLE;

也就是说只有基于函数的索引才能临时禁用和启用。

ALTER INDEX indexname UNUSABLE;
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
--do something here
ALTER INDEX indexname REBUILD ONLINE;

d:\oracle\product\10.2.0\db_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;D:\Program Files\Java\jdk1.6.0_10\jre\bin;D:\Program Files\Java\jdk1.6.0_10\bin;D:\Program Files\TortoiseSVN\bin;C:\strawberry\c\bin;C:\strawberry\perl\bin;D:\Android\android-sdk-windows\platform-tools;D:\Android\android-sdk-windows\tools;C:\Program Files\jEdit;C:\Program Files\MySQL\MySQL Server 5.7\bin;C:\Python27;C:\Program Files\MySQL\MySQL Server 5.6\bin;E:\Tools\apache-ant-1.8.2\bin;F:\jmeter\bin
`