oracle使用细节

1、clob类型字段的数据查找：
SELECT t.* FROM 表名 t where dbms_lob.instr(表字段,’模糊查询的字符串’,1,1) > 0
大于0，则这个字段的值包含了你要查找的数据

2、oracle的并行查询：
SELECT /+ Parallel(t,8) / * FROM emp t;
SELECT /+ Parallel(8) / * FROM emp t;

3、job的创建于删除：
begin
sys.dbms_scheduler.create_job(
job_name => ‘stl.pro_monthly_job’,
job_type => ‘stored_procedure’,
job_action => ‘stl.pro_tmp_monthly_job’,
start_date => to_date(‘2016-03-01 00:00:00’,’yyyy-mm-dd hh24:mi:ss’),
repeat_interval => ‘freq=monthly; interval=1; bymonthday=1;byhour=0;byminute=0;bysecond=0’, –每天都跑
end_date => to_date(null),
job_class => ‘DEFAULT_JOB_CLASS’,
enabled => true,
auto_drop => false,
comments => ‘XXXXjob’);
end;

删除：
begin
dbms_scheduler.drop_job(job_name => ‘PRO_MONTHLY_JOB’,force => TRUE);
commit;
end;

4、如何让oracle的select强制走索引：
SELECT /+INDEX(SEG IDX_T_RES_ALLOSEG_ALLOID)/
ALLO.ALLOID AS RESID,
NULL AS AWB,
ALLO.ALLOTMENT AS ALLO_ID,
DAYS.FDATE + NVL(SEG.DAYSDISP, 0) AS FDATE,
ALLO.SPECULD AS SPECULD
FROM T_RES_ALLO ALLO, T_RES_ALLOSEG SEG, V_FDATE DAYS
WHERE ALLO.ALLOID = SEG.ALLOID
AND ((ALLO.ALLOIND = ‘A’ AND ALLO.ALLO_DATE = DAYS.FDATE) OR
(ALLO.ALLOIND = ‘S’ AND
NVL(ALLO.SDATE, ALLO.ALLO_DATE) = DAYS.FDATE))

1、/+INDEX(SEG IDX_T_RES_ALLOSEG_ALLOID)/ 这里的/…/中间不要有空格

2、表名要用别名，即：以上面的sql语句为例，要使用SEG，而不是T_RES_ALLOSEG

强制走多个索引
使用hint技术,表别名+索引名
select/*+
INDEX(pa IDX_PAGREE_1)
INDEX(pi IDX_PITEM_5)
INDEX(pd IDX_PRODUCTS_3)
/
from table1 c,
table2 pa,
table3 pi,
table4 pd
where pa.customerid = c.customerid
and pi.purchaseagreementid = pa.id
and pi.productid = pd.id
and pd.statusid=3601;
5、查询表中有几个字段：
select count(*) from user_tab_columns c where c.table_name=upper(‘表名’)

6、查表的数据
select table_name,num_rows from all_tables r
where r.ownEr = ‘XXX’–用户名
and r.TABLE_NAME =’XXX’–表名大写
查询所有表的数据量：
1.对表进行分析
analyze table DBACCADM.PD_PRODSVC_REL COMPUTE STATISTICS;

2.查询数据库用户表
select table_name,num_rows from all_tables;

7、创建主键：
alter table T_NDVD(表名) add constraint pk_T_NDVD_id primary key(id);

8：快速创建表
create table XXXX_tmp as
Select * From XXXX v
Where 1 = 1
And v.VOUCHER_BEGIN_TIME >= TO_DATE(‘201601’ || ‘01’, ‘yyyymmdd’)
And v.VOUCHER_END_TIME <= ADD_MONTHS(TO_DATE(‘201601’ || ‘01’, ‘yyyymmdd’),1);

9：EXECUTE IMMEDIATE ‘DELETE FROM XXXX T WHERE T.PERIOD >= :BEGIN_PERIOD AND T.PERIOD < :END_PERIOD’ USING V_START_PERIOD, V_END_PERIOD;
using 传入两个参数

10：识别’低效执行’的SQL语句：
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;