亚洲午夜精品一区二区,亚洲第一福利网站在线观看,亚洲日韩av无码中文

��翼渐丰 — Tue, 25 Aug 2009 08:01:00 GMT

Sql_Trace File Identifier

我们讄��了sql_trace为true之后�Q�trace file�?x��)dump到udump中，但是该目录下也许已经有很多trace file了，或者其他session也设�|�了sql_trace为true�Q�也在��生trace file。如何能�q�速找��C��的session产生的trace file呢？我们可以通过如下语句讄��Sql_Trace File Identifier�Q?br />

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'my_trace_id';

�q�样你的session产生的trace file��׃��(x��)以设�|�的identifier为后�~��?/p>

SQL> alter session set tracefile_identifier='test_identifier';

Session altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from dual;

D
-
X

SQL> alter session set sql_trace=false;

Session altered.

然后我们可以看到udump下生成的trace file文�g名�ؓ(f��)�Q�test_ora_4168_test_identifier.trc

Sample Block

抽样表扫描（sample table scan�Q�只��d��一个表的部分数据。其实我们只选择表的部分数据的话�Q�可以用rownum来限制也很方�ѝ��但是考虑如下情况�Q?br /> A表和B表join�Q�我只想A表的部分数据去连接B表，此时用sample��方便的多。如果用rownum��需要用一个subquery�?br />

--用Sample�Q?/span>
select A.id,A.name from A sample block(20),B
where A.id=B.id
--用Rownum�Q?/span>
select A.id,A.name from (select a.id,a.name from a where rownum<100) A,B
where A.id=B.id

抽样表扫描有两种形式�Q�一�U�是抽样表中行数的一个百分比�Q�一个是抽样表中块数的一个百分比。注意都是百分比。语法如下：(x��)

SELECT * FROM employees SAMPLE (1);--�q�回行数乘以1%
SELECT * FROM employees SAMPLE BLOCK (1);--�q�回块数乘以1%

也可以在视图上��用SAMPLE�?br />

��翼渐丰 2009-08-25 16:01 发表评论

ETL时先disable外键�U�束再load数据

��翼渐丰 — Tue, 15 Jan 2008 08:12:00 GMT

Kimball在他的ETL Toolkit一书中�Q�一直强调：(x��)对于有外键约束的表，��Z��提高load的速度�Q�可以先��外键约束disable�Q�加载完成后再enable�Q�当然另外一个原因是�q�入数据仓库的数据都是规范的�Q�甚臛_��以考虑不��用外键约束，卌��反约束的数据在Transformation部分��应该解��x��Q�当然这是设计问题了�Q�。ps:另外两个提高load速度的方法分别�ؓ(f��)使用sql loader和insert、update分离。当时还不是明白�q�个�q�程怎么具体实现�Q�今天看《oracle concept》看��C��面话�l�于明白了一些：(x��)
Flexibility for Data Loads and Identification of Integrity Violations
You can disable integrity constraints temporarily so that large amounts of data can be loaded without the overhead of constraint checking. When the data load is complete,you can easily enable the integrity constraints, and you can automatically report any new rows that violate integrity constraints to a separate exceptions table.
看来oracle�q�点上做的比较完善，�q�可以将�q�反完整性约束的数据自动记录��C��个exception表中�?

��翼渐丰 2008-01-15 16:12 发表评论

��翼渐丰 — Tue, 08 Jan 2008 02:04:00 GMT

今天google分析函数row_number()的时候发现的�Q�觉得很好：(x��)

表demo是重复拷贝自dba_objects�Q�有88万左叻I��不重复的�?7323�Q�没有烦�?br style="font-family: " /> �Ҏ(gu��)��一�Q�delete from demo a where a.rowid <> (select max(rowid) from demo b where
b.object_id=a.object_id);
耗时�Q�几个小时以�?
�Ҏ(gu��)��二：(x��) delete from demo where rowid in
(select rid from
(select rowid rid,row_number() over(partition by object_id order by rowid) rn
from demo)
where rn <> 1 );
耗时�Q?0�U?br style="font-family: " /> �Ҏ(gu��)��三：(x��) create table demo2 as
select object_id,owner... from
(select demo.*,row_number() over(partition by object_id order by rowid) rn from demo)
where rn = 1;
truncate table demo; insert into demo select * from demo2; drop table demo2;
��p��时�Q?10�U�，适合大数据量的情况，产生更少回滚量；

