how may I force a full table scan for all tables in Oracle database

jjoz
jjoz used Ask the Experts™
on
We know we can use FULL hint to instruct CBO to do a full table scan, but CBO may disregard this request when the query can be done by accessing index file instead of data file.

Are there any trick we can exploit to force Oracle to do a full table scan? I need do this to do gather system level performance stats.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
delete the indexes, do your testing, then recreate the index

Author

Commented:
Thanks for your prompt response. It's not a feasible solution for my case though. I got hundreds of tables and indexes ... .

My query is something like     " 'select count(*) from ' || v_table ;" of which the v_table is dynamically generated.
there is a NO_INDEX hint as well.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
yes, but no_index(table_name, index_name) needs I know index name first and the tricky thing is that some table might have multiple indexes.
http://www.mcs.csueastbay.edu/support/oracle/doc/10.2/server.102/b14200/sql_elements006.htm#BABHJBIB

If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This behavior is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.

Naveen KumarProduction Manager / Application Support Manager

Commented:
try using both FULL hint and NO_INDEX hint as well and that should give the results you are expecting.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial