ORACLE1.16-练习题和索引在海量数据的应用-CSDN博客

--思考：

--1.求算每个同学每科是否超过班级的单科平均分

-- 回顾：行转列

-- 1.利用decode（不只是用于行转列）

-- 2.利用oracle 11g以后提供pivot

select

ooo.*,

1 struts_avg,

2 srping_avg,

3 hibernate_avg,

4 oracle_avg

from (

select * from (

(select user_name,ke,score from t_user_score)

)

pivot(

sum(score) -- 统计值

for ke in ('struts','spring','hibernate','oracle') -- 划分多少列

)

) ooo

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

-- struts的平均分

select * from t_user_score where ke='struts'

select avg(score) from t_user_score where ke='struts'

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

select id ,

(select 1 from dual) pp

from t_user_score

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

select

ooo.*,

(select avg(score) from t_user_score where ke='struts') struts_avg,

(select avg(score) from t_user_score where ke='spring') srping_avg,

(select avg(score) from t_user_score where ke='hibernate') hibernate_avg,

(select avg(score) from t_user_score where ke='oracle') oracle_avg

from (

select * from (

(select user_name,ke,score from t_user_score)

)

pivot(

sum(score) -- 统计值

for ke in ('struts' as struts,'spring' as spring,'hibernate' as hibernate,'oracle' as oracle) -- 划分多少列

)

) ooo

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

select

ooo.*,

(select avg(score)-struts from t_user_score where ke='struts') low_struts_avg,

(select avg(score)-spring from t_user_score where ke='spring') low_srping_avg,

(select avg(score)-hibernate from t_user_score where ke='hibernate') low_hibernate_avg,

(select avg(score)-oracle from t_user_score where ke='oracle') low_oracle_avg

from (

select * from (

(select user_name,ke,score from t_user_score)

)

pivot(

sum(score) -- 统计值

for ke in ('struts' as struts,'spring' as spring,'hibernate' as hibernate,'oracle' as oracle) -- 划分多少列

)

) ooo

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

select

ooo.*,

sign((select avg(score)-struts from t_user_score where ke='struts')) low_struts_avg,

sign((select avg(score)-spring from t_user_score where ke='spring')) low_srping_avg,

sign((select avg(score)-hibernate from t_user_score where ke='hibernate')) low_hibernate_avg,

sign((select avg(score)-oracle from t_user_score where ke='oracle')) low_oracle_avg

from (

select * from (

(select user_name,ke,score from t_user_score)

)

pivot(

sum(score) -- 统计值

for ke in ('struts' as struts,'spring' as spring,'hibernate' as hibernate,'oracle' as oracle) -- 划分多少列

)

) ooo

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

--2.揪出偏科最严重的同学出来。

select

ooo.*,

decode(sign((select avg(score)-struts from t_user_score where ke='struts')),1,'是的，低于平均分',-1,'高于平均分','跟平均分持平') low_struts_avg,

decode(sign((select avg(score)-spring from t_user_score where ke='spring')),1,'是的，低于平均分',-1,'高于平均分','跟平均分持平') low_srping_avg,

decode(sign((select avg(score)-hibernate from t_user_score where ke='hibernate')),1,'是的，低于平均分',-1,'高于平均分','跟平均分持平') low_hibernate_avg,

decode(sign((select avg(score)-oracle from t_user_score where ke='oracle')),1,'是的，低于平均分',-1,'高于平均分','跟平均分持平') low_oracle_avg

from (

select * from (

(select user_name,ke,score from t_user_score)

)

pivot(

sum(score) -- 统计值

for ke in ('struts' as struts,'spring' as spring,'hibernate' as hibernate,'oracle' as oracle) -- 划分多少列

)

) ooo

-- 速度（提升速度）

-- 程序员必备技能

-- 1.绝对不能有not in

-- 2.对常见的查询条件的字段，需要添加索引

-- 3.对于海量的数据，设置分区

-- 系统维护

-- 保证数据库服务器内存足够大，让SGA足够大，保证缓存足够，减少硬盘的读写

-- 尽可能把最小而且查询频率最高的数据，要规划好表空间，放在SSD

--原理：

--索引，减少遍历（由头到尾的查询一次）

-- 2张表

create table t_user5 (

id number primary key,

user_name varchar2(100),

sex number

);

select key_value,display from dicts where key_type='sex' and lang='en'

insert into t_user5 values(1,'小平',1);

insert into t_user5 values(2,'小盛',1);

insert into t_user5 values(3,'小龙',0);

insert into t_user5 values(4,'小君',0);

insert into t_user5 values(5,'小芳',0);

commit

select * from t_user5

select key_value,display from dicts where key_type='sex' and lang='en'

-- 违反主键这个唯一性约束

insert into t_user5(select id,user_name,sex from t_user5)

select myseq.nextval from dual

insert into t_user5(

select

myseq.nextval,

user_name||id,

sex

from t_user5

)

commit

select count(1) from t_user5

-- 0.013 (利用主键唯一索引)

select * from t_user5 where id=8000000

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

-- 0.014

select t_user5.id,t_user5.user_name,dicts.display from t_user5

left join dicts on

t_user5.sex=dicts.key_value

and key_type='sex' and lang='en'

where t_user5.id=8000000

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

--- 2.193秒（相差了156倍）

select count(1) from t_user5

left join dicts on

t_user5.sex=dicts.key_value

and key_type='sex' and lang='en'

-- 耗时2.104

select count(1) from t_user5

left join dicts on

t_user5.sex=dicts.key_value

and key_type='sex' and lang='en'

where t_user5.user_name = '小平1'

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

-- 2.085

select * from t_user5

left join dicts on

t_user5.sex=dicts.key_value

and key_type='sex' and lang='en'

where t_user5.user_name = '小平1'

--- 数据从1000万提升（失败了）

-- 以空间换时间（写好书后，新建目录）

create index user5_name_index

on t_user5(user_name)

alter tablespace oa_data

add datafile 'c:\data\oa_data_003.dbf'

size 100m

autoextend on next 5m maxsize 2048m;

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

--user_name 有了索引后，速度提升会0.045秒

select * from t_user5

left join dicts on

t_user5.sex=dicts.key_value

and key_type='sex' and lang='en'

where t_user5.user_name = '小平1'

select * from dicts

select * from t_user5

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

insert into dicts (

select

myseq.nextval,

display,

myseq.nextval key_value,

key_type,

lang from dicts

)

commit

select count(1) from dicts

-- 没有索引：耗时1秒

select * from dicts where key_type='sex' and lang='en' and key_value=1

---------添加索引

create index dicts_kkl on dicts(key_value,key_type,lang)

-- 有索引：0.02秒

select * from dicts where key_type='sex' and lang='en' and key_value=1

转载于:https://www.cnblogs.com/wyj1212/p/8601538.html