Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Query optimization ?

    Hi,

    I am trying the below query :

    SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS uo , USER_NESTED_TABLES unt WHERE OBJECT_TYPE ='TABLE' AND uo.OBJECT_NAME NOT in (SELECT TABLE_NAME FROM USER_NESTED_TABLES) ORDER BY OBJECT_ID

    Is there anyway to optimize the above query. It takes a long time for execution.

    Please advice.

    Thanks,
    Sam

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    use explain plan to check what happen and how your query
    works. it's hard to tell you what you should do to optimize
    your query, it depends on many things. maybe create an
    index will help. or see some tutorials (how to optimize queries)
    for more info.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I agree, use explain plan to see what is going on .... Statistics updated ???

    You might also try :

    SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS uo
    WHERE OBJECT_TYPE ='TABLE' AND not exists (SELECT 'x' FROM USER_NESTED_TABLES where table_name = uo.object_name) ORDER BY OBJECT_ID

    HTH
    Gregg

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    don't bother with the plan, since it doesn't need to be optimized. you're creating a cartesian product between 2 very complex oracle views, so of course it takes forever. ITS WRONG. Why is user USER_NESTED_TABLES in the from clause? It's not in the select list or where clause. Look at the query, think about what you want, write the correct query, then worry about performance.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •