ITPub博客

By placing a constraint in the enabled novalidated state, you enable the constraint without locking the table.如doc所言，把constriant由状态disable变成enable时在表上会产生lock，期间所有的DML, queries, or DDL都被阻塞（If you change a constraint from disabled to enabled, then the table must be locked. No new DML, queries, or DDL can occur, because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation.）。阻塞的原因是：because there is no

primary key和unique约束是要依赖index的，下面通过试验来看看他们之间的依赖关系![@more@]SQL> select * from tt;ID NA---------- --1 a2 b3 c4 d5 e6 f1000 h已选择7行。SQL> alter table tt add constraint pk_tt primary key (id);表已更改。SQL> select index_name,table_name,uniqueness,status from user_indexes where table_name='TT';INDEX_NAME TABLE_NAME UNIQUENES STATUS------------------------------ ------------------------------ --------- --------PK_TT TT UNIQUE VALIDSQL> alter table tt add const

tanfufa 上传了一些不错的东西，记录一下！[@more@]http://www.itpub.net/thread-976360-1-1.html

之前没有想过这个问题，第一次看到这样的说法是在piner的面试题中。当时觉得有点意思，这几天细读doc，上面也提到了这样的说法：Consider an index on a table on which a lot of DML has been performed. Because of the DML, the size of the index can increase to the point where each block is only 50% full, or even less. If the index refers to most of the columns in the table, then the index could actually be larger than the table.[@more@]doc：Re-creating IndexesYou might want to re-create an index to compact

看到经常有人问此问题！doc的原话，看到了随手记录下！[@more@]A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:CREATE INDEX comp_ind ON table1(x, y, z); x, xy, and xyz combinations of columns are leading portions of the index yz, y, and z combinations of columns are not leading portions of the index

那么究竟什么是DETERMINISTIC Functions，看看doc的解释！DETERMINISTIC FunctionsAny user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future.[@more@]SQL> desc tt 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID

