Re: Strange Oracle Optimization

kin_ng5_at_yahoo.com (Kin Ng) wrote i
.
> Until my DBA fixed the Explain Plan problem (getting Plan table not
> setup error), I can tell you that when I changed the result to a
> single row Count(*), I still get much faster result (12 seconds vs 43
> seconds).

Of course!!!

The select count(*) does _not_ have to read entire physical rows (especially when it can use something like a bitmap index instead). It does_not_ have to built a (big) cursor in memory containing all the rows selected (the resulting cursor contains a single row and column). It does _not_ have to return the physical rows across the network to your application.

It takes me just under 3 seconds to run a 'SELECT count(*) FROM table' where the result is 77.8 million rows.

Changing that into a 'SELECT * FROM table' will mean that I want to pull 14+ GB worth of data across to the client. That will _not_ happen in 3 hours, never mind the 3 second response I got from the count(*).

BTW, you do not need the DBA to set up a plan table for you. You can create that table inside your schema and the EXPLAIN PLAN statement will use you schema (assuming it is rum from inside your schema).

The script to create it sits in ORACLE_HOME/rdbms/admin and is called UTLPLAN.SQL if I'm not mistaken.

--
Billy