学到了分析函数row_number(),对于object_id和rowid也有了一些认识。oracle要学的东西太多了�Q�什么时候是个头�?img alt="" src="/CuteSoft_Client/CuteEditor/images/emteeth.gif" align="absMiddle" border="0" />。上面的�Ҏ(gu��)��不是很难理解�Q�但也还没有完全理解�Q�有��Z��(x��)实际试试�?/p>

��翼渐丰 2008-01-08 10:04 发表评论

在load的时候去掉sequential file的header和footer

��翼渐丰 — Mon, 13 Aug 2007 11:37:00 GMT

今天在论坛看到的�Ҏ(gu��)��Q�先记录下来�Q?br>�Ҏ(gu��)��一�Q?/span>
在sequential file stage中有个属性可以ignore�W�一行。对于footer�Q�如果footer使用了与正文不同的格式，可以用��用transformer stage�?@INROWNUM stage来将footer��L��?br>�Ҏ(gu��)��二：(x��)
在sequential file的filter option中��用sed -e '$d' -e '1d'来讲header和footer��L��?br>�Ҏ(gu��)��三：(x��)
写个before job subroutine�Q�可以先对这个文件做各种处理�Q�不�q�我想也是subroutine中调用DSExecute sed -e '$d' -e '1d'。当然Basic中也有专门对sequential file处理的函数�?br>

��翼渐丰 2007-08-13 19:37 发表评论

datastage中Orchadmin命��o(h��)的��用（转）

��翼渐丰 — Wed, 08 Aug 2007 07:37:00 GMT

Orchadmin is a command line utility provided by datastage to research on data sets.
The general callable format is : $orchadmin [options] [descriptor file]

1. Before using orchadmin, you should make sure that either the working directory or the $APT_ORCHHOME/etc contains the file “config.apt” OR

The environment variable $APT_CONFIG_FILE should be defined for your session.
Orchadmin commands

The various commands available with orchadmin are
1. CHECK: $orchadmin check

Validates the configuration file contents like , accesibility of all nodes defined in the configuration file, scratch disk definitions and accesibility of all the nodes etc. Throws an error when config file is not found or not defined properly

2. COPY : $orchadmin copy
Makes a complete copy of the datasets of source with new destination descriptor file name. Please not that

a. You cannot use UNIX cp command as it justs copies the config file to a new name. The data is not copied.

b. The new datasets will be arranged in the form of the config file that is in use but not according to the old confing file that was in use with the source.

3. DELETE : $orchadmin <> [-f -x] descriptorfiles….
The unix rm utility cannot be used to delete the datasets. The orchadmin delete or rm command should be used to delete one or more persistent data sets.
-f options makes a force delete. If some nodes are not accesible then -f forces to delete the dataset partitions from accessible nodes and leave the other partitions in inaccesible nodes as orphans.
-x forces to use the current config file to be used while deleting than the one stored in data set.

4. DESCRIBE: $orchadmin describe [options] descriptorfile.ds
This is the single most important command.
1. Without any option lists the no.of.partitions, no.of.segments, valid segments, and preserve partitioning flag details of the persistent dataset.
-c : Print the configuration file that is written in the dataset if any
-p: Lists down the partition level information.
-f: Lists down the file level information in each partition
-e: List down the segment level information .
-s: List down the meta-data schema of the information.
-v: Lists all segemnts , valid or otherwise
-l : Long listing. Equivalent to -f -p -s -v -e

5. DUMP: $orchadmin dump [options] descriptorfile.ds
The dump command is used to dump(extract) the records from the dataset.
Without any options the dump command lists down all the records starting from first record from first partition till last record in last partition.
-delim ‘’ : Uses the given string as delimtor for fields instead of space.
-field : Lists only the given field instead of all fields.
-name : List all the values preceded by field name and a colon
-n numrecs : List only the given number of records per partition.
-p period(N) : Lists every Nth record from each partition starting from first record.
-skip N: Skip the first N records from each partition.
-x : Use the current system configuration file rather than the one stored in dataset.

6. TRUNCATE: $orchadmin truncate [options] descriptorfile.ds
Without options deletes all the data(ie Segments) from the dataset.
-f: Uses force truncate. Truncate accessible segments and leave the inaccesible ones.
-x: Uses current system config file rather than the default one stored in the dataset.
-n N: Leaves the first N segments in each partition and truncates the remaining.

7. HELP: $orchadmin -help OR $orchadmin -help
Help manual about the usage of orchadmin or orchadmin commands

��翼渐丰 2007-08-08 15:37 发表评论

