Good Efforts David, Appreciate it. On Wed, May 18, 2011 at 6:23 AM, ddf <orat...@msn.com> wrote:
> > > 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 > -- 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
