DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sql query help

  1. #1
    Join Date
    Dec 2002
    Posts
    28

    sql query help

    Please can someone advise the select query to perform the following:-

    How do I select index_name,table_name,column_name from all_ind_columns and where the tables listed in table_name are not empty.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Assuming you analyze your tables on a regular basis, this is what you are asking for, but I don't see any practical purpose for it:
    Code:
    select ic.index_owner, ic.index_name, ic.table_owner, ic.table_name, ic.column_name
    from dba_ind_columns ic, dba_tables t
    where t.table_name = ic.table_name
    and t.owner = ic.table_owner
    and t.num_rows = 0
    /
    INDEX_OWNER  INDEX_NAME   TABLE_OWNER  TABLE_NAME   COLUMN_NAME
    ------------ ------------ ------------ ------------ ------------
    SYSTEM       XYZ_PK       SYSTEM       XYZ          X
    
    SQL> desc xyz
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     X                                                  NUMBER(10)
     Y                                                  DATE
     Z                                                  VARCHAR2(20)
    Jeff Hunter

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by marist89
    ... but I don't see any practical purpose for it...
    How charmingly naive of you, Jeff.

    Obviously, the purpose is to identify the indexes that need rebuilding every week
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2002
    Posts
    28

    analze table

    I intend to run this query on customer databases. The above query is only valid if they compute statistics on tables (Some will, Some dont), If dont then will return 0 rows etc. Is there another method to perfrom the above including an on the fly check to see if tables are not empty.

    Thanks D

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    Declare
    l_tab_has_rows boolean;
    begin
    for user_tab in (select table_name from user_tables)
    loop
       execute immediate 'select count(*) from '||user_tab.table_name||' where rownum = 1' Into l_tab_has_rows;
       if l_tab_has_rows
       then
          dbms_output.put_line(RPad(user_tab.table_name,31)||': Y');
       else
          dbms_output.put_line(RPad(user_tab.table_name,31)||': N');
       end if;
    end loop;
    end;
    /
    Just substitute whatever you want to do to the empty tables in the appropriate point.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width