Harvinder The predicate doesn't allow indexed access. And remember ALL_TABLES is a (complex) view. The name of the owner is stored in USER$, the name of the object in OBJ$. So the CBO (in effect) builds the entire ALL_TABLES view and trawls through it matching the predicate. Rewrite the predicate as: WHERE OWNER = :b1 AND TABLE_NAME = :b2 Now you're giving the CBO the chance to decide whether to drive off OWNER or off TABLE_NAME HTH Regards Nigel ----- Original Message ---- From: Harvinder Singh <Harvinder.Singh@xxxxxxxxxxxxx> To: oracle-l <oracle-l@xxxxxxxxxxxxx> Sent: Wednesday, May 9, 2007 9:17:52 PM Subject: Query on all_tables taking long time Hi, We have a function that checks if the table exists in current schema if specified without the schema name or all_tables if we specify the table name as object_name.table_name and it looks like query against all_tables is running very slow. Following is the output from the tkprof and it is not clear why optimizer is using 18 sec for cpu and doing 134k reads. This is 10.2.0.1 on RHAT ENT 4 SELECT COUNT(1) FROM ALL_TABLES WHERE OWNER || '.' || TABLE_NAME = UPPER(:B1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 158 0.02 0.02 0 0 0 0 Execute 158 0.04 0.03 0 0 0 0 Fetch 158 17.99 17.88 0 134540 0 158 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 474 18.06 17.93 0 134540 0 158 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 172 (recursive depth: 1) Thanks --Harvinder -- //www.freelists.org/webpage/oracle-l