Re: URGENT! How many rows will be returned?

In article <7um7aj$c86$1_at_nnrp1.deja.com>, Sue Han <sue_han_at_my-deja.com> wrote:
>Hi,
>
>How can I know how many rows will be returned from the query
>
>SELECT *
>FROM table1 t1, table2 t2
>where t1.column1 = t2.cloumn2;
>
>I know it can be done by
>
>SELECT count(*)
>FROM table1 t1, table2 t2
>where t1.column1 = t2.cloumn2;
>
>But I don't want to touch the actual data to get the information. Is
>there any statistics about that, and where is it stored? How can I get
>it by program?
>
>I am wondering how the optimizer works it out.
>
>Thanks in advance.

The optimizer look in the data dictionary for information generated during the last analyze. The row count can been seen in sys.dba_tables (or user_tables), but its value is probably not current.

You really can't count the rows in a table without touching the data. If the table has an indexed column, you can count on that column to force an index scan instead of a table scan:

Assume: There is an index named MYINDEX on the "not null" column

               MYCOLUMN in the table MYTABLE. Then the query might look like:

 SQL> select /*+ index (MYTABLE MYINDEX) +*/ count(MYCOLUMN) from MYTABLE;

• OraSaurus --
• Remove "not_" to reply... -- NOTICE TO BULK EMAILERS: Pursuant to US Code, Title 47, Chapter 5, Subchapter II, 227, any and all nonsolicited commercial E-mail sent to this address is subject to a download and archival fee in the amount of $500 US. E-mailing denotes acceptance of these terms.