Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60

    Question Unanswered: WHy so long in new Oracle versions

    To list Table names without SnapShots and snapshot logs, I Used this query which was very fast (instantaneous)

    select Owner||'.'||TABLE_name from sys.ALL_TABLES
    where Owner||'.'||TABLE_name not in (
    select Owner||'.'||Table_name from sys.ALL_SNAPSHOTS
    union
    select LOG_Owner||'.'||LOG_TABLE from sys.ALL_SNAPSHOT_LOGS)

    With new versions of Oracle Snapshots views are obsolete and I use now te following Query

    select Owner||'.'||TABLE_name aObj from sys.ALL_TABLES
    where Owner||'.'||TABLE_name not in (
    select Owner||'.'||mview_name aObj from sys.all_MVIEWS
    union
    select LOG_Owner||'.'||LOG_TABLE aObj from sys.ALL_MVIEW_LOGS)

    Whichh is very Long

    Thanks to every body that can tell why and how to optimize this query

    Regards
    Philippe

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: WHy so long in new Oracle versions

    For a start you could drop the concatenations, which preclude using any indexes on those columns:

    select Owner||'.'||TABLE_name aObj from sys.ALL_TABLES
    where (Owner, TABLE_name) not in (
    select Owner, mview_name aObj from sys.all_MVIEWS
    union
    select LOG_Owner, LOG_TABLE aObj from sys.ALL_MVIEW_LOGS)

    Also, MINUS may be better performance-wise than NOT IN:

    select Owner, TABLE_name aObj from sys.ALL_TABLES
    MINUS
    (
    select Owner, mview_name aObj from sys.all_MVIEWS
    union
    select LOG_Owner, LOG_TABLE aObj from sys.ALL_MVIEW_LOGS
    );

  3. #3
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60

    Re: WHy so long in new Oracle versions

    Thanks, it is very fast, and I will use this solution

    but it does'nt explain the diff between the two queries

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: WHy so long in new Oracle versions

    Sorry, I don't know why they should be different.

Posting Permissions

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