高效的SQL（Index-Organized Tables優化精確查詢和範圍查詢）

高效的SQL（Index-Organized Tables優化精確查詢和範圍查詢）

本文3部分

1、建立索引組織表（PCTTHRESHOLD和OVERFLOW的意義）

2、索引組織表中刪除大量資料及重建索引（不是index rebuild而是alter table tablenames move online）

3、索引組織表查詢優化（2種情況：3-1、exact match | 3-2、range search）【還有一種情況exact match 和range search同時使用，但我們的實驗沒有涉及】

1、 create Index-Organized Tables

CREATE TABLE doudou_idx(

token char(20),

doc_id NUMBER,

token_frequency NUMBER,

token_offsets VARCHAR2(2000),

CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))

ORGANIZATION INDEX

TABLESPACE doudou_index

PCTTHRESHOLD 20

OVERFLOW TABLESPACE users;

This example creates an index-organized table named doudou_idx_organized, with a primary key composed of the columns token and doc_id. The OVERFLOW and PCTTHRESHOLD clauses specify that if the length of a row exceeds 20% of the index block size, then the column that exceeded that threshold and all columns after it are moved to the overflow segment. The overflow segment is stored in the user tablespace

doudou@TEST> CREATE TABLE doudou_idx(

2 token char(20),

3 doc_id NUMBER,

4 token_frequency NUMBER,

5 token_offsets VARCHAR2(2000),

6 CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))

7 ORGANIZATION INDEX

8 TABLESPACE doudou_index

9 PCTTHRESHOLD 20

10 OVERFLOW TABLESPACE users;

Table created.

doudou@TEST> begin

2 for i in 1..100000 loop

3 insert into doudou_idx (token,doc_id) values (i,i+2);

4 end loop;

5 commit;

6 end ;

7 /

PL/SQL procedure successfully completed.

doudou@TEST> select index_name,index_type,status from user_indexes where table_name='DOUDOU_IDX';

INDEX_NAME INDEX_TYPE STATUS

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

PK_ADMIN_DOCINDEX IOT - TOP VALID

【索引組織表的索引index_type 是IOT – TOP】

2、deleting substantial amount of rows on index organized tables

doudou@TEST> analyze index PK_ADMIN_DOCINDEX validate structure;

doudou@TEST> select height, blocks, del_lf_rows from index_stats;

HEIGHT BLOCKS DEL_LF_ROWS

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

3 896 0

doudou@TEST> delete from doudou_idx where mod(doc_id,2)=1;

50000 rows deleted.

doudou@TEST> commit;

Commit complete.

doudou@TEST> analyze index PK_ADMIN_DOCINDEX validate structure;

doudou@TEST> select height, blocks, del_lf_rows from index_stats;

HEIGHT BLOCKS DEL_LF_ROWS

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

3 896 50000

索引組織表中重建索引（move online）【9i以後有的特性】

doudou@TEST> alter table doudou_idx move online;

Table altered.

doudou@TEST> analyze index PK_ADMIN_DOCINDEX validate structure;

doudou@TEST> select height, blocks, del_lf_rows from index_stats;

HEIGHT BLOCKS DEL_LF_ROWS

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

2 256 0

Delete many rows of index organized tables are OK!

3、Using Index-Organized Tables for Performance

Index-organized tables data rows are stored in the index, index-organized tables provide faster key-based access to table data for queries that involve exact match or range search or both.

3-1：exact match

doudou@TEST> select * from doudou_idx where token='6' and doc_id=8;

Execution Plan

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

Plan hash value: 2472525284

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

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

| 0 | SELECT STATEMENT | | 1 | 1050 | 2 (0)| 00:00:01 |

|* 1 | INDEX UNIQUE SCAN| PK_ADMIN_DOCINDEX | 1 | 1050 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("TOKEN"='6' AND "DOC_ID"=8)

Statistics

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

1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

615 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

doudou@TEST> select * from doudou_test where token='6' and doc_id=8;

Execution Plan

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

Plan hash value: 930504952

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

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

| 0 | SELECT STATEMENT | | 1 | 1050 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DOUDOU_TEST | 1 | 1050 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_TEST | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("TOKEN"='6' AND "DOC_ID"=8)

Statistics

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

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

615 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

【索引組織表中2 consistent gets，一般表中3 consistent gets，同樣都INDEX UNIQUE SCAN ，為什麼一般表會多一次consistent gets呢？多出的一次consistent gets就是附加的索引掃描IO。實驗環境效果不是很明顯，生產環境中會有效果的。】

結論：索引組織表exact match query 效能高

3-2：range search

doudou@TEST> select * from doudou_idx where token like '22%';

556 rows selected.

Elapsed: 00:00:00.03

Execution Plan

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

Plan hash value: 2166667558

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

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

| 0 | SELECT STATEMENT | | 841 | 862K| 12 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| PK_ADMIN_DOCINDEX | 841 | 862K| 4 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("TOKEN" LIKE '22%')

filter("TOKEN" LIKE '22%')

Note

-----

- dynamic sampling used for this statement

Statistics

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

557 recursive calls

0 db block gets

185 consistent gets

0 physical reads

0 redo size

22453 bytes sent via SQL*Net to client

807 bytes received via SQL*Net from client

39 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

556 rows processed

doudou@TEST> select * from doudou_test where token like '22%';

556 rows selected.

Elapsed: 00:00:00.01

Execution Plan

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

Plan hash value: 502932140

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

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

| 0 | SELECT STATEMENT | | 556 | 570K| 7 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DOUDOU_TEST | 556 | 570K| 7 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | PK_TEST | 556 | | 4 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("TOKEN" LIKE '22%')

filter("TOKEN" LIKE '22%')

Note

-----

- dynamic sampling used for this statement

Statistics

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

425 recursive calls

0 db block gets

214 consistent gets

0 physical reads

0 redo size

22453 bytes sent via SQL*Net to client

807 bytes received via SQL*Net from client

39 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

556 rows processed

【索引組織表中185 consistent gets，一般表中214 consistent gets。】

結論：索引組織表range search query 效能高

總結：

1、 索引組織表資料儲存在索引裡，減少了附加的索引掃描IO，提高了效能

2、 索引組織表重建索引是（alter table tablenames move online;）【不是index rebuild 需注意】

3、 索引組織表PCTTHRESHOLD 和OVERFLOW 的意義：

PCTTHRESHOLD描述了一行長度與索引塊大小的關係。

OVERFLOW指明所有列move到的新表空間或說新的段。

附表

Creating Index-Organized Tables

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables012.htm#ADMIN01506

deleting substantial amount of rows on index organized tables

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:239614547000 （asktom）

Using Index-Organized Tables for Performance

http://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm#PFGRF94787

普通表（HOT）與索引組織表（IOT）的不同

Table 3-4 Comparison of Heap-Organized Tables with Index-Organized Tables

Heap-Organized Table	Index-Organized Table
The rowid uniquely identifies a row. Primary key constraint may optionally be defined.	Primary key uniquely identifies a row. Primary key constraint must be defined.
Physical rowid in ROWID pseudocolumn allows building secondary indexes.	Logical rowid in ROWID pseudocolumn allows building secondary indexes.
Individual rows may be accessed directly by rowid.	Access to individual rows may be achieved indirectly by primary key.
Sequential full table scan returns all rows in some order.	A full index scan or fast full index scan returns all rows in some order.
Can be stored in a table cluster with other tables.	Cannot be stored in a table cluster.
Can contain a column of the LONG data type and columns of LOB data types.	Can contain LOB columns but not LONG columns.
Can contain virtual columns (only relational heap tables are supported).	Cannot contain virtual columns.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-752322/，如需轉載，請註明出處，否則將追究法律責任。

高效的SQL（Index-Organized Tables優化精確查詢和範圍查詢）

相關文章