-
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.
-
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
-
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
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|