On May 17, 1:28 pm, gayathri Dev <gd0...@gmail.com> wrote:
> Hi All,
>
> I have a question on performance..
>
> After adding a CLOB column to the existing table, "select * from table" is
> taking a while to retrieve.
> But the cost in the Explain plan looks same.
>
> Is there a way to make the retrieve faster by adding any index on the CLOB
> column? Please suggest
>
> Thanks in advance!
> G
No, since you cannot create an index on columns or attributes whose
type is user-defined, LONG, LONG RAW, LOB, or REF. Besides when you
added the CLOB column an index was generated automatically. Simply
adding a CLOB column doesn't dramatically change the retrieval time
from what I see in this example:
SQL> create table mytest(
2 myid number,
3 myval varchar2(20),
4 mydt date);
Table created.
Elapsed: 00:00:00.00
SQL>
SQL> begin
2 for i in 1..1000 loop
3 insert into mytest
4 values(i, 'Test data '||i, sysdate + mod(i,7));
5 end loop;
6
7 commit;
8
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL>
SQL> set autotrace on timing on trimspool on
SQL>
SQL> select * From mytest;
MYID MYVAL MYDT
---------- -------------------- ---------
1 Test data 1 18-MAY-11
2 Test data 2 19-MAY-11
3 Test data 3 20-MAY-11
4 Test data 4 21-MAY-11
5 Test data 5 22-MAY-11
6 Test data 6 23-MAY-11
7 Test data 7 17-MAY-11
8 Test data 8 18-MAY-11
9 Test data 9 19-MAY-11
10 Test data 10 20-MAY-11
11 Test data 11 21-MAY-11
...
705 Test data 705 22-MAY-11
706 Test data 706 23-MAY-11
707 Test data 707 17-MAY-11
708 Test data 708 18-MAY-11
709 Test data 709 19-MAY-11
1000 rows selected.
Elapsed: 00:00:00.30
Execution Plan
----------------------------------------------------------
Plan hash value: 1692938441
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 34000 | 3 (0)|
00:00:01 |
| 1 | TABLE ACCESS FULL| MYTEST | 1000 | 34000 | 3 (0)|
00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
68 recursive calls
0 db block gets
86 consistent gets
0 physical reads
0 redo size
35967 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL>
SQL> set autotrace off timing off
SQL>
SQL> select index_name from user_indexes;
no rows selected
SQL>
SQL> alter table mytest add myclob clob lob(myclob) store as
(tablespace users chunk 4000);
Table altered.
SQL>
SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
SYS_IL0000016854C00004$$ LOB
SQL>
SQL> update mytest set myclob = rpad(myval, 20000, '*');
1000 rows updated.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set autotrace on timing on
SQL>
SQL> select * From mytest;
MYID MYVAL MYDT MYCLOB
---------- -------------------- ---------
--------------------------------------------------------------------------------
1 Test data 1 18-MAY-11 Test data
1*********************************************************************
2 Test data 2 19-MAY-11 Test data
2*********************************************************************
3 Test data 3 20-MAY-11 Test data
3*********************************************************************
4 Test data 4 21-MAY-11 Test data
4*********************************************************************
5 Test data 5 22-MAY-11 Test data
5*********************************************************************
6 Test data 6 23-MAY-11 Test data
6*********************************************************************
7 Test data 7 17-MAY-11 Test data
7*********************************************************************
8 Test data 8 18-MAY-11 Test data
8*********************************************************************
9 Test data 9 19-MAY-11 Test data
9*********************************************************************
10 Test data 10 20-MAY-11 Test data
10********************************************************************
11 Test data 11 21-MAY-11 Test data
11********************************************************************
...
786 Test data 786 19-MAY-11 Test data
786*******************************************************************
787 Test data 787 20-MAY-11 Test data
787*******************************************************************
789 Test data 789 22-MAY-11 Test data
789*******************************************************************
790 Test data 790 23-MAY-11 Test data
790*******************************************************************
792 Test data 792 18-MAY-11 Test data
792*******************************************************************
1000 rows selected.
Elapsed: 00:00:01.50
Execution Plan
----------------------------------------------------------
Plan hash value: 1692938441
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 1988K| 5 (0)|
00:00:01 |
| 1 | TABLE ACCESS FULL| MYTEST | 1000 | 1988K| 5 (0)|
00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
3022 consistent gets
2000 physical reads
0 redo size
643319 bytes sent via SQL*Net to client
224385 bytes received via SQL*Net from client
2002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL>
SQL> set autotrace off timing off
SQL>
SQL> drop table mytest purge;
Table dropped.
SQL>
SQL> create table mytest(
2 myid number,
3 myval varchar2(20),
4 mydt date);
Table created.
SQL>
SQL> begin
2 for i in 1..1000 loop
3 insert into mytest
4 values(i, 'Test data '||i, sysdate + mod(i,7));
5 end loop;
6
7 commit;
8
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> select index_name, index_type from user_indexes;
no rows selected
SQL>
SQL> alter table mytest add myclob clob;
Table altered.
SQL>
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
SYS_IL0000016857C00004$$
SQL>
SQL> update mytest set myclob = rpad(myval, 20000, '*');
1000 rows updated.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set autotrace on timing on
SQL>
SQL> select * From mytest;
MYID MYVAL MYDT MYCLOB
---------- -------------------- ---------
--------------------------------------------------------------------------------
1 Test data 1 18-MAY-11 Test data
1*********************************************************************
2 Test data 2 19-MAY-11 Test data
2*********************************************************************
3 Test data 3 20-MAY-11 Test data
3*********************************************************************
4 Test data 4 21-MAY-11 Test data
4*********************************************************************
5 Test data 5 22-MAY-11 Test data
5*********************************************************************
6 Test data 6 23-MAY-11 Test data
6*********************************************************************
7 Test data 7 17-MAY-11 Test data
7*********************************************************************
8 Test data 8 18-MAY-11 Test data
8*********************************************************************
9 Test data 9 19-MAY-11 Test data
9*********************************************************************
10 Test data 10 20-MAY-11 Test data
10********************************************************************
11 Test data 11 21-MAY-11 Test data
11********************************************************************
...
786 Test data 786 19-MAY-11 Test data
786*******************************************************************
787 Test data 787 20-MAY-11 Test data
787*******************************************************************
789 Test data 789 22-MAY-11 Test data
789*******************************************************************
790 Test data 790 23-MAY-11 Test data
790*******************************************************************
792 Test data 792 18-MAY-11 Test data
792*******************************************************************
1000 rows selected.
Elapsed: 00:00:01.55
Execution Plan
----------------------------------------------------------
Plan hash value: 1692938441
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 1988K| 5 (0)|
00:00:01 |
| 1 | TABLE ACCESS FULL| MYTEST | 1000 | 1988K| 5 (0)|
00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
49 recursive calls
0 db block gets
3026 consistent gets
2000 physical reads
0 redo size
643319 bytes sent via SQL*Net to client
224385 bytes received via SQL*Net from client
2002 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL>
Show us an autotrace report for your query and maybe we can see where
the problem lies.
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
