Hi all, I'm running a simple select by joining two tables.
I get an output of about 1.3 million rows:

SQL> SELECT count(*) from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF
N WHERE N.OBJ_ID=O.OBJ_ID;

COUNT(*)
----------
1322139

When I run the select query as follows:

SELECT UPPER(OBJ_NAME), N.OBJ_IP_ADDR1 ,
N.OBJ_IP_ADDR2 ,N.OBJ_IP_ADDR, N.OBJ_IP_ADDR4
from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF N
WHERE N.OBJ_ID=O.OBJ_ID

it takes almost three hours to run. The server is only hosting the
data server and the CPU usage is low.

Any pointers in helping me resolve this issue are appreciated.
Thanks in advance,
Sashi

"Sashi" <smalladi (AT) gmail (DOT) com> wrote


explain plan for
SELECT UPPER(OBJ_NAME), N.OBJ_IP_ADDR1 ,
N.OBJ_IP_ADDR2 ,N.OBJ_IP_ADDR, N.OBJ_IP_ADDR4
from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF N
WHERE N.OBJ_ID=O.OBJ_ID;

select * from table(dbms_xplan.display)

"Sashi" <smalladi (AT) gmail (DOT) com> wrote


explain plan for
SELECT UPPER(OBJ_NAME), N.OBJ_IP_ADDR1 ,
N.OBJ_IP_ADDR2 ,N.OBJ_IP_ADDR, N.OBJ_IP_ADDR4
from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF N
WHERE N.OBJ_ID=O.OBJ_ID;

select * from table(dbms_xplan.display)

"Sashi" <smalladi (AT) gmail (DOT) com> wrote


explain plan for
SELECT UPPER(OBJ_NAME), N.OBJ_IP_ADDR1 ,
N.OBJ_IP_ADDR2 ,N.OBJ_IP_ADDR, N.OBJ_IP_ADDR4
from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF N
WHERE N.OBJ_ID=O.OBJ_ID;

select * from table(dbms_xplan.display)

"Sashi" <smalladi (AT) gmail (DOT) com> wrote


explain plan for
SELECT UPPER(OBJ_NAME), N.OBJ_IP_ADDR1 ,
N.OBJ_IP_ADDR2 ,N.OBJ_IP_ADDR, N.OBJ_IP_ADDR4
from QIPADMIN.OBJ_NAME_PROF O, QIPADMIN.OBJ_PROF N
WHERE N.OBJ_ID=O.OBJ_ID;

select * from table(dbms_xplan.display)

On Feb 7, 10:00*am, "news.verizon.net" <kenned... (AT) verizon (DOT) net> wrote:
Sashi, are the statistics on these two tables current? Take a look at
the last_analyzed columns of dba_tables and if either table has not
been recently updated get the statistics updated.

Then rerun the explain plan and see if it changed.

Also for Oracle to not have to full table scan both tables then the
objid column would have to be the leading column of an index for at
least one of these tables. (Likely it is or should be the leading
column or only column of an index on both but at least one of the two
tables has to be full scanned to solve the query). It may still be
possible that full scans on both and a hash join would be better than
using an index but current statistics would be necessary for Oracle to
make the right choice.

If the explain plan changed re-test (run and measure response time).

HTH -- Mark D Powell --

On Feb 7, 10:00*am, "news.verizon.net" <kenned... (AT) verizon (DOT) net> wrote:
Sashi, are the statistics on these two tables current? Take a look at
the last_analyzed columns of dba_tables and if either table has not
been recently updated get the statistics updated.

Then rerun the explain plan and see if it changed.

Also for Oracle to not have to full table scan both tables then the
objid column would have to be the leading column of an index for at
least one of these tables. (Likely it is or should be the leading
column or only column of an index on both but at least one of the two
tables has to be full scanned to solve the query). It may still be
possible that full scans on both and a hash join would be better than
using an index but current statistics would be necessary for Oracle to
make the right choice.

If the explain plan changed re-test (run and measure response time).

HTH -- Mark D Powell --

On Feb 7, 10:00*am, "news.verizon.net" <kenned... (AT) verizon (DOT) net> wrote:
Sashi, are the statistics on these two tables current? Take a look at
the last_analyzed columns of dba_tables and if either table has not
been recently updated get the statistics updated.

Then rerun the explain plan and see if it changed.

Also for Oracle to not have to full table scan both tables then the
objid column would have to be the leading column of an index for at
least one of these tables. (Likely it is or should be the leading
column or only column of an index on both but at least one of the two
tables has to be full scanned to solve the query). It may still be
possible that full scans on both and a hash join would be better than
using an index but current statistics would be necessary for Oracle to
make the right choice.

If the explain plan changed re-test (run and measure response time).

HTH -- Mark D Powell --

On Feb 7, 10:00*am, "news.verizon.net" <kenned... (AT) verizon (DOT) net> wrote:
Sashi, are the statistics on these two tables current? Take a look at
the last_analyzed columns of dba_tables and if either table has not
been recently updated get the statistics updated.

Then rerun the explain plan and see if it changed.

Also for Oracle to not have to full table scan both tables then the
objid column would have to be the leading column of an index for at
least one of these tables. (Likely it is or should be the leading
column or only column of an index on both but at least one of the two
tables has to be full scanned to solve the query). It may still be
possible that full scans on both and a hash join would be better than
using an index but current statistics would be necessary for Oracle to
make the right choice.

If the explain plan changed re-test (run and measure response time).

HTH -- Mark D Powell --