Home » RDBMS Server » Performance Tuning » Inefficient sql
Inefficient sql [message #194704] Sun, 24 September 2006 10:29 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,
Here is an evidently inefficient sql that I wrote, can u help me improve it?

This sql is to find out , in a schema of about 400 tables, which tables have a composite primary key and then delete those table from a master table named mytab. (this table has ALL the table of the schema, and this query is to delete from this table those records..)


SQL> desc mytab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BASET                                     NOT NULL VARCHAR2(100)
 HRBASE                                             VARCHAR2(100)
 PK_COLUMN                                          VARCHAR2(100)
 PK_COLUMN_VALUE                                    NUMBER
 PK_SEQ_VALUE                                       NUMBER

SQL> delete from mytab where baset in 
  2  (
  3  select a.table_name from user_constraints a, user_cons_columns b
  4  where a.constraint_name=b.constraint_name and
  5  a.constraint_type='P' and a.constraint_name in
  6  (
  7  select d.constraint_name from(
  8  select c.constraint_name,count(*) from user_cons_columns c group by
  9  c.constraint_name having count(*)>1) d )
 10  )
 11  /

10 rows deleted.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
         21  recursive calls
         36  db block gets
 159254434  consistent gets 
          2  physical reads
       6304  redo size
        792  bytes sent via SQL*Net to client
        995  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> commit;

Commit complete.



While the query takes a lot of time and see the value of consistent gets - too much for getting rid of 10 rows.

thanks..






[Updated on: Sun, 24 September 2006 10:30]

Report message to a moderator

Re: Inefficient sql [message #194706 is a reply to message #194704] Sun, 24 September 2006 11:11 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
No, please ignore this..I found the mistake of lack of stats! Now after getting the stats,its a wink!

 1  delete from mytab where baset in
  2  (
  3  select a.table_name from user_constraints a, user_cons_columns b
  4  where a.constraint_name=b.constraint_name and
  5  a.constraint_type='P' and a.constraint_name in
  6  (
  7  select d.constraint_name from(
  8  select c.constraint_name,count(*) from user_cons_columns c group by
  9  c.constraint_name having count(*)>1) d )
 10* )
SQL> /

0 rows deleted.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
        301  recursive calls
          0  db block gets
      56850  consistent gets
         32  physical reads
          0  redo size
        796  bytes sent via SQL*Net to client
        995  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          0  rows processed

Sorry!
Previous Topic: Maximum # of sessions. What can we do.
Next Topic: query problem
Goto Forum:
  


Current Time: Thu Dec 08 16:40:42 CST 2016

Total time taken to generate the page: 0.11079 seconds