| Inefficient sql [message #194704] |
Sun, 24 September 2006 10:29  |
orausern
Messages: 826
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  |
orausern
Messages: 826
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
