Home » SQL & PL/SQL » SQL & PL/SQL » inner join with system tables takes too long!!!!!!!
inner join with system tables takes too long!!!!!!! [message #227996] Fri, 30 March 2007 10:36 Go to next message
cefb
Messages: 56
Registered: March 2007
Member
hi there !!!


i made a query using system tables, very simple,

"select * from all_constraints a inner join all_constraints c on a.r_constraint_name = c.constraint name"

is it normal that this takes too much time?


thanks


Re: inner join with system tables takes too long!!!!!!! [message #227999 is a reply to message #227996] Fri, 30 March 2007 11:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, normally this would take just enough time.
Re: inner join with system tables takes too long!!!!!!! [message #228000 is a reply to message #227996] Fri, 30 March 2007 11:10 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
No it's not - the query runs in under 1 sec on my PC (using 10g Enterprise Edition Release 10.2.0.1.0).

How long does it take on your system?
Re: inner join with system tables takes too long!!!!!!! [message #228003 is a reply to message #227996] Fri, 30 March 2007 12:19 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
the last time i tested maybe one minute. but i tested it know and it run quickly.

maybe it was a bad day for my computer.
Re: inner join with system tables takes too long!!!!!!! [message #228005 is a reply to message #227996] Fri, 30 March 2007 12:25 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
That can't be the query you are running as there is a space between constraint and name.
Why didn't you cut and paste the actual command you ran?
Re: inner join with system tables takes too long!!!!!!! [message #228006 is a reply to message #227996] Fri, 30 March 2007 12:27 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
my exact query is

"select a.constraint_name,b.table_name from all_constraints a inner join
all_constraints b on a.r_constraint_name=b.constraint_name and a.owner='xxx';" and it takes an eternity but if i change a.owner='xxx' to a.owner!='SYS' it run quickly
Re: inner join with system tables takes too long!!!!!!! [message #228015 is a reply to message #228006] Fri, 30 March 2007 14:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Somehow, I find it quite hard to believe. There will be more rows for owner <> 'SYS' then for owner = 'xxx'
Also, normally non-equijoins don't outperform equijoins.

Maybe you can show us some copy-paste from sqlplus with timings?
Re: inner join with system tables takes too long!!!!!!! [message #228020 is a reply to message #228015] Fri, 30 March 2007 14:05 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Is 'xxx' a variable in the actual statement?
Re: inner join with system tables takes too long!!!!!!! [message #228029 is a reply to message #228006] Fri, 30 March 2007 15:12 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And I'm sorry, but that is STILL not a cut and paste of what you ran. Why are there double quotes around your statement, splt over two lines?
Is it so hard to just cut and paste the damn thing, and not retype it which is wrought for error?
Re: inner join with system tables takes too long!!!!!!! [message #228231 is a reply to message #227996] Mon, 02 April 2007 03:07 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
xxx is a user.
Re: inner join with system tables takes too long!!!!!!! [message #228249 is a reply to message #228231] Mon, 02 April 2007 04:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please copy-paste from sqlplus.
Re: inner join with system tables takes too long!!!!!!! [message #228261 is a reply to message #227996] Mon, 02 April 2007 05:34 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
select a.constraint_name,b.table_name from all_constraints a inner join all_constraints b on a.r_constraint_name=b.constraint_name
where a.owner='NORTHWIND'


this takes 3 seconds to finish
Re: inner join with system tables takes too long!!!!!!! [message #228277 is a reply to message #228261] Mon, 02 April 2007 06:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
returning how many rows?

SQL> set timing on
SQL> select a.constraint_name,b.table_name from all_constraints a inner join all_constraints b on a.r_constraint_name=b.constraint_name
  2* where a.owner='SCOTT';

CONSTRAINT_NAME                TABLE_NAME                                                                                                             
------------------------------ ------------------------------                                                                                         
<snipped results>

167 rows selected.

Elapsed: 00:00:00.32

SQL> select a.constraint_name,b.table_name from all_constraints a inner join all_constraints b on a.r_constraint_name=b.constraint_name
  2* where a.owner<>'SYS';

CONSTRAINT_NAME                TABLE_NAME                                                                                                             
------------------------------ ------------------------------                                                                                         
<snipped results>

691 rows selected.

Elapsed: 00:00:01.18

[Updated on: Mon, 02 April 2007 06:38]

Report message to a moderator

Re: inner join with system tables takes too long!!!!!!! [message #228294 is a reply to message #227996] Mon, 02 April 2007 07:57 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
using scott user as you did is quickly

but

set timing on;
select a.constraint_name,b.table_name from all_constraints a inner join all_constraints b on a.r_constraint_name=b.constraint_name
where a.owner='NORTHWIND';


CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
FK_EMPLOYEES_EMPLOYEES EMPLOYEES
FK_ORDERS_EMPLOYEES EMPLOYEES
FK_PRODUCTS_CATEGORIES CATEGORIES
FK_CUSTCUSTDEMO_CUSTOMERS CUSTOMERS
FK_ORDERS_CUSTOMERS CUSTOMERS
FK_ORDERS_SHIPPERS SHIPPERS
FK_PRODUCTS_SUPPLIERS SUPPLIERS
FK_ORDER_DETAILS_ORDERS ORDERS
FK_ORDER_DETAILS_PRODUCTS PRODUCTS
FK_CUSTOMERCUSTOMERDEMO CUSTOMERDEMOGRAPHICS
FK_TERRITORIES_REGION REGION

11 rows selected.

Decorreram: 00:00:05.01


Can it be the permissions and roles of the user NORTHWIND or my database definitions?
Re: inner join with system tables takes too long!!!!!!! [message #228298 is a reply to message #228294] Mon, 02 April 2007 08:07 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And I don't see any timing of a query with
a.owner<>'SYS'
.

"quickly" is just a relative term. To me, quickly mean less than a day.
Re: inner join with system tables takes too long!!!!!!! [message #228300 is a reply to message #227996] Mon, 02 April 2007 08:18 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
set timing on;

select a.constraint_name,b.table_name from all_constraints a inner join all_constraints b on a.r_constraint_name=b.constraint_name
where a.owner<>'SYS';


246 rows selected.

Elapsed: 00:00:00.07
Re: inner join with system tables takes too long!!!!!!! [message #228301 is a reply to message #228300] Mon, 02 April 2007 08:26 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Ok, my guess is that the user you are running this in with OWNER<>'SYS' is very limited and can only see it's own objects, therefore <>'SYS' is generally the same as OWNER='{whatever}' and the user you are running ='NORTHWIND' from a user that has more rights.
Re: inner join with system tables takes too long!!!!!!! [message #228302 is a reply to message #228300] Mon, 02 April 2007 08:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is it just this one statement, or do you have other performance issues as well?
And do you use this query in your production code, or is it something you just came across and was wondering about?
(aka, is it interesting or not)
Re: inner join with system tables takes too long!!!!!!! [message #228305 is a reply to message #227996] Mon, 02 April 2007 08:30 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
but my running both using sys user
Re: inner join with system tables takes too long!!!!!!! [message #228306 is a reply to message #228302] Mon, 02 April 2007 08:32 Go to previous message
cefb
Messages: 56
Registered: March 2007
Member
i only found this problem using this type of query. i need this because i'am using it in a asp, so if it takes very long i can't do nothing.
Previous Topic: how create view in procedure
Next Topic: SQL Question
Goto Forum:
  


Current Time: Sat Dec 03 20:14:44 CST 2016

Total time taken to generate the page: 0.07952 seconds