stronghearted life

not in 2 not exists

今天调整sql的时候发现not in的操作，oracle自动会把它调整not exists(可能还更关联的两张表的记录多少有关系)
网上介绍not in和not exists文章比较多，这里我不介绍了

SQL> set autot on
SQL> select t.test_list_id, t.title
  2    from test_list t
  3   where t.owner_id =25704147
  4     and t.status >= 0
  5     and t.test_list_id not in
  6         ((select i.test_list_id
  7             from test_list_item i
  8            where i.test_list_id = t.test_list_id
  9              and i.collect_item_id =
 10                  (select i1.collect_item_id
 11                     from collect_item i1
 12                    where i1.item_id =’123456′)))
 13  /

—————————————————————————————————————
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————————————
|   0 | SELECT STATEMENT             |                                |     3 |    81 |     5   (0)| 00:00:01 |
|*  1 |  FILTER                      |                                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_LIST                     |     3 |    81 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST_LIST_OWNER_STA       |     3 |       |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | IND_TEST_LIST_ITEM_ID_NUM_COL |     1 |     8 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_COLLECT_ITEM_ITEMID        |     1 |    36 |     1   (0)| 00:00:01 |
—————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM “TEST_LIST_ITEM” “I” WHERE “I”.”TEST_LIST_ID”=:B1
              AND “I”.”COLLECT_ITEM_ID”= (SELECT /*+ */ “I1″.”COLLECT_ITEM_ID” FROM “COLLECT_ITEM” “I1″ WHERE
              “I1″.”ITEM_ID”=’123456′)))
   3 - access(”T”.”OWNER_ID”=25704147 AND “T”.”STATUS”>=0 AND “T”.”STATUS” IS NOT NULL)
   4 - access(”I”.”TEST_LIST_ID”=:B1 AND “I”.”COLLECT_ITEM_ID”= (SELECT /*+ */ “I1″.”COLLECT_ITEM_ID”
              FROM “COLLECT_ITEM” “I1″ WHERE “I1″.”ITEM_ID”=’123456′))
       filter(”I”.”COLLECT_ITEM_ID”= (SELECT /*+ */ “I1″.”COLLECT_ITEM_ID” FROM “COLLECT_ITEM” “I1″
              WHERE “I1″.”ITEM_ID”=’123456′))
   5 - access(”I1″.”ITEM_ID”=’123456′)

Posted in 技术基础 | No Comments »

Reverse index and like

今天同事说一个很有趣的问题，我们在用like时，会这样用select * from table_name where table_col like ‘taobao%’。
这样的话，like操作能使用index了，但如果like ‘%taobao’的话，这个就不能使用index了。我同事从理论上推测：如果在
table_col上建个reverse index，like ‘%taobao’也能使用index了。事实确不是这样的。(记得是字节的反转咯，dump出来
看一下就明白了 ^ ^)

测试：
SQL> create table test_reverse_index(a number,b varchar(200));

Table created.

SQL> begin
  2  for i in 1..10 loop
  3  insert into test_reverse_index (select object_id,object_name from dba_objects);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> insert into test_reverse_index values(1000000,’luoxuan’);    

1 row created.

SQL> commit;

Commit complete.

SQL> create index ind_no_reverse_index on test_reverse_index (b) online compute statistics;

Index created.

SQL> set autot traceonly
SQL> select a from test_reverse_index where b like ‘luo%’;
Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=100)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘TEST_REVERSE_INDEX’ (Cos
          t=4 Card=1 Bytes=100)

   2    1     INDEX (RANGE SCAN) OF ‘IND_NO_REVERSE_INDEX’ (NON-UNIQUE
          ) (Cost=3 Card=1)

SQL> drop index ind_no_reverse_index;

Index dropped.
SQL> create index ind_reverse_index on test_reverse_index (b) reverse online compute statistics;

Index created.

SQL> select a from test_reverse_index where b like ‘%xuan’;

Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=124 Card=13634 Bytes
          =490824)

   1    0   TABLE ACCESS (FULL) OF ‘TEST_REVERSE_INDEX’ (Cost=124 Card
          =13634 Bytes=490824)
 

Posted in 技术基础 | No Comments »

ORA-01450

