oracle xml效率高,xmltype字段的查询速度很慢，如何优化？

bill@BILLORA> declare

2  a varchar2(32767):='

---

3  

4  

5  205

6  Higgins

7  12008

8  

9  

10  

11  ';

12  begin

13     insert into t select rownum, a from dual connect by rownum<=200;

14     commit;

15  end;

16  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.43

bill@BILLORA> select dbms_rowid.rowid_block_number(rowid), count(*) from t group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

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

558         20

560         20

526         20

528         20

527         20

553         20

557         20

525         20

559         20

524         20

10 rows selected.

Elapsed: 00:00:01.15

bill@BILLORA> select length(val) from t where rownum=1;

LENGTH(VAL)

-----------

156

Elapsed: 00:00:00.35

bill@BILLORA> create table t2 (id int, val varchar2(256));

Table created.

Elapsed: 00:00:00.46

bill@BILLORA> insert into t2 select rownum, lpad('a', 156, 'a') from dual connect by rownum<=200;

200 rows created.

Elapsed: 00:00:00.06

bill@BILLORA> select dbms_rowid.rowid_block_number(rowid), count(*) from t2 group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

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

567         44

565         43

568         44

564         43

566         26

Elapsed: 00:00:00.03

bill@BILLORA> truncate table t2;

Table truncated.

Elapsed: 00:00:00.01

bill@BILLORA> insert into t2 select rownum, lpad('a', 300, 'a') from dual connect by rownum<=200;

insert into t2 select rownum, lpad('a', 300, 'a') from dual connect by rownum<=200

*

ERROR at line 1:

ORA-12899: value too large for column "BILL"."T2"."VAL" (actual: 300, maximum: 256)

Elapsed: 00:00:00.03

bill@BILLORA> alter table t2 modify val varchar2(1024);

Table altered.

Elapsed: 00:00:02.16

bill@BILLORA> insert into t2 select rownum, lpad('a', 340, 'a') from dual connect by rownum<=200;

200 rows created.

Elapsed: 00:00:00.01

bill@BILLORA> select dbms_rowid.rowid_block_number(rowid), count(*) from t2 group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

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

573         20

567         20

570         20

565         20

576         20

568         20

575         20

569         20

564         20

566         20

10 rows selected.

bill@BILLORA> declare

2  a varchar2(32767):='

---

3  

4  

5  205

6  Higgins

7  12008

8  

9  

10  

11  ';

12  begin

13     dbms_output.put_line(length(a));

14  end;

15  /

156

PL/SQL procedure successfully completed.