从10g开始CBO下group by之后不能保证数据是按照分组字段排序的！[@more@]大致步骤：SQL> select id , avg(age) from tt1 group by id; ID AVG(AGE)---------- ---------- 6 0 4 0 3 0 0 0 10 1执行计划----------------------------------------------------------Plan hash value: 112617873---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (

在CBO下，oracle会把不含通配符的like表达式转为为等式，前提是like前面的字段类型必须是可变宽度字符类型的。当遇到char类型的字段时它不会这样做！[@more@]doc描述：The optimizer simplifies conditions that use the LIKE comparison operator to compare an expression with no wildcard characters into an equivalent condition that uses an equality operator instead.In the following example, the optimizer simplifies the first condition into the second:last_name LIKE 'SMITH' is transformed intolast_name = 'SMIT

可以通过dbms_stats提供的set_table_stats来修改表的统计信息从而使cbo产生我们希望的执行计划！[@more@]SQL> edit已写入 file afiedt.buf 1 DECLARE 2 num_rows NUMBER; 3 num_blocks NUMBER; 4 avg_row_len NUMBER; 5 BEGIN 6 -- retrieve the values of table statistics on OE.ORDERS 7 -- statistics table name: OE.SAVESTATS statistics ID: TEST1 8 DBMS_STATS.GET_TABLE_STATS('XYS','TT',null, 9 'MY_STAT_TAB','TEST_20080416', 10 num_rows,num_blocks,avg_row_len); 11 --

通过dbms_stats提供的几个procedure可以监视stale statistics的对象以便之后对这些对象搜集statistics。[@more@]SQL> exec dbms_stats.alter_schema_tab_monitoring('xys');PL/SQL 过程已成功完成。SQL> select * from tt;ID N---------- -1 a2 A3 B4 b5 cSQL> update tt set name='m';已更新5行。SQL> commit;提交完成。--user_tab_modifications中没有记录，oracle flush sga到user_tab_modifications可能需要一段时间，不过我们可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来手动flushSQL> select * from user_t

利用dbms_stats的procedure gather_schema_stats 和gather_database_stats通过使用参数options 和objlist可以列出那些没有统计信息或者统计信息stale的对象。[@more@]其中options的值可以是：ValueMeaningGATHER STALEGathers statistics on tables with stale statistics.GATHERGathers statistics on all tables. (default)GATHER EMPTYGathers statistics only on tables without statistics.LIST STALECreates a list of tables with stale statistics.LIST EMPTYCreates a list of tables that do not have statistics.GATHER AUTOGa

在创建函数index之后系统会自动产生虚拟列，而这一列也是隐藏的，在创建index时系统自动使用了compute statistics选项为创建的index搜集了statistics，然而并不会为生成的虚拟列搜集statistics，这时如果需要为虚拟列搜集statistics就需要重新分析表或者单独为虚拟列搜集statistics。[@more@]SQL> create table tt(id int , name char(1));表已创建。SQL> insert into tt values(1 , 'a');已创建 1 行。SQL> insert into tt values(2 , 'A');已创建 1 行。SQL> insert into tt values(3 , 'B');已创建 1 行。SQL> insert i

dbms_stats package中提供了过程export_X_stats和import_X_stats（其中X代表column,table,index,schema,database）可以实现统计信息的export和import。通过这种方法我们可以把生产环境的统计信息导入到测试库中模拟生产库。[@more@]SQL> exec dbms_stats.create_stat_table('XYS','MY_STAT_TAB');PL/SQL 过程已成功完成。SQL> DESC MY_STAT_TAB; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- STATID

doc上的原话，随手记录一下。 [@more@]Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for mor

对象被分析之后，所有和该对象相关的被parse过的sql都会失效，下次需要执行相同的sql时需要再次parse，因此当系统执行过搜集statistics的操作后面临的可能是在短时间内产生很多hard parse。 其实非常容易理解，因为有了新的statistics，oracle在下次执行相同的sql时会使用新的statistics产生新的执行计划。[@more@]When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. Oracle also invalidates any currentl

Use the DBMS_STATS package to generate statistics.Statistics generated include the following:[@more@]Use the DBMS_STATS package to generate statistics.Statistics generated include the following:Table statistics Number of rows Number of blocks Average row length Column statistics Number of distinct values (NDV) in column Number of nulls in column Data distribution (histogram) Index statistics Number of leaf blocks Levels Clustering factor System statistics I/O performance and utilization CPU perf

user_tab_cols differs from "USER_TAB_COLUMNS" in that hidden columns are not filtered out。10.2版本中user_tab_cols比user_tab_columns多出下面几列：HIDDEN_COLUMN VARCHAR2(3)VIRTUAL_COLUMN VARCHAR2(3)SEGMENT_COLUMN_ID NUMBERINTERNAL_COLUMN_ID NOT NULL NUMBERQUALIFIED_COL_NAME VARCHAR2(4000)[@more@]简单的测试过程如下：SQL> desc tt名称 是否为空? 类型----------------------------------------- -------- ----------------------------ID NOT NULL NUMBER(38)NAME VARCHAR2(10)AGE NUMBER(38)COL_HIDD

index和index之间是如何进行hash join的，因为index中包括了rowid，所以通过rowid可以连接。Index JoinsAn index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation. The index join is available only with the CBO.Index Join HintsYou can specify an inde

其实Fast Full Index Scans是针对full table scan而言的,Fast Full Index Scans的一些特点也和full table scan相似(如：多块读)：Fast Full Index Scans的一些特点：1.Fast full scan is available only with the CBO2.at least one column in the index key has the NOT NULL constraint3. A fast full scan accesses the data in the index itself, without accessing the table4.It cannot be used to eliminate a sort operation, because the data is not ordered by the index key.（粗略的试验了一下发现也是排好序了，按照index_k

唯一index和非唯一index的index entry中尽管都存在rowid，但是其实有一点细微的差别，在唯一index中index_column就是index_column, 数据通过index_column排序；而在非唯一index中index_column其实是index_key+rowid，如果index_key相同，那么会使用rowid排序。[@more@]Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.--=======================================================SQL> select /*+ index(a idx_tt) */ a.*,rowid from tt a wh