今天在rebuild index online过程中报ORA-01450,网上一查，在create index过程中也可能出现
上metalink一查，原因清楚
PURPOSE
——-
Explain why an ORA-1450 is returned, and how the Maximum Key Length
is calculated.  For more information on the error itself see Note 18854.1.
SCOPE & APPLICATION
——————-
All Audiences
ORA-1450 and Maximum Key Length - How it is Calculated
——————————————————
When creating an Index, the total length of the index cannot exceed a
certain value.  This value depends primarily on the DB_BLOCK_SIZE.  If
an attempt is made to create an index larger than the Maximum value, an
ORA-1450 is raised:

ORA-01450 maximum key length (758) exceeded  ->(2K Block)
ORA-01450 maximum key length (1578) exceeded ->(4K block)
ORA-01450 maximum key length (3218) exceeded ->(8K Block)
ORA-01450 maximum key length (6498) exceeded ->(16K Block)

The number in parends is the maximum allowable length of the index key for that
particular system. 

So, how is this number calculated?

The maximum key size means:

The total index length + length of the key (2 Bytes) + ROWID (6 Bytes) +
the length of the rowid (1 byte). 

The total index length is computed as the sum of the width of all indexed
columns plus the number of indexed columns. Date fields have a length of 7,
character fields have their defined length, and numeric fields have a
length of 22. Numeric length = (precision/2) + 1. If negative, add +1.
For Funtion-based indexes, we must calculate the length of the return type.
This index key size is limited by the value of db_block_size, because a key 
value may not span multiple blocks.  In fact, it is required that any index
block must contain at least TWO index entries per block.

Therefore, the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE.  The Oracle 8i Administrator’s Guide states that the
maximum size of a single index entry is approximately one-half the data block
size.  However, when considering that we must also leave space in the block
according to PCTFREE, INITRANS, and space for block overhead (Block Header,
ROW Directory, Table Directory, etc)  the actual space that can be used for
the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Using default values for these storage options, the maximum length for indexes
is as follows for different block sizes:

DB_BLOCK_SIZE:                  Maximum Index Key Length:
==============                  =========================

2K  (2048)                       758  Bytes
4K  (4096)                       1578 Bytes
8K  (8192)                       3218 Bytes
16K (16384)                      6498 Bytes
If you hit a maximum key length in an index according to the DB_BLOCK_SIZE,
you may need to recreate the database with a larger block size.  The other
alternative is to limit the size of the index.  This is slightly more
difficult with a Function-based index, when the return type is a varchar or
RAW.

To limit the size of a function-based index you should consider using the
SUBSTR or SUBSTRB function, to limit the number of Characters or Bytes
returned.  For more information on SUBTR and SUBSTRB, refer to the
Oracle8i SQL Reference Guide.

Posted in 技术基础 | No Comments »

Rename tablespace in oracle 9i

前段时间发现线上库\开发库\测试库的表空间不同名，那样导致我们在开发库上写好的sql脚本中tablespace name要修改，不是很方便。
这里我和我同事想了三种方法:
比如：表空间Tbs_taobao改为tbs_taobao_db
1、 exp/imp
exp表空间Tbs_taobao，创建一个表空间tbs_taobao_db，imp进行导入。这个过程有许多细节要注意。
2、 和上面差不多，就是利用10g可以rename tablespace的特性。Imp到10g中，再进行rename tablespace，这种方法，我看到有几个老外就在用，呵呵。
3、 最后一个就是利用toad这个工具了，的确方便。
这里我介绍一下使用方法:
我使用的是9.0版本的，选databaseoptimizerebuild multiple objects
在这里，你可以选择你要的tables\index\lob。你选择要的tables，在storage clause adjustments，选择你要移动到那个表空间。可以让toad为你生成相关脚本（我就是这样做的）。其实toad为你做的，就是批量生成alter table table_name remove to tablespace。
Move table后，index都会unusable，toad又可以给你批量rebuild。别忘了rebuild 加个online compute statistics(如果空间允许的话)。总之挺方便的。

不解：rename table在9i中就有了，为什么rename tablespace在10g中才有，我看到orafaq上，有人也问相同的问题，其实就是修改几个数据字典就ok，why?要到10g才有呢？

Read the rest of this entry »