��翼渐丰 — Thu, 02 Aug 2007 13:01:00 GMT

当一条记录过来，某个字段为空�Q�而在transformer中又对该字段trim了，�׃��该字�D��ؓ(f��)�I�，不能trim�Q�所以DataStage�?x��)认��是个错误�Q�从而把�q�个记录drop了，�q�与实际业务不符合，非主键字�D��ؓ(f��)�I��ƈ不能��把�q�条记录drop了。我们可以通过写一个判断来解决该问题：(x��)if LK_1.EMAIL <> '' then trim(LK_1.EMAIL) else LK_1.EMAIL

��翼渐丰 2007-08-02 21:01 发表评论

DataStage中可以执行routine�Q�命令行以控制的地方

��翼渐丰 — Thu, 02 Aug 2007 11:29:00 GMT

1 before/after job和before/after stage,可以执行shell�Q�命令行以及(qi��ng)一些DataStage API�Q�如DSJobReport
2 job sequence中有个stage�Q�可以在里面写控制job调度的地�?br>3 每个job的properties中有个job control tab
4 命��o(h��)行可以执行、导入job�{�，它通过dsjob实现�Q�在DS Manager的文档中�q�讲了有专门的命令行来导入job
5 可以�~�写shell来控制job�Q�对dsjob命��o(h��)做封装，可以传入参数�{��?br>对于上面�q�些概念�q�是很模�p�，先记录在此�?

��翼渐丰 2007-08-02 19:29 发表评论

trim全�ؓ(f��)null的某个字�D�之后，输出�?

��翼渐丰 — Thu, 02 Aug 2007 07:21:00 GMT

今天又碰到新问题�Q�在transformer中，�q�去是有很多record�Q�出来之后�ؓ(f��)0条record�Q�用peek�Q�看了一下，�q�去的数据的某个字段数据全�ؓ(f��)null�Q�而在transformer中对此字�D�trim了，改�ؓ(f��)不trim��可以了�?

��翼渐丰 2007-08-02 15:21 发表评论

��翼渐丰 — Wed, 01 Aug 2007 09:33:00 GMT

今天遇到一个问题，job不停的在那里�q�行�Q�然后link上的数据昄��各个环节都是正确的，包括最后插入数据库的link上也昄��了数据，但是最后数据库里�ƈ没有数据。在Director的log中，日志在从两个源文件把所有数据load出来完之后，日志��死在那里了�?br>以前�q�个job是正��的�Q�昨天由于重新l(f��)oad其中一个源文�g的元数据�Q�所以出��C��上述问题。所以先前以为是�׃��load的新的源数据有问题，��׃��此处来找问题的原因，�q�且认�ؓ(f��)可能是改了元数据�Q�在其他地方映射的时候有位置不对的地方，所以整了很久。因��Z��前是好好的，然后又以为是服务器的问题�?br>
�q�都是定势思维的错误，然后又一急，所以浪费了很多旉��Q�其实很多时候都是这��P��Z��问题我们不能理性的好好思考�?/span>

其实问题很简单：(x��)
如果我们按照正常逻辑来分析的话，既然不能��d��数据库，肯定是数据不�W�合数据库对数据的约束，包括主键啊，非空啊，本问题就是由于在stage的不断流转中产生了很多空��|��使得最后待插入的数据长度远�q�大于数据库中定义的字段长度。由于在那里不断reject�Q�所以媄响了速度�Q�job一直在那里�q�行。最后用APT_STRING_PADDER,��其设�ؓ(f��)0x0�Q�用null代替�I�格�Q�搞定�?br>ps�Q�在插入数据库时使用一个reject文�g�Ҏ(gu��)��错有好处�Q�这栯��看到reject是些什么数据，然后��p��知道��Z��么被reject了�?br>同时我们可以得出如果最后插入数据库时很多数据被reject�Q�但是你�q�没有用一个reject文�g来接收这些reject掉的数据�Q�将使得job基本处于停滞状态�?/span>

��翼渐丰 2007-08-01 17:33 发表评论

��翼渐丰 — Wed, 01 Aug 2007 02:17:00 GMT

当一个Job跑到一半终止了要还原，在DS Director中主要有三种�Ҏ(gu��)��来还原：(x��)
1 Job-----Reset
2 Job-----Clear Status File
3 Job-----Cleanup Resource
具体�l�节也不懂，有时间慢慢研�I?/p>

��翼渐丰 2007-08-01 10:17 